Adding columns in one dataframe from calculations based on other dataframe using pandas library

Question

I have a dataframe df1 like:

  cycleName quarter  product        qty  price sell/buy

0       2020      q3    wood           10   100   sell

1       2020      q3    leather        5    200   buy

2       2020      q3    wood           2    200   buy

3       2020      q4    wood          12   40   sell 

4       2020      q4    leather       12   40   sell

5       2021      q1    wood          12   80   sell 

6       2021      q2    leather       12   90   sell

And another dataframe df2 as below. It has unique products of df1:

  product        currentValue

0     wood           20

1     leather        50

I want to create new column in df2, called income which will be based on calculations on df1 data. Example if product is wood the income2020 will be created seeing if cycleName is 2020 and if sell/buy is sell then add quantity * price else subtract quantity * price.

product currentValue income2020

0 wood 20 10 * 100 - 2 * 200 + 12 * 40 (=1080)

1 leather 50 -5 * 200 + 12 * 40 (= -520)

I have a problem statement in python, which I am trying to do using pandas dataframes, which I am very new to.

I am not able to understand how to create that column in df2 based on different conditions on df1.

Ch3steR · Accepted Answer · 2020-08-13 16:53:09Z

1

You can map sell as 1 and buy as -1 using pd.Series.map then multiply columns qty, price and sell/buy using df.prod to get only 2020 cycleName values use df.query and groupby by product and take sum using GroupBy.sum

df_2020 = df.query('cycleName == 2020').copy() # `df[df['cycleName'] == 2020].copy()`
df_2020['sell/buy'] = df_2020['sell/buy'].map({'sell':1, 'buy':-1})
df_2020[['qty', 'price', 'sell/buy']].prod(axis=1).groupby(df_2020['cycleName']).sum()

product
leather    -520
wood       1080
dtype: int64

Note:

Use .copy else you would get SettingWithCopyWarning

To maintain the order use sort=False in df.groupby

(df_2020[['qty', 'price', 'sell/buy']].
    prod(axis=1).
    groupby(df_2020['product'],sort=False).sum()
)

product
wood       1080
leather    -520
dtype: int64

edited Aug 13, 2020 at 16:53

answered Aug 13, 2020 at 16:41

Ch3steR

20.8k4 gold badges34 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

Adding columns in one dataframe from calculations based on other dataframe using pandas library

product currentValue income2020

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

product currentValue income2020

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related