CODEWITHSUNDEEP
pythonpandasdataframe
Berto
Berto

Reputation: 67

Conditional operations on a set of columns

With this two simplified dataframes 

df1=pd.DataFrame({'COUNTRY':['A','A','A','B','B','C','C','C'],'YEAR':[1,2,3,1,2,1,2,3],'VALUE':[100,100,100,100,100,100,100,100]})
df2=pd.DataFrame({'COUNTRY':['A','A','B','B','C'],'YEAR':[1,3,1,2,3],'PROPORTION':[0.5,0.1,0.5,0.2,0.1]})

df1

  COUNTRY  YEAR  VALUE
0       A     1    100
1       A     2    100
2       A     3    100
3       B     1    100
4       B     2    100
5       C     1    100
6       C     2    100
7       C     3    100

df2

  COUNTRY  YEAR  PROPORTION
0       A     1         0.5
1       A     3         0.1
2       B     1         0.5
3       B     2         0.2
4       C     3         0.1

How can I multiply df1.VALUE by df2.PROPORTION matching df1.COUNTRY=df2.COUNTRY and df1.YEAR=df2.YEAR resulting in

VALUE=[50,100,10,50,20,100,100,10]

Upvotes: 2

Views: 58

Answers (4)

Scott Boston
Scott Boston

Reputation: 153460

Another way to do this is to use pandas intrinsic data alignment with indexes. Use set_index and mul with fill_value=1.

df1i = df1.set_index(['COUNTRY','YEAR'])
df2i = df2.set_index(['COUNTRY','YEAR'])

df2i['PROPORTION'].mul(df1i['VALUE'], fill_value=1).rename('PROPORTION').reset_index()

Output:

  COUNTRY  YEAR  PROPORTION
0       A     1        50.0
1       A     2       100.0
2       A     3        10.0
3       B     1        50.0
4       B     2        20.0
5       C     1       100.0
6       C     2       100.0
7       C     3        10.0

Upvotes: 2

moys
moys

Reputation: 8033

df1['VALUE']=df1.merge(df2,how='left').fillna(1)['PROPORTION'].mul(df1['VALUE'])

Upvotes: 0

niuer
niuer

Reputation: 1669

Try this:

df1=pd.DataFrame({'COUNTRY':['A','A','A','B','B','C','C','C'],'YEAR':[1,2,3,1,2,1,2,3],'VALUE':[100,100,100,100,100,100,100,100]})
df2=pd.DataFrame({'COUNTRY':['A','A','B','B','C'],'YEAR':[1,3,1,2,3],'PROPORTION':[0.5,0.1,0.5,0.2,0.1]})
df = df1.merge(df2, on=['COUNTRY', 'YEAR'], how='left').fillna(1)
df['res'] = df['VALUE']*df['PROPORTION']
df

The output:

    COUNTRY YEAR    VALUE   PROPORTION  res
0   A   1   100 0.5 50.0
1   A   2   100 1.0 100.0
2   A   3   100 0.1 10.0
3   B   1   100 0.5 50.0
4   B   2   100 0.2 20.0
5   C   1   100 1.0 100.0
6   C   2   100 1.0 100.0
7   C   3   100 0.1 10.0

Upvotes: 0

BENY
BENY

Reputation: 323226

You can check with merge then mul

df1['New Value']=df1.merge(df2,how='left').PROPORTION.mul(df1.VALUE)

Upvotes: 1

Related Questions