CODEWITHSUNDEEP
pythonpandas
honeymoon
honeymoon

Reputation: 2520

Pandas calculate percent from two data frames

I have two dataframes:

A   B   C   D           
X   Y   1.0 49.453125
        2.0 67.767857
        3.0 48.770833
        4.0 43.583333


A   E   F   G   C   H
X   Z   1.0 807 1.0 34.375000
        2.0 808 1.0 35.375000
        1.0 909 2.0 1.750000
        2.0 910 2.0 48.750000

Now I would like to calculate the relative percentage of column H in data frame 2 with the corresponding value of column D data frame 1 if column C of both data frames are identical:

34.375000 * 100 / 49.453125 = 69.51
35.375000 * 100 / 49.453125 = 71.53
 1.750000 * 100 / 67.767857 = 2.58
48.750000 * 100 / 67.767857 = 71.94

How can I achieve this?

Upvotes: 1

Views: 1338

Answers (1)

jezrael
jezrael

Reputation: 862771

Use map if all values of df2['C'] exist in df1['C'] and then multiple by mul and divide by div:

mapping = df1.set_index('C')['D']
s = df2['H'].mul(100).div(df2['C'].map(mapping))
print (s)
0    69.510269
1    71.532385
2     2.582345
3    71.936759
dtype: float64

Another solution is use merge with inner join for new DataFrame:

df = df1.merge(df2, on='C')
s = df['H'].mul(100).div(df['D'])
print (s)
0    69.510269
1    71.532385
2     2.582345
3    71.936759
dtype: float64

Upvotes: 1

Related Questions