How to merge two dataframes based on header and columns values?

user_3590067 0
Viewed 727

I have two dataframes. The first one contains the data of different cities.

dfTmp:
    City        2005    2007    
0   London       3       7
1   Paris        2       0

then an empty dataframe that I want to populate

dfData:
    City        Year        
0   London      2005     
1   London      2007     
2   Paris       2005         
3   Paris       2007

I want to merge the two datasets. Then dfData should become as the following

dfData:
    City        Year    Value   
0   London      2005     3
1   London      2007     7  
2   Paris       2005     2      
3   Paris       2007     0
pandas dataframe merge python
2 Answers

You don't need the second dataframe, dfData. All the data you need is in the dfTmp. You just need to reshape dfTmp using the following:

Option 1

using set_index and stack

dfData = dftmp.rename_axis('Year', 1).set_index('City').stack().reset_index(name='Value')

print(dfData)

Output:

     City  Year  Value
0  London  2005      3
1  London  2007      7
2   Paris  2005      2
3   Paris  2007      0

Option 2

using melt:

dfData = dftmp.melt(id_vars='City', var_name='Year', value_name='Value')

print(dfData)

Output:

     City  Year  Value
0  London  2005      3
1   Paris  2005      2
2  London  2007      7
3   Paris  2007      0
user_63615310

The solution given above, - avoiding merge using only dfTmp -, seems optimal. In case you insist to use Merge, the following could be done:

First take care that the Pandas package is imported:

import pandas as pd

We have the following data:

Tmp = {'City': ['London', 'Paris'],
    '2005': [3,2],
   '2007': [7,0]}
dfTmp = pd.DataFrame(data, columns = ['City', '2005', '2007'])

And:

data = {'City': ['London', 'London', 'Paris', 'Paris'],
    'Year': [2005,2007, 2005, 2007]}
dfData = pd.DataFrame(data, columns = ['City', 'Year'])

Then use Merge in Pandas:

dfNew = pd.merge(dfData, dfTmp)
dfNew.iloc[0:4,0:3]
user_40086570
Related