Pivoting DataFrame in Python Pandas

Question

I'm trying to pivot my df from wide to long, and I am attempting to replicate R's dplyr::pivot_longer() function. I have tried pd.wide_to_long() and pd.melt() but have had no success in correctly formatting the df. I also attempted using df.pivot() and come to the same conclusion.

Here is what a subset of the df (called df_wide) looks like: Rows are Store Numbers, Columns are Dates, Values are Total Sales

My current function looks like this:

df_wide.pivot(index = df_wide.index, 
              columns = ["Store", "Date", "Value"], # Output Col Names
              values = df_wide.values) 

My desired output is a df that looks like this:

• Note - this question is distinct from merging, as it is looking at changing the structure of a single data frame

Answer 1

The stack() function is useful to achieve your objective, then reformat as needed:

pd.DataFrame( df.stack() ).reset_index(drop=False).rename(columns={'level_0':'store', 'level_1':'Date', 0:'Value'})