Heisenberg Heisenberg - 4 years ago 78
Python Question

How to pivot with binning with complicated condition in pandas

I have dataframe like below

age type days
1 a 1
2 b 3
2 b 4
3 a 5
4 b 2
6 c 1
7 f 0
7 d 4
10 e 2
14 a 1


first I would like to binning with age

age

[0~4]


age type days
1 a 1
2 b 3
2 b 4
3 a 5
4 b 2


Then sum up and count days by grouping with
type


sum count
a 6 2
b 9 3
c 0 0
d 0 0
e 0 0
f 0 0


Then I would like to apply this method to another binns.

[5~9]
[11~14]

My desired result is below

[0~4] [5~9] [10~14]
sum count sum count sum count
a 6 2 0 0 1 1
b 9 3 0 0 0 0
c 0 0 1 1 0 0
d 0 0 4 1 0 0
e 0 0 0 0 2 1
f 0 0 0 1 0 0


How can this be done?
It is very complicated for me..

Answer Source

Consider a pivot_table with pd.cut if you do not care too much about column ordering as count and sum are not paired together under the bin. With manipulation you can change such ordering.

df['bin'] = pd.cut(df.age, [0,4,9,14])

pvtdf = df.pivot_table(index='type', columns=['bin'], values='days', 
                       aggfunc=('count', 'sum')).fillna(0)

#       count                   sum               
# bin  (0, 4] (4, 9] (9, 14] (0, 4] (4, 9] (9, 14]
# type                                            
# a       2.0    0.0     1.0    6.0    0.0     1.0
# b       3.0    0.0     0.0    9.0    0.0     0.0
# c       0.0    1.0     0.0    0.0    1.0     0.0
# d       0.0    1.0     0.0    0.0    4.0     0.0
# e       0.0    0.0     1.0    0.0    0.0     2.0
# f       0.0    1.0     0.0    0.0    0.0     0.0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download