Creating a new column based on multiple columns from another dataframe

Question

I have two dataframes.

The first is individual game data, which looks similar to this:

    Team1 Team2  Team1Score   Team2Score   Year
0     a     g        100          90       2019
1     b     h         90         100       2018
2     c     i         95         105       2017
3     d     j        100         115       2018
4     e     k        105         100       2016
5     f     l        120         130       2015
6     a     f        100         115       2015

The second is team data for the year, which looks similar to this:

    Team Rating   Year
0     a     111    2019
1     g     108    2019
2     j     105    2018
3     l      98    2015
4     b     104    2018
5     f     120    2015
6     a     100    2015

I want to merge them so that it looks like this:

    Team1 Team2  Team1Score   Team2Score   Year   Team1Rating  Team2Rating
0     a     g        100          90       2019       111          108
1     b     h         90         100       2018       104          NULL
2     c     i         95         105       2017       NULL         NULL
3     d     j        100         115       2018       NULL         105
4     e     k        105         100       2016       NULL         NULL
5     f     l        120         130       2015       120           98
6     a     f        100         115       2015       100          120

I basically want to have the team ratings with the game data. I'm not sure how to approach this and what steps to take

ansev · Answer 1 · 2019-09-20T02:51:09.720

0

You can use DataFrame.lookup to map df2:

df1['Team1Rating']='NULL'
df1['Team2Rating']='NULL'
cond1=df1['Team1'].isin(df2['Team'])&df1['Year'].isin(df2['Year'])
cond2=df1['Team2'].isin(df2['Team'])&df1['Year'].isin(df2['Year'])
lk=df2.pivot_table(index='Year',columns='Team')
lk.columns=lk.columns.droplevel(0)
rt1=lk.lookup(df1.loc[cond1,'Year'],df1.loc[cond1,'Team1'])
rt2=lk.lookup(df1.loc[cond2,'Year'],df1.loc[cond2,'Team2'])
df1.loc[cond1,'Team1Rating']=rt1
df1.loc[cond2,'Team2Rating']=rt2
print(df1)

  Team1 Team2  Team1Score  Team2Score  Year Team1Rating Team2Rating
0     a     g         100          90  2019         111         108
1     b     h          90         100  2018         104        NULL
2     c     i          95         105  2017        NULL        NULL
3     d     j         100         115  2018        NULL         105
4     e     k         105         100  2016        NULL        NULL
5     f     l         120         130  2015         120          98
6     a     f         100         115  2015         100         120

edited Sep 20 '19 at 02:51

answered Sep 20 '19 at 01:38

ansev

30,322
5
17
31

I'm getting this error when I try that: InvalidIndexError: Reindexing only valid with uniquely valued Index objects – user12092970 Sep 20 '19 at 01:56
what line do you get it? – ansev Sep 20 '19 at 01:58
Second line. When adding the Team1Rating column – user12092970 Sep 20 '19 at 02:00
if you copy dataframe of your ask using `df1=pd.read_clipboard()` you should not get any error. – ansev Sep 20 '19 at 02:01
are there duplicates team in the list of team? – ansev Sep 20 '19 at 02:06
try replace instead of map. I update solution – ansev Sep 20 '19 at 02:07
Yes there are duplicates. For example, team 'a' has a different rating each year – user12092970 Sep 20 '19 at 02:09
Can you update datasets df1 and df2 for solve this case? – ansev Sep 20 '19 at 02:10
ok, it is updated now – user12092970 Sep 20 '19 at 02:15
Thanks, solution update. – ansev Sep 20 '19 at 09:28

Creating a new column based on multiple columns from another dataframe

1 Answers1