how to un-pivot multi-column data?

user_14388481 0
Viewed 37

I am trying to Unpivot columns and get 1 attributes and 2 value from a pandas dataframe, could anyone help me on this?

original data:

 id Jan-Value1 Jan-Value2 Feb-Value1 Feb-Value2
 1  1          10         2          15
 2  0          5          3          20

desire output:

 id Month Value1 Value2
 1  Jan   1      10
 1  Feb   2      15
 2  Jan   0      5
 2  Feb   3      20
unpivot python
2 Answers

You might consider pandas.wide_to_long():

import pandas as pd

df = pd.DataFrame({
"Jan-Value1": [1, 0],
"Jan-Value2": [10, 5],
"Feb-Value1": [2, 3],
"Feb-Value2": [15,20]
})

df.columns = ["-".join(col.split("-")[::-1]) for col in df.columns]

df["id"] = df.index

transformed_df = pd.wide_to_long(df, ["Value1", "Value2"], i="id", j="Month", sep="-", suffix="\w+")

Since pandas requires the suffix to end up in a value of the column, I ended up reversing the order of your column names (without that transform, Value1 and Value2 ended up in the months column)

user_102434740

One possible way to do this is to use MultiIndex and stack. For this solution, I am assuming that id is the index of the dataframe:

#df.set_index('id',inplace=True)  #set 'id' as index

#creating a Multiindex using existing columns 
df.columns = df.columns.str.split('-', expand=True).swaplevel(0,1)

#stacking the dataframe
df = df.stack().reset_index()

#renaming the column 
df.rename(columns={'level_1':'Month'},inplace=True)
print(df)

Output:

   id Month  Value1  Value2
0   1   Feb       2      15
1   1   Jan       1      10
2   2   Feb       3      20
3   2   Jan       0       5
user_56045620
Related