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