I have dataframe (MultiIndex) which includes one datetime column with missing values (NaT). The time was collected in 0.2s interval but saved in seconds. Therefore, if not NaT, the dataframe would have 5 duplicates for each second, e.g.
2020-09-30 14:18:44 #for 14:18:44:00
2020-09-30 14:18:44 #for 14:18:44:20
2020-09-30 14:18:44 #for 14:18:44:40
2020-09-30 14:18:44 #for 14:18:44:60
2020-09-30 14:18:44 #for 14:18:44:80
2020-09-30 14:18:45 #for 14:18:45:00
However, life ain't easy. So, my DataFrame looks like below and I would like to interpolate the time in the DT column. The only thing I know is that the measurements were continuous for each Group (Gr column) with 0.2s time interval. What I do not know is:
- at which decisecond the measurement has started for a group.
- which decisecond was saved (one value may represent 0.2 s whereas the next saved output may be 0.6)
Because of the first bullet, I cannot use modulo (i.e. No where x%5 == 0 represents 0.0 s) because x%5 == 0 may represent 0.0, 0.2, 0.4, 0.6 or 0.8 s.
The second bullet prevents me from filling NaT values by time interval starting from the first time occurrence for a group. This is what I have tried (code example at the end) but it doesn't work for e.g. group B as the output would look like this:
B 18 4 NaT
19 7 NaT
20 11 NaT
21 3 NaT
22 7 NaT
23 5 2020-09-30 14:30:43:00
24 23 2020-09-30 14:30:43:02
25 1 2020-09-30 14:30:43:04
26 9 2020-09-30 14:30:43:06 #missing 0.8 sec. here
27 2 2020-09-30 14:30:44:00
28 4 2020-09-30 14:30:44:02
The sample data which I'm providing below are not large but I hope it's enough to present my problem. My original data have >100 rows for each Group (index level=0) so I am sure it is possible to figure out the pattern, I just don't know how to do it.
Sample data, where:
Gr - group MultiIndex level = 0;
No - ID of measurement, may start at e.g. 18 for a Group (it means that 0-17 were excluded from further processing), MultiIndex level = 1;
x - value of measurement;
DT - datetime of measurement.
x DT
Gr No
A 1 2 2020-09-30 14:18:43
2 4 NaT
3 5 NaT
4 2 NaT
5 4 NaT
6 6 2020-09-30 14:18:44
7 9 NaT
8 9 NaT
9 9 NaT
10 9 NaT
11 1 2020-09-30 14:18:45
12 2 NaT
13 6 NaT
14 8 NaT
15 22 NaT
B 18 4 NaT
19 7 NaT
20 11 NaT
21 3 NaT
22 7 NaT
23 5 2020-09-30 14:30:43
24 23 NaT
25 1 NaT
26 9 NaT
27 2 2020-09-30 14:30:44
28 4 NaT
29 3 NaT
30 11 NaT
31 15 NaT
32 20 NaT
C 0 13 NaT
1 6 2020-09-30 14:48:53
2 22 NaT
3 26 NaT
4 2 NaT
5 7 NaT
6 3 2020-09-30 14:48:54
7 6 NaT
8 1 NaT
9 9 NaT
10 2 NaT
11 14 2020-09-30 14:48:55
12 24 NaT
13 20 NaT
14 5 NaT
Sample data:
data = {
'Group': ['A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C'],
'No': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29],
'x': [2, 4, 5, 2, 4, 6, 9, 9, 9, 9, 1, 2, 6, 8, 22, 4, 7, 11, 3, 7, 5, 23, 1, 9, 2, 4, 3, 11, 15, 20, 13, 6, 22, 26, 2, 7, 3, 6, 1, 9, 2, 14, 24, 20, 5, 3, 6, 9, 22, 15, 4, 21, 15, 12, 10, 12, 5, 8, 1, 7, 24, 2, 19, 6, 9, 23, 26, 21, 13, 3, 9, 12, 9, 13, 18, 14, 20, 9, 8, 20, 7, 3, 1, 7, 11, 6, 5, 2, 9, 3],
'DT': ['2020-09-30 17:18:43', None, None, None, None, '2020-09-30 17:18:44', None, None, None, None, '2020-09-30 17:18:45', None, None, None, None, '2020-09-30 17:18:46', None, None, None, None, '2020-09-30 17:18:47', None, None, None, None, '2020-09-30 17:18:48', None, None, None, '2020-09-30 17:18:49', None, None, None, None, None, '2020-09-30 17:30:43', None, None, None, '2020-09-30 17:30:44', None, None, None, None, None, '2020-09-30 17:30:45', None, None, None, None, '2020-09-30 17:30:46', None, None, None, None, '2020-09-30 17:30:47', None, None, None, None, None, '2020-09-30 17:48:53', None, None, None, None, '2020-09-30 17:48:54', None, None, None, None, '2020-09-30 17:48:55', None, None, None, None, '2020-09-30 17:48:56', None, None, None, None, '2020-09-30 17:48:57', None, None, None, None, '2020-09-30 17:48:58', None, None, None]
}
df = pd.DataFrame.from_dict(data)
df = df.set_index(keys = ["Group", "No"])
df["DT"] = pd.to_datetime(df["DT"])
And a piece of code which I have used to calculate the time interval assuming that first time occurrence is a measurement at 0.0 s. Which is wrong. bfill code in a similar manner.
mask = df["DT"].notna() #bool for NaT
g = df["DT"].groupby([pd.Grouper(level = 0), mask.cumsum()]) #group by Group, cumsum for NaT-bool
t = pd.to_timedelta(g.cumcount() * 0.20, unit = "s") #calculate time interval
df["DT"] = df['DT'].groupby(level = 0).ffill() + t #ffill