I am trying to reshape a dataframe in Pandas. I have a dataframe in wide format with multiple variables I want to reshape into long format.
I have generated a sample dataframe. Here is the wide format.
# current data
d = {'ID': [1, 2, 3, 4],
'Var1_a': [1, 2, 3, 4],
'Var1_b': [2, 4, 6, 8],
'Var1_c': [9, 8, 7, 6],
'Var2_a': [1, 2, 3, 4],
'Var2_b': [2, 4, 6, 8],
'Var2_c': [9, 8, 7, 6]
}
df = pd.DataFrame(data=d)
df
Here is what I would like the data to look like in tall format.
# new data
dnew = {'ID1': [1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4],
'Var1_letter': ['a', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c'],
'Var1': [1, 2, 9, 2, 4, 8, 3, 6, 7, 4, 8, 6],
'Var2_Letter': ['a', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c', 'a', 'b', 'c'],
'Var2': [1, 2, 9, 2, 4, 8, 3, 6, 7, 4, 8, 6]
}
dfnew = pd.DataFrame(data=dnew)
dfnew
I found this post and several youtube videos that walk though how to do a wide to long transformation, but these examples assume that we are only reshaping 1 variable. I am trying to figure out how to do this with 2 or 3 variables.
Reshape wide to long in pandas
The following code will reshape 1 column from wide to tall format. I suppose I could repeat this for every variable and then merge everything back together using pd.merge, but this seems like it would be very difficult when I start trying to reshape 5 or 10 variables.
df_var1 = pd.melt (df,
id_vars = ['ID'],
value_vars = ['Var1_a', 'Var1_b', 'Var1_c'],
var_name = 'Var1_Letter',
value_name = 'Var1'
)
df_var1