CODEWITHSUNDEEP
pythonpython-3.xpandasdataframelookup
Harvey Koh
Harvey Koh

Reputation: 103

KeyError during Lookup for reference to crosstables

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)'}}

Upvotes: 2

Views: 268

Answers (1)

jpp
jpp

Reputation: 164653

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

Upvotes: 2

Related Questions