/
Day_14_A_Pivot_table_example.py
90 lines (65 loc) · 1.55 KB
/
Day_14_A_Pivot_table_example.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
# -*- coding: utf-8 -*-
# <nbformat>3.0</nbformat>
# <markdowncell>
# A simple demonstration of pivot_table. [Reshaping and Pivot Tables — pandas 0.13.1 documentation](http://pandas.pydata.org/pandas-docs/stable/reshaping.html)
# <codecell>
import pandas as pd
from pandas import DataFrame, Series
# <codecell>
df = DataFrame([{
'year':1880,
'name':'John',
'sex': 'M',
'births': 13
},
{'year':1880,
'name':'Pat',
'sex': 'M',
'births': 13
},
{'year':1880,
'name':'Pat',
'sex': 'F',
'births': 13
},
{
'year':1880,
'name':'Jane',
'sex': 'F',
'births': 20
},
{
'year':1881,
'name':'John',
'sex': 'M',
'births': 90
},
{
'year':1881,
'name':'Jane',
'sex': 'F',
'births': 21
},])
df
# <codecell>
pt = df.pivot_table(rows='year', cols=['name','sex'])['births']
pt
# <codecell>
# let's make a new table in which there is M/F subindex for all names
names = set(pt.columns.get_level_values(level=0))
sexes = set(pt.columns.get_level_values(level=1))
names, sexes
# <codecell>
# http://pandas.pydata.org/pandas-docs/stable/indexing.html#creating-a-multiindex-hierarchical-index-object
new_index = pd.MultiIndex.from_product([list(names), list(sexes)],
names=['name','sex'])
new_index
# <codecell>
# http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html
pt.T.reindex(new_index).T
# <codecell>
pt.T.reindex(new_index).T.fillna(0)
# <codecell>
df.groupby('year').apply(lambda s: s.groupby(['name','sex']).agg('sum'))
# <codecell>
df.groupby('year').apply(lambda s: s.groupby(['name','sex']).agg('sum')).unstack()