Making operations with Multindex DataFrame

Question

I have a GroupBy Data Frame that is similar to this one:

I want to create a column named PL as the difference between the Position of the same Product of the same Client buyed on the same day with the previous day's position. Also the first dates should have PL = 0.

The dataframe should look like this

Edit: the unstacked dataframe looks like this:

Link to original dataframe

Dataframe constructor:

data = {'Client': ['Client 1', 'Client 1', 'Client 2', 'Client 2', 'Client 1', 'Client 1', 'Client 2', 'Client 2'],
        'Position Date': ['2022-01-02', '2022-01-02', '2022-01-02', '2022-01-02', '2022-01-03', '2022-01-03', '2022-01-03', '2022-01-03'],
        'Product': ['Product 1', 'Product 4', 'Product 2', 'Product 3', 'Product 1', 'Product 4', 'Product 2', 'Product 3'],
        'Buy Date': ['2022-05-02', '2022-06-02', '2022-03-12', '2022-01-25', '2022-05-02', '2022-06-02', '2022-03-12', '2022-01-25'],
        'Position': [100, 5000, 120, 50, 150, 7000, 200, 100]}
df = pd.DataFrame(data).set_index(['Position Date', 'Client', 'Product', 'Buy Date'])

It's even worse with images, please read [how to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — mozway, Jan 16 '23 at 18:39
It is important to post the dataframe constructor to be able to reproduce the example. It was a pain for me to create the dataframe from the images :-( You can use `df.reset_index().to_dict('list')` for example — Corralien, Jan 16 '23 at 19:00

Corralien · Accepted Answer · 2023-01-16T18:59:13.797

1

You can use groupby with level as parameter of your index levels:

df['PL'] = df.groupby(level=['Client', 'Product', 'Buy Date']).diff().fillna(0)
print(df)

# Output
                                             Position      PL
Position Date Client   Product   Buy Date                    
2022-01-02    Client 1 Product 1 2022-05-02       100     0.0
                       Product 4 2022-06-02      5000     0.0
              Client 2 Product 2 2022-03-12       120     0.0
                       Product 3 2022-01-25        50     0.0
2022-01-03    Client 1 Product 1 2022-05-02       150    50.0
                       Product 4 2022-06-02      7000  2000.0
              Client 2 Product 2 2022-03-12       200    80.0
                       Product 3 2022-01-25       100    50.0

edited Jan 16 '23 at 18:59

answered Jan 16 '23 at 18:50

Corralien

109,409
8
28
52

1

yes it does! Thank you so much. One more thing, if now my function isn't necessarily the diff, but a function like: x = PL / last day position? – João Weckerle Jan 16 '23 at 19:23
Something like = `df.groupby(level=['Client', 'Product', 'Buy Date'])['PL'].apply(lambda x: print(x))`? – Corralien Jan 16 '23 at 19:30
No, I mean using a function that divides the PL of a current day over the position of the previous day. – João Weckerle Jan 16 '23 at 19:34
Can you give an example of a computed value, please? – Corralien Jan 16 '23 at 19:38
On the 2022-01-03 Client 1 Product 1 2022-05-02, the x column would be x = PL / yesterday position = 50 / 100 = 0.5 – João Weckerle Jan 16 '23 at 19:55
`df.groupby(level=['Client', 'Product', 'Buy Date']).apply(lambda x: x['Position'] / x['PL'].iloc[-1])`? – Corralien Jan 16 '23 at 20:12

Making operations with Multindex DataFrame

1 Answers1