Create column in pandas based on two other columns and table

Question

table = pd.DataFrame(data=[[1,2,3],[4,5,6],[7,8,9]],
                 columns=['High','Middle','Low'],
                index=['Blue','Green','Red'])

df = pd.DataFrame(data=[['High','Blue'],
                    ['High','Green'],
                    ['Low','Red'],
                   ['Middle','Blue'],
                    ['Low','Blue'],
                    ['Low','Red']],
             columns=['A','B'])

>>> df
        A      B
0    High   Blue
1    High  Green
2     Low    Red
3  Middle   Blue
4     Low   Blue
5     Low    Red

>>> table
       High  Middle  Low
Blue      1       2    3
Green     4       5    6
Red       7       8    9

I'm trying to add a third column 'C' which is based on the values in the table. So the first row would get a value of 1, the second of 4 etc.

If this would be be a one-dimensional lookup I would convert the table to a dictionary and would use df['C'] = df['A'].map(table). However since this is two-dimensional I can't figure out how to use map or apply.

Ideally I would convert the table to dictionary format so I save it together with other dictionaries in a json, however this is not essential.

If what you're after is a mapping of your table values to categorical pairs in df why don't you try the following: table.unstack().reset_index(). This will melt your table down into the 9 values you want to manipulate. — Dascienz
– Dascienz, Commented Aug 15, 2018 at 20:17

BENY · Accepted Answer · 2018-08-15 20:16:53Z

5

pandas lookup

table.lookup(df.B,df.A)
Out[248]: array([1, 4, 9, 2, 3, 9], dtype=int64)

#table['c']=table.lookup(df.B,df.A)

Or df.apply(lambda x : table.loc[x['B'],x['A']],1) personally do not like apply

answered Aug 15, 2018 at 20:16

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sacuL · Accepted Answer · 2018-08-15 20:13:13Z

3

You can use a merge for this:

df2 = (df.merge(table.stack().reset_index(),
                left_on=['A','B'], right_on=['level_1', 'level_0'])
       .drop(['level_0', 'level_1'], 1)
       .rename(columns={0:'C'}))

>>> df2
        A      B  C
0    High   Blue  1
1    High  Green  4
2     Low    Red  9
3     Low    Red  9
4  Middle   Blue  2
5     Low   Blue  3

answered Aug 15, 2018 at 20:13

sacuL

51.6k9 gold badges88 silver badges115 bronze badges

Collectives™ on Stack Overflow

Create column in pandas based on two other columns and table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related