Linking 2 data frames and returning a value using lookup

I'm a beginner in coding and data in general so any help I can get would be really helpful.

If I have a data frame as below,where every matchup is a tuple.

df1 = Team A Player 1.1 Team A Player 2.1 Team A Player 3.1
('Max', 'Hatteberg') ('Hatteberg', 'Tejada') ('Max', 'Rincon')
('Tejada', 'Brown') ('Hatteberg', 'Rincon') ('Hatteberg','Brown')


and so on..

and I have a crosstable:

df2 = Max Hatteberg Tejada Brown Rincon

Max NaN -1.0 +2.0 -8.0 +5.0

Hatteberg +1.0 NaN +2.5 +3.0 0

Tejada -2.0 -2.5 NaN +5.5 -3.5

Brown +8.0 -3.0 -5.5 NaN +2.8

Rincon -5.0 0 +3.5 2.8 NaN


And I wanted for each matchup to return a value as so

df1 = matchups 1 matchups 2 matchups 3
+1.0 -2.5 -5.0
-5.5 0 -3.0


I've tried,

df1.applymap(lambda x : df2.lookup([x[0]],[x[1]])[0])


But it returned,

('One or more row labels was not found', 'occurred at index Team A Player 1.1')


I'm having issues referencing to both of the data frames and returning a value. Could someone help me out? Thanks in advance

2 Answers
2

One functional solution is to use at and a simple for loop

at

df3 = pd.DataFrame()
for col in df1.columns:
df3[col] = df1[col].apply(lambda x: df2.at[x[0], x[1]])

col1 col2 col3
0 -1.0 2.5 5.0
1 5.5 0.0 3.0


It is more like you have some value in df1 tuples refer to None value from df2

You can do with reindex before apply lookup

reindex

apply

lookup

name=set(list(itertools.chain(*list(itertools.chain(*df1.values.tolist())))))
df2=df2.reindex(name).reindex(name,axis=1)
df1.applymap(lambda x : df2.lookup([x[0]],[x[1]])[0])


name

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.