how to get .value_count and values in single data frame

This is my sample csv

    ID DESC 
1    1 Car
2    2 Boat
3    3 Bike
4    4 Car
5    5 Car
6    6 Car
7    7 Car

When I do .value_counts() I get

5
1
1

I want to get

ID DESC COUNT
1  Car  5
2  Boat 1
3  Bike 1

This is my current attempt

import pandas as pd

path = "C:Matching_Desc.csv"

df = pd.read_csv(path, encoding= 'unicode_escape')

df2 = df['DESC'].value_counts().index.tolist()

df3 = df['DESC'].value_counts()

df2 = pd.DataFrame(df2)
df3 = pd.DataFrame(df3)

df3.columns = ['COUNT']
df2.columns = ['DESC']

frames = [df2,df3]

result = pd.concat(frames)

result.to_csv("C:matching.csv", index=False)

This does not concat the two df properly and does not have the ID

Any suggestions?

Answer

You can use a groupby.agg in place of value_counts:

(df.groupby('DESC', as_index=False, sort=False)
   .agg(**{'ID': ('ID', 'first'),
           'COUNT': ('ID', 'size')
           })
 )

Output:

   DESC  ID  COUNT
0   Car   1      5
1  Boat   2      1
2  Bike   3      1

Advertisement

Answer