CODEWITHSUNDEEP
pythonpandasslicemulti-index
Pat Patterson
Pat Patterson

Reputation: 327

Python pandas, multindex, slicing

I have got a pd.DataFrame

       Time    Value  
a   1  1       1      
    2  2       5
    3  5       7
b   1  1       5
    2  2       9
    3  10      11

I want to multiply the column Value with the column Time - Time(t-1) and write the result to a column Product, starting with row b, but separately for each top level index.

For example Product('1','b') should be (Time('1','b') - Time('1','a')) * Value('1','b'). To do this, i would need a "shifted" version of column Time "starting" at row b so that i could do df["Product"] = (df["Time"].shifted - df["Time"]) * df["Value"]. The result should look like this:

       Time    Value   Product 
a   1  1       1       0
    2  2       5       5
    3  5       7       21
b   1  1       5       0
    2  2       9       9
    3  10      11      88

Upvotes: 2

Views: 146

Answers (2)

champagne_campaign
champagne_campaign

Reputation: 46

Hey this should do what you need it to. Comment if I missed anything.

import pandas as pd
import numpy as np

df = pd.DataFrame({'Time':[1,2,5,1,2,10],'Value':[1,5,7,5,9,11]},
    index = [['a','a','a','b','b','b'],[1,2,3,1,2,3]])

def product(x):
    x['Product'] = (x['Time']-x.shift()['Time'])*x['Value']
    return x

df = df.groupby(level =0).apply(product)
df['Product'] = df['Product'].replace(np.nan, 0)
print df

Upvotes: 0

elyase
elyase

Reputation: 40973

This should do it:

>>> time_shifted = df['Time'].groupby(level=0).apply(lambda x: x.shift())
>>> df['Product'] = ((df.Time - time_shifted)*df.Value).fillna(0)
>>> df
     Time  Value  Product
a 1     1      1        0
  2     2      5        5
  3     5      7       21
b 1     1      5        0
  2     2      9        9
  3    10     11       88

Upvotes: 1

Related Questions