How to turn long back into wide format in pandas

I have some data in long format.

I want to transform the categories columns to columns so I can plot in Altair. Can I do this directly in Altair or is there a simple line pandas to transform?

Solution

You don't provide any output. However, I suppose you are looking for pivot_table with aggfunc='first':

records = df.groupby(['to_drop', 'categories']).cumcount().rename('records')
out = (df.pivot_table(index=['to_drop', records], columns='categories',
                      values='values', aggfunc='first')
         .ffill().reset_index().rename_axis(columns=None))
print(out)

# Output
      to_drop  records Admissions             Area  Gender Number of admissions per 100,000 population  Year
0           0        0       1860     Amber Valley    Male                                        2810  2019
1           0        1        780     Amber Valley  Female                                        1110  2019
2           0        2       1600     Amber Valley    Male                                        2470  2018
3           0        3        680     Amber Valley  Female                                         980  2018
4           0        4       1500     Amber Valley    Male                                        2350  2017
...       ...      ...        ...              ...     ...                                         ...   ...
2659      332        3       3310  North Yorkshire  Female                                         960  2018
2660      332        4       7660  North Yorkshire    Male                                        2360  2017
2661      332        5       2920  North Yorkshire  Female                                         850  2017
2662      332        6       7790  North Yorkshire    Male                                        2450  2016
2663      332        7       2740  North Yorkshire  Female                                         810  2016

[2664 rows x 7 columns]