[FIXED] Rows to columns using Pandas (Python)

Issue

I have information regarding yearly firm reports on a page level (i.e., multiple pagewise information by report). Pagewise information is in separate rows, so that reports span over multiple rows covering pagewise information.

Correspondingly, my data looks as follows:

import pandas as pd

data = {
    'FIRM': ['A', 'A', 'B', 'B'],
    'YEAR': [2012, 2012, 2013, 2013],
    'Report Page': [1, 2, 1, 2],
    'Value1': [10, 15, 20, 25]
}

df = pd.DataFrame(data)

Resulting in the following dataframe:

  FIRM  YEAR  Report Page  Value1
0    A  2012            1       10
1    A  2012            2       15
2    B  2013            1       20
3    B  2013            2       25

The objective is to group the page-wise information on a report (i.e., year) level. Hence, the intended output looks as follows:

  Firm  Year  Value1_Page1  Value1_Page2
0    A  2012            10            15
1    B  2013            20            25

As shown in the example, page-wise information shall be maintained but transposed to a column level, with adding the page number in column names (e.g., Value1_Page1). Reports do not always have the same number of pages. So far, I did not manage to come up with a meaningful attempt.

Solution

You can do it with pandas.pivot:

df.pivot(index=['FIRM', 'YEAR'], columns='Report Page', values='Value1').add_prefix('Value1_Page').reset_index().rename_axis(None, axis=1)
df

             FIRM  YEAR  Value1_Page1  Value1_Page2
0              A  2012            10            15
1              B  2013            20            25

Answered By - gtomer