Pandas: Reshape multiple variables wide to long

40 Views Asked by At

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
0

There are 0 best solutions below