Python pandas merge multiple dataframe-CodePudding

I have a dataframe with structure like that :

date	X1	date	X2	date	X3
01/01/1970	5	01/01/1970	1	02/01/71	10
05/01/1970	15	02/01/1970	5	04/01/71	11
06/01/1970	15.1	03/01/1970	5.5	05/01/71	11
07/01/1970	15.1	04/01/1970	5.5	06/01/71	11
...	...	...	...	...	...

it could be generate with this code:

df = pd.DataFrame({'date1': [pd.to_datetime('1970-01-01'),
                        pd.to_datetime('1970-01-05'),
                        pd.to_datetime('1970-01-06'),
                        pd.to_datetime('1970-01-07')], 
                        'X1': [5, 15,15.1, 15.1], 
                'date2': [pd.to_datetime('1970-01-01'),
                        pd.to_datetime('1970-01-02'),
                        pd.to_datetime('1970-01-03'),
                        pd.to_datetime('1970-01-04')], 
                        'X2': [1, 5,5.1, 1.1],
                'date3': [pd.to_datetime('1970-01-02'),
                        pd.to_datetime('1970-01-04'),
                        pd.to_datetime('1970-01-05'),
                        pd.to_datetime('1970-01-06')], 
                        'X3': [10, 11,11, 11],
                        })

From this dataframe, I would like to get a dataframe like that :

date	X1	X2	X3
01/01/1970	5	1	na
02/01/1970	na	5	10
03/01/1970	na	5.5	na
04/01/1970	na	5.5	11
05/02/1970	15	na	11
06/03/1970	15.1	na	11
07/04/1970	15.1	na	na
...	...	...	...

CodePudding user response：

You can break the dataframe (df) into 3 different dataframes, and then append them.

df1 = df[['date','X1']]
df2 = df[['date','X2']]
df3 = df[['date','X3']]

intermediate = df1.append(df2, ignore_index =True)
output = intermediate.append(df3, ignore_index = True)

You could chain the two commands together too

CodePudding user response：

pd.concat(
    [
        df.iloc[0-1, :].rename({"X1": "X1"}, , axis=1),
        df.iloc[2-3, :].rename({"X2": "X1"}, , axis=1),
        df.iloc[4-5, :].rename({"X3": "X1"}, , axis=1),
    ]
)