Wide to long dataframes - Error using timestamp as index

Question

I am working with a dataframe having datetime as index in the form DD/MM/YYYY HH:mm:ss, followed by a series of columns named as Sitename_Parameter. For the same Sitename I have different parameters.

I'd like to convert it into a long format with the datetime and the name of the sites as indices and the Parameters as columns.

I am using this code:

df=pd.wide_to_long(df, stubnames=['Sitename1','Sitename2',...,'Sitenamen'], i=["Timestamp"], j="Parameter",sep='_', suffix=r'\w+')

but I am encountering two issues:

1. Data must be 1-dimensional, when I use Timestamp as index. I don't get this error when I use a standard index.
2. This code places the Sitenames as columns and the Parameters as rows, which is the opposite of what I want. But if I replace the sitenames with the parameters into stubnames I get an error.

Answer 1

These are a series of melt and pivot commands. I am not sure if it's optimal, but it can get where you want. Also, perhaps use pd.to_datetime to make sure that your "Timestamp" column is in datetime format.

df = pd.DataFrame({"Kharid-et_Heat" : {0 : "a", 1 : "b", 2 : "c"},
                   "Kharid-et_Humidity" : {0 : "d", 1 : "e", 2 : "f"},
                   "Zzetek_Heat" : {0 : 2.5, 1 : 1.2, 2 : .7},
                   "Zzetek_Humidity" : {0 : 3.2, 1 : 1.3, 2 : .1}
                  })
df["Timestamp"] = [datetime.datetime(2021, 3, 15, 11, 9, 15), datetime.datetime(2021, 3, 16, 11, 9, 15),\
            datetime.datetime(2021, 3, 17, 11, 9, 15)]

df["Timestamp"]=pd.to_datetime(df["Timestamp"])

df=pd.wide_to_long(df, stubnames=['Kharid-et','Zzetek'], i=["Timestamp"], j="Parameter",sep='_', suffix='\w+')\
    .reset_index(level=[0,1])

df1=df.melt(id_vars=["Timestamp", "Parameter"], value_vars=["Kharid-et", "Zzetek"])\
     .pivot(index=["Timestamp", "variable"], columns="Parameter", values="value")\
     .sort_index(level=[0,1])\
     .reset_index(level=1)

print(df1)

output

Parameter             variable Heat Humidity
Timestamp                                   
2021-03-15 11:09:15  Kharid-et    a        d
2021-03-15 11:09:15     Zzetek  2.5      3.2
2021-03-16 11:09:15  Kharid-et    b        e
2021-03-16 11:09:15     Zzetek  1.2      1.3
2021-03-17 11:09:15  Kharid-et    c        f
2021-03-17 11:09:15     Zzetek  0.7      0.1