How to impute missing value in time series data with the value of the same day and time from the previous week(day) in python

user_8573717 0
Viewed 535

I have a dataframe with columns of timestamp and energy usage. The timestamp is taken for every min of the day i.e., a total of 1440 readings for each day. I have few missing values in the data frame.

I want to impute those missing values with the mean of the same day, same time from the last two or three week. This way if the previous week is also missing, I can use the value for two weeks ago.

Here's a example of the data:

                    mains_1
timestamp   
2013-01-03 00:00:00 155.00
2013-01-03 00:01:00 154.00
2013-01-03 00:02:00 NaN
2013-01-03 00:03:00 154.00
2013-01-03 00:04:00 153.00
... ...
2013-04-30 23:55:00 NaN
2013-04-30 23:56:00 182.00
2013-04-30 23:57:00 181.00
2013-04-30 23:58:00 182.00
2013-04-30 23:59:00 182.00

Right now I have this line of code:

df['mains_1'] = (df
    .groupby((df.index.dayofweek * 24) + (df.index.hour) + (df.index.minute / 60))
    .transform(lambda x: x.fillna(x.mean()))
)

So what this does is it uses the average of the usage from the same hour of the day on the whole dataset. I want it to be more precise and use the average of the last two or three weeks.

missing-data pandas time-series python
1 Answers

You can concat together the Series with shift in a loop, as the index alignment will ensure it's matching on the previous weeks with the same hour. Then take the mean and use .fillna to update the original

Sample Data

import pandas as pd
import numpy as np

np.random.seed(5)
df = pd.DataFrame(index=pd.date_range('2010-01-01 10:00:00', freq='W', periods=10),
                  data = np.random.choice([1,2,3,4, np.NaN], 10),
                  columns=['mains_1'])
#                     mains_1
#2010-01-03 10:00:00      4.0
#2010-01-10 10:00:00      1.0
#2010-01-17 10:00:00      2.0
#2010-01-24 10:00:00      1.0
#2010-01-31 10:00:00      NaN
#2010-02-07 10:00:00      4.0
#2010-02-14 10:00:00      1.0
#2010-02-21 10:00:00      1.0
#2010-02-28 10:00:00      NaN
#2010-03-07 10:00:00      2.0

Code

# range(4) for previous 3 weeks. 
df1 = pd.concat([df.shift(periods=x, freq='W') for x in range(4)], axis=1)
#                     mains_1  mains_1  mains_1  mains_1
#2010-01-03 10:00:00      4.0      NaN      NaN      NaN
#2010-01-10 10:00:00      1.0      4.0      NaN      NaN
#2010-01-17 10:00:00      2.0      1.0      4.0      NaN
#2010-01-24 10:00:00      1.0      2.0      1.0      4.0
#2010-01-31 10:00:00      NaN      1.0      2.0      1.0
#2010-02-07 10:00:00      4.0      NaN      1.0      2.0
#2010-02-14 10:00:00      1.0      4.0      NaN      1.0
#2010-02-21 10:00:00      1.0      1.0      4.0      NaN
#2010-02-28 10:00:00      NaN      1.0      1.0      4.0
#2010-03-07 10:00:00      2.0      NaN      1.0      1.0
#2010-03-14 10:00:00      NaN      2.0      NaN      1.0
#2010-03-21 10:00:00      NaN      NaN      2.0      NaN
#2010-03-28 10:00:00      NaN      NaN      NaN      2.0

df['mains_1'] = df['mains_1'].fillna(df1.mean(axis=1))

print(df)

                      mains_1
2010-01-03 10:00:00  4.000000
2010-01-10 10:00:00  1.000000
2010-01-17 10:00:00  2.000000
2010-01-24 10:00:00  1.000000
2010-01-31 10:00:00  1.333333
2010-02-07 10:00:00  4.000000
2010-02-14 10:00:00  1.000000
2010-02-21 10:00:00  1.000000
2010-02-28 10:00:00  2.000000
2010-03-07 10:00:00  2.000000
user_43333590
Related