接着上一期。
New Columns
Efficiently and dynamically creating new columns using applymap
In [51]: df = pd.DataFrame(
....: {'AAA' : [1,2,1,3], 'BBB' : [1,1,2,2], 'CCC' : [2,1,3,1]}); df
....:
Out[51]:
AAA BBB CCC
0 1 1 2
1 2 1 1
2 1 2 3
3 3 2 1
In [52]: source_cols = df.columns # or some subset would work too.
In [53]: new_cols = [str(x) + "_cat" for x in source_cols]
In [54]: categories = {1 : 'Alpha', 2 : 'Beta', 3 : 'Charlie' }
In [55]: df[new_cols] = df[source_cols].applymap(categories.get);df
Out[55]:
AAA BBB CCC AAA_cat BBB_cat CCC_cat
0 1 1 2 Alpha Alpha Beta
1 2 1 1 Beta Alpha Alpha
2 1 2 3 Alpha Beta Charlie
3 3 2 1 Charlie Beta Alpha
Keep other columns when using min() with groupby
In [56]: df = pd.DataFrame(
....: {'AAA' : [1,1,1,2,2,2,3,3], 'BBB' : [2,1,3,4,5,1,2,3]}); df
....:
Out[56]:
AAA BBB
0 1 2
1 1 1
2 1 3
3 2 4
4 2 5
5 2 1
6 3 2
7 3 3
Method 1 : idxmin() to get the index of the mins
In [57]: df.loc[df.groupby("AAA")["BBB"].idxmin()]
Out[57]:
AAA BBB
1 1 1
5 2 1
6 3 2
Method 2 : sort then take first of each
In [58]: df.sort_values(by="BBB").groupby("AAA", as_index=False).first()
Out[58]:
AAA BBB
0 1 1
1 2 1
2 3 2
Notice the same results, with the exception of the index.
MultiIndexing
The multindexing docs.
Creating a multi-index from a labeled frame
In [59]: df = pd.DataFrame({'row' : [0,1,2],
....: 'One_X' : [1.1,1.1,1.1],
....: 'One_Y' : [1.2,1.2,1.2],
....: 'Two_X' : [1.11,1.11,1.11],
....: 'Two_Y' : [1.22,1.22,1.22]}); df
....:
Out[59]:
One_X One_Y Two_X Two_Y row
0 1.1 1.2 1.11 1.22 0
1 1.1 1.2 1.11 1.22 1
2 1.1 1.2 1.11 1.22 2
# As Labelled Index
In [60]: df = df.set_index('row');df
Out[60]:
One_X One_Y Two_X Two_Y
row
0 1.1 1.2 1.11 1.22
1 1.1 1.2 1.11 1.22
2 1.1 1.2 1.11 1.22
# With Hierarchical Columns
In [61]: df.columns = pd.MultiIndex.from_tuples([tuple(c.split('_')) for c in df.columns]);df
Out[61]:
One Two
X Y X Y
row
0 1.1 1.2 1.11 1.22
1 1.1 1.2 1.11 1.22
2 1.1 1.2 1.11 1.22
# Now stack & Reset
In [62]: df = df.stack(0).reset_index(1);df
Out[62]:
level_1 X Y
row
0 One 1.10 1.20
0 Two 1.11 1.22
1 One 1.10 1.20
1 Two 1.11 1.22
2 One 1.10 1.20
2 Two 1.11 1.22
# And fix the labels (Notice the label 'level_1' got added automatically)
In [63]: df.columns = ['Sample','All_X','All_Y'];df
Out[63]:
Sample All_X All_Y
row
0 One 1.10 1.20
0 Two 1.11 1.22
1 One 1.10 1.20
1 Two 1.11 1.22
2 One 1.10 1.20
2 Two 1.11 1.22
Arithmetic
Performing arithmetic with a multi-index that needs broadcasting
In [64]: cols = pd.MultiIndex.from_tuples([ (x,y) for x in ['A','B','C'] for y in ['O','I']])
In [65]: df = pd.DataFrame(np.random.randn(2,6),index=['n','m'],columns=cols); df
Out[65]:
A B C
O I O I O I
n 1.920906 -0.388231 -2.314394 0.665508 0.402562 0.399555
m -1.765956 0.850423 0.388054 0.992312 0.744086 -0.739776
In [66]: df = df.div(df['C'],level=1); df
Out[66]:
A B C
O I O I O I
n 4.771702 -0.971660 -5.749162 1.665625 1.0 1.0
m -2.373321 -1.149568 0.521518 -1.341367 1.0 1.0
Slicing
In [67]: coords = [('AA','one'),('AA','six'),('BB','one'),('BB','two'),('BB','six')]
In [68]: index = pd.MultiIndex.from_tuples(coords)
In [69]: df = pd.DataFrame([11,22,33,44,55],index,['MyData']); df
Out[69]:
MyData
AA one 11
six 22
BB one 33
two 44
six 55
To take the cross section of the 1st level and 1st axis the index:
In [70]: df.xs('BB',level=0,axis=0) #Note : level and axis are optional, and default to zero
Out[70]:
MyData
one 33
two 44
six 55
...and now the 2nd level of the 1st axis.
In [71]: df.xs('six',level=1,axis=0)
Out[71]:
MyData
AA 22
BB 55
Slicing a multi-index with xs, method #2
In [72]: index = list(itertools.product(['Ada','Quinn','Violet'],['Comp','Math','Sci']))
In [73]: headr = list(itertools.product(['Exams','Labs'],['I','II']))
In [74]: indx = pd.MultiIndex.from_tuples(index,names=['Student','Course'])
In [75]: cols = pd.MultiIndex.from_tuples(headr) #Notice these are un-named
In [76]: data = [[70+x+y+(x*y)%3 for x in range(4)] for y in range(9)]
In [77]: df = pd.DataFrame(data,indx,cols); df
Out[77]:
Exams Labs
I II I II
Student Course
Ada Comp 70 71 72 73
Math 71 73 75 74
Sci 72 75 75 75
Quinn Comp 73 74 75 76
Math 74 76 78 77
Sci 75 78 78 78
Violet Comp 76 77 78 79
Math 77 79 81 80
Sci 78 81 81 81
In [78]: All = slice(None)
In [79]: df.loc['Violet']
Out[79]:
Exams Labs
I II I II
Course
Comp 76 77 78 79
Math 77 79 81 80
Sci 78 81 81 81
In [80]: df.loc[(All,'Math'),All]
Out[80]:
Exams Labs
I II I II
Student Course
Ada Math 71 73 75 74
Quinn Math 74 76 78 77
Violet Math 77 79 81 80
In [81]: df.loc[(slice('Ada','Quinn'),'Math'),All]
Out[81]:
Exams Labs
I II I II
Student Course
Ada Math 71 73 75 74
Quinn Math 74 76 78 77
In [82]: df.loc[(All,'Math'),('Exams')]
Out[82]:
I II
Student Course
Ada Math 71 73
Quinn Math 74 76
Violet Math 77 79
In [83]: df.loc[(All,'Math'),(All,'II')]
Out[83]:
Exams Labs
II II
Student Course
Ada Math 73 74
Quinn Math 76 77
Violet Math 79 80
Setting portions of a multi-index with xs
排序
Sort by specific column or an ordered list of columns, with a multi-index
In [84]: df.sort_values(by=('Labs', 'II'), ascending=False)
Out[84]:
Exams Labs
I II I II
Student Course
Violet Sci 78 81 81 81
Math 77 79 81 80
Comp 76 77 78 79
Quinn Sci 75 78 78 78
Math 74 76 78 77
Comp 73 74 75 76
Ada Sci 72 75 75 75
Math 71 73 75 74
Comp 70 71 72 73
Partial Selection, the need for sortedness;
Levels
Prepending a level to a multiindex
Flatten Hierarchical columns
Missing Data
The missing data docs.
Fill forward a reversed timeseries
In [85]: df = pd.DataFrame(np.random.randn(6,1), index=pd.date_range('2013-08-01', periods=6, freq='B'), columns=list('A'))
In [86]: df.ix[3,'A'] = np.nan
In [87]: df
Out[87]:
A
2013-08-01 -1.054874
2013-08-02 -0.179642
2013-08-05 0.639589
2013-08-06 NaN
2013-08-07 1.906684
2013-08-08 0.104050
In [88]: df.reindex(df.index[::-1]).ffill()
Out[88]:
A
2013-08-08 0.104050
2013-08-07 1.906684
2013-08-06 1.906684
2013-08-05 0.639589
2013-08-02 -0.179642
2013-08-01 -1.054874
Replace
Grouping
The grouping docs.
Basic grouping with apply
Unlike agg, apply’s callable is passed a sub-DataFrame which gives you access to all the columns
In [89]: df = pd.DataFrame({'animal': 'cat dog cat fish dog cat cat'.split(),
....: 'size': list('SSMMMLL'),
....: 'weight': [8, 10, 11, 1, 20, 12, 12],
....: 'adult' : [False] * 5 + [True] * 2}); df
....:
Out[89]:
adult animal size weight
0 False cat S 8
1 False dog S 10
2 False cat M 11
3 False fish M 1
4 False dog M 20
5 True cat L 12
6 True cat L 12
#List the size of the animals with the highest weight.
In [90]: df.groupby('animal').apply(lambda subf: subf['size'][subf['weight'].idxmax()])
Out[90]:
animal
cat L
dog M
fish M
dtype: object
In [91]: gb = df.groupby(['animal'])
In [92]: gb.get_group('cat')
Out[92]:
adult animal size weight
0 False cat S 8
2 False cat M 11
5 True cat L 12
6 True cat L 12
Apply to different items in a group
In [93]: def GrowUp(x):
....: avg_weight = sum(x[x['size'] == 'S'].weight * 1.5)
....: avg_weight += sum(x[x['size'] == 'M'].weight * 1.25)
....: avg_weight += sum(x[x['size'] == 'L'].weight)
....: avg_weight /= len(x)
....: return pd.Series(['L',avg_weight,True], index=['size', 'weight', 'adult'])
....:
In [94]: expected_df = gb.apply(GrowUp)
In [95]: expected_df
Out[95]:
size weight adult
animal
cat L 12.4375 True
dog L 20.0000 True
fish L 1.2500 True
In [96]: S = pd.Series([i / 100.0 for i in range(1,11)])
In [97]: def CumRet(x,y):
....: return x * (1 + y)
....:
In [98]: def Red(x):
....: return functools.reduce(CumRet,x,1.0)
....:
In [99]: S.expanding().apply(Red)
Out[99]:
0 1.010000
1 1.030200
2 1.061106
3 1.103550
4 1.158728
5 1.228251
6 1.314229
7 1.419367
8 1.547110
9 1.701821
dtype: float64
Replacing some values with mean of the rest of a group
In [100]: df = pd.DataFrame({'A' : [1, 1, 2, 2], 'B' : [1, -1, 1, 2]})
In [101]: gb = df.groupby('A')
In [102]: def replace(g):
.....: mask = g < 0
.....: g.loc[mask] = g[~mask].mean()
.....: return g
.....:
In [103]: gb.transform(replace)
Out[103]:
B
0 1.0
1 1.0
2 1.0
3 2.0
Sort groups by aggregated data
In [104]: df = pd.DataFrame({'code': ['foo', 'bar', 'baz'] * 2,
.....: 'data': [0.16, -0.21, 0.33, 0.45, -0.59, 0.62],
.....: 'flag': [False, True] * 3})
.....:
In [105]: code_groups = df.groupby('code')
In [106]: agg_n_sort_order = code_groups[['data']].transform(sum).sort_values(by='data')
In [107]: sorted_df = df.ix[agg_n_sort_order.index]
In [108]: sorted_df
Out[108]:
code data flag
1 bar -0.21 True
4 bar -0.59 False
0 foo 0.16 False
3 foo 0.45 True
2 baz 0.33 False
5 baz 0.62 True
Create multiple aggregated columns
In [109]: rng = pd.date_range(start="2014-10-07",periods=10,freq='2min')
In [110]: ts = pd.Series(data = list(range(10)), index = rng)
In [111]: def MyCust(x):
.....: if len(x) > 2:
.....: return x[1] * 1.234
.....: return pd.NaT
.....:
In [112]: mhc = {'Mean' : np.mean, 'Max' : np.max, 'Custom' : MyCust}
In [113]: ts.resample("5min").apply(mhc)
Out[113]:
Mean Max Custom
2014-10-07 00:00:00 1.0 2 1.234
2014-10-07 00:05:00 3.5 4 NaT
2014-10-07 00:10:00 6.0 7 7.404
2014-10-07 00:15:00 8.5 9 NaT
In [114]: ts
Out[114]:
2014-10-07 00:00:00 0
2014-10-07 00:02:00 1
2014-10-07 00:04:00 2
2014-10-07 00:06:00 3
2014-10-07 00:08:00 4
2014-10-07 00:10:00 5
2014-10-07 00:12:00 6
2014-10-07 00:14:00 7
2014-10-07 00:16:00 8
2014-10-07 00:18:00 9
Freq: 2T, dtype: int64
Create a value counts column and reassign back to the DataFrame
In [115]: df = pd.DataFrame({'Color': 'Red Red Red Blue'.split(),
.....: 'Value': [100, 150, 50, 50]}); df
.....:
Out[115]:
Color Value
0 Red 100
1 Red 150
2 Red 50
3 Blue 50
In [116]: df['Counts'] = df.groupby(['Color']).transform(len)
In [117]: df
Out[117]:
Color Value Counts
0 Red 100 3
1 Red 150 3
2 Red 50 3
3 Blue 50 1
Shift groups of the values in a column based on the index
In [118]: df = pd.DataFrame(
.....: {u'line_race': [10, 10, 8, 10, 10, 8],
.....: u'beyer': [99, 102, 103, 103, 88, 100]},
.....: index=[u'Last Gunfighter', u'Last Gunfighter', u'Last Gunfighter',
.....: u'Paynter', u'Paynter', u'Paynter']); df
.....:
Out[118]:
beyer line_race
Last Gunfighter 99 10
Last Gunfighter 102 10
Last Gunfighter 103 8
Paynter 103 10
Paynter 88 10
Paynter 100 8
In [119]: df['beyer_shifted'] = df.groupby(level=0)['beyer'].shift(1)
In [120]: df
Out[120]:
beyer line_race beyer_shifted
Last Gunfighter 99 10 NaN
Last Gunfighter 102 10 99.0
Last Gunfighter 103 8 102.0
Paynter 103 10 NaN
Paynter 88 10 103.0
Paynter 100 8 88.0
Select row with maximum value from each group
In [121]: df = pd.DataFrame({'host':['other','other','that','this','this'],
.....: 'service':['mail','web','mail','mail','web'],
.....: 'no':[1, 2, 1, 2, 1]}).set_index(['host', 'service'])
.....:
In [122]: mask = df.groupby(level=0).agg('idxmax')
In [123]: df_count = df.loc[mask['no']].reset_index()
In [124]: df_count
Out[124]:
host service no
0 other web 2
1 that mail 1
2 this mail 2
Grouping like Python’s itertools.groupby
In [125]: df = pd.DataFrame([0, 1, 0, 1, 1, 1, 0, 1, 1], columns=['A'])
In [126]: df.A.groupby((df.A != df.A.shift()).cumsum()).groups
Out[126]: {1: [0], 2: [1], 3: [2], 4: [3, 4, 5], 5: [6], 6: [7, 8]}
In [127]: df.A.groupby((df.A != df.A.shift()).cumsum()).cumsum()
Out[127]:
0 0
1 1
2 0
3 1
4 2
5 3
6 0
7 1
8 2
Name: A, dtype: int64
Expanding Data
Alignment and to-date
Rolling Computation window based on values instead of counts
Rolling Mean by Time Interval
splitting
Splitting a frame
Create a list of dataframes, split using a delineation based on logic included in rows.
In [128]: df = pd.DataFrame(data={'Case' : ['A','A','A','B','A','A','B','A','A'],
.....: 'Data' : np.random.randn(9)})
.....:
In [129]: dfs = list(zip(*df.groupby((1*(df['Case']=='B')).cumsum().rolling(window=3,min_periods=1).median())))[-1]
In [130]: dfs[0]
Out[130]:
Case Data
0 A 0.174068
1 A -0.439461
2 A -0.741343
3 B -0.079673
In [131]: dfs[1]
Out[131]:
Case Data
4 A -0.922875
5 A 0.303638
6 B -0.917368
In [132]: dfs[2]
Out[132]:
Case Data
7 A -1.624062
8 A -0.758514