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

Question

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

Scott Boston · Accepted Answer · 2017-09-21 19:11:52Z

4

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

edited Sep 21, 2017 at 19:11

answered Sep 21, 2017 at 19:06

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

Comments

Ruthger Righart · Accepted Answer · 2017-09-21 20:04:36Z

0

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]

answered Sep 21, 2017 at 20:04

Ruthger Righart

4,9692 gold badges32 silver badges35 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Option 1

using `set_index` and `stack`

Option 2

using `melt`:

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Option 1

using set_index and stack

Option 2

using melt:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

using `set_index` and `stack`

using `melt`: