Pandas - How to scale a column in a multi-index dataframe to the top row in each level=0 group

Question

I have a multi-index dataframe dfu:

                      open   high     low   close
Date       Time
2016-11-28 09:43:00  26.03  26.03  26.030  26.030
           09:48:00  25.90  25.90  25.760  25.760
           09:51:00  26.00  26.00  25.985  25.985
2016-11-29 09:30:00  24.98  24.98  24.98  24.9800
           09:33:00  25.00  25.00  24.99  24.9900
           09:35:00  25.33  25.46  25.33  25.4147

I would like to create a new column, ['closeScaled'] that is calculated by doing a function, foo, using the first row of the current level=0 value from the ['open'] column and the current row['close'] as arguments. I suspect the solution will involve something looking like:

dfu['closeScaled']=dfu.apply(lambda x: foo(*get first row of current date*[0],x[3]))

I just can't seem to figure out the get first row of current level=0 part.

if foo is:

def foo(firstOpen,currentClose):
    return (currentClose / firstOpen)

then I would expect the closeScaled column to contain (truncating to 4 decimals):

                      open   high     low   close  closeScaled
Date       Time
2016-11-28 09:43:00  26.03  26.03  26.030  26.030  1.0000
           09:48:00  25.90  25.90  25.760  25.760  0.9896
           09:51:00  26.00  26.00  25.985  25.985  0.9982
2016-11-29 09:30:00  24.98  24.98  24.98  24.9800  1.0000
           09:33:00  25.00  25.00  24.99  24.9900  1.0004
           09:35:00  25.33  25.46  25.33  25.4147  1.0174

jezrael · Accepted Answer · 2017-01-02T07:09:21.653

You can divide by div Series created by groupby with transform first and last round:

print (dfu.groupby(level=0)['open'].transform('first'))
Date        Time    
2016-11-28  09:43:00    26.03
            09:48:00    26.03
            09:51:00    26.03
2016-11-29  09:30:00    24.98
            09:33:00    24.98
            09:35:00    24.98
Name: open, dtype: float64

dfu['closeScaled'] = dfu.close.div(dfu.groupby(level=0)['open'].transform('first')).round(4)
print (dfu)
                      open   high     low    close  closeScaled
Date       Time                                                
2016-11-28 09:43:00  26.03  26.03  26.030  26.0300       1.0000
           09:48:00  25.90  25.90  25.760  25.7600       0.9896
           09:51:00  26.00  26.00  25.985  25.9850       0.9983
2016-11-29 09:30:00  24.98  24.98  24.980  24.9800       1.0000
           09:33:00  25.00  25.00  24.990  24.9900       1.0004
           09:35:00  25.33  25.46  25.330  25.4147       1.0174

If need truncate float values to 4 decimals:

First multiple by 10000, convert to int and divide by 10000.

dfu['closeScaled'] = dfu.close.div(dfu.groupby(level=0)['open'].transform('first'))
                              .mul(10000).astype(int).div(10000)
print (dfu)
                      open   high     low    close  closeScaled
Date       Time                                                
2016-11-28 09:43:00  26.03  26.03  26.030  26.0300       1.0000
           09:48:00  25.90  25.90  25.760  25.7600       0.9896
           09:51:00  26.00  26.00  25.985  25.9850       0.9982
2016-11-29 09:30:00  24.98  24.98  24.980  24.9800       1.0000
           09:33:00  25.00  25.00  24.990  24.9900       1.0004
           09:35:00  25.33  25.46  25.330  25.4147       1.0174

#http://stackoverflow.com/a/783927/2901002
def truncate(f, n):
    '''Truncates/pads a float f to n decimal places without rounding'''
    s = '{}'.format(f)
    if 'e' in s or 'E' in s:
        return '{0:.{1}f}'.format(f, n)
    i, p, d = s.partition('.')
    return '.'.join([i, (d+'0'*n)[:n]])

dfu['closeScaled'] = dfu.close.div(dfu.groupby(level=0)['open'].transform('first'))
                        .apply(lambda x: truncate(x,4)).astype(float)
print (dfu)
                      open   high     low    close  closeScaled
Date       Time                                                
2016-11-28 09:43:00  26.03  26.03  26.030  26.0300       1.0000
           09:48:00  25.90  25.90  25.760  25.7600       0.9896
           09:51:00  26.00  26.00  25.985  25.9850       0.9982
2016-11-29 09:30:00  24.98  24.98  24.980  24.9800       1.0000
           09:33:00  25.00  25.00  24.990  24.9900       1.0004
           09:35:00  25.33  25.46  25.330  25.4147       1.0174

score 2 · Answer 2 · answered Jan 02 '17 at 08:35

Using groupby + apply + lambda

df.groupby(level=0).apply(
    lambda df: df.assign(closeScaled=df.close.div(df.open.iloc[0]).round(4))
)

                      open   high     low    close  closeScaled
Date       Time                                                
2016-11-28 09:43:00  26.03  26.03  26.030  26.0300       1.0000
           09:48:00  25.90  25.90  25.760  25.7600       0.9896
           09:51:00  26.00  26.00  25.985  25.9850       0.9983
2016-11-29 09:30:00  24.98  24.98  24.980  24.9800       1.0000
           09:33:00  25.00  25.00  24.990  24.9900       1.0004
           09:35:00  25.33  25.46  25.330  25.4147       1.0174

Pandas - How to scale a column in a multi-index dataframe to the top row in each level=0 group

2 Answers2