Search code examples
pythonpandasdataframedatetimetranspose

Manipulate dataframe with pandas to transpose columns and adjust dates?


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.


Solution

  • # 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