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

Question

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 ?

jezrael · Accepted Answer · 2017-07-16 04:19:39Z

3

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

edited Jul 16, 2017 at 4:19

answered Jul 16, 2017 at 4:05

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

7 Comments

Rajtheaudacityofhope Over a year ago

you are not at all using the 'date' column ?

jezrael Over a year ago

@MohanRaj - I add solutions for date column, but then output is different.

Rajtheaudacityofhope Over a year ago

I want only a dataframe which has only 2 columns in the result - company and date

jezrael Over a year ago

@MohanRaj - yes. But it depends you need count column Company or column Dates. If my solution is not what you need, can you change sample data and add desired output?

Rajtheaudacityofhope Over a year ago

Sorry, the i erred in the comment section - i needed only 2 columns - company and no_of_days_per_company.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related