How to create a combined wide and long format dataframe from a long input dataset?

user_14739761 0
Viewed 35

I have a dataframe in long format which can be created using the code below:

import pandas as pd
import numpy as np

long_dict = {'Period':['2021-01-01','2021-02-01','2021-01-03','2021-02-04','2022-02-01','2022-03-01','2021-03-01'],
                'Indicator':['number of tracks','number of tracks','defects','defects','gears and bobs','gears and bobs','staff'],
                'indicator_status':['active','active','active','active','active','active','active'],
                'mech_code':['73','73','44','44','106','107','106'],
                'Value':[100,120,7,17,25,99,81]}
df = pd.DataFrame(long_dict)

Data View

The client/customer wants to specifically have a unified long and wide view using the Indicator field (for proprietary software visualisation purposes). What is the best way which results in the output below:

Final output

data-wrangling pandas dataframe pivot python
1 Answers

Try:

df = pd.concat([df, df.pivot(columns="Indicator", values="Value")], axis=1)
print(df)

Prints:

       Period         Indicator indicator_status mech_code  Value  defects  gears and bobs  number of tracks  staff
0  2021-01-01  number of tracks           active        73    100      NaN             NaN             100.0    NaN
1  2021-02-01  number of tracks           active        73    120      NaN             NaN             120.0    NaN
2  2021-01-03           defects           active        44      7      7.0             NaN               NaN    NaN
3  2021-02-04           defects           active        44     17     17.0             NaN               NaN    NaN
4  2022-02-01    gears and bobs           active       106     25      NaN            25.0               NaN    NaN
5  2022-03-01    gears and bobs           active       107     99      NaN            99.0               NaN    NaN
6  2021-03-01             staff           active       106     81      NaN             NaN               NaN   81.0
user_100359850
Related