I have a data frame as shown
I need to convert it like this
This is just sample dummy replica of my actual data .. It has over 5000 ID'd and 20 Events.
So I need for each ID , sum of how many times each event where triggered individually and events need to be separate columns.How to achieve this in pandas?
You can used pandas pivot_table function
df = pd.DataFrame({'foo': ['one', 'one', 'one', 'two', 'two',
'two','one','three'],
'bar': ['A', 'B', 'C', 'A', 'B', 'C','A','A'],
'baz': [1, 2, 3, 4, 5, 6,1,2],
'zoo': ['x', 'y', 'z', 'q', 'w', 't','x','x']})
print(df)
bar baz foo zoo
0 A 1 one x
1 B 2 one y
2 C 3 one z
3 A 4 two q
4 B 5 two w
5 C 6 two t
6 A 1 one x
7 A 2 three x
df.pivot_table(index='foo',columns='bar',values='baz',aggfunc='count',fill_value=0)
bar A B C
foo
one 2 1 1
three 1 0 0
two 1 1 1
In this case you might have to do like below.
df = pd.DataFrame({'Event':['a','b','c','d','c','a','a'],'ID':['001','002','003','004','004','004','001']})
df = df.reset_index()
df.pivot_table(index='ID',columns='Event',values='index',aggfunc='count',fill_value=0)
Event a b c d
ID
001 2 0 0 0
002 0 1 0 0
003 0 0 1 0
004 1 0 1 1