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

Arkadiusz Arkadiusz · Accepted Answer · 2021-02-11T20:40:17

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

Using Add() function to merge multiple dataframes in Panda

2 Answers