CODEWITHSUNDEEP
pythonpandas
nullmonies
nullmonies

Reputation: 33

Converting values in column to column headers, and the column headers to values in a single column

I have this dataframe:

University id # Year fin_element prior_year current_year
1 2022 element #1 $1 $2
1 2022 element #2 $3 $5
1 2022 element #3 $2 $0

And I want to convert it to the following using pandas:

University id # Year financial_period element #1 element #2 element #3
1 2022 prior_year $1 $3 $2
1 2022 current_year $2 $5 $0

While keeping columns University # and Year the same, I want the values in column fin_element to become the column headers in the new table, and the column headers to become values in a column called financial_period.

How can I do this? I have read into pivot, stack and melt, and am a bit overwhelmed since I am new to python and pandas.

Upvotes: 1

Views: 118

Answers (1)

Rabinzel
Rabinzel

Reputation: 7913

Try this:

out = (df
       .set_index(['University_id', 'Year', 'fin_element'])
       .stack()
       .unstack(level=2)
       .rename(columns={'level_2' : 'financial_period'})
       .reset_index()
      )
print(out)

fin_element  University_id  Year financial_period element #1 element #2 element #3
0                        1  2022       prior_year         $1         $3         $2
1                        1  2022     current_year         $2         $5         $0

Upvotes: 1

Related Questions