melt/Pivot in python dataframe

Question

I am trying to convert Input data frame to output data frame, but code is not working properly, not sure where am I missing.

What I tried:

• I tried to use pandas.pivot(index, columns, values) function but its showing me error not sure where I am missing. I also tried to play around with index in above command but still dint get towards right answer. I also tried to use melt command to reach to output dataframe but it dint work.

  Input.melt( id_vars='MeasurementDate',var_name = ['UID'], value_name = ['Value'])

Input DataFrame:

Input=pd.DataFrame({'MeasurementDate':['22-07-2020',
'23-07-2020',
'24-07-2020',
'25-07-2020',
'26-07-2020',
'27-07-2020',
'28-07-2020'
], 'U1':['V1.1',
'V1.2',
'V1.3',
'V1.4',
'V1.5',
'V1.6',
'V1.7'
],'U2':['V2.1',
'V2.2',
'V2.3',
'V2.4',
'V2.5',
'V2.6',
'V2.7'
],'Forecast_Generation_Date':[
'22-09-2020',
'23-09-2020',
'24-09-2020',
'25-09-2020',
'26-09-2020',
'27-09-2020',
'28-09-2020'

]} )


print(Input)

Output DataFrame:

Output=pd.DataFrame({'MeasurementDate':['22-07-2020',
'23-07-2020',
'24-07-2020',
'25-07-2020',
'26-07-2020',
'27-07-2020',
'28-07-2020',
'22-07-2020',
'23-07-2020',
'24-07-2020',
'25-07-2020',
'26-07-2020',
'27-07-2020',
'28-07-2020'
], 'UID':['U1',
'U1',
'U1',
'U1',
'U1',
'U1',
'U1',
'U2',
'U2',
'U2',
'U2',
'U2',
'U2',
'U2'

],'Value':['V1.1',
'V1.2',
'V1.3',
'V1.4',
'V1.5',
'V1.6',
'V1.7',
'V2.1',
'V2.2',
'V2.3',
'V2.4',
'V2.5',
'V2.6',
'V2.7'],'Forecast_Generation_Date':['22-09-2020',
'23-09-2020',
'24-09-2020',
'25-09-2020',
'26-09-2020',
'27-09-2020',
'28-09-2020',
'22-09-2020',
'23-09-2020',
'24-09-2020',
'25-09-2020',
'26-09-2020',
'27-09-2020',
'28-09-2020'


]} )
print(Output)

Answer 1

You were not passing value_vars argument.

df=pd.DataFrame({'MeasurementDate':['22-07-2020',
    '23-07-2020','24-07-2020','25-07-2020','26-07-2020','27-07-2020','28-07-2020'],
    'U1':['V1.1','V1.2','V1.3','V1.4','V1.5','V1.6','V1.7'],
    'U2':['V2.1','V2.2','V2.3','V2.4','V2.5','V2.6','V2.7'],
    'Forecast_Generation_Date':['22-09-2020','23-09-2020','24-09-2020','25-09-2020','26-09-2020','27-09-2020','28-09-2020']
    } )


df = df.melt(id_vars=['MeasurementDate','Forecast_Generation_Date'],value_vars=['U1','U2'],var_name='UID')
df

Output

+-------------------+----------------------------+-------+---------+
| MeasurementDate   | Forecast_Generation_Date   | UID   | value   |
+===================+============================+=======+=========+
| 22-07-2020        | 22-09-2020                 | U1    | V1.1    |
| 23-07-2020        | 23-09-2020                 | U1    | V1.2    |
| 24-07-2020        | 24-09-2020                 | U1    | V1.3    |
| 25-07-2020        | 25-09-2020                 | U1    | V1.4    |
| 26-07-2020        | 26-09-2020                 | U1    | V1.5    |
| 27-07-2020        | 27-09-2020                 | U1    | V1.6    |
| 28-07-2020        | 28-09-2020                 | U1    | V1.7    |
| 22-07-2020        | 22-09-2020                 | U2    | V2.1    |
| 23-07-2020        | 23-09-2020                 | U2    | V2.2    |
| 24-07-2020        | 24-09-2020                 | U2    | V2.3    |
| 25-07-2020        | 25-09-2020                 | U2    | V2.4    |
| 26-07-2020        | 26-09-2020                 | U2    | V2.5    |
| 27-07-2020        | 27-09-2020                 | U2    | V2.6    |
| 28-07-2020        | 28-09-2020                 | U2    | V2.7    |
+-------------------+----------------------------+-------+---------+