pandas: changing values while repeating rows

Question

Here is my df:

import pandas as pd
import numpy as np

d = {'number': ['12A', '12B', '23A', '23B', '23B'],
     'code': ['Q1A', 'Q1A', 'Q1A', 'Q1A', 'Q1B'],
     'symbole': ['ABC', 'ABC', 'XYZ', 'XZY', 'GFD'],
     'qty': [-100, 100, -20, 20, 0]}
df = pd.DataFrame(data=d)
df

  number code symbole  qty
0    12A  Q1A     ABC -100
1    12B  Q1A     ABC  100
2    23A  Q1A     XYZ  -20
3    23B  Q1A     XZY   20
4    23B  Q1B     ABC   0

I do a basic manipulations:

df = df.loc[df['code'] == 'Q1A']
df['new'] = np.where(df['qty'] > 0, 'buy', 'sell')
df['new2'] = 'cash'
df

  number code symbole  qty   new  new2
0    12A  Q1A     ABC -100  sell  cash
1    12B  Q1A     ABC  100   buy  cash
2    23A  Q1A     XYZ  -20  sell  cash
3    23B  Q1A     XZY   20   buy  cash

Now I want to duplicate each row while I change the value of my 2 new columns.

settings:

inverse qty
if sell, replace by buy, and if buy, replace by sell
if cash, replace by asset, and if asset, replace by cash

Desired output:

  number code symbole  qty   new  new2
0    12A  Q1A     ABC -100  sell  cash
0    12A  Q1A     ABC  100   buy  asset
1    12B  Q1A     ABC  100   buy  cash
1    12B  Q1A     ABC  -100 sell  asset

and so on.

I can't seem to figure it out how to do it. I wrote my manipulations because I thought it might help if a simplify version is possible.

Sam Chats · Accepted Answer · 2019-03-02 21:41:34Z

1

First, do df2 = df[:] # copy df to df2

Inverse qty:

df2["qty"] = -df["qty"]

Inverse new:

df2["new"] = ["buy" if ele  == "sell" else "sell" for ele in df["new"]]

Inverses new2:

df2["new2"] = ["cash" if ele  == "asset" else "asset" for ele in df["new2"]]

Now, concatenate them:

df = pd.concat([df, df2])

Result:

  number code symbole  qty   new   new2
0    12A  Q1A     ABC -100  sell   cash
1    12B  Q1A     ABC  100   buy   cash
2    23A  Q1A     XYZ  -20  sell   cash
3    23B  Q1A     XZY   20   buy   cash
0    12A  Q1A     ABC -100  sell  asset
1    12B  Q1A     ABC  100   buy  asset
2    23A  Q1A     XYZ  -20  sell  asset
3    23B  Q1A     XZY   20   buy  asset

edited Mar 2, 2019 at 21:41

answered Mar 2, 2019 at 21:25

Sam Chats

2,3211 gold badge14 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

pandas: changing values while repeating rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related