CODEWITHSUNDEEP
python-3.xpandaspandas-groupby
Pallavi Verma
Pallavi Verma

Reputation: 85

groupby value_counts store in a data frame

My data frame looks like - 

city
a
f
m
m
m
d

I want to store this data into other data frame -

city       total
a           1
f           1
m           3
d           1

my code is - 

df_city = df.groupby(['city'])['city'].count()

but not getting proper results.

Upvotes: 0

Views: 106

Answers (4)

jezrael
jezrael

Reputation: 862611

Solution with groupby is better if want avoid sorting total - add Series.reset_index with name parameter:

df_city = df.groupby('city')['city'].count().reset_index(name='total')
print (df_city)
  city  total
0    a      1
1    d      1
2    f      1
3    m      3

If use Series.value_counts output is sorting, for DataFrame add Series.rename_axis and Series.reset_index:

df_city = df['city'].value_counts().rename_axis('city').reset_index(name="total")
print (df_city)
  city  total
0    m      3
1    d      1
2    f      1
3    a      1

Upvotes: 0

RavinderSingh13
RavinderSingh13

Reputation: 133478

Could you please try following.

df['total']=df.groupby('city').cumcount()+1
df.drop_duplicates('city',keep='last').reset_index(drop='True')

To store this in a data frame use:

df['total']=df.groupby('city').cumcount()+1
df1=df.drop_duplicates('city',keep='last').reset_index(drop='True')
df1

When we print df its value will be as follows:

   city total
0   a   1
1   f   1
2   m   3
3   d   1

Upvotes: 0

Andy L.
Andy L.

Reputation: 25239

Develop from your codes

df.groupby('city').city.count().rename('total').reset_index()

Out[505]:
  city  total
0    a      1
1    d      1
2    f      1
3    m      3

Upvotes: 1

Maku
Maku

Reputation: 1610

This will do: 

df['city'].value_counts().to_frame(name="Total")

Upvotes: 2

Related Questions