How to divide one column by another where one dataframe's column value corresponds to another dataframe's column's value in Python Pandas?

user_15070786 0
Viewed 811

Consider the following data frames in Python Pandas:

DataframeA

ColA ColB ColC
1 dog 439
1 cat 932
1 frog 932
2 dog 2122
2 cat 454
2 frog 773
3 dog 9223
3 cat 3012
3 frog 898

DataframeB

ColD ColE
1 101
2 314
3 124

To note, ColB just repeats it's string values as ColA iterates upwards. ColC and ColE are random. ColA and ColD correspond. ColD values will never have repeats (like a map).

I want to divide ColC by ColE wherever ColA == ColD and ideally put the resulting value in a new column in DataframeA (or just have it overwrite ColC). The resulting value should be able to have decimals.

How can I do this in Python Pandas?

data-science pandas dataframe python-3.x python
2 Answers

IIUC, you can merge and then divide , just for a new df use below else use @Manakin's answer.

out = (df_a.merge(df_b,left_on='ColA',right_on='ColD',how='left')
.assign(new=lambda x:x['ColC'].div(x['ColE'])).reindex(columns=[*df_a.columns]+['new']))

print(out)

   ColA  ColB  ColC        new
0     1   dog   439   4.346535
1     1   cat   932   9.227723
2     1  frog   932   9.227723
3     2   dog  2122   6.757962
4     2   cat   454   1.445860
5     2  frog   773   2.461783
6     3   dog  9223  74.379032
7     3   cat  3012  24.290323
8     3  frog   898   7.241935
user_98406370

you can use .map

if you have multiple keys to join on then merge would be more useful as demonstrated by @anky

df1['ColF'] = df1['ColC'] / df1['ColA'].map(df2.set_index(['ColD'])['ColE'])

ColA   ColB  ColC       ColF
0     1   dog    439   4.346535
1     1   cat    932   9.227723
2     1  frog    932   9.227723
3     2   dog   2122   6.757962
4     2   cat    454   1.445860
5     2  frog    773   2.461783
6     3   dog   9223  74.379032
7     3   cat   3012  24.290323
8     3  frog    898   7.241935
user_93751020
Related