Pandas: reformatting CSV data from single column into multiple new columns

Question

I'm relatively new to python and have been using Pandas to manipulate scientific data. I have 79 datasets in CSV format of inconsistent satellite imagery of pixel values (lots of NaNs) that have been averaged to bi-monthly values (two months averaged together). The data is formatted similar to the the example data frame "df". The actual time series data extends from 1985-2020 with a screen shot at the bottom showing it's actual format for reference.

df =  pd.DataFrame({'grouping': ['F-M', 'A-M', 'J-J', 'A-S', 'O-N', 'D-J', 'F-M', 'A-M', 'J-J', 'A-S', 'O-N', 'D-J'],
                    'year': ['1985', '1985','1985','1985','1985','1985', '1986','1986','1986','1986','1986','1986'],
                    'region_1': ['NaN', 0.264, 0.339, 0.321, 0.305, 'NaN', 'NaN', 0.404, 0.206, 0.217, 0.266, 0.217 ],
                    'region_2': ['NaN', 0.457, 0.649,  0.625, 0.531, 'NaN', 0.503, 0.656, 0.437, 0.568, 0.547, 'NaN' ]})

I need to reformat the data so each row is just one year with each two month grouping as a column header. However, each dataset has two regions that need to be compared to each other. "April-May region 1" and "April-May region 2". The final data set would look something like this:

df2 = pd.DataFrame({'year':['1985', '1986'],
                    'F-M reg_1': ['NaN', 'NaN'],
                    'A-M reg_1': [0.264, 0.404],
                    'J-J reg_1': [0.339, 0.206],
                    'A-S reg_1': [0.321, 0.217],
                    'O-N reg_1': [0.305, 0.266],
                    'D-J reg_1': ['NaN', 0.217],
                    'F-M reg_2': ['NaN', 0.503],
                    'A-M reg_2': [0.457, 0.656],
                    'J-J reg_2': [0.649, 0.437],
                    'A-S reg_2': [0.635, 0.568],
                    'O-N reg_2': [0.531, 0.547],
                    'D-J reg_2': ['NaN', 'NaN']})

I've tried using the following code, but I dont know how to include the region_2 data within the data frame. It also creates an index value and calls it "grouping" and shuffles the order of the bi-monthly grouping.

df.pivot(index='year', columns = 'grouping', values = ('region_1')).reset_index()

Would it be better to create two separate data frames for each region?

I also can't seem to find any posts that show how to do this.

Answer 1

I think all you need to do is use a list for the values parameter:

bimonths = ['F-M', 'A-M', 'J-J', 'A-S', 'O-N', 'D-J']
df.pivot(index='year', columns = 'grouping', values = ['region_1','region_2']).reindex(bimonths, axis=1, level=1)

Output (column alignment messed up by the cut and paste):

    region_1    region_2
grouping    F-M A-M J-J A-S O-N D-J F-M A-M J-J A-S O-N D-J
year                                                
1985    NaN 0.264   0.339   0.321   0.305   NaN NaN 0.457   0.649   0.625   0.531   NaN
1986    NaN 0.404   0.206   0.217   0.266   0.217   0.503   0.656   0.437   0.568   0.547   NaN