Filtering Dataframe in Python

Question

I have a dataframe with 2 columns as below:

Index Year        Country
0     2015        US
1     2015        US
2     2015        UK
3     2015        Indonesia
4     2015        US
5     2016        India
6     2016        India
7     2016        UK

I want to create a new dataframe containing the maximum count of country in every year. The new dataframe will contain 3 columns as below:

    Index      Year      Country     Count
    0          2015      US          3
    1          2016      India       2

Is there any function in pandas where this can be done quickly?

niraj · Accepted Answer · 2017-08-12 16:43:25Z

1

One way can be to use groupby and along with size for finding in each category adn sort values and slice by possible number of year. You can try the following:

num_year = df['Year'].nunique()
new_df = df.groupby(['Year', 'Country']).size().rename('Count').sort_values(ascending=False).reset_index()[:num_year]

Result:

   Year   Country  Count
0  2015      US      3
1  2016   India      2

answered Aug 12, 2017 at 16:43

niraj

18.2k4 gold badges36 silver badges50 bronze badges

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

Comments

jezrael · Accepted Answer · 2017-08-12 13:32:58Z

0

Use:

1.

First get count of each pairs Year and Country by groupby and size. Then get index of max value by idxmax and select row by loc:

df = df.groupby(['Year','Country']).size()
df = df.loc[df.groupby(level=0).idxmax()].reset_index(name='Count')
print (df)
   Year Country  Count
0  2015      US      3
1  2016   India      2

2.

Use custom function with value_counts and head:

df = df.groupby('Year')['Country']
       .apply(lambda x: x.value_counts().head(1))
       .rename_axis(('Year','Country'))
       .reset_index(name='Count')

print (df)
   Year Country  Count
0  2015      US      3
1  2016   India      2

edited Aug 12, 2017 at 13:32

answered Aug 12, 2017 at 13:25

jezrael

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

Comments

BENY · Accepted Answer · 2017-08-12 14:33:55Z

0

Just provide a method without groupby

Count=pd.Series(list(zip(df2.Year,df2.Country))).value_counts()
          .head(2).reset_index(name='Count')
Count[['Year','Country']]=Count['index'].apply(pd.Series)
Count.drop('index',1)


Out[266]: 
   Count  Year Country
0      3  2015      US
1      2  2016   India

answered Aug 12, 2017 at 14:33

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Filtering Dataframe in Python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related