How to create a column using different dataframe without for loop

Question

I have a main dataframe df and another dataframe ext_map:

df = pd.DataFrame(data={'true': [1, 2, 3], 'billed': [4, 5, 6], 'genre':['a','b','c']})

    true    billed  genre
0    1         4    a
1    2         5    b
2    3         6    c

ext_map = pd.DataFrame(data={'label':[1,2,3], 'a':[1.1,1.2,1.3], 'b':[2.1,2.2,2.3],'c':[3.1,3.2,3.3]})

    label   a   b   c
0    1      1.1 2.1 3.1
1    2      1.2 2.2 3.2
2    3      1.3 2.3 3.3

I want to create a new column new_col in my main dataframe such that for each row in df I want to fetch value from columns a, b, c of ext_map based on row.genre and row.true is same as label of ext_map.

[Expected]

    true    billed  genre new_col
0    1         4      a     1.1
1    2         5      b     2.3
2    3         6      c     3.3

df['new_col'] = ext_map.lookup(df.index,ext_map.columns[1:])? — sammywemmy
– sammywemmy, Commented May 5, 2020 at 3:26

loadbox · Accepted Answer · 2020-05-09 12:19:47Z

Sounds like a you should be able to join a modified version of ext_map to df to do this. Some good documentation here: https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html

A rough example (there's probably a more terse way to do this):

You can pivot the data from a wide format to a long format (enumerating each combination of true and genre) using pd.melt()

temp = ext_map.melt(id_vars=['label'], value_vars=['a', 'b', 'c'], var_name='genre', value_name = 'new_col')

Which yields this result:

   label     genre  new_col
0      1        a      1.1
1      2        a      1.2
2      3        a      1.3
3      1        b      2.1
4      2        b      2.2
5      3        b      2.3
6      1        c      3.1
7      2        c      3.2
8      3        c      3.3

Then use pd.merge() to join the dataframes using the lookup method you mentioned:

df.merge(temp, left_on = ['true', 'genre'], right_on = ['label', 'variable'])

Giving you this result:

   true  billed genre  label variable  new_col
0     1       4     a      1        a      1.1
1     2       5     b      2        b      2.2
2     3       6     c      3        c      3.3

Collectives™ on Stack Overflow

How to create a column using different dataframe without for loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related