Python pandas, multindex, slicing

Question

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

I don't get it, take the value 88 in product, I see a 11 in the Value column but I can't see an 8 anywhere in the Time column. — elyase
– elyase, Commented Jan 17, 2015 at 17:17
Like i described above: Product('b','3') = (Time('b','3') - Time('b','2')) * Value('b','3') --> 88 = (10-2) * 11 — Pat Patterson
– Pat Patterson, Commented Jan 17, 2015 at 18:23

elyase · Accepted Answer · 2015-01-17 19:54:32Z

1

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

answered Jan 17, 2015 at 19:54

elyase

41.2k12 gold badges121 silver badges123 bronze badges

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

Comments

champagne_campaign · Accepted Answer · 2015-01-17 20:27:28Z

0

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

answered Jan 17, 2015 at 20:27

champagne_campaign

464 bronze badges

Collectives™ on Stack Overflow

Python pandas, multindex, slicing

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related