CODEWITHSUNDEEP
pandas
Rajtheaudacityofhope
Rajtheaudacityofhope

Reputation: 584

How to count the no. of same strings in pandas dataframe

i have a dataframe like:

Company Date        Country
ABC     2017-09-17   USA
BCD     2017-09-16   USA
ABC     2017-09-17   USA
BCD     2017-09-16   USA
BCD     2017-09-16   USA

I want to get a resultant df as :

Company  No: of Days
ABC      2
BCD      3

How do i do it ?

Upvotes: 1

Views: 2887

Answers (1)

jezrael
jezrael

Reputation: 862681

You can use value_counts and rename_axis with reset_index:

df1 = df['Company'].value_counts()
                   .rename_axis('Company').reset_index(name='No: of Companies')
print (df1)
  Company  No: of Companies
0     BCD                 3
1     ABC                 2

Another solution with groupby and aggregating size, last reset_index:

df1 = df.groupby('Company').size().reset_index(name='No: of Companies')
print (df1)
  Company  No: of Companies
0     BCD                 3
1     ABC                 2

If need count Date columns:

df1 = df['Date'].value_counts().rename_axis('Date').reset_index(name='No: of Days')
print (df1)
         Date  No: of Days
0  2017-09-16            3
1  2017-09-17            2

df1 = df.groupby('Date').size().reset_index(name='No: of Days')
print (df1)
         Date  No: of Days
0  2017-09-16            3
1  2017-09-17            2

EDIT:

If need count pairs Date and Company columns:

df1 = df.groupby(['Date', 'Company']).size().reset_index(name='No: of Days per company')
print (df1)
         Date Company  No: of Days per company
0  2017-09-16     BCD                        3
1  2017-09-17     ABC                        2

Upvotes: 3

Related Questions