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
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)
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
