CODEWITHSUNDEEP
python-3.xpandasdataframe
Dcook
Dcook

Reputation: 961

data transformation-percentage calculation by comparing two dataframes in pandas

I have a input dataframe which has some fixed percentage across different account.

df1:

Product AU-MGT  AU-STG  AU-PRD  
prod1   20      60      70  
prod2   80      40      30

The other dataframe is : df2:

       Account Product  cost
0       AU-MGT  COMMON  4
1       AU-STG  COMMON  6
2       AU-PRD  COMMON  8

df2 only gas one product -'COMMON'. I want to split the common cost across accounts and different product based on the percentage dataframe. The output I want is:

       Account Product  cost
0       AU-MGT  prod-1  0.8
1       AU-MGT  prod-2  3.2
2       AU-STG  prod-1  3.6
3       AU-STG  prod-2  2.4
4       AU-PRD  prod-1  5.6
5       AU-PRD  prod-2  2.4

How can I achieve this using pandas? I tried with dataframe.unstack() for df1.

Upvotes: 1

Views: 37

Answers (2)

Suhas Mucherla
Suhas Mucherla

Reputation: 1413

You need to first transpose df1 and unstack and then merge with df2 and operate:

df1=pd.DataFrame({'prod':['prod1','prod2'],'AU-MGT':[20,80],'AU-STG':[60,40],'AU-PRD':[70,30]})
df2=pd.DataFrame({'Account':['AU-MGT','AU-STG','AU-PRD'],'Product':['COMMON','COMMON','COMMON'],'Cost':[4,6,8]})
df1=df1.set_index('prod').T.unstack().reset_index()
df1.columns=['prod','Account','Cost%']
df_new=df1.merge(df2,how='outer')
df_new['Cost_new']=df_new['Cost']*(df_new['Cost%']/100)
df_new=df_new[['Account','prod','Cost_new']]

Out[89]: 
  Account   prod  Cost_new
0  AU-MGT  prod1       0.8
1  AU-MGT  prod2       3.2
2  AU-STG  prod1       3.6
3  AU-STG  prod2       2.4
4  AU-PRD  prod1       5.6
5  AU-PRD  prod2       2.4

Upvotes: 1

Andrej Kesely
Andrej Kesely

Reputation: 195458

Try:

x = (
    df1.set_index("Product")
    .stack()
    .unstack(level=0)
    .merge(df2, left_index=True, right_on="Account")
)

x = x.melt(["Account", "Product", "cost"])
x["cost"] *= x["value"] / 100
x = (
    x.sort_values(by="Account")
    .drop(columns=["Product", "value"])
    .rename(columns={"variable": "Product"})
)
print(x)

Prints:

  Account  cost Product
0  AU-MGT   0.8   prod1
3  AU-MGT   3.2   prod2
2  AU-PRD   5.6   prod1
5  AU-PRD   2.4   prod2
1  AU-STG   3.6   prod1
4  AU-STG   2.4   prod2

Upvotes: 1

Related Questions