CODEWITHSUNDEEP
pythonpandas
chhibbz
chhibbz

Reputation: 480

Python Pandas adding column values based on condition

I have a DataFrame (df) with following values:

                  Title
fintech_countries          
US                 60
UK                 54
India              28
Australia          25
Germany            13
Singapore          11
Canada             10

I want to add all the countries with values < 25, and show them as 'Others' with their sum (34).

I have created a column name for countries through the following code:

df1 = df.rename_axis('fintech_countries').rename_axis("countries", axis="columns" , inplace=True)


countries         Title
fintech_countries          
US                 60
UK                 54
India              28
Australia          25
Germany            13
Singapore          11
Canada             10

Now, I have tried the following code based on another query on StackOverflow:

df1.loc[df1['Title'] < 25, "countries"].sum()

but am getting the following error:

KeyError: 'the label [countries] is not in the [columns]'

Can someone please help? I need the final output as:

countries         Title
fintech_countries          
US                 60
UK                 54
India              28
Australia          25
Others             34

TIA

Upvotes: 3

Views: 2137

Answers (1)

jezrael
jezrael

Reputation: 862661

Solution with loc for setting with enlargement and filtering by boolean indexing:

mask = df['Title'] < 25
print (mask)
fintech_countries
US           False
UK           False
India        False
Australia    False
Germany       True
Singapore     True
Canada        True
Name: Title, dtype: bool

df1 = df[~mask].copy()
df1.loc['Others', 'Title'] = df.loc[mask, 'Title'].sum()
df1.Title = df1.Title.astype(int)
print (df1)
countries          Title
fintech_countries       
US                    60
UK                    54
India                 28
Australia             25
Others                34

Upvotes: 3

Related Questions