Merge two dataframes based on some similar columns-CodePudding

I am guessing the answer is out there but my googling is not finding it.

I have two dataframes (eventually will have a bunch) that I want to merge together. Eventually this dataframe should grow into something with many rows and columns.

df1

Init Date   2021-01-01 00:00:00 2021-01-01 06:00:00 2021-01-01 12:00:00 2021-01-01 18:00:00
2021-01-01  37.3                35.29               35.72               41.28   

df2
Init Date   2021-01-01 06:00:00 2021-01-01 12:00:00 2021-01-01 18:00:00 2021-01-02 00:00:00
2021-01-01 06:00:00 35.35       35.54               41.12               42.33

The general structure is this: each dataframe has an Init Date, which are sequentially ordered every 6 hours. Each column after Init Date represents a time in which there is a value, which also are sequentially ordered every 6 hours. The trick is that as you increase the Init Date time, so also does the first hour of data. As such, when we get the desired output we would expect that the second row, first column that is not Init Date to have no data, and so on down the line (third row would have first two columns empty). I tried several pd.merge() and pd.concat() options but could not find the correct one.

Expected output would be:

df3
Init Date   2021-01-01 00:00:00 2021-01-01 06:00:00 2021-01-01 12:00:00 2021-01-01 18:00:00 2021-01-02 00:00:00
2021-01-01  37.3                35.29               35.72               41.28
2021-01-01 06:00:00             35.35               35.54               41.12               42.25

CodePudding user response：

pd.concat([df, df2])


    Init Date   2021-01-01 00:00:00     2021-01-01 06:00:00     2021-01-01 12:00:00     2021-01-01 18:00:00     2021-01-02 00:00:00
0   2021-01-01             37.3                       35.29                   35.72                   41.28                NaN
0   2021-01-01 06:00:00     NaN                       35.35                   35.54                   41.12              42.33