Create column from multiple dataframes

Question

I need to create some new columns based on the value of a dataframe filed and a look up dataframe with some rates.

Having df1 as

    zone    hh      hhind
0   14      112.0   3.4
1   15      5.0     4.4
2   16      0.0     1.0

and a look_up df as

    ind     per1    per2    per3    per4
0   1.0     1.000   0.000   0.000   0.000
24  3.4     0.145   0.233   0.165   0.457
34  4.4     0.060   0.114   0.075   0.751

how can i update df1.hh1 by multiplying the look_up.per1 based on df1.hhind and lookup.ind

    zone    hh      hhind  hh1
 0  14      112.0   3.4    16.240
 1  15      5.0     4.4    0.300
 2  16      0.0     1.0    0.000

at the moment im getting the result by merging the tables and the doing the arithmetic.

r = pd.merge(df1, look_up, left_on="hhind", right_on="ind")
r["hh1"] = r.hh *r.per1

i'd like to know if there is a more straight way to accomplish this by not merging the tables?

Nickil Maveli · Accepted Answer · 2017-02-15 16:59:21Z

1

You could first set hhind and ind as the index axis of df1 and look_up dataframes respectively. Then, multiply corresponding elements in hh and per1 element-wise.

Map these results to the column hhind and assign these to a new column later as shown:

mapper = df1.set_index('hhind')['hh'].mul(look_up.set_index('ind')['per1'])
df1.assign(hh1=df1['hhind'].map(mapper))

answered Feb 15, 2017 at 16:59

Nickil Maveli

29.8k10 gold badges86 silver badges88 bronze badges

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

Comments

akasolace · Accepted Answer · 2017-02-15 17:06:12Z

1

another solution:

df1['hh1'] = (df1['hhind'].map(lambda x: look_up[look_up["ind"]==x]["per1"])) * df1['hh']

answered Feb 15, 2017 at 17:06

akasolace

6341 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

Create column from multiple dataframes

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