How to group by one column and count frequency from other column for each item in the previous column in python?

Question

Farah

2021年6月24日 12:01

I am trying to group my data by the 'ID' column. Then I want to count the frequency of 'sequence' for each 'ID'. Here is a sample of the data frame:

ID     Sequence
101    1-2
101    3-1
101    1-2
102    4-6
102    7-8
102    4-6
102    4-6
103    1118-69
104    1-2
104    1-2

I am looking for a count same as:

ID   Sequence   Count
101    1-2        2   
       3-1        1
102    4-6        3
       7-8        1
103    1118-69    1
104    1-2        2

I tried this code in python which doesn't give me what I want

df.groupby('ID') Blockquote

df.groupby('Sequence').count()

Farah · Accepted Answer · 2020年5月29日 21:57

Farah answered at 2020年5月29日 21:57

This simple Code worked:

Count_sequence = df.groupby(['ID','Sequence']).count()

For obtaining the output in an excel sheet:

Count_sequence.to_excel('sequence_count.xlsx)