how to reverse a dataframe to its original form in pandas

Question

i have data frame and used the command pd.pivot_table(df,columns="category",index=["year","period"]) did some data processing at this dataframe and i want to reverse the process to get the original formation of the df. i tried with pd.melt and pd.wide_to_long without any luck. also on the pivot_table the the columns are a combination of two values, something like that: ("a","1"),("a","2"),("a","3"), .... ,("d","5") where the 1st position are columns from the original dataframe and the 2nd values of the category column

original df cols:

year  period  category a  b  c  d
                 1
                 2
                 3
                 4
                 5

pivot_table:

                  |a             | b             | ... | d
        category  |1  2  3  4  5 | 1  2  3  4  5 | ... | 1  2  3  4  5 
year |  period

>

 data={"col1":[1111,1111,1111,1111,2222,2222,2222,2222], 
        "col2":["a1","a1","a1","a1","a2","a2","a2","a2"],
        "col3":[1,2,1,2,1,2,1,2],"
        "a":[555,555,555,555,555,555,555,555],
        "b":[666,666,666,666,666,666,666,666]}
df=pd.DataFrame.from_dict(data)

table=pd.pivot_table(df,columns="col3",index=["col1","col2"])

Please provide a [mcve], including the current and expected output. — AMC, May 04 '20 at 04:08
i have the dataframe created by data and then used the pivot_table for some calculations, now i want to un-pivot the table to get it on the df form — Willzyx, May 04 '20 at 09:28

score 0 · Answer 1 · answered May 04 '20 at 11:08

In general, for a given way to construct a pivot table from a data frame, two different data frames may yield the same pivot table. So this process is irreversible. For example, compare the following to your df and table:

data2 = {"col1": [1111, 1111, 2222, 2222], 
         "col2": ["a1", "a1", "a2", "a2"],
         "col3": [1, 2, 1, 2],
         "a": [555, 555, 555, 555],
         "b": [666, 666, 666, 666]}
df2 = pd.DataFrame.from_dict(data2)

table2 = pd.pivot_table(df2, columns="col3", index=["col1", "col2"])

df2 is different from your df, but table2 is the same as your table.

Having said that, if you know more about the data frame you want to reconstruct from the pivot table than could be inferred from the table alone, you may be able to write a custom function implementing this knowledge.

The double index structures you see in the pivot tables are instances of a special index class in pandas called MultiIndex.

how to reverse a dataframe to its original form in pandas

1 Answers1