"Indexing" a price series to a starting time point (index level = 100) with pandas data frame : P(i,t) / P(i)

Question

I have a pandas data frame, where datetime is the index of the data frame (I use t=0 for simplification, in fact there is something like 20170101 09:30:00)

datetime    Stock A    Stock B
t=0           5          20
t=1           6          30
t=2           8          25
t=3           4          20

and I would like to return:

datetime    Stock A    Stock B
t=0           100        100
t=1           120        150
t=2           140        125
t=3           80         100

in mathematical terms: Index(i, t) = P(i, t) / P(i, 0).

I tried

df_norm =  df[0:] / df[0:1]
print(df_norm)

which gives me an error.

edit1: I tried option 3 which works fine (couldn't try on NaN's yet, but at least it does not create an NaN for the first obs (which is caused by pctchange)). I wonder also that after performing, my datetime is not the set index anymore, which is easy to fix by just re-assigning it.

Now I am trying now to wrap it in a function, but I think the index is causing a problem (actually same error as with my "first" attempt):

def norming(x):
    return x.assign(**x.drop('datetime', 1).pipe(
    lambda d: d.div(d.shift().bfill()).cumprod()))

edit2: if my column datetime is an index, i.e.

df_norm.set_index(['datetime'], inplace = True)

I'll get an error though, what would I need to change?

Answer 1

Option 1

df.set_index('datetime').pct_change().fillna(0) \
  .add(1).cumprod().mul(100).reset_index()

  datetime  Stock A  Stock B
0      t=0    100.0    100.0
1      t=1    120.0    150.0
2      t=2    160.0    125.0
3      t=3     80.0    100.0

---

Option 2

def idx_me(a):
    a = np.asarray(a)
    r = np.append(1, a[1:] / a[:-1])
    return r.cumprod() * 100

df.assign(**df.drop('datetime', 1).apply(idx_me))

  datetime  Stock A  Stock B
0      t=0    100.0    100.0
1      t=1    120.0    150.0
2      t=2    160.0    125.0
3      t=3     80.0    100.0

---

Option 3

df.assign(**df.drop('datetime', 1).pipe(
    lambda d: d.div(d.shift().bfill()).cumprod().mul(100)))

  datetime  Stock A  Stock B
0      t=0    100.0    100.0
1      t=1    120.0    150.0
2      t=2    160.0    125.0
3      t=3     80.0    100.0

Answer 2

Seems like

p=100/df.iloc[0,1:]
df.iloc[:,1:]*=p
df
Out[1413]: 
  datetime StockA StockB
0      t=0    100    100
1      t=1    120    150
2      t=2    160    125
3      t=3     80    100