This is a short introduction to Mars DataFrame which is originated from 10 minutes to pandas.
Customarily, we import as follows:
In [1]: import mars.tensor as mt In [2]: import mars.dataframe as md
Creating a Series by passing a list of values, letting it create a default integer index:
Series
In [3]: s = md.Series([1, 3, 5, mt.nan, 6, 8]) In [4]: s.execute() Out[4]: 0 1.0 1 3.0 2 5.0 3 NaN 4 6.0 5 8.0 dtype: float64
Creating a DataFrame by passing a Mars tensor, with a datetime index and labeled columns:
DataFrame
In [5]: dates = md.date_range('20130101', periods=6) In [6]: dates.execute() Out[6]: DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03', '2013-01-04', '2013-01-05', '2013-01-06'], dtype='datetime64[ns]', freq='D') In [7]: df = md.DataFrame(mt.random.randn(6, 4), index=dates, columns=list('ABCD')) In [8]: df.execute() Out[8]: A B C D 2013-01-01 1.931407 0.690645 -0.356246 -1.871824 2013-01-02 -0.715638 1.199600 0.890146 -0.353785 2013-01-03 0.120903 1.217065 0.779198 0.945284 2013-01-04 -0.424614 -1.611170 0.236505 0.784191 2013-01-05 0.565586 1.038352 -0.118088 -1.297175 2013-01-06 1.134355 -0.184953 -0.922892 1.322544
Creating a DataFrame by passing a dict of objects that can be converted to series-like.
In [9]: df2 = md.DataFrame({'A': 1., ...: 'B': md.Timestamp('20130102'), ...: 'C': md.Series(1, index=list(range(4)), dtype='float32'), ...: 'D': mt.array([3] * 4, dtype='int32'), ...: 'E': 'foo'}) ...: In [10]: df2.execute() Out[10]: A B C D E 0 1.0 2013-01-02 1.0 3 foo 1 1.0 2013-01-02 1.0 3 foo 2 1.0 2013-01-02 1.0 3 foo 3 1.0 2013-01-02 1.0 3 foo
The columns of the resulting DataFrame have different dtypes.
In [11]: df2.dtypes Out[11]: A float64 B datetime64[ns] C float32 D int32 E object dtype: object
Here is how to view the top and bottom rows of the frame:
In [12]: df.head().execute() Out[12]: A B C D 2013-01-01 1.931407 0.690645 -0.356246 -1.871824 2013-01-02 -0.715638 1.199600 0.890146 -0.353785 2013-01-03 0.120903 1.217065 0.779198 0.945284 2013-01-04 -0.424614 -1.611170 0.236505 0.784191 2013-01-05 0.565586 1.038352 -0.118088 -1.297175 In [13]: df.tail(3).execute() Out[13]: A B C D 2013-01-04 -0.424614 -1.611170 0.236505 0.784191 2013-01-05 0.565586 1.038352 -0.118088 -1.297175 2013-01-06 1.134355 -0.184953 -0.922892 1.322544
Display the index, columns:
In [14]: df.index.execute() Out[14]: DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03', '2013-01-04', '2013-01-05', '2013-01-06'], dtype='datetime64[ns]', freq='D') In [15]: df.columns.execute() Out[15]: Index(['A', 'B', 'C', 'D'], dtype='object')
DataFrame.to_tensor() gives a Mars tensor representation of the underlying data. Note that this can be an expensive operation when your DataFrame has columns with different data types, which comes down to a fundamental difference between DataFrame and tensor: tensors have one dtype for the entire tensor, while DataFrames have one dtype per column. When you call DataFrame.to_tensor(), Mars DataFrame will find the tensor dtype that can hold all of the dtypes in the DataFrame. This may end up being object, which requires casting every value to a Python object.
DataFrame.to_tensor()
object
For df, our DataFrame of all floating-point values, DataFrame.to_tensor() is fast and doesn’t require copying data.
df
In [16]: df.to_tensor().execute() Out[16]: array([[ 1.93140749, 0.69064467, -0.3562465 , -1.87182408], [-0.71563757, 1.19960013, 0.89014555, -0.35378519], [ 0.12090274, 1.2170653 , 0.77919784, 0.94528373], [-0.42461393, -1.61116961, 0.23650504, 0.78419148], [ 0.5655862 , 1.03835244, -0.11808809, -1.2971749 ], [ 1.13435483, -0.18495251, -0.9228917 , 1.32254439]])
For df2, the DataFrame with multiple dtypes, DataFrame.to_tensor() is relatively expensive.
df2
In [17]: df2.to_tensor().execute() Out[17]: array([[1.0, Timestamp('2013-01-02 00:00:00'), 1.0, 3, 'foo'], [1.0, Timestamp('2013-01-02 00:00:00'), 1.0, 3, 'foo'], [1.0, Timestamp('2013-01-02 00:00:00'), 1.0, 3, 'foo'], [1.0, Timestamp('2013-01-02 00:00:00'), 1.0, 3, 'foo']], dtype=object)
Note
DataFrame.to_tensor() does not include the index or column labels in the output.
describe() shows a quick statistic summary of your data:
describe()
In [18]: df.describe().execute() Out[18]: A B C D count 6.000000 6.000000 6.000000 6.000000 mean 0.435333 0.391590 0.084770 -0.078461 std 0.990651 1.112247 0.693529 1.306312 min -0.715638 -1.611170 -0.922892 -1.871824 25% -0.288235 0.033947 -0.296707 -1.061327 50% 0.343244 0.864499 0.059208 0.215203 75% 0.992163 1.159288 0.643525 0.905011 max 1.931407 1.217065 0.890146 1.322544
Sorting by an axis:
In [19]: df.sort_index(axis=1, ascending=False).execute() Out[19]: D C B A 2013-01-01 -1.871824 -0.356246 0.690645 1.931407 2013-01-02 -0.353785 0.890146 1.199600 -0.715638 2013-01-03 0.945284 0.779198 1.217065 0.120903 2013-01-04 0.784191 0.236505 -1.611170 -0.424614 2013-01-05 -1.297175 -0.118088 1.038352 0.565586 2013-01-06 1.322544 -0.922892 -0.184953 1.134355
Sorting by values:
In [20]: df.sort_values(by='B').execute() Out[20]: A B C D 2013-01-04 -0.424614 -1.611170 0.236505 0.784191 2013-01-06 1.134355 -0.184953 -0.922892 1.322544 2013-01-01 1.931407 0.690645 -0.356246 -1.871824 2013-01-05 0.565586 1.038352 -0.118088 -1.297175 2013-01-02 -0.715638 1.199600 0.890146 -0.353785 2013-01-03 0.120903 1.217065 0.779198 0.945284
While standard Python / Numpy expressions for selecting and setting are intuitive and come in handy for interactive work, for production code, we recommend the optimized DataFrame data access methods, .at, .iat, .loc and .iloc.
.at
.iat
.loc
.iloc
Selecting a single column, which yields a Series, equivalent to df.A:
df.A
In [21]: df['A'].execute() Out[21]: 2013-01-01 1.931407 2013-01-02 -0.715638 2013-01-03 0.120903 2013-01-04 -0.424614 2013-01-05 0.565586 2013-01-06 1.134355 Freq: D, Name: A, dtype: float64
Selecting via [], which slices the rows.
[]
In [22]: df[0:3].execute() Out[22]: A B C D 2013-01-01 1.931407 0.690645 -0.356246 -1.871824 2013-01-02 -0.715638 1.199600 0.890146 -0.353785 2013-01-03 0.120903 1.217065 0.779198 0.945284 In [23]: df['20130102':'20130104'].execute() Out[23]: A B C D 2013-01-02 -0.715638 1.199600 0.890146 -0.353785 2013-01-03 0.120903 1.217065 0.779198 0.945284 2013-01-04 -0.424614 -1.611170 0.236505 0.784191
For getting a cross section using a label:
In [24]: df.loc['20130101'].execute() Out[24]: A 1.931407 B 0.690645 C -0.356246 D -1.871824 Name: 2013-01-01 00:00:00, dtype: float64
Selecting on a multi-axis by label:
In [25]: df.loc[:, ['A', 'B']].execute() Out[25]: A B 2013-01-01 1.931407 0.690645 2013-01-02 -0.715638 1.199600 2013-01-03 0.120903 1.217065 2013-01-04 -0.424614 -1.611170 2013-01-05 0.565586 1.038352 2013-01-06 1.134355 -0.184953
Showing label slicing, both endpoints are included:
In [26]: df.loc['20130102':'20130104', ['A', 'B']].execute() Out[26]: A B 2013-01-02 -0.715638 1.199600 2013-01-03 0.120903 1.217065 2013-01-04 -0.424614 -1.611170
Reduction in the dimensions of the returned object:
In [27]: df.loc['20130102', ['A', 'B']].execute() Out[27]: A -0.715638 B 1.199600 Name: 2013-01-02 00:00:00, dtype: float64
For getting a scalar value:
In [28]: df.loc['20130101', 'A'].execute() Out[28]: 1.9314074910209367
For getting fast access to a scalar (equivalent to the prior method):
In [29]: df.at['20130101', 'A'].execute() Out[29]: 1.9314074910209367
Select via the position of the passed integers:
In [30]: df.iloc[3].execute() Out[30]: A -0.424614 B -1.611170 C 0.236505 D 0.784191 Name: 2013-01-04 00:00:00, dtype: float64
By integer slices, acting similar to numpy/python:
In [31]: df.iloc[3:5, 0:2].execute() Out[31]: A B 2013-01-04 -0.424614 -1.611170 2013-01-05 0.565586 1.038352
By lists of integer position locations, similar to the numpy/python style:
In [32]: df.iloc[[1, 2, 4], [0, 2]].execute() Out[32]: A C 2013-01-02 -0.715638 0.890146 2013-01-03 0.120903 0.779198 2013-01-05 0.565586 -0.118088
For slicing rows explicitly:
In [33]: df.iloc[1:3, :].execute() Out[33]: A B C D 2013-01-02 -0.715638 1.199600 0.890146 -0.353785 2013-01-03 0.120903 1.217065 0.779198 0.945284
For slicing columns explicitly:
In [34]: df.iloc[:, 1:3].execute() Out[34]: B C 2013-01-01 0.690645 -0.356246 2013-01-02 1.199600 0.890146 2013-01-03 1.217065 0.779198 2013-01-04 -1.611170 0.236505 2013-01-05 1.038352 -0.118088 2013-01-06 -0.184953 -0.922892
For getting a value explicitly:
In [35]: df.iloc[1, 1].execute() Out[35]: 1.1996001268725445
In [36]: df.iat[1, 1].execute() Out[36]: 1.1996001268725445
Using a single column’s values to select data.
In [37]: df[df['A'] > 0].execute() Out[37]: A B C D 2013-01-01 1.931407 0.690645 -0.356246 -1.871824 2013-01-03 0.120903 1.217065 0.779198 0.945284 2013-01-05 0.565586 1.038352 -0.118088 -1.297175 2013-01-06 1.134355 -0.184953 -0.922892 1.322544
Selecting values from a DataFrame where a boolean condition is met.
In [38]: df[df > 0].execute() Out[38]: A B C D 2013-01-01 1.931407 0.690645 NaN NaN 2013-01-02 NaN 1.199600 0.890146 NaN 2013-01-03 0.120903 1.217065 0.779198 0.945284 2013-01-04 NaN NaN 0.236505 0.784191 2013-01-05 0.565586 1.038352 NaN NaN 2013-01-06 1.134355 NaN NaN 1.322544
Operations in general exclude missing data.
Performing a descriptive statistic:
In [39]: df.mean().execute() Out[39]: A 0.435333 B 0.391590 C 0.084770 D -0.078461 dtype: float64
Same operation on the other axis:
In [40]: df.mean(1).execute() Out[40]: 2013-01-01 0.098495 2013-01-02 0.255081 2013-01-03 0.765612 2013-01-04 -0.253772 2013-01-05 0.047169 2013-01-06 0.337264 Freq: D, dtype: float64
Operating with objects that have different dimensionality and need alignment. In addition, Mars DataFrame automatically broadcasts along the specified dimension.
In [41]: s = md.Series([1, 3, 5, mt.nan, 6, 8], index=dates).shift(2) In [42]: s.execute() Out[42]: 2013-01-01 NaN 2013-01-02 NaN 2013-01-03 1.0 2013-01-04 3.0 2013-01-05 5.0 2013-01-06 NaN Freq: D, dtype: float64 In [43]: df.sub(s, axis='index').execute() Out[43]: A B C D 2013-01-01 NaN NaN NaN NaN 2013-01-02 NaN NaN NaN NaN 2013-01-03 -0.879097 0.217065 -0.220802 -0.054716 2013-01-04 -3.424614 -4.611170 -2.763495 -2.215809 2013-01-05 -4.434414 -3.961648 -5.118088 -6.297175 2013-01-06 NaN NaN NaN NaN
Applying functions to the data:
In [44]: df.apply(lambda x: x.max() - x.min()).execute() Out[44]: A 2.647045 B 2.828235 C 1.813037 D 3.194368 dtype: float64
Series is equipped with a set of string processing methods in the str attribute that make it easy to operate on each element of the array, as in the code snippet below. Note that pattern-matching in str generally uses regular expressions by default (and in some cases always uses them). See more at Vectorized String Methods.
In [45]: s = md.Series(['A', 'B', 'C', 'Aaba', 'Baca', mt.nan, 'CABA', 'dog', 'cat']) In [46]: s.str.lower().execute() Out[46]: 0 a 1 b 2 c 3 aaba 4 baca 5 NaN 6 caba 7 dog 8 cat dtype: object
Mars DataFrame provides various facilities for easily combining together Series and DataFrame objects with various kinds of set logic for the indexes and relational algebra functionality in the case of join / merge-type operations.
Concatenating DataFrame objects together with concat():
concat()
In [47]: df = md.DataFrame(mt.random.randn(10, 4)) In [48]: df.execute() Out[48]: 0 1 2 3 0 -1.249995 -0.377896 0.881990 0.428890 1 -1.131498 1.340801 -1.052318 0.201938 2 -1.200298 0.822057 0.136660 0.798779 3 0.627171 0.395426 -2.056140 1.325629 4 0.140962 0.480946 0.446364 0.638284 5 1.165917 -0.227402 0.468240 0.987829 6 0.679503 0.403699 0.176963 0.391028 7 2.011149 0.692399 1.290789 1.937144 8 1.911354 0.609072 -0.111463 0.134656 9 2.196636 0.242138 0.588012 -1.585527 # break it into pieces In [49]: pieces = [df[:3], df[3:7], df[7:]] In [50]: md.concat(pieces).execute() Out[50]: 0 1 2 3 0 -1.249995 -0.377896 0.881990 0.428890 1 -1.131498 1.340801 -1.052318 0.201938 2 -1.200298 0.822057 0.136660 0.798779 3 0.627171 0.395426 -2.056140 1.325629 4 0.140962 0.480946 0.446364 0.638284 5 1.165917 -0.227402 0.468240 0.987829 6 0.679503 0.403699 0.176963 0.391028 7 2.011149 0.692399 1.290789 1.937144 8 1.911354 0.609072 -0.111463 0.134656 9 2.196636 0.242138 0.588012 -1.585527
Adding a column to a DataFrame is relatively fast. However, adding a row requires a copy, and may be expensive. We recommend passing a pre-built list of records to the DataFrame constructor instead of building a DataFrame by iteratively appending records to it.
SQL style merges. See the Database style joining section.
In [51]: left = md.DataFrame({'key': ['foo', 'foo'], 'lval': [1, 2]}) In [52]: right = md.DataFrame({'key': ['foo', 'foo'], 'rval': [4, 5]}) In [53]: left.execute() Out[53]: key lval 0 foo 1 1 foo 2 In [54]: right.execute() Out[54]: key rval 0 foo 4 1 foo 5 In [55]: md.merge(left, right, on='key').execute() Out[55]: key lval rval 0 foo 1 4 1 foo 1 5 2 foo 2 4 3 foo 2 5
Another example that can be given is:
In [56]: left = md.DataFrame({'key': ['foo', 'bar'], 'lval': [1, 2]}) In [57]: right = md.DataFrame({'key': ['foo', 'bar'], 'rval': [4, 5]}) In [58]: left.execute() Out[58]: key lval 0 foo 1 1 bar 2 In [59]: right.execute() Out[59]: key rval 0 foo 4 1 bar 5 In [60]: md.merge(left, right, on='key').execute() Out[60]: key lval rval 0 foo 1 4 1 bar 2 5
By “group by” we are referring to a process involving one or more of the following steps:
Splitting the data into groups based on some criteria Applying a function to each group independently Combining the results into a data structure
Splitting the data into groups based on some criteria
Applying a function to each group independently
Combining the results into a data structure
In [61]: df = md.DataFrame({'A': ['foo', 'bar', 'foo', 'bar', ....: 'foo', 'bar', 'foo', 'foo'], ....: 'B': ['one', 'one', 'two', 'three', ....: 'two', 'two', 'one', 'three'], ....: 'C': mt.random.randn(8), ....: 'D': mt.random.randn(8)}) ....: In [62]: df.execute() Out[62]: A B C D 0 foo one 1.710894 1.546415 1 bar one 0.241495 -0.278982 2 foo two -0.016772 0.110815 3 bar three -1.186565 -1.336349 4 foo two -1.033151 1.333253 5 bar two -1.263034 0.594817 6 foo one 0.869332 -0.483507 7 foo three 2.206120 0.025061
Grouping and then applying the sum() function to the resulting groups.
sum()
In [63]: df.groupby('A').sum().execute() Out[63]: C D A bar -2.208104 -1.020514 foo 3.736423 2.532036
Grouping by multiple columns forms a hierarchical index, and again we can apply the sum function.
In [64]: df.groupby(['A', 'B']).sum().execute() Out[64]: C D A B foo one 2.580226 1.062908 two -1.049923 1.444068 three 2.206120 0.025061 bar one 0.241495 -0.278982 two -1.263034 0.594817 three -1.186565 -1.336349
We use the standard convention for referencing the matplotlib API:
In [65]: import matplotlib.pyplot as plt In [66]: plt.close('all')
In [67]: ts = md.Series(mt.random.randn(1000), ....: index=md.date_range('1/1/2000', periods=1000)) ....: In [68]: ts = ts.cumsum() In [69]: ts.plot() Out[69]: <matplotlib.axes._subplots.AxesSubplot at 0x7fc8e0633f98>
On a DataFrame, the plot() method is a convenience to plot all of the columns with labels:
plot()
In [70]: df = md.DataFrame(mt.random.randn(1000, 4), index=ts.index, ....: columns=['A', 'B', 'C', 'D']) ....: In [71]: df = df.cumsum() In [72]: plt.figure() Out[72]: <Figure size 640x480 with 0 Axes> In [73]: df.plot() Out[73]: <matplotlib.axes._subplots.AxesSubplot at 0x7fc8e011e128> In [74]: plt.legend(loc='best') Out[74]: <matplotlib.legend.Legend at 0x7fc8e00db320>
In [75]: df.to_csv('foo.csv').execute() Out[75]: Empty DataFrame Columns: [] Index: []
Reading from a csv file.
In [76]: md.read_csv('foo.csv').execute() Out[76]: Unnamed: 0 A B C D 0 2000-01-01 0.020635 -1.829055 0.804398 -0.362481 1 2000-01-02 0.119671 -3.021767 0.853802 -1.621981 2 2000-01-03 -1.828637 -2.215143 -0.609730 -0.799588 3 2000-01-04 -2.382725 -2.952165 -0.966116 -1.064462 4 2000-01-05 -3.046591 -2.646843 -1.700743 -1.218309 .. ... ... ... ... ... 995 2002-09-22 51.697014 12.498474 -0.093453 23.503771 996 2002-09-23 52.396735 12.992341 -1.932032 23.240207 997 2002-09-24 51.236871 14.046080 -1.572348 24.537271 998 2002-09-25 50.581787 13.398644 -2.347733 24.002623 999 2002-09-26 50.267412 12.670912 -2.124686 23.756498 [1000 rows x 5 columns]