Converting only two columns from a long dataframe to a wide format in pandas

Question

I am trying to reproduce a solution from this answer, but make a mistake somewhere.

I have a dataframe with multiple columns:

'Region', 'Region code', 'Subregion', 'Country', 'Country code',
'IDA Status', 'FCV Status', 'Landlocked Status', 'Pillar', 'Indicator',
'Year', 'Value', 'Status', 'Measurement', 'Updated', 'Sources'

I need to convert two long columns into a wide format:

Indicator     Status    Value

Indicator 1   Actual    20
Indicator 2   Actual    30
Indicator 3   Actual    40
Indicator 1   Forecast  30
Indicator 2   Forecast  40
Indicator 3   Forecast  50
Indicator 1   Target    60
Indicator 2   Target    60
Indicator 3   Target    60

I want to make it from 2 to 9 columns

Indicator1_Actual | Indicator1_Forecast | Indicator1_Target | Indicator2_Actual

20                  30                    60                  60

etc

I am doing the solution from the question above

foo['idx'] = foo.groupby('Region').cumcount() + 1
foo = foo.pivot_table(index = ['Region', 'Region code', 'Subregion', 'Country', 'Country code',
                               'IDA Status', 'FCV Status', 'Landlocked Status', 'Pillar','Indicator',
                               'Year', 'Value', 'Status', 'Measurement', 'Updated', 'Sources'], 
                               columns = 'idx', values = ['Indicator', 'Status'], 
                               aggfunc = 'first')

foo = foo.sort_index(axis = 1, level = 1)
foo.columns = [f'{x}_{y}' for x, y in foo.columns]
foo = foo.reset_index()

But I do not receive any meaningful output, the dataframe remains the same.

What am I missing? Thanks!

Try this : ``df.assign(Indicator = df.Indicator.str.replace(" ", "_").str.cat(df.Status, sep = '_'), Status = 0).pivot('Status', 'Indicator', 'Value')``. See if that is what you have in mind. — sammywemmy, Mar 27 '21 at 21:31
@sammywemmy, `ValueError: Index contains duplicate entries, cannot reshape` — Anakin Skywalker, Mar 27 '21 at 21:48
on the data you shared? there are no duplicates in the data you shared — sammywemmy, Mar 27 '21 at 21:49
No, on my final data. I have no clue what is going on and which duplicates are there. There are some missing values, but there are no duplicates. — Anakin Skywalker, Mar 27 '21 at 21:53
kindly share more data that reflects the actual data you are working on — sammywemmy, Mar 27 '21 at 21:59
@sammywemmy, I need to check data and understand what is wrong with data — Anakin Skywalker, Mar 27 '21 at 22:08

Converting only two columns from a long dataframe to a wide format in pandas

0 Answers0