All geek questions in one place

Pandas - How to scale a column in a multi-index data frame to the top row at each level = 0

I have a multi-index frame 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'], which is calculated by executing the foo function, using the first row of the current level value = 0 from the column ['open'] and the current row ['close'] as arguments. I suspect the solution will include something like:

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

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

if foo:

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

then I would expect the column closeScaledto contain (trimming to 4 decimal places):

                      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
+4
2

div Series, groupby transform first 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

4 :

10000, int 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
+2

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
+2

Source: https://habr.com/ru/post/1665483/

More articles:


All Articles