How can I manipulate a dataframe like this, making a transpose that joins one under the other and arranges it in the form of dates?
Let it be by transforming this existing dataframe, without messing with its creation.
Dataframe:
df = pd.DataFrame({
'Jan':[1, 2, 3],
'Feb':[4, 5, 6],
'Mar':[7, 8, 9]
})
df
Output:
Jan Feb Mar
0 1 4 7
1 2 5 8
2 3 6 9
Desired Result:
1990-Jan 1
1990-Feb 4
1990-Mar 7
1991-Jan 2
1991-Feb 5
1991-Mar 8
1992-Jan 3
1992-Feb 6
1992-Mar 9
obs: In the final result the dates don't have to be the name of the months, it can be their number.
# flatten the DF
df2=df.stack().reset_index().rename(columns={0:'val'})
# create the date
df2['date'] = (1990 + df2['level_0']).astype('str') + "-" + df2['level_1']
# drop unwanted columns
df2=df2.drop(columns=['level_0','level_1'])
df2
val date
0 1 1990-Jan
1 4 1990-Feb
2 7 1990-Mar
3 2 1991-Jan
4 5 1991-Feb
5 8 1991-Mar
6 3 1992-Jan
7 6 1992-Feb
8 9 1992-Mar