0

I need to reshape my dataframe so that it is wide instead of long, showing each date as column headings and two indices for state and variable name. I've tried using transpose(), melt(), stack(), unstack(), pivot() and set_index() unsuccessfully. Please advise!

The closest that I've come to the solutions is forecasts.set_index(['State', 'Revenue', 'YoY_Change]) or forecasts.set_index(['Date']).T to transpose the date column, but neither are the correct solution.

My data looks like this:

enter image description here

And I need it to look like this:

enter image description here

Improve this question

1 Answer 1

Reset to default
1

This is melt followed by pivot:

(df.melt(['State','Date'])
   .pivot_table(index=['State', 'variable'], columns='Date', values='value', aggfunc='first')
)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I am getting an error that there are no numeric types to aggregate.
@JCM pass agg_func='first' to pivot_table.
Quang, can you help me understand how this solution works? I cannot understand why we melt state and date, and then pass in 'variable' and 'value' to pivot_table() when there are no variables called 'variable' and 'value'.
print df.melt(['State','Date']) to see how the data change. Basically it keeps State and Date as columns and stacks Revenue, YoY_Change in a new column named variable while putting the values in value column. Then next is pivot_table. You can read about melt and pivot_table from the document.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.