How can I loop through a list of times, find a skipped/missing time, then fill that time in?

user_11725002 0
Viewed 125

I have a list of files for every 12 minutes in a day. I should have 120 files, but I only have 114. This means there are 6, 12 minute periods of missing data/files. I created an array with datetimes on the left and my data on the right:


"row" -|-     "Time"       -|-       "Rain_Rate"      


1:    2014/04/02 00:00:09  |           0.5

2:    2014/04/02 00:12:20  |           1.1

3:    2014/04/02 00:24:05  |           0.48

4:    2014/04/02 00:36:51  |           2.3

5:    2014/04/02 01:00:08  |           4.1

6:    2014/04/02 01:12:26  |           5.0  

7:    2014/04/02 01:24:02  |           3.2

As you can see there is a missing time between row 4 and 5 (should be 00:48:00). How can I fill this row in by taking the average of the Rain_Rates from either side?

Also, how could I do this if there are 2 times/rows back to back missing?

missing-data pandas interpolation python loops
2 Answers

First, build an DateTimeIndex with 12 minute frequency:

import datetime
import pandas as pd
import numpy as np

start = datetime.datetime(2014, 4, 2)
end = datetime.datetime(2014, 8, 1)  # whenever your time series ends
idx = pd.date_range(start, end, freq='12T')  # 12T = 12 minutes

Next, you have to build a dataframe with your new index:

df = pd.DataFrame(np.nan, index=idx, columns=['dummy']) # you need to provide a column name

I suppose, your Data is in a pd.Series s with a DateTimeIndex, otherwise you have to build one.

Now you can use the full power of DateTimeIndex and pandas:

df['Rain_Rate'] = s
df['Rain_Rate'] = df['Rain_Rate'].interpolate()  # standard is linear approximation

Check out interpolate() for more options.

user_85733360

As an alternative, you could keep your Time series as is, and only add rows into the missing positions you have describded, using the diffence in time between row been greater than 12min as the condition. As a trad-off depending on your previous existing values you won't get perfect 12min. time slices between the genereted row and the next existing value.

import pandas as pd

df = pd.DataFrame([
["2014/04/02 00:00:09",0.5],
["2014/04/02 00:12:20",1.1],
["2014/04/02 00:24:05",0.48],
["2014/04/02 00:36:51",2.3],
["2014/04/02 01:00:08",4.1],
["2014/04/02 01:12:26",5.0],
["2014/04/02 01:24:02",3.2],
["2014/04/02 02:44:02",1.2], # added for test
["2014/04/02 03:54:02",7.72] # added for test
])

df[0] = pd.to_datetime(df[0])
print(df)

delta = df[0].diff()
diff_idx = delta.where(delta > pd.Timedelta("00:12:59"))
print(delta)
idx = df[diff_idx.notnull()].index

td = pd.Timedelta("00:12:00")

for k in idx:
    deltaT = (df.loc[k, 0] - df.loc[k - 1, 0])
    num_missrows =  deltaT // td
    num_missrows -= (-1, 0)[deltaT % td == pd.Timedelta(0)] # don't overlap last time value
    new_avg = df.loc[k - 1, 1]  # previous existing Rain value

    for i in range(1, num_missrows):
        avg = (df.loc[k, 1] + new_avg)/2
        new_row = [[df.loc[k-1, 0] + i * td, avg]]
        new_avg = avg
        df = df.append(new_row)

df = df.sort_values(by=0).reset_index(drop=True)
print(df)

Output from df

                     0        1
0  2014-04-02 00:00:09  0.50000
1  2014-04-02 00:12:20  1.10000
2  2014-04-02 00:24:05  0.48000
3  2014-04-02 00:36:51  2.30000
4  2014-04-02 00:48:51  3.20000 # added row
5  2014-04-02 01:00:08  4.10000
6  2014-04-02 01:12:26  5.00000
7  2014-04-02 01:24:02  3.20000
8  2014-04-02 01:36:02  2.20000 # added row
9  2014-04-02 01:48:02  1.70000 # added row
10 2014-04-02 02:00:02  1.45000 # added row
11 2014-04-02 02:12:02  1.32500 # added row
12 2014-04-02 02:24:02  1.26250 # added row
13 2014-04-02 02:36:02  1.23125 # added row, Not 12min. (~8min. diff.)
14 2014-04-02 02:44:02  1.20000
15 2014-04-02 02:56:02  4.46000 # added row
16 2014-04-02 03:08:02  6.09000 # added row
17 2014-04-02 03:20:02  6.90500 # added row
18 2014-04-02 03:32:02  7.31250 # added row
19 2014-04-02 03:44:02  7.51625 # added row, Not 12min. (~10min. diff.)
20 2014-04-02 03:54:02  7.72000
user_131441430
Related