Stacking Pandas Dataframe

Question

I have the following pandas data frame where I have NDVI value of 5 different points on different dates-

print (df)

>>>
                         PSC            Intel
FID   Lat    Lon    23-May  18-May  25-May  28-May
0   51.62   -63.81  -0.04   0.08    0.10    0.13
1   51.62   -63.80  -0.05   0.09    0.10    0.13
2   51.62   -63.80  -0.05   0.08    0.07    0.12
3   51.62   -63.80  -0.06   0.08    0.11    0.14
4   51.62   -63.80  -0.05   0.09    0.11    0.16

However, I need them converted where NDVI value will be stacked, and a new column will be there to indicate the data collection date. The required format is as follows -

FID Lat      Lon    NVAL    Date    SAT
0   51.62   -63.81  -0.04   23-May  PSC
1   51.62   -63.80  -0.05   23-May  PSC
2   51.62   -63.80  -0.05   23-May  PSC
3   51.62   -63.80  -0.06   23-May  PSC
4   51.62   -63.80  -0.05   23-May  PSC
0   51.62   -63.81  0.08    18-May  PSC
1   51.62   -63.80  0.09    18-May  PSC
2   51.62   -63.80  0.08    18-May  PSC
3   51.62   -63.80  0.08    18-May  PSC
4   51.62   -63.80  0.09    18-May  PSC
0   51.62   -63.81  0.10    25-May  Inter
1   51.62   -63.80  0.10    25-May  Inter
2   51.62   -63.80  0.07    25-May  Inter
3   51.62   -63.80  0.11    25-May  Inter
4   51.62   -63.80  0.11    25-May  Inter
0   51.62   -63.81  0.13    28-May  Inter
1   51.62   -63.80  0.13    28-May  Inter
2   51.62   -63.80  0.12    28-May  Inter
3   51.62   -63.80  0.14    28-May  Inter
4   51.62   -63.80  0.16    28-May  Inter

Is there any way to do that using the pandas or any other library of python?

Answer 1

Setup:

from io import StringIO
import pandas as pd
df = pd.read_table(StringIO("""FID   Lat    Lon    23-May  18-May  25-May  28-May
0   51.62   -63.81  -0.04   0.08    0.10    0.13
1   51.62   -63.80  -0.05   0.09    0.10    0.13
2   51.62   -63.80  -0.05   0.08    0.07    0.12
3   51.62   -63.80  -0.06   0.08    0.11    0.14
4   51.62   -63.80  -0.05   0.09    0.11    0.16"""), sep='\s+')

Use melt:

df = pd.melt(df,id_vars=['FID','Lat','Lon'], var_name='Date', value_name='Value')

Output: df[['FID','Lat','Lon','Value','Date']]

    FID    Lat    Lon  Value    Date
0     0  51.62 -63.81  -0.04  23-May
1     1  51.62 -63.80  -0.05  23-May
2     2  51.62 -63.80  -0.05  23-May
3     3  51.62 -63.80  -0.06  23-May
4     4  51.62 -63.80  -0.05  23-May
5     0  51.62 -63.81   0.08  18-May
6     1  51.62 -63.80   0.09  18-May
7     2  51.62 -63.80   0.08  18-May
8     3  51.62 -63.80   0.08  18-May
9     4  51.62 -63.80   0.09  18-May
10    0  51.62 -63.81   0.10  25-May
11    1  51.62 -63.80   0.10  25-May
12    2  51.62 -63.80   0.07  25-May
13    3  51.62 -63.80   0.11  25-May
14    4  51.62 -63.80   0.11  25-May
15    0  51.62 -63.81   0.13  28-May
16    1  51.62 -63.80   0.13  28-May
17    2  51.62 -63.80   0.12  28-May
18    3  51.62 -63.80   0.14  28-May
19    4  51.62 -63.80   0.16  28-May

Answer 2

Use the function stack()

#Creating DataFrame 
df=pd.DataFrame({'FID':[0,1,2,3,4],'Lat':[51.62,51.62,51.62,51.62,51.62],'Lon':[-63.81,-63.80,-63.80,-63.80,-63.80],'23-May':[-.04,-.05,-.05,-.06,-.05],'18-May':[0.08,0.09,0.08,0.08,0.09],'25-May':[.1,.1,.07,.11,.11],'28-May':[0.13,.13,.12,.14,.16]})
df=df[['FID','Lat','Lon','23-May','18-May','25-May','28-May']]    

df
   FID    Lat    Lon  23-May  18-May  25-May  28-May
0    0  51.62 -63.81   -0.04    0.08    0.10    0.13
1    1  51.62 -63.80   -0.05    0.09    0.10    0.13
2    2  51.62 -63.80   -0.05    0.08    0.07    0.12
3    3  51.62 -63.80   -0.06    0.08    0.11    0.14
4    4  51.62 -63.80   -0.05    0.09    0.11    0.16   

df_stacked=df.set_index(['FID','Lat','Lon']).stack().reset_index()
df_stacked=df_stacked.rename(columns={'level_3':'Date',0:'Value'})
df_stacked=df_stacked[['FID','Lat','Lon','Value','Date']]

df_stacked 
    FID    Lat    Lon  Value    Date
0     0  51.62 -63.81  -0.04  23-May
1     0  51.62 -63.81   0.08  18-May
2     0  51.62 -63.81   0.10  25-May
3     0  51.62 -63.81   0.13  28-May
4     1  51.62 -63.80  -0.05  23-May
5     1  51.62 -63.80   0.09  18-May
6     1  51.62 -63.80   0.10  25-May
7     1  51.62 -63.80   0.13  28-May
8     2  51.62 -63.80  -0.05  23-May
9     2  51.62 -63.80   0.08  18-May
10    2  51.62 -63.80   0.07  25-May
11    2  51.62 -63.80   0.12  28-May
12    3  51.62 -63.80  -0.06  23-May
13    3  51.62 -63.80   0.08  18-May
14    3  51.62 -63.80   0.11  25-May
15    3  51.62 -63.80   0.14  28-May
16    4  51.62 -63.80  -0.05  23-May
17    4  51.62 -63.80   0.09  18-May
18    4  51.62 -63.80   0.11  25-May
19    4  51.62 -63.80   0.16  28-May