KeyError during Lookup for reference to crosstables

Question

I have a data frame of :

dfAll = match ID    Team A Hero 1.1 Team A Hero 1.2   Team A Hero 1.3
     123124140         (1, 2)            (2, 3)            (1, 3)
     123123124         (4, 1)            (3, 4)            (1, 5)

and so on..

and I have a crosstable:

dfEloCross =     1     2     3     4     5
             1  NaN  -1.0  +2.0  -8.0   +5.0

             2  +1.0  NaN  +2.5  +3.0    0

             3  -2.0 -2.5  NaN   +5.5   -3.5

             4  +8.0 -3.0  -5.5  NaN   +2.8

             5  -5.0   0   +3.5  -2.8    NaN

And I wanted for each matchup to return a value as so, where it is vertical against horizontal.

df1 = matchups 1    matchups 2    matchups 3
       +1.0           -2.5         -2.0
       -8.0           -5.5         -5.0

I've tried using this code:

for uv in range(1, 6):
for xv in range(1,6):
    dfAll['Matchup' + ' ' + str(uv) + '.' + str(xv)] = dfAll['Team A Hero' + ' ' + str(uv) + '.' + str(xv)].apply(lambda x: dfEloCross.lookup([x[0]],[x[1]])[0])

And it works fine for the first column of Team A Hero 1.1

But returns key error for others KeyError: 'One or more column labels was not found

I'm a beginner so I would love to get all the help I can get. Thanks in Advance!

EDIT

For reference, here's a snippet of my dataframe:

dfAll = {'Team A Hero 1.1': {0: '(22, 21)', 1: '(12, 3)', 2: '(6, 7)', 3: '(13, 18)', 4: '(28, 7)', 5: '(9, 36)', 6: '(36, 7)', 7: '(9, 7)', 8: '(4, 61)'}, 'Team A Hero 1.2': {0: '(22, 43)', 1: '(12, 15)', 2: '(6, 31)', 3: '(13, 49)', 4: '(28, 13)', 5: '(9, 58)', 6: '(36, 13)', 7: '(9, 8)', 8: '(4, 64)'}, 'Team A Hero 1.3': {0: '(22, 71)', 1: '(12, 30)', 2: '(6, 40)', 3: '(13, 64)', 4: '(28, 56)', 5: '(9, 78)', 6: '(36, 30)', 7: '(9, 13)', 8: '(4, 72)'}, 'Team A Hero 1.4': {0: '(22, 77)', 1: '(12, 65)', 2: '(6, 43)', 3: '(13, 65)', 4: '(28, 87)', 5: '(9, 95)', 6: '(36, 80)', 7: '(9, 15)', 8: '(4, 76)'}, 'Team A Hero 1.5': {0: '(22, 85)', 1: '(12, 103)', 2: '(6, 69)', 3: '(13, 107)', 4: '(28, 106)', 5: '(9, 107)', 6: '(36, 98)', 7: '(9, 112)', 8: '(4, 84)'}, 'Team A Hero 2.1': {0: '(28, 21)', 1: '(58, 3)', 2: '(20, 7)', 3: '(54, 18)', 4: '(44, 7)', 5: '(28, 36)', 6: '(49, 7)', 7: '(52, 7)', 8: '(51, 61)'}}

jpp · Accepted Answer · 2018-07-05T01:46:47.957

2

You do not need to use nested loops. Instead, you can use the pd.DataFrame.lookup method available to Pandas.

In this case, you can iterate your Team columns, unpack your coordinates and perform your lookup one series at a time:

from ast import literal_eval

df1 = pd.DataFrame({'matchID': [123124140, 123123124],
                    'TeamA_1.1': ['(1, 2)', '(4, 1)'],
                    'TeamA_1.2': ['(2, 3)', '(3, 4)']})

# convert mapping table columns to integer type
dfEloCross.columns = dfEloCross.columns.astype(int)

# calculate columns which need mapping
team_cols = df1.columns[df1.columns.str.startswith('Team')]

# cycle each column, strip column & row coordinates and use with lookup
for col in team_cols:
    c, r = zip(*df1[col].apply(literal_eval))
    df1[col] = dfEloCross.lookup(r, c)

print(df1)

   TeamA_1.1  TeamA_1.2    matchID
0        1.0       -2.5  123124140
1       -8.0       -5.5  123123124

edited Jul 05 '18 at 01:46

answered Jul 04 '18 at 14:16

jpp

159,742
34
281
339

Thanks for the answer, unfortunately for me, it still returns this error `'One or more row labels was not found'` – Harvey Koh Jul 05 '18 at 00:12
That's interesting. Unfortunately, I can't reproduce your error. If you are able to share some code (as I've done) to demonstrate the problem, I may be able to help. – jpp Jul 05 '18 at 00:24
I would love to, the problem is that its a pretty big code, how do I share it properly? – Harvey Koh Jul 05 '18 at 00:54
See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). – jpp Jul 05 '18 at 01:02
So it looks like you have strings, not tuples, in your dataframe. You can try using `ast.literal_eval` as per my update. – jpp Jul 05 '18 at 01:47
1

Interesting that it works on the dataframe that I gave you but when applied to my larger dataframe it again returns the same error, I'll try to replicate the error on my post again. I just wanted to say thanks for all the help. I'm really grateful. Could it be duplicate errors? The code works on the first column but not the second – Harvey Koh Jul 05 '18 at 02:04
1

Thanks for all the help!! Turns out there were some missing values that resulted in tuples (1,") and returned an error, fixed and it worked!! – Harvey Koh Jul 05 '18 at 02:24

KeyError during Lookup for reference to crosstables

1 Answers1