10 minutes to Mars DataFrame¶
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
Object creation¶
Creating a Series by passing a list of values, letting it create
a default integer index:
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:
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 -0.120158 -0.040134 -2.430967 0.507171
2013-01-02 1.003609 0.814796 1.091861 1.882128
2013-01-03 -0.366732 2.914654 1.027408 -0.778850
2013-01-04 1.569097 -0.831328 0.711279 -1.069198
2013-01-05 -0.902673 0.023938 0.847465 -0.152080
2013-01-06 2.531149 0.421086 0.511665 0.503603
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
Viewing data¶
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 -0.120158 -0.040134 -2.430967 0.507171
2013-01-02 1.003609 0.814796 1.091861 1.882128
2013-01-03 -0.366732 2.914654 1.027408 -0.778850
2013-01-04 1.569097 -0.831328 0.711279 -1.069198
2013-01-05 -0.902673 0.023938 0.847465 -0.152080
In [13]: df.tail(3).execute()
Out[13]:
A B C D
2013-01-04 1.569097 -0.831328 0.711279 -1.069198
2013-01-05 -0.902673 0.023938 0.847465 -0.152080
2013-01-06 2.531149 0.421086 0.511665 0.503603
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.
For df, our DataFrame of all floating-point values,
DataFrame.to_tensor() is fast and doesn’t require copying data.
In [16]: df.to_tensor().execute()
Out[16]:
array([[-0.12015799, -0.04013391, -2.43096663, 0.50717122],
[ 1.00360941, 0.81479583, 1.09186133, 1.88212826],
[-0.36673249, 2.9146545 , 1.02740799, -0.77884954],
[ 1.56909745, -0.83132794, 0.7112787 , -1.06919811],
[-0.90267342, 0.02393847, 0.847465 , -0.15207997],
[ 2.53114908, 0.42108618, 0.51166509, 0.50360284]])
For df2, the DataFrame with multiple dtypes,
DataFrame.to_tensor() is relatively expensive.
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:
In [18]: df.describe().execute()
Out[18]:
A B C D
count 6.000000 6.000000 6.000000 6.000000
mean 0.619049 0.550502 0.293119 0.148796
std 1.306938 1.281514 1.351128 1.067082
min -0.902673 -0.831328 -2.430967 -1.069198
25% -0.305089 -0.024116 0.561568 -0.622157
50% 0.441726 0.222512 0.779372 0.175761
75% 1.427725 0.716368 0.982422 0.506279
max 2.531149 2.914654 1.091861 1.882128
Sorting by an axis:
In [19]: df.sort_index(axis=1, ascending=False).execute()
Out[19]:
D C B A
2013-01-01 0.507171 -2.430967 -0.040134 -0.120158
2013-01-02 1.882128 1.091861 0.814796 1.003609
2013-01-03 -0.778850 1.027408 2.914654 -0.366732
2013-01-04 -1.069198 0.711279 -0.831328 1.569097
2013-01-05 -0.152080 0.847465 0.023938 -0.902673
2013-01-06 0.503603 0.511665 0.421086 2.531149
Sorting by values:
In [20]: df.sort_values(by='B').execute()
Out[20]:
A B C D
2013-01-04 1.569097 -0.831328 0.711279 -1.069198
2013-01-01 -0.120158 -0.040134 -2.430967 0.507171
2013-01-05 -0.902673 0.023938 0.847465 -0.152080
2013-01-06 2.531149 0.421086 0.511665 0.503603
2013-01-02 1.003609 0.814796 1.091861 1.882128
2013-01-03 -0.366732 2.914654 1.027408 -0.778850
Selection¶
Note
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.
Getting¶
Selecting a single column, which yields a Series,
equivalent to df.A:
In [21]: df['A'].execute()
Out[21]:
2013-01-01 -0.120158
2013-01-02 1.003609
2013-01-03 -0.366732
2013-01-04 1.569097
2013-01-05 -0.902673
2013-01-06 2.531149
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 -0.120158 -0.040134 -2.430967 0.507171
2013-01-02 1.003609 0.814796 1.091861 1.882128
2013-01-03 -0.366732 2.914654 1.027408 -0.778850
In [23]: df['20130102':'20130104'].execute()
Out[23]:
A B C D
2013-01-02 1.003609 0.814796 1.091861 1.882128
2013-01-03 -0.366732 2.914654 1.027408 -0.778850
2013-01-04 1.569097 -0.831328 0.711279 -1.069198
Selection by label¶
For getting a cross section using a label:
In [24]: df.loc['20130101'].execute()
Out[24]:
A -0.120158
B -0.040134
C -2.430967
D 0.507171
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 -0.120158 -0.040134
2013-01-02 1.003609 0.814796
2013-01-03 -0.366732 2.914654
2013-01-04 1.569097 -0.831328
2013-01-05 -0.902673 0.023938
2013-01-06 2.531149 0.421086
Showing label slicing, both endpoints are included:
In [26]: df.loc['20130102':'20130104', ['A', 'B']].execute()
Out[26]:
A B
2013-01-02 1.003609 0.814796
2013-01-03 -0.366732 2.914654
2013-01-04 1.569097 -0.831328
Reduction in the dimensions of the returned object:
In [27]: df.loc['20130102', ['A', 'B']].execute()
Out[27]:
A 1.003609
B 0.814796
Name: 2013-01-02 00:00:00, dtype: float64
For getting a scalar value:
In [28]: df.loc['20130101', 'A'].execute()
Out[28]: -0.12015799114003474
For getting fast access to a scalar (equivalent to the prior method):
In [29]: df.at['20130101', 'A'].execute()
Out[29]: -0.12015799114003474
Selection by position¶
Select via the position of the passed integers:
In [30]: df.iloc[3].execute()
Out[30]:
A 1.569097
B -0.831328
C 0.711279
D -1.069198
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 1.569097 -0.831328
2013-01-05 -0.902673 0.023938
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 1.003609 1.091861
2013-01-03 -0.366732 1.027408
2013-01-05 -0.902673 0.847465
For slicing rows explicitly:
In [33]: df.iloc[1:3, :].execute()
Out[33]:
A B C D
2013-01-02 1.003609 0.814796 1.091861 1.882128
2013-01-03 -0.366732 2.914654 1.027408 -0.778850
For slicing columns explicitly:
In [34]: df.iloc[:, 1:3].execute()
Out[34]:
B C
2013-01-01 -0.040134 -2.430967
2013-01-02 0.814796 1.091861
2013-01-03 2.914654 1.027408
2013-01-04 -0.831328 0.711279
2013-01-05 0.023938 0.847465
2013-01-06 0.421086 0.511665
For getting a value explicitly:
In [35]: df.iloc[1, 1].execute()
Out[35]: 0.8147958293978422
For getting fast access to a scalar (equivalent to the prior method):
In [36]: df.iat[1, 1].execute()
Out[36]: 0.8147958293978422
Boolean indexing¶
Using a single column’s values to select data.
In [37]: df[df['A'] > 0].execute()
Out[37]:
A B C D
2013-01-02 1.003609 0.814796 1.091861 1.882128
2013-01-04 1.569097 -0.831328 0.711279 -1.069198
2013-01-06 2.531149 0.421086 0.511665 0.503603
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 NaN NaN NaN 0.507171
2013-01-02 1.003609 0.814796 1.091861 1.882128
2013-01-03 NaN 2.914654 1.027408 NaN
2013-01-04 1.569097 NaN 0.711279 NaN
2013-01-05 NaN 0.023938 0.847465 NaN
2013-01-06 2.531149 0.421086 0.511665 0.503603
Operations¶
Stats¶
Operations in general exclude missing data.
Performing a descriptive statistic:
In [39]: df.mean().execute()
Out[39]:
A 0.619049
B 0.550502
C 0.293119
D 0.148796
dtype: float64
Same operation on the other axis:
In [40]: df.mean(1).execute()
Out[40]:
2013-01-01 -0.521022
2013-01-02 1.198099
2013-01-03 0.699120
2013-01-04 0.094963
2013-01-05 -0.045837
2013-01-06 0.991876
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 -1.366732 1.914654 0.027408 -1.778850
2013-01-04 -1.430903 -3.831328 -2.288721 -4.069198
2013-01-05 -5.902673 -4.976062 -4.152535 -5.152080
2013-01-06 NaN NaN NaN NaN
Apply¶
Applying functions to the data:
In [44]: df.apply(lambda x: x.max() - x.min()).execute()
Out[44]:
A 3.433822
B 3.745982
C 3.522828
D 2.951326
dtype: float64
String Methods¶
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
Merge¶
Concat¶
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():
In [47]: df = md.DataFrame(mt.random.randn(10, 4))
In [48]: df.execute()
Out[48]:
0 1 2 3
0 0.626268 0.454423 0.699246 -0.182832
1 0.679943 1.012039 -0.824650 0.603742
2 1.149038 -2.003584 -1.417892 0.648638
3 1.824861 0.640595 -2.686665 -1.504616
4 1.266308 -0.554611 0.237674 0.102814
5 1.349196 -0.048248 0.460777 -0.883182
6 -1.881278 0.396064 1.771801 -0.446482
7 1.158092 -1.145694 -0.599761 -1.395103
8 -0.342691 0.238440 0.067866 -1.292253
9 -0.994050 -0.160303 0.536953 0.220982
# 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.626268 0.454423 0.699246 -0.182832
1 0.679943 1.012039 -0.824650 0.603742
2 1.149038 -2.003584 -1.417892 0.648638
3 1.824861 0.640595 -2.686665 -1.504616
4 1.266308 -0.554611 0.237674 0.102814
5 1.349196 -0.048248 0.460777 -0.883182
6 -1.881278 0.396064 1.771801 -0.446482
7 1.158092 -1.145694 -0.599761 -1.395103
8 -0.342691 0.238440 0.067866 -1.292253
9 -0.994050 -0.160303 0.536953 0.220982
Join¶
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
Grouping¶
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
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.610148 0.175453
1 bar one 3.110999 -0.049308
2 foo two -1.274660 1.545058
3 bar three 1.637160 -0.179529
4 foo two 1.738358 0.094878
5 bar two -0.965869 1.375800
6 foo one -1.072098 -1.026966
7 foo three 0.367772 0.006146
Grouping and then applying the sum() function to the resulting
groups.
In [63]: df.groupby('A').sum().execute()
Out[63]:
C D
A
bar 3.78229 1.146964
foo 0.36952 0.794569
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.461950 -0.851513
two 0.463699 1.639936
three 0.367772 0.006146
bar one 3.110999 -0.049308
two -0.965869 1.375800
three 1.637160 -0.179529
Plotting¶
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:
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 0x7f21fe613f50>
Getting data in/out¶
CSV¶
In [75]: df.to_csv('foo.csv').execute()
Out[75]:
Empty DataFrame
Columns: []
Index: []
In [76]: md.read_csv('foo.csv').execute()
Out[76]:
Unnamed: 0 A B C D
0 2000-01-01 0.246690 1.152843 0.234475 -0.889559
1 2000-01-02 -0.941188 1.111507 0.109133 -0.315961
2 2000-01-03 -0.617940 -0.779686 -0.881437 0.040979
3 2000-01-04 1.309464 0.728078 -0.858242 -0.543386
4 2000-01-05 2.128311 1.187034 -0.564888 -0.892389
.. ... ... ... ... ...
995 2002-09-22 -66.341967 20.184272 17.355185 -18.027189
996 2002-09-23 -67.497541 18.068704 17.983740 -16.897568
997 2002-09-24 -66.083148 17.323924 17.033433 -18.309645
998 2002-09-25 -66.039451 16.938180 17.921725 -17.824954
999 2002-09-26 -66.191765 15.703785 16.922038 -19.037078
[1000 rows x 5 columns]