How to do count from dataframe python?

Question

I have data like as below:

df = pd.DataFrame({'date':['2020.2.1','2020.2.1','2020.2.1','2020.2.2','2020.2.1'],
                'fips':[4,5,4,5,5]})

and I want to get a result counting the frequency of fips each day like as below, but I don't want to ignore the overlapped index column (desired output):

pd.DataFrame({'date':['2020.2.1','2020.2.1','2020.2.2'],
                'fips':[4,5,5], 'count':[2,2,1]})

I tried df.groupby(['date','fips']).size() But it ignore the index value at second row and first column '2020.2.1'. Thanks in advance for your help..

Yeah, I did. but it gives error saying 'DataFrame' object has no attribute 'value_counts' — Rachelle
– Rachelle, Commented Oct 11, 2020 at 3:35
Which version of pandas do you have? I tried it worked for me — Nakor
– Nakor, Commented Oct 11, 2020 at 3:35
In the meantime, this should work too: df.groupby(['date', 'fips']).fips.agg(fips='first', freq=len) — Nakor
– Nakor, Commented Oct 11, 2020 at 3:38
Thank you for your help. but what I am trying to do is not to ignore the second row index column which is '2020.2.1' for second row. I want it visible twice in the dataframe. but only show only once.. — Rachelle
– Rachelle, Commented Oct 11, 2020 at 3:52

LevB · Accepted Answer · 2020-10-11 04:44:00Z

1

Just reset the index to make the first column 'fully visible'

df2 = df.groupby(['date','fips'])['fips'].agg(['count']).reset_index()

answered Oct 11, 2020 at 4:44

LevB

9537 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to do count from dataframe python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related