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
In [2]: import mars.tensor as mt
In [3]: import mars.dataframe as md
Now create a new default session.
In [4]: mars.new_session()
Out[4]: <mars.deploy.oscar.session.SyncSession at 0x7f4b321440d0>
Object creation¶
Creating a Series by passing a list of values, letting it create
a default integer index:
In [5]: s = md.Series([1, 3, 5, mt.nan, 6, 8])
In [6]: s.execute()
Out[6]:
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 [7]: dates = md.date_range('20130101', periods=6)
In [8]: dates.execute()
Out[8]:
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 [9]: df = md.DataFrame(mt.random.randn(6, 4), index=dates, columns=list('ABCD'))
In [10]: df.execute()
Out[10]:
A B C D
2013-01-01 0.308116 0.068169 0.021408 -0.424766
2013-01-02 0.780138 -1.092194 -0.438759 -0.647873
2013-01-03 0.144402 0.626780 1.414595 -0.410170
2013-01-04 0.363341 0.507513 -1.109360 1.217660
2013-01-05 1.135997 0.467638 0.421051 0.250454
2013-01-06 -1.979694 -0.676802 -1.278337 0.544590
Creating a DataFrame by passing a dict of objects that can be converted to series-like.
In [11]: 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 [12]: df2.execute()
Out[12]:
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 [13]: df2.dtypes
Out[13]:
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 [14]: df.head().execute()
Out[14]:
A B C D
2013-01-01 0.308116 0.068169 0.021408 -0.424766
2013-01-02 0.780138 -1.092194 -0.438759 -0.647873
2013-01-03 0.144402 0.626780 1.414595 -0.410170
2013-01-04 0.363341 0.507513 -1.109360 1.217660
2013-01-05 1.135997 0.467638 0.421051 0.250454
In [15]: df.tail(3).execute()
Out[15]:
A B C D
2013-01-04 0.363341 0.507513 -1.109360 1.217660
2013-01-05 1.135997 0.467638 0.421051 0.250454
2013-01-06 -1.979694 -0.676802 -1.278337 0.544590
Display the index, columns:
In [16]: df.index.execute()
Out[16]:
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 [17]: df.columns.execute()
Out[17]: 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 [18]: df.to_tensor().execute()
Out[18]:
array([[ 0.30811565, 0.0681689 , 0.02140802, -0.42476583],
[ 0.78013817, -1.09219365, -0.43875932, -0.64787293],
[ 0.14440198, 0.62677977, 1.41459487, -0.41017037],
[ 0.36334113, 0.50751325, -1.10936047, 1.21766045],
[ 1.13599729, 0.46763783, 0.42105137, 0.2504538 ],
[-1.97969363, -0.6768018 , -1.27833727, 0.54459007]])
For df2, the DataFrame with multiple dtypes,
DataFrame.to_tensor() is relatively expensive.
In [19]: df2.to_tensor().execute()
Out[19]:
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 [20]: df.describe().execute()
Out[20]:
A B C D
count 6.000000 6.000000 6.000000 6.000000
mean 0.125383 -0.016483 -0.161567 0.088316
std 1.092865 0.710362 1.007922 0.716046
min -1.979694 -1.092194 -1.278337 -0.647873
25% 0.185330 -0.490559 -0.941710 -0.421117
50% 0.335728 0.267903 -0.208676 -0.079858
75% 0.675939 0.497544 0.321141 0.471056
max 1.135997 0.626780 1.414595 1.217660
Sorting by an axis:
In [21]: df.sort_index(axis=1, ascending=False).execute()
Out[21]:
D C B A
2013-01-01 -0.424766 0.021408 0.068169 0.308116
2013-01-02 -0.647873 -0.438759 -1.092194 0.780138
2013-01-03 -0.410170 1.414595 0.626780 0.144402
2013-01-04 1.217660 -1.109360 0.507513 0.363341
2013-01-05 0.250454 0.421051 0.467638 1.135997
2013-01-06 0.544590 -1.278337 -0.676802 -1.979694
Sorting by values:
In [22]: df.sort_values(by='B').execute()
Out[22]:
A B C D
2013-01-02 0.780138 -1.092194 -0.438759 -0.647873
2013-01-06 -1.979694 -0.676802 -1.278337 0.544590
2013-01-01 0.308116 0.068169 0.021408 -0.424766
2013-01-05 1.135997 0.467638 0.421051 0.250454
2013-01-04 0.363341 0.507513 -1.109360 1.217660
2013-01-03 0.144402 0.626780 1.414595 -0.410170
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 [23]: df['A'].execute()
Out[23]:
2013-01-01 0.308116
2013-01-02 0.780138
2013-01-03 0.144402
2013-01-04 0.363341
2013-01-05 1.135997
2013-01-06 -1.979694
Freq: D, Name: A, dtype: float64
Selecting via [], which slices the rows.
In [24]: df[0:3].execute()
Out[24]:
A B C D
2013-01-01 0.308116 0.068169 0.021408 -0.424766
2013-01-02 0.780138 -1.092194 -0.438759 -0.647873
2013-01-03 0.144402 0.626780 1.414595 -0.410170
In [25]: df['20130102':'20130104'].execute()
Out[25]:
A B C D
2013-01-02 0.780138 -1.092194 -0.438759 -0.647873
2013-01-03 0.144402 0.626780 1.414595 -0.410170
2013-01-04 0.363341 0.507513 -1.109360 1.217660
Selection by label¶
For getting a cross section using a label:
In [26]: df.loc['20130101'].execute()
Out[26]:
A 0.308116
B 0.068169
C 0.021408
D -0.424766
Name: 2013-01-01 00:00:00, dtype: float64
Selecting on a multi-axis by label:
In [27]: df.loc[:, ['A', 'B']].execute()
Out[27]:
A B
2013-01-01 0.308116 0.068169
2013-01-02 0.780138 -1.092194
2013-01-03 0.144402 0.626780
2013-01-04 0.363341 0.507513
2013-01-05 1.135997 0.467638
2013-01-06 -1.979694 -0.676802
Showing label slicing, both endpoints are included:
In [28]: df.loc['20130102':'20130104', ['A', 'B']].execute()
Out[28]:
A B
2013-01-02 0.780138 -1.092194
2013-01-03 0.144402 0.626780
2013-01-04 0.363341 0.507513
Reduction in the dimensions of the returned object:
In [29]: df.loc['20130102', ['A', 'B']].execute()
Out[29]:
A 0.780138
B -1.092194
Name: 2013-01-02 00:00:00, dtype: float64
For getting a scalar value:
In [30]: df.loc['20130101', 'A'].execute()
Out[30]: 0.30811564606784136
For getting fast access to a scalar (equivalent to the prior method):
In [31]: df.at['20130101', 'A'].execute()
Out[31]: 0.30811564606784136
Selection by position¶
Select via the position of the passed integers:
In [32]: df.iloc[3].execute()
Out[32]:
A 0.363341
B 0.507513
C -1.109360
D 1.217660
Name: 2013-01-04 00:00:00, dtype: float64
By integer slices, acting similar to numpy/python:
In [33]: df.iloc[3:5, 0:2].execute()
Out[33]:
A B
2013-01-04 0.363341 0.507513
2013-01-05 1.135997 0.467638
By lists of integer position locations, similar to the numpy/python style:
In [34]: df.iloc[[1, 2, 4], [0, 2]].execute()
Out[34]:
A C
2013-01-02 0.780138 -0.438759
2013-01-03 0.144402 1.414595
2013-01-05 1.135997 0.421051
For slicing rows explicitly:
In [35]: df.iloc[1:3, :].execute()
Out[35]:
A B C D
2013-01-02 0.780138 -1.092194 -0.438759 -0.647873
2013-01-03 0.144402 0.626780 1.414595 -0.410170
For slicing columns explicitly:
In [36]: df.iloc[:, 1:3].execute()
Out[36]:
B C
2013-01-01 0.068169 0.021408
2013-01-02 -1.092194 -0.438759
2013-01-03 0.626780 1.414595
2013-01-04 0.507513 -1.109360
2013-01-05 0.467638 0.421051
2013-01-06 -0.676802 -1.278337
For getting a value explicitly:
In [37]: df.iloc[1, 1].execute()
Out[37]: -1.0921936515677702
For getting fast access to a scalar (equivalent to the prior method):
In [38]: df.iat[1, 1].execute()
Out[38]: -1.0921936515677702
Boolean indexing¶
Using a single column’s values to select data.
In [39]: df[df['A'] > 0].execute()
Out[39]:
A B C D
2013-01-01 0.308116 0.068169 0.021408 -0.424766
2013-01-02 0.780138 -1.092194 -0.438759 -0.647873
2013-01-03 0.144402 0.626780 1.414595 -0.410170
2013-01-04 0.363341 0.507513 -1.109360 1.217660
2013-01-05 1.135997 0.467638 0.421051 0.250454
Selecting values from a DataFrame where a boolean condition is met.
In [40]: df[df > 0].execute()
Out[40]:
A B C D
2013-01-01 0.308116 0.068169 0.021408 NaN
2013-01-02 0.780138 NaN NaN NaN
2013-01-03 0.144402 0.626780 1.414595 NaN
2013-01-04 0.363341 0.507513 NaN 1.217660
2013-01-05 1.135997 0.467638 0.421051 0.250454
2013-01-06 NaN NaN NaN 0.544590
Operations¶
Stats¶
Operations in general exclude missing data.
Performing a descriptive statistic:
In [41]: df.mean().execute()
Out[41]:
A 0.125383
B -0.016483
C -0.161567
D 0.088316
dtype: float64
Same operation on the other axis:
In [42]: df.mean(1).execute()
Out[42]:
2013-01-01 -0.006768
2013-01-02 -0.349672
2013-01-03 0.443902
2013-01-04 0.244789
2013-01-05 0.568785
2013-01-06 -0.847561
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 [43]: s = md.Series([1, 3, 5, mt.nan, 6, 8], index=dates).shift(2)
In [44]: s.execute()
Out[44]:
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 [45]: df.sub(s, axis='index').execute()
Out[45]:
A B C D
2013-01-01 NaN NaN NaN NaN
2013-01-02 NaN NaN NaN NaN
2013-01-03 -0.855598 -0.373220 0.414595 -1.410170
2013-01-04 -2.636659 -2.492487 -4.109360 -1.782340
2013-01-05 -3.864003 -4.532362 -4.578949 -4.749546
2013-01-06 NaN NaN NaN NaN
Apply¶
Applying functions to the data:
In [46]: df.apply(lambda x: x.max() - x.min()).execute()
Out[46]:
A 3.115691
B 1.718973
C 2.692932
D 1.865533
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 [47]: s = md.Series(['A', 'B', 'C', 'Aaba', 'Baca', mt.nan, 'CABA', 'dog', 'cat'])
In [48]: s.str.lower().execute()
Out[48]:
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 [49]: df = md.DataFrame(mt.random.randn(10, 4))
In [50]: df.execute()
Out[50]:
0 1 2 3
0 1.110057 1.009094 0.219718 0.014843
1 -0.282451 -0.008944 1.244634 -0.468203
2 -0.609592 -0.362508 -1.244294 -0.458921
3 1.445054 -1.307466 0.280833 -0.696568
4 -0.809004 -0.169908 0.288524 -1.109452
5 2.215682 0.635755 -0.956003 2.047665
6 1.198108 0.065485 -0.011019 0.867449
7 0.972841 0.742011 -0.865246 1.047195
8 0.920429 1.162683 2.523458 -0.277906
9 1.215033 0.464214 -1.241130 0.280925
# break it into pieces
In [51]: pieces = [df[:3], df[3:7], df[7:]]
In [52]: md.concat(pieces).execute()
Out[52]:
0 1 2 3
0 1.110057 1.009094 0.219718 0.014843
1 -0.282451 -0.008944 1.244634 -0.468203
2 -0.609592 -0.362508 -1.244294 -0.458921
3 1.445054 -1.307466 0.280833 -0.696568
4 -0.809004 -0.169908 0.288524 -1.109452
5 2.215682 0.635755 -0.956003 2.047665
6 1.198108 0.065485 -0.011019 0.867449
7 0.972841 0.742011 -0.865246 1.047195
8 0.920429 1.162683 2.523458 -0.277906
9 1.215033 0.464214 -1.241130 0.280925
Join¶
SQL style merges. See the Database style joining section.
In [53]: left = md.DataFrame({'key': ['foo', 'foo'], 'lval': [1, 2]})
In [54]: right = md.DataFrame({'key': ['foo', 'foo'], 'rval': [4, 5]})
In [55]: left.execute()
Out[55]:
key lval
0 foo 1
1 foo 2
In [56]: right.execute()
Out[56]:
key rval
0 foo 4
1 foo 5
In [57]: md.merge(left, right, on='key').execute()
Out[57]:
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 [58]: left = md.DataFrame({'key': ['foo', 'bar'], 'lval': [1, 2]})
In [59]: right = md.DataFrame({'key': ['foo', 'bar'], 'rval': [4, 5]})
In [60]: left.execute()
Out[60]:
key lval
0 foo 1
1 bar 2
In [61]: right.execute()
Out[61]:
key rval
0 foo 4
1 bar 5
In [62]: md.merge(left, right, on='key').execute()
Out[62]:
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 [63]: 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 [64]: df.execute()
Out[64]:
A B C D
0 foo one 0.533526 -0.853642
1 bar one -1.119476 1.208151
2 foo two 1.454714 0.526749
3 bar three 0.119490 -0.463419
4 foo two -0.757796 1.510202
5 bar two -1.225713 0.495087
6 foo one -0.028903 0.650819
7 foo three 1.118328 1.324734
Grouping and then applying the sum() function to the resulting
groups.
In [65]: df.groupby('A').sum().execute()
Out[65]:
C D
A
bar -2.225699 1.239819
foo 2.319870 3.158862
Grouping by multiple columns forms a hierarchical index, and again we can apply the sum function.
In [66]: df.groupby(['A', 'B']).sum().execute()
Out[66]:
C D
A B
bar one -1.119476 1.208151
three 0.119490 -0.463419
two -1.225713 0.495087
foo one 0.504624 -0.202824
three 1.118328 1.324734
two 0.696918 2.036951
Plotting¶
We use the standard convention for referencing the matplotlib API:
In [67]: import matplotlib.pyplot as plt
In [68]: plt.close('all')
In [69]: ts = md.Series(mt.random.randn(1000),
....: index=md.date_range('1/1/2000', periods=1000))
....:
In [70]: ts = ts.cumsum()
In [71]: ts.plot()
Out[71]: <AxesSubplot:>
On a DataFrame, the plot() method is a convenience to plot all
of the columns with labels:
In [72]: df = md.DataFrame(mt.random.randn(1000, 4), index=ts.index,
....: columns=['A', 'B', 'C', 'D'])
....:
In [73]: df = df.cumsum()
In [74]: plt.figure()
Out[74]: <Figure size 640x480 with 0 Axes>
In [75]: df.plot()
Out[75]: <AxesSubplot:>
In [76]: plt.legend(loc='best')
Out[76]: <matplotlib.legend.Legend at 0x7f4b32fb4150>
Getting data in/out¶
CSV¶
In [77]: df.to_csv('foo.csv').execute()
Out[77]:
Empty DataFrame
Columns: []
Index: []
In [78]: md.read_csv('foo.csv').execute()
Out[78]:
Unnamed: 0 A B C D
0 2000-01-01 -0.981105 1.913908 -0.661294 -0.210964
1 2000-01-02 -1.035318 0.835594 -0.419938 0.703844
2 2000-01-03 -1.836223 1.421732 0.454346 0.673662
3 2000-01-04 -2.259643 0.002368 -1.707156 0.897979
4 2000-01-05 -1.235017 0.508093 -1.311825 1.932156
.. ... ... ... ... ...
995 2002-09-22 -14.576360 19.059307 27.982727 14.289766
996 2002-09-23 -13.955815 19.190226 27.564814 14.083096
997 2002-09-24 -14.602541 20.191390 29.156286 13.154644
998 2002-09-25 -15.063893 21.286490 28.108586 13.253978
999 2002-09-26 -15.097882 21.109083 28.416373 12.642112
[1000 rows x 5 columns]