CODEWITHSUNDEEP
pythonpandasdataframe
aj7amigo
aj7amigo

Reputation: 378

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

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.

Upvotes: 1

Views: 757

Answers (1)

Corralien
Corralien

Reputation: 120549

Update

If you have duplicates, use pivot_table and an agg function:

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

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

Upvotes: 2

Related Questions