I have a DataFrame like this
>>> df = pd.DataFrame([[1,1,2,3,4,5,6],[2,7,8,9,10,11,12]], columns=['id', 'ax','ay','az','bx','by','bz']) >>> df id ax ay az bx by bz 0 1 1 2 3 4 5 6 1 2 7 8 9 10 11 12
and I want to transform it into something like this
id name x y z 0 1 a 1 2 3 1 2 a 7 8 9 2 1 b 4 5 6 3 2 b 10 11 12
This is an unpivot / melt problem, but I don’t know of any way to melt by keeping these groups intact. I know I can create projections across the original dataframe and then concat
those but I’m wondering if I’m missing some common melt tricks from my toolbelt.
Advertisement
Answer
Set_index, convert columns to multi index and stack,
df = df.set_index('id') df.columns = [df.columns.str[1], df.columns.str[0]] new_df = df.stack().reset_index().rename(columns = {'level_1': 'name'}) id name x y z 0 1 a 1 2 3 1 1 b 4 5 6 2 2 a 7 8 9 3 2 b 10 11 12