Put values from one df in two columns of another df, depending on two columns

user_12778634 0
Viewed 46

How can I achieve putting values from one df in two columns of another df, depending on two columns like:

import pandas as pd
import numpy as np

# data = home  away      values = team value
#        A     B                  A    1
#        B     A                  A    2
#        C     A                  A    3

tmp = {"home": ["A", "B", "C"], "away": ["B", "A", "A"]}
data = pd.DataFrame(data=tmp)

tmp = {"team": ["A", "A", "A"], "value": [1, 2, 3]}
values = pd.DataFrame(data=tmp)

# result =  home away home_value away_value
#           A    B    1          nan
#           B    A    nan        2
#           C    A    nan        3

result = # do something here

I am looking for a Pythonic way, without iteration.

pandas python-3.x numpy
1 Answers

Let's try this:

df_games = data.rename_axis(index='Game', columns='Location')\
               .stack().rename('Team').reset_index()
df_values = values.rename_axis(index='Game', columns='Team')\
                  .stack().rename('value').reset_index()

df_out = df_games.merge(df_values, on=['Game', 'Team'], how='left')\
                 .set_index(['Game', 'Location']).unstack()
df_out.columns = df_out.columns.map('_'.join)

print(df_out)

Output:

     Team_away Team_home  value_away  value_home
Game                                            
0            B         A         NaN         1.0
1            A         B         2.0         NaN
2            A         C         3.0         NaN

Details:

Here, I am doing a lot of renaming to get things to align as expected. Then I reshape to get a single column merge those two single columns dataframes together, reshape with unstack, and flatten multiIndex header to get home/away teams and values.

user_63615310
Related