Using Add() function to merge multiple dataframes in Panda

Question

Given three data frames containing the number of gold, silver, and bronze Olympic medals won by some countries, determine the total number of medals won by each country. Note: All the three data frames don’t have all the same countries.Also, sort the final dataframe, according to the total medal count in descending order.

This is my code below - but I am not getting the desired output.Can someone please suggest what is wrong?

import numpy as np 
import pandas as pd

# Defining the three dataframes indicating the gold, silver, and bronze medal counts
# of different countries
gold = pd.DataFrame({'Country': ['USA', 'France', 'Russia'],
                         'Medals': [15, 13, 9]}
                    )
silver = pd.DataFrame({'Country': ['USA', 'Germany', 'Russia'],
                        'Medals': [29, 20, 16]}
                    )
bronze = pd.DataFrame({'Country': ['France', 'USA', 'UK'],
                        'Medals': [40, 28, 27]}
                    )
#gold.set_index('Country',inplace = True)
#silver.set_index('Country',inplace = True)
#bronze.set_index('Country',inplace = True)

Total = gold.add(silver,fill_value = 0).add(bronze,fill_value = 0)
Total.sort_values('Medals',ascending = True)

Could you edit the post to tell us what output you are getting and what output you are expecting? — Sid Kwakkel
– Sid Kwakkel, Commented Feb 12, 2021 at 4:04

Arkadiusz · Accepted Answer · 2021-02-11 20:40:17Z

2

You can try:

pd.concat([gold, silver, bronze]).groupby('Country').sum().\
          sort_values('Medals', ascending=False).reset_index()

If you do like that you have three dataframes in one. It's grouped by country and you get sum of medals for each of them. At the end we sort it in a descending order and reset the index.

Output:

    Country  Medals
0       USA      72
1    France      53
2        UK      27
3    Russia      25
4   Germany      20

answered Feb 11, 2021 at 20:40

Arkadiusz

1,8751 gold badge10 silver badges15 bronze badges

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

6 Comments

Rajib Biswas Over a year ago

Thanks mate.Two things -

Rajib Biswas Over a year ago

And Whats wrong with my original using .add function?

Arkadiusz Over a year ago

If it is assigned to a dataframe named 'Total' you can add a new line: Total['Medals'] = Total['Medals'].astype('float64')

Trenton McKinney Over a year ago

@RajibBiswas The entire approach is probably not correct. The medal types should not have been separated from the original dataframe into separate dataframes.

Rajib Biswas Over a year ago

@TrentonMcKinney,what should be the right approach?

|

Imam_AI · Accepted Answer · 2021-06-13 04:09:34Z

0

You can do below way as well:

    gold.set_index('Country', inplace=True)
    silver.set_index('Country', inplace=True)
    bronze.set_index('Country', inplace=True)
    #print(gold)
    #print(silver)
    #print(bronze)

   Total= gold.add(silver,    fill_value=0).add(bronze,fill_value=0).sort_values('Medals', ascending=False)

Output:

             Medals
    Country        
    USA        72.0
    France     53.0
    UK         27.0
    Russia     25.0
    Germany    20.0

edited Jun 13, 2021 at 4:09

answered Jun 12, 2021 at 17:40

Imam_AI

1511 silver badge7 bronze badges

Comments

Komal Komal · Accepted Answer · 2022-01-20 16:59:59Z

import numpy as np 
import pandas as pd

# Defining the three dataframes indicating the gold, silver, and bronze medal counts
# of different countries
gold = pd.DataFrame({'Country': ['USA', 'France', 'Russia'],
                         'Medals': [15, 13, 9]}
                    )
silver = pd.DataFrame({'Country': ['USA', 'Germany', 'Russia'],
                        'Medals': [29, 20, 16]}
                    )
bronze = pd.DataFrame({'Country': ['France', 'USA', 'UK'],
                        'Medals': [40, 28, 27]}
                    )

# Set the index of the dataframes to 'Country' so that you can get the countrywise
# medal count
gold.set_index('Country', inplace = True)
silver.set_index('Country', inplace = True) 
bronze.set_index('Country', inplace = True) 

# Add the three dataframes and set the fill_value argument to zero to avoid getting
# NaN values
total = gold.add(silver, fill_value = 0).add(bronze, fill_value = 0)

# Sort the resultant dataframe in a descending order
total = total.sort_values(by = 'Medals', ascending = False).astype("int64")

# Print the sorted dataframe
print(total)

int64 is used to convert the float value into integer and 64 indicates 64bit memory location

Komal Komal · Accepted Answer · 2022-01-20 17:06:42Z

0

import numpy as np 
import pandas as pd

# Defining the three dataframes indicating the gold, silver, and bronze medal counts
# of different countries
gold = pd.DataFrame({'Country': ['USA', 'France', 'Russia'],
                         'Medals': [15, 13, 9]}
                    )
silver = pd.DataFrame({'Country': ['USA', 'Germany', 'Russia'],
                        'Medals': [29, 20, 16]}
                    )
bronze = pd.DataFrame({'Country': ['France', 'USA', 'UK'],
                        'Medals': [40, 28, 27]}
                    )
gold.set_index('Country' , inplace = True)
silver.set_index('Country' , inplace = True) 
bronze.set_index('Country' , inplace = True ) 

total = gold.add(silver , fill_value = 0).add(bronze , fill_value = 0)
total = total.sort_values(by = 'Medals', ascending = False)
total = total.astype(int)
print(total)

answered Jan 20, 2022 at 17:06

Komal Komal

12 bronze badges

1 Comment

joanis Over a year ago

This is a duplicate of your own other answer. Instead of posting twice, please delete one of the answers, and edit the other to shape it up as you want. Also, a few sentences explaining how your code solves the issue would be great!

Collectives™ on Stack Overflow

Using Add() function to merge multiple dataframes in Panda

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related