Need To Perform a Merge in Pandas Exactly Like VLOOKUP

Question

I have a spreadsheet of fantasy players and their individual game stats. What I would like to add is a column that lists the Vegas Line of that individual game.

I'm merging from the below spreadsheet:

Favorite    Spread  Underdog    Spread2 Total   Away Money Line Home Money Line Week
Patriots    -7.0    Steelers    7   51.0    +270    -340    1
Packers -6.0    Bears   6   48.0    -286    +230    1
Chiefs  -1.0    Texans  1   40.0    -115    -105    1
Jets    -4.0    Browns  4   40.0    +170    -190    1
Colts   -1.0    Bills   1   44.0    -115    -105    1
Dolphins    -4.0    Football Team   4    46.0   -210    +175    1
Panthers    -3.0    Jaguars 3   41.0    -150    +130    1
Seahawks    -4.0    Rams    4   42.0    -185    +160    1
Cardinals   -2.0    Saints  2   49.0    +120    -140    1
Chargers    -4.0    Lions   4   46.0     +160   -180    1
Buccaneers  -3.0    Titans  3   40.0    +130    -150    1
Bengals -3.0    Raiders 3   43.0    -154    +130    1
Broncos -4.0    Ravens  4   46.0    +180    -220    1
Cowboys -7.0    Giants  7   52.0    +240    -300    1
Eagles  -3.0    Falcons 3   55.0    -188    +150    1
Vikings -2.0    49ers   2   42.0    -142    +120    1


df = pd.read_excel('Fantasy2015.xlsx')
odds = pd.read_excel('Odds2015Wk1.xlsx')


odds['Favorite'] = odds['Favorite'].map(lambda x: x.lstrip('at'))
odds['Underdog'] = odds['Underdog'].map(lambda x: x.lstrip('at'))


df['Week'] = df['Week'].astype(int)
odds['Week'] = 1
odds['Favorite'] = odds['Favorite'].astype(str) + odds['Week'].astype(str) 
odds['Underdog'] = odds['Underdog'].astype(str) + odds['Week'].astype(str)  

df['Merge'] = df['Tm'] + df['Week'].astype(str)

df =  df.merge(odds['Spread'], left_on='Merge', right_on=odds['Favorite'], how='left')
df =  df.merge(odds['Spread'], left_on='Merge', right_on=odds['Underdog'], how='left')

What I'm getting:

But I need a value in each row. Is there a reason its not working for each team?

Answer 1

The problem is you do 2 merges, and the last merge overwrites the first one. You need to do it in a way that after the first merge, the second merge only fills in the missing parts and not overwrite.

One way you could do it is merge the favorites, the merge the underdogs (by an inner join). Then just but the 2 together:

import pandas as pd

odds = pd.DataFrame([
['Patriots',    '-7.0 ' ,  'Steelers' ,   '7'  , '51.0'  ,  '+270' ,   '-340' ,   '1'],
['Packers', '-6.0' ,   'Bears'  , '6' ,  '48.0'  ,  '-286' ,   '+230',    '1'],
['Chiefs', '-1.0'  ,  'Texans'  ,'1'  , '40.0'   , '-115'  ,  '-105' ,   '1'],
['Jets' ,   '-4.0' ,   'Browns' , '4' ,  '40.0'  ,  '+170' ,   '-190',    '1'],
['Colts',   '-1.0' ,   'Bills'  , '1'  , '44.0'  ,  '-115' ,   '-105',    '1'],
['Dolphins',    '-4.0' ,  'Football Team' ,  '4'   , '46.0' ,  '-210',    '+175'  ,  '1'],
['Panthers',    '-3.0' ,   'Jaguars', '3' ,  '41.0' ,   '-150' ,   '+130' ,   '1'],
['Seahawks' ,   '-4.0' ,   'Rams'   , '4' ,  '42.0' ,   '-185' ,   '+160' ,   '1'],
['Cardinals',   '-2.0' ,   'Saints' ,'2'  , '49.0'  ,  '+120'  ,  '-140'  ,  '1'],
['Chargers' ,   '-4.0' ,   'Lions'  , '4' ,  '46.0' ,    '+160' ,  '-180' ,   '1'],
['Buccaneers',  '-3.0' ,   'Titans' , '3' ,  '40.0' ,   '+130'  ,  '-150' ,   '1'],
['Bengals' ,'-3.0'  ,  'Raiders', '3',   '43.0'  ,  '-154'  ,  '+130'  ,  '1'],
['Broncos', '-4.0'  , 'Ravens' , '4' ,  '46.0'   , '+180'   , '-220'   , '1'],
['Cowboys', '-7.0'  ,  'Giants' , '7' ,  '52.0'  ,  '+240'  ,  '-300'  ,  '1'],
['Eagles',  '-3.0'  ,  'Falcons', '3'  , '55.0'  ,  '-188'  ,  '+150'  ,  '1'],
['Vikings', '-2.0'  ,  '49ers'  , '2'   ,'42.0'  ,  '-142'  ,  '+120'  , '1']],
columns = ['Favorite',    'Spread',  'Underdog' ,   'Spread2', 'Total' ,  'Away Money Line', 'Home Money Line', 'Week'])


df = pd.DataFrame([
        ['Devonte Freemon', 'RB', '2015-09-14 00:00:00', '1', 'Falcons'],
        ['Antonion Brownn', 'WR', '2015-09-10 00:00:00', '1', 'Steelers'],
        ['Adrian Peterson', 'RB', '2015-09-14 00:00:00', '1', 'Vikings'],
        ['Cam Newton', 'QB', '2015-09-14 00:00:00', '1', 'Panthers']],
        columns = ['Name','Position','Date','Week','Tm'])
        

df['Week'] = df['Week'].astype(int)
odds['Week'] = odds['Week'].astype(int)
odds['Spread'] = odds['Spread'].astype(float)


favorite_merged = df.merge(odds.rename(columns={'Favorite':'Tm'})[['Tm','Week','Spread']], how='inner', on=['Tm','Week'])
underdog_merged = df.merge(odds.rename(columns={'Underdog':'Tm'})[['Tm','Week','Spread']], how='inner', on=['Tm','Week'])

# Because the spread for the Underdog should be positive
underdog_merged['Spread'] = underdog_merged['Spread'] * -1

final_df = pd.concat([favorite_merged, underdog_merged]).reset_index(drop=True)

Output:

print(df)
              Name Position                 Date  Week        Tm
0  Devonte Freemon       RB  2015-09-14 00:00:00     1   Falcons
1  Antonion Brownn       WR  2015-09-10 00:00:00     1  Steelers
2  Adrian Peterson       RB  2015-09-14 00:00:00     1   Vikings
3       Cam Newton       QB  2015-09-14 00:00:00     1  Panthers

is now...

print(final_df)
              Name Position                 Date  Week        Tm  Spread
0  Adrian Peterson       RB  2015-09-14 00:00:00     1   Vikings    -2.0
1       Cam Newton       QB  2015-09-14 00:00:00     1  Panthers    -3.0
2  Devonte Freemon       RB  2015-09-14 00:00:00     1   Falcons     3.0
3  Antonion Brownn       WR  2015-09-10 00:00:00     1  Steelers     7.0

Answer 2

Try changing x.lstrip('at') to x.lstrip('at ')