I have the following dataframe:

Year  Membership Counts
2015  New         10
2015  Old         9
2016  New         7
2017  New         11
2017  Old         4

I would like to convert it to the following:

Year   Old   New
2015   9     10
2016   7     0
2017   4     11

Note that the original dataframe does not have the value 2016 Old 0. That value would have to be inferred. If that makes the question too hard, then I can append it to the original dataframe.

Currently, I am converting it using “brute force”. I group by year and then manually create the new dataframe. However, I can imagine there has to be a cleaner way to do this.

I was trying to use pd.melt() but have not been able to make it work. I’d appreciate any suggestions.