Restructuring a pandas dataframe (convert dates in rows to columns)-CodePudding

I have a pandas dataframe with the following contents

Room	Position	Sensor	Measuring Time	Value1	Value2	Value3
Living Room	A1	111	27-12-2021	1.1	1.2	0.9
Living Room	A2	112	27-12-2021	2.1	2.1	1.9
Living Room	B1	113	27-12-2021	1.5	1.4	1.4
Living Room	B2	114	27-12-2021	1.8	1.7	1.9
Bed Room	A1	211	27-12-2021	4.2	4.5	4.4
Living Room	A1	111	29-12-2021	0.9	1.1	0.8
Living Room	A2	112	29-12-2021	1.9	1.8	1.7
Living Room	B1	115	29-12-2021	1.4	1.3	1.2
Living Room	B2	114	29-12-2021	1.7	1.5	1.7

I want to convert the dataframe as below

Room	Position	Sensor	Value	27-12-2021	29-12-2021
Living Room	A1	111	Value1	1.1	0.9
Living Room	A1	111	Value2	1.2	1.1
Living Room	A1	111	Value3	0.9	0.8
Living Room	A2	112	Value1	2.1	1.9
Living Room	A2	112	Value2	2.1	1.8
Living Room	A2	112	Value3	1.9	1.7
Living Room	B1	113	Value1	1.5	N/A
Living Room	B1	113	Value2	1.4	N/A
Living Room	B1	113	Value3	1.4	N/A
Living Room	B1	115	Value1	N/A	1.4
Living Room	B1	115	Value2	N/A	1.3
Living Room	B1	115	Value3	N/A	1.2
Living Room	B2	114	Value1	1.8	1.7
Living Room	B2	114	Value2	1.7	1.5
Living Room	B2	114	Value3	1.9	1.7
Bed Room	A1	211	Value1	4.2	N/A
Bed Room	A1	211	Value2	4.5	N/A
Bed Room	A1	211	Value3	4.4	N/A

I tried using pivot but was unsuccessful.

CodePudding user response：

You can use pivot:

out = df.pivot(index=['Room', 'Position', 'Sensor'],
               columns=['Measuring Time'],
               values=['Value1', 'Value2', 'Value3']) \
        .rename_axis(columns=['Value', None]).stack(level=0).reset_index()

Output:

           Room Position  Sensor   Value  27-12-2021  29-12-2021
0   Living Room       A1     111  Value1         1.1         0.9
1   Living Room       A1     111  Value2         1.2         1.1
2   Living Room       A1     111  Value3         0.9         0.8
3   Living Room       A2     112  Value1         2.1         1.9
4   Living Room       A2     112  Value2         2.1         1.8
5   Living Room       A2     112  Value3         1.9         1.7
6   Living Room       B1     113  Value1         1.5         NaN
7   Living Room       B1     113  Value2         1.4         NaN
8   Living Room       B1     113  Value3         1.4         NaN
9   Living Room       B1     115  Value1         NaN         1.4
10  Living Room       B1     115  Value2         NaN         1.3
11  Living Room       B1     115  Value3         NaN         1.2
12  Living Room       B2     114  Value1         1.8         1.7
13  Living Room       B2     114  Value2         1.7         1.5
14  Living Room       B2     114  Value3         1.9         1.7

CodePudding user response：

Another way with using melt and pivot:

df.melt(
        id_vars=['Room', 'Position', 'Sensor', 'Measuring Time'])\
    .pivot(index=['Room', 'Position', 'Sensor','variable'],columns=['Measuring Time'], values='value').reset_index()

Measuring Time         Room Position  Sensor variable  27/12/2021  29/12/2021
0               Living Room       A1     111   Value1         1.1         0.9
1               Living Room       A1     111   Value2         1.2         1.1
2               Living Room       A1     111   Value3         0.9         0.8
3               Living Room       A2     112   Value1         2.1         1.9
4               Living Room       A2     112   Value2         2.1         1.8
5               Living Room       A2     112   Value3         1.9         1.7
6               Living Room       B1     113   Value1         1.5         NaN
7               Living Room       B1     113   Value2         1.4         NaN
8               Living Room       B1     113   Value3         1.4         NaN
9               Living Room       B1     115   Value1         NaN         1.4
10              Living Room       B1     115   Value2         NaN         1.3
11              Living Room       B1     115   Value3         NaN         1.2
12              Living Room       B2     114   Value1         1.8         1.7
13              Living Room       B2     114   Value2         1.7         1.5
14              Living Room       B2     114   Value3         1.9         1.7