Pandas : Mapping one column values using other dataframe column

Question

I have two dataframes as described above

I would like to create in the second table an additional feature (Col_to_create) related to the value of feature A.

Table 2 has more than 800 000 samples so that I ask for a faster way to do that.

First table:

Second table:

id   Refer_to_A     Col_to_create
0        3               500
1        1               100
2        3               500
3        2               400
4        1               100

Are you supposed to optimize a join

pissall
– pissall

2019-12-04 16:53:38 +00:00
Commented Dec 4, 2019 at 16:53 — pissall
– pissall, Commented Dec 4, 2019 at 16:53
I didn t understand your question

Diana_doudi
– Diana_doudi

2019-12-04 16:58:52 +00:00
Commented Dec 4, 2019 at 16:58 — Diana_doudi
– Diana_doudi, Commented Dec 4, 2019 at 16:58

Mykola Zotko · Accepted Answer · 2019-12-04 18:39:37Z

3

You can use the method map:

df2['Col_to_create'] = df2['Refer_to_A'].map(df1.set_index('a')['b'])

Output:

    Refer_to_A  Col_to_create
id                           
0            3            500
1            1            100
2            3            500
3            2            400
4            1            100

edited Dec 4, 2019 at 18:39

answered Dec 4, 2019 at 18:34

Mykola Zotko

18.2k6 gold badges88 silver badges90 bronze badges

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

Comments

Aaditya Ura · Accepted Answer · 2019-12-04 17:10:53Z

2

One possible way is you can apply the function on new column of the dataset :

If your dataset is :

dataframe_a = pd.DataFrame({'a': [1,2,3], 'b': [100,400,500]})
dataframe_b = pd.DataFrame({'Refer_to_A': [3,1,3,2,1]})

You can try something like :

dataframe_b['Col_to_create'] = dataframe_b['Refer_to_A'].apply(lambda col: dataframe_a['b'][col-1])

output:

   Refer_to_A  Col_to_create
0           3            500
1           1            100
2           3            500
3           2            400
4           1            100

answered Dec 4, 2019 at 17:10

Aaditya Ura

12.8k7 gold badges60 silver badges96 bronze badges

Collectives™ on Stack Overflow

Pandas : Mapping one column values using other dataframe column

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