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

Question

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.

Rabinzel · Accepted Answer · 2022-08-30 14:37:27Z

1

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

answered Aug 30, 2022 at 14:37

Rabinzel

7,9533 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

nullmonies Over a year ago

That works! Thanks! I just added a list comprehension to change the columns names so that 'fin_element' didn't show up in the beginning. Code I used for that: df.columns = [column for column in df.columns.to_list()]

Rabinzel Over a year ago

nice! but where does the code you pasted here differ from mine? except level=2 you changed it to the name. What do you mean with list comprehension?

nullmonies Over a year ago

Oh wow you're quick! I accidentally submitted my comment before I was done completing it.

Rabinzel Over a year ago

still don't get it because your original dataframe has different number of columns than the result at the end, BUT not important. happy it works for you! accept and vote the answer if you don't mind :)

nullmonies Over a year ago

The example I gave above was a very simplified version of the dataset/problem I was working with. I applied your suggestion within a function, and in there I ended up using the list comprehension. Accepted and voted! Thanks again :)

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related