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.043755 2.309170 0.324749 1.144729 2013-01-02 0.500592 -0.370719 1.094474 -0.401957 2013-01-03 1.080591 -0.345569 0.437794 -0.557006 2013-01-04 -2.540776 -0.056718 -0.200216 0.239328 2013-01-05 -0.471682 -0.037104 -1.783580 0.228240 2013-01-06 0.915979 -0.695047 0.252153 1.100600
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.043755 2.309170 0.324749 1.144729 2013-01-02 0.500592 -0.370719 1.094474 -0.401957 2013-01-03 1.080591 -0.345569 0.437794 -0.557006 2013-01-04 -2.540776 -0.056718 -0.200216 0.239328 2013-01-05 -0.471682 -0.037104 -1.783580 0.228240 In [13]: df.tail(3).execute() Out[13]: A B C D 2013-01-04 -2.540776 -0.056718 -0.200216 0.239328 2013-01-05 -0.471682 -0.037104 -1.783580 0.228240 2013-01-06 0.915979 -0.695047 0.252153 1.100600
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.04375499, 2.30916957, 0.32474853, 1.14472944], [ 0.50059249, -0.37071868, 1.09447436, -0.40195708], [ 1.08059091, -0.34556868, 0.4377943 , -0.55700621], [-2.54077626, -0.05671761, -0.20021623, 0.23932794], [-0.47168183, -0.0371038 , -1.7835804 , 0.22823989], [ 0.91597909, -0.6950472 , 0.25215328, 1.1005995 ]])
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.088077 0.134002 0.020896 0.292322 std 1.412671 1.092617 0.977467 0.719751 min -2.540776 -0.695047 -1.783580 -0.557006 25% -0.228613 -0.364431 -0.087124 -0.244408 50% 0.708286 -0.201143 0.288451 0.233784 75% 1.011811 -0.042007 0.409533 0.885282 max 1.080591 2.309170 1.094474 1.144729
Sorting by an axis:
In [19]: df.sort_index(axis=1, ascending=False).execute() Out[19]: D C B A 2013-01-01 1.144729 0.324749 2.309170 1.043755 2013-01-02 -0.401957 1.094474 -0.370719 0.500592 2013-01-03 -0.557006 0.437794 -0.345569 1.080591 2013-01-04 0.239328 -0.200216 -0.056718 -2.540776 2013-01-05 0.228240 -1.783580 -0.037104 -0.471682 2013-01-06 1.100600 0.252153 -0.695047 0.915979
Sorting by values:
In [20]: df.sort_values(by='B').execute() Out[20]: A B C D 2013-01-06 0.915979 -0.695047 0.252153 1.100600 2013-01-02 0.500592 -0.370719 1.094474 -0.401957 2013-01-03 1.080591 -0.345569 0.437794 -0.557006 2013-01-04 -2.540776 -0.056718 -0.200216 0.239328 2013-01-05 -0.471682 -0.037104 -1.783580 0.228240 2013-01-01 1.043755 2.309170 0.324749 1.144729
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.043755 2013-01-02 0.500592 2013-01-03 1.080591 2013-01-04 -2.540776 2013-01-05 -0.471682 2013-01-06 0.915979 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.043755 2.309170 0.324749 1.144729 2013-01-02 0.500592 -0.370719 1.094474 -0.401957 2013-01-03 1.080591 -0.345569 0.437794 -0.557006 In [23]: df['20130102':'20130104'].execute() Out[23]: A B C D 2013-01-02 0.500592 -0.370719 1.094474 -0.401957 2013-01-03 1.080591 -0.345569 0.437794 -0.557006 2013-01-04 -2.540776 -0.056718 -0.200216 0.239328
For getting a cross section using a label:
In [24]: df.loc['20130101'].execute() Out[24]: A 1.043755 B 2.309170 C 0.324749 D 1.144729 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.043755 2.309170 2013-01-02 0.500592 -0.370719 2013-01-03 1.080591 -0.345569 2013-01-04 -2.540776 -0.056718 2013-01-05 -0.471682 -0.037104 2013-01-06 0.915979 -0.695047
Showing label slicing, both endpoints are included:
In [26]: df.loc['20130102':'20130104', ['A', 'B']].execute() Out[26]: A B 2013-01-02 0.500592 -0.370719 2013-01-03 1.080591 -0.345569 2013-01-04 -2.540776 -0.056718
Reduction in the dimensions of the returned object:
In [27]: df.loc['20130102', ['A', 'B']].execute() Out[27]: A 0.500592 B -0.370719 Name: 2013-01-02 00:00:00, dtype: float64
For getting a scalar value:
In [28]: df.loc['20130101', 'A'].execute() Out[28]: 1.0437549859156983
For getting fast access to a scalar (equivalent to the prior method):
In [29]: df.at['20130101', 'A'].execute() Out[29]: 1.0437549859156983
Select via the position of the passed integers:
In [30]: df.iloc[3].execute() Out[30]: A -2.540776 B -0.056718 C -0.200216 D 0.239328 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 -2.540776 -0.056718 2013-01-05 -0.471682 -0.037104
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.500592 1.094474 2013-01-03 1.080591 0.437794 2013-01-05 -0.471682 -1.783580
For slicing rows explicitly:
In [33]: df.iloc[1:3, :].execute() Out[33]: A B C D 2013-01-02 0.500592 -0.370719 1.094474 -0.401957 2013-01-03 1.080591 -0.345569 0.437794 -0.557006
For slicing columns explicitly:
In [34]: df.iloc[:, 1:3].execute() Out[34]: B C 2013-01-01 2.309170 0.324749 2013-01-02 -0.370719 1.094474 2013-01-03 -0.345569 0.437794 2013-01-04 -0.056718 -0.200216 2013-01-05 -0.037104 -1.783580 2013-01-06 -0.695047 0.252153
For getting a value explicitly:
In [35]: df.iloc[1, 1].execute() Out[35]: -0.3707186767080351
In [36]: df.iat[1, 1].execute() Out[36]: -0.3707186767080351
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.043755 2.309170 0.324749 1.144729 2013-01-02 0.500592 -0.370719 1.094474 -0.401957 2013-01-03 1.080591 -0.345569 0.437794 -0.557006 2013-01-06 0.915979 -0.695047 0.252153 1.100600
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.043755 2.30917 0.324749 1.144729 2013-01-02 0.500592 NaN 1.094474 NaN 2013-01-03 1.080591 NaN 0.437794 NaN 2013-01-04 NaN NaN NaN 0.239328 2013-01-05 NaN NaN NaN 0.228240 2013-01-06 0.915979 NaN 0.252153 1.100600
Operations in general exclude missing data.
Performing a descriptive statistic:
In [39]: df.mean().execute() Out[39]: A 0.088077 B 0.134002 C 0.020896 D 0.292322 dtype: float64
Same operation on the other axis:
In [40]: df.mean(1).execute() Out[40]: 2013-01-01 1.205601 2013-01-02 0.205598 2013-01-03 0.153953 2013-01-04 -0.639596 2013-01-05 -0.516032 2013-01-06 0.393421 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.080591 -1.345569 -0.562206 -1.557006 2013-01-04 -5.540776 -3.056718 -3.200216 -2.760672 2013-01-05 -5.471682 -5.037104 -6.783580 -4.771760 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 3.621367 B 3.004217 C 2.878055 D 1.701736 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 -0.717758 0.810613 -1.500468 -1.461232 1 -0.794482 0.704914 -0.569860 -0.406238 2 -0.701629 0.881165 0.590703 -0.113939 3 1.396237 -0.607225 0.541873 -0.306653 4 1.121766 -0.747996 -0.412997 -1.707973 5 1.790304 -0.141414 -0.401037 0.825045 6 0.913342 0.274045 0.844454 -0.130757 7 -1.259749 2.112436 0.347796 -0.586452 8 -0.636899 -1.147331 -0.048829 -0.678280 9 0.850827 0.776528 2.307371 -1.425310 # 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 -0.717758 0.810613 -1.500468 -1.461232 1 -0.794482 0.704914 -0.569860 -0.406238 2 -0.701629 0.881165 0.590703 -0.113939 3 1.396237 -0.607225 0.541873 -0.306653 4 1.121766 -0.747996 -0.412997 -1.707973 5 1.790304 -0.141414 -0.401037 0.825045 6 0.913342 0.274045 0.844454 -0.130757 7 -1.259749 2.112436 0.347796 -0.586452 8 -0.636899 -1.147331 -0.048829 -0.678280 9 0.850827 0.776528 2.307371 -1.425310
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 -0.235811 0.802962 1 bar one 0.232855 2.015546 2 foo two -0.164371 0.541638 3 bar three -1.499688 -0.171886 4 foo two -1.769741 1.394730 5 bar two 0.580503 0.993597 6 foo one -0.275785 -0.611533 7 foo three -0.417126 0.446499
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 -0.686331 2.837257 foo -2.862833 2.574296
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 -0.511596 0.191429 two -1.934111 1.936368 three -0.417126 0.446499 bar one 0.232855 2.015546 two 0.580503 0.993597 three -1.499688 -0.171886
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]: <AxesSubplot:>
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]: <AxesSubplot:> In [74]: plt.legend(loc='best') Out[74]: <matplotlib.legend.Legend at 0x7fa0df6b0810>
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.771673 -0.504049 0.620185 2.326599 1 2000-01-02 1.890644 0.002323 2.077618 1.615241 2 2000-01-03 0.137598 1.507905 2.214008 2.173546 3 2000-01-04 -1.317706 1.452865 2.185098 2.765024 4 2000-01-05 -1.052392 0.332555 2.136626 3.417819 .. ... ... ... ... ... 995 2002-09-22 -10.982176 78.410901 -16.634185 15.069612 996 2002-09-23 -10.634887 78.473156 -17.257791 15.683571 997 2002-09-24 -10.947374 78.524738 -16.746819 14.429695 998 2002-09-25 -10.079642 79.320475 -18.122158 14.725914 999 2002-09-26 -10.665226 81.167987 -17.386248 13.205201 [1000 rows x 5 columns]