how can i replace time-series dataframe specific values in pandas?

Question

I have the dataframes below (date/time is multi index) and I want to replace column values in (00:00:00~07:00:00) as a numpy array:

[[ 21.63920663  21.62012822  20.9900515   21.23217008  21.19482458
   21.10839656  20.89631935  20.79977166  20.99176729  20.91567565
   20.87258765  20.76210464  20.50357827  20.55897631  20.38005033
   20.38227309  20.54460993  20.37707293  20.08279925  20.09955877
   20.02559575  20.12390737  20.2917257   20.20056711  20.1589065
   20.41302289  20.48000767  20.55604102  20.70255192]]

     date        time    
2018-01-26  00:00:00    21.65
            00:15:00      NaN
            00:30:00      NaN
            00:45:00      NaN
            01:00:00      NaN
            01:15:00      NaN
            01:30:00      NaN
            01:45:00      NaN
            02:00:00      NaN
            02:15:00      NaN
            02:30:00      NaN
            02:45:00      NaN
            03:00:00      NaN
            03:15:00      NaN
            03:30:00      NaN
            03:45:00      NaN
            04:00:00      NaN
            04:15:00      NaN
            04:30:00      NaN
            04:45:00      NaN
            05:00:00      NaN
            05:15:00      NaN
            05:30:00      NaN
            05:45:00      NaN
            06:00:00      NaN
            06:15:00      NaN
            06:30:00      NaN
            06:45:00      NaN
            07:00:00      NaN
            07:15:00      NaN
            07:30:00      NaN
            07:45:00      NaN
            08:00:00      NaN
            08:15:00      NaN
            08:30:00      NaN
            08:45:00      NaN
            09:00:00      NaN
            09:15:00      NaN
            09:30:00      NaN
            09:45:00      NaN
            10:00:00      NaN
            10:15:00      NaN
            10:30:00      NaN
            10:45:00      NaN
            11:00:00      NaN
Name: temp, dtype: float64
<class 'datetime.time'>

How can I do this?

jezrael · Accepted Answer · 2018-02-02 12:50:30Z

1

You can use slicers:

idx = pd.IndexSlice
df1.loc[idx[:, '00:00:00':'02:00:00'],:] = 1

Or if second levels are times:

import datetime

idx = pd.IndexSlice
df1.loc[idx[:, datetime.time(0, 0, 0):datetime.time(2, 0, 0)],:] = 1

Sample:

print (df1)
                       aaa
date       time           
2018-01-26 00:00:00  21.65
           00:15:00    NaN
           00:30:00    NaN
           00:45:00    NaN
           01:00:00    NaN
           01:15:00    NaN
           01:30:00    NaN
           01:45:00    NaN
           02:00:00    NaN
           02:15:00    NaN
           02:30:00    NaN
           02:45:00    NaN
           03:00:00    NaN
2018-01-27 00:00:00   2.00
           00:15:00    NaN
           00:30:00    NaN
           00:45:00    NaN
           01:00:00    NaN
           01:15:00    NaN
           01:30:00    NaN
           01:45:00    NaN
           02:00:00    NaN
           02:15:00    NaN
           02:30:00    NaN
           02:45:00    NaN
           03:00:00    NaN

idx = pd.IndexSlice
df1.loc[idx[:, '00:00:00':'02:00:00'],:] = 1
print (df1)
                     aaa
date       time         
2018-01-26 00:00:00  1.0
           00:15:00  1.0
           00:30:00  1.0
           00:45:00  1.0
           01:00:00  1.0
           01:15:00  1.0
           01:30:00  1.0
           01:45:00  1.0
           02:00:00  1.0
           02:15:00  NaN
           02:30:00  NaN
           02:45:00  NaN
           03:00:00  NaN
2018-01-27 00:00:00  1.0
           00:15:00  1.0
           00:30:00  1.0
           00:45:00  1.0
           01:00:00  1.0
           01:15:00  1.0
           01:30:00  1.0
           01:45:00  1.0
           02:00:00  1.0
           02:15:00  NaN
           02:30:00  NaN
           02:45:00  NaN
           03:00:00  NaN

EDIT:

For assign array is necessary use numpy.tile for repeat by length of first level unique values:

df1.loc[idx[:, '00:00:00':'02:00:00'],:] = np.tile(np.arange(1, 10),len(df1.index.levels[0]))
print (df1)
                     aaa
date       time         
2018-01-26 00:00:00  1.0
           00:15:00  2.0
           00:30:00  3.0
           00:45:00  4.0
           01:00:00  5.0
           01:15:00  6.0
           01:30:00  7.0
           01:45:00  8.0
           02:00:00  9.0
           02:15:00  NaN
           02:30:00  NaN
           02:45:00  NaN
           03:00:00  NaN
2018-01-27 00:00:00  1.0
           00:15:00  2.0
           00:30:00  3.0
           00:45:00  4.0
           01:00:00  5.0
           01:15:00  6.0
           01:30:00  7.0
           01:45:00  8.0
           02:00:00  9.0
           02:15:00  NaN
           02:30:00  NaN
           02:45:00  NaN
           03:00:00  NaN

More general solution with generated array by length of slice:

idx = pd.IndexSlice
len0 = df1.loc[idx[df1.index.levels[0][0], '00:00:00':'02:00:00'],:].shape[0]
len1 = len(df1.index.levels[0])
df1.loc[idx[:, '00:00:00':'02:00:00'],:] = np.tile(np.arange(1, len0 + 1), len1)

Tested with times:

import datetime
idx = pd.IndexSlice
arr =np.tile(np.arange(1, 10),len(df1.index.levels[0]))
df1.loc[idx[:, datetime.time(0, 0, 0):datetime.time(2, 0, 0)],:] = arr
print (df1)
                     aaa
date       time         
2018-01-26 00:00:00  1.0
           00:15:00  2.0
           00:30:00  3.0
           00:45:00  4.0
           01:00:00  5.0
           01:15:00  6.0
           01:30:00  7.0
           01:45:00  8.0
           02:00:00  9.0
           02:15:00  NaN
           02:30:00  NaN
           02:45:00  NaN
           03:00:00  NaN
2018-01-27 00:00:00  1.0
           00:15:00  2.0
           00:30:00  3.0
           00:45:00  4.0
           01:00:00  5.0
           01:15:00  6.0
           01:30:00  7.0
           01:45:00  8.0
           02:00:00  9.0
           02:15:00  NaN
           02:30:00  NaN
           02:45:00  NaN
           03:00:00  NaN

EDIT:

Last was problem found - my solution wokrs with one column DataFrame, but if working with Series need remove one ::

arr = np.array([[ 21.63920663, 21.62012822, 20.9900515, 21.23217008, 21.19482458, 21.10839656, 
                 20.89631935, 20.79977166, 20.99176729, 20.91567565, 20.87258765, 20.76210464,
                 20.50357827, 20.55897631, 20.38005033, 20.38227309, 20.54460993, 20.37707293, 
                 20.08279925, 20.09955877, 20.02559575, 20.12390737, 20.2917257, 20.20056711, 
                 20.1589065, 20.41302289, 20.48000767, 20.55604102, 20.70255192]])

import datetime
idx = pd.IndexSlice
df1.loc[idx[:, datetime.time(0, 0, 0): datetime.time(7, 0, 0)]] = arr[0]
                                                          ---^^^

edited Feb 2, 2018 at 12:50

answered Feb 2, 2018 at 6:07

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

17 Comments

jerry han Over a year ago

excellent .. i have one more question.. if i need to change value [1,2,3,4,5,6,7,8,9] .. then how can i apply ii?

jezrael Over a year ago

Data are nice - always si same length of rows between 00:00:00 and 02:00:00 in all levels?

jerry han Over a year ago

could you input 1~9 values as numpy array ?

jezrael Over a year ago

It is np.arange(1, 10)

jerry han Over a year ago

Could you chk when multi index is datetine format how to change cod?

|

Collectives™ on Stack Overflow

how can i replace time-series dataframe specific values in pandas?

1 Answer 1

17 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Related