create a new column for each strategy and add or subtract an amount

Question

I want to extract from a dataset the amount accumulated by strategy according to the transactions between the strategies (from or to):

import pandas as pd

df = pd.DataFrame({"value": [1000, 4000, 2000, 3000],
                   "out": ["cash", "cash", "lending", "DCA"],
                   "in": ["DCA", "lending", "cash", "lending"]})

    value   out      in
0   1000    cash     DCA
1   4000    cash     lending
2   2000    lending  cash
3   3000    DCA      lending

What I expect to do:

    value   out      in       cash    lending   DCA    
0   1000    cash     DCA      -1000   0         1000
1   4000    cash     lending  -5000   4000      1000
2   2000    lending  cash     -3000   2000      1000
3   3000    DCA      lending  -3000   5000     -2000

I don't know how to approach the problem. Any help would be appreciated.

Answer 1

You can try like this:

import pandas as pd

df = pd.DataFrame({"value": [1000, 4000, 2000, 3000],
                   "out": ["cash", "cash", "lending", "DCA"],
                   "in": ["DCA", "lending", "cash", "lending"]})

# get strategies from data source and create an account for each
accounts = {strat: 0 for strat in list(df["out"]) + list(df["in"])}

# add new columns for each strategy to dataframe
for strat in accounts.keys():
    df[strat] = 0

# loop through transactions and enter values to accounts
for i, t in df.iterrows():
    accounts[t["out"]] -= t["value"]
    accounts[t["in"]] += t["value"]
    for strat, v in accounts.items():
        df.loc[i, strat] = v

print(df)

Output:

   value      out       in  cash  lending   DCA
0   1000     cash      DCA -1000        0  1000
1   4000     cash  lending -5000     4000  1000
2   2000  lending     cash -3000     2000  1000
3   3000      DCA  lending -3000     5000 -2000