Panda concat multiple timeseries with grouping and extend missing data

Question

I have multiple timeseries data imported from CSV files. Those data all have time stamps but the timestamps don't always match:

Time series 1:

                      UUT  Data
DateTime                                           
2017-11-21 18:54:31  uut1  1
2017-11-22 02:26:48  uut1  2
2017-11-22 10:19:44  uut1  3
2017-11-22 15:11:28  uut1  6
2017-11-22 23:21:58  uut1  7

Time series 2:

                      UUT  Data
DateTime
2017-11-21 18:47:29  uut2  1
2017-11-22 02:26:49  uut2  2
2017-11-22 10:19:44  uut2  3
2017-11-22 15:17:47  uut2  4
2017-11-22 23:21:58  uut2  5
2017-11-23 07:10:56  uut2  6
2017-11-23 15:15:48  uut2  7
2017-11-24 12:16:58  uut2  9

I can use concat function to join them together and groupby 'UUT', however, how can I fill up the empty time slot with previous value to have the final table looks similar to:

DateTime          UUT   Data
11/21/17 18:47:29 uut1  1
11/21/17 18:54:31       1
11/22/17 2:26:48        2
11/22/17 2:26:49        2
11/22/17 10:19:44       3
11/22/17 15:11:28       6
11/22/17 15:17:47       6
11/22/17 23:21:58       7
11/23/17 7:10:56        8
11/23/17 15:15:48       8
11/23/17 15:22:16       9
11/24/17 12:16:58       11 
11/21/17 18:47:29 uut2  1
11/21/17 18:54:31       1
11/22/17 2:26:48        1
11/22/17 2:26:49        2
11/22/17 10:19:44       3
11/22/17 15:11:28       3
11/22/17 15:17:47       4
11/22/17 23:21:58       5
11/23/17 7:10:56        6
11/23/17 15:15:48       7
11/23/17 15:22:16       7
11/24/17 12:16:58       9

or to this:

DateTime            uut1 uut2
11/21/17 18:47:29   1   1
11/21/17 18:54:31   1   1
11/22/17 2:26:48    2   1
11/22/17 2:26:49    2   2
11/22/17 10:19:44   3   3
11/22/17 15:11:28   6   3
11/22/17 15:17:47   6   4
11/22/17 23:21:58   7   5
11/23/17 7:10:56    8   6
11/23/17 15:15:48   8   7
11/23/17 15:22:16   9   7
11/24/17 12:16:58   11  9

My end goal is to be able to plot both uut1 and uut2 data on a single timeseries plot.

Answer 1

Find the union of the indexes with index.union, reindex the dataframes, concat and then pivot to get your desired output.

i = df1.index.union(df2.index)
df1 = df1.reindex(i).reset_index().bfill().ffill()
df2 = df2.reindex(i).reset_index().bfill().ffill()

df = pd.concat([df1, df2]).pivot('DateTime', 'UUT', 'Data')
df

UUT                  uut1  uut2
DateTime                       
2017-11-21 18:47:29   1.0   1.0
2017-11-21 18:54:31   1.0   2.0
2017-11-22 02:26:48   2.0   2.0
2017-11-22 02:26:49   2.0   2.0
2017-11-22 10:19:44   3.0   3.0
2017-11-22 15:11:28   6.0   4.0
2017-11-22 15:17:47   6.0   4.0
2017-11-22 23:21:58   7.0   5.0
2017-11-23 07:10:56   7.0   6.0
2017-11-23 15:15:48   7.0   7.0
2017-11-24 12:16:58   7.0   9.0

And finally, to plot, use df.plot -

df.plot(subplots=True, drawstyle='steps-post')
plt.show()