1

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.

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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()]
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?
Oh wow you're quick! I accidentally submitted my comment before I was done completing it.
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 :)
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 :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.