How To Combine 3 Complex Data Frames In Pandas

I have 3 pandas data frames named df1, df2 and df3 df1: match_up result 0 1985_1116_1234 1 1 1985_1120_1345 1 2 1985_1207_1250 1 3 1985_1229_142

Solution 1:

import pandas as pddf1= pd.DataFrame({'match_up':['1985_1116_1234','1985_1120_1345','1985_1207_1250','1985_1229_1425','1985_1242_1325'],
                    'results':[1,1,1,1,1]})

df2 = pd.DataFrame({'team_df2':[1207,1116,1120,1229,1242],
                    'win_df2':[0.700,0.636,0.621,0.615,0.679]})

df3 = pd.DataFrame({'team_df3':[1234,1250,1325,1345,1425],
                    'win_df3':[0.667,0.759,0.774,0.742,0.667]})


df1['match_up'].apply(lambda x: x.split('_')[1])

final = pd.merge(df1,df2,
        left_on=df1['match_up'].apply(lambda x: int(x.split('_')[1])).values,
        right_on='team_df2',how='left')

final = pd.merge(final,df3,
        left_on=df1['match_up'].apply(lambda x: int(x.split('_')[2])).values,
        right_on='team_df3',how='left')

Output:

In [23]:finalOut[23]:match_upresultsteam_df2win_df2team_df3win_df301985_1116_123411116    0.6361234    0.66711985_1120_134511120    0.6211345    0.74221985_1207_125011207    0.7001250    0.75931985_1229_142511229    0.6151425    0.66741985_1242_132511242    0.6791325    0.774

Solution 2:

You are going to need to extract the strings and convert the to ints in order to merge correctly...

# Set up result DataFramedf = df1.copy()
df['year'], df['id2'], df['id3'] = list(zip(*df['match_up'].str.split('_')))
df[['id2', 'id3']] = df[['id2', 'id3']].astype(int)

# Do mergesdf = pd.merge(df, df2, left_on='id2', right_on='team_df2')
df = pd.merge(df, df3, left_on='id3', right_on='team_df3')

# Drop unneeded columns and printdf = df.drop(['id2', 'year', 'id3'], axis=1)
print(df)

yields

match_upresultteam_df2win_df2team_df3win_df301985_1116_123411116    0.6361234    0.66711985_1120_134511120    0.6211345    0.74221985_1207_125011207    0.7001250    0.75931985_1229_142511229    0.6151425    0.66741985_1242_132511242    0.6791325    0.774