How to use column names (year) and indexes (day/month) to reindex all values to a datetime type-CodePudding

Here is a sample of my DataFrame :

        2016        2017        2018        2019        2020        2021
Date                        
01/01   7.911808    0.913360    9.256094    7.263467    5.459530    2.856667
02/01   9.166619    3.000757    9.671416    6.167104    6.813601    2.255417

Here is the data to reproduce the example :

{'2016': {'01/01': 7.91180835607475, '02/01': 9.166619186818957},
 '2017': {'01/01': 0.9133599067435504, '02/01': 3.00075717302118},
 '2018': {'01/01': 9.256094179303766, '02/01': 9.671415667353237},
 '2019': {'01/01': 7.263467475115588, '02/01': 6.167104485385209},
 '2020': {'01/01': 5.459530161206799, '02/01': 6.8136014047445785},
 '2021': {'01/01': 2.8566666666666665, '02/01': 2.2554166666666666}}

I would like to use both columns and indexes to create a two column DataFrame containing every values and dates such as:

    date                value
0   2016-01-01 00:00:00 value_0
1   2017-01-01 00:00:00 value_1
2   2018-01-01 00:00:00 value_2
3   2019-01-01 00:00:00 value_3
4   2020-01-01 00:00:00 value_4
5   2021-01-01 00:00:00 value_5
6   2016-01-02 00:00:00 value_7
7   2017-01-02 00:00:00 value_8
8   2018-01-02 00:00:00 value_9
9   2019-01-02 00:00:00 value_10
10  2020-01-02 00:00:00 value_11
11  2021-01-02 00:00:00 value_12
12  . . .               . . .

CodePudding user response：

Use DataFrame.stack with convert MultiIndex to datetimes, last convert Series to DataFrame:

df1 = df.stack()
df1.index = pd.to_datetime(df1.index.map(lambda x: f'{x[0]} {x[1]}'), dayfirst=True)
df1 = df1.rename_axis('date').reset_index(name='value')
print (df1)
         date     value
0  2016-01-01  7.911808
1  2017-01-01  0.913360
2  2018-01-01  9.256094
3  2019-01-01  7.263467
4  2020-01-01  5.459530
5  2021-01-01  2.856667
6  2016-01-02  9.166619
7  2017-01-02  3.000757
8  2018-01-02  9.671416
9  2019-01-02  6.167104
10 2020-01-02  6.813601
11 2021-01-02  2.255417