Reshaping dataframe with column names inside the other column

user_7647857 0
Viewed 52

I have a dataframe with rather unintuitive structure:

device1    device2    column_name
      1          1    date
    111        211    param1
    112        212    param2
      2          2    date
    121        221    param1
    122        222    param2
      3          3    date
    131        231    param1
    132        232    param2

I'm having hard time trying to convert it into the desired dataframe:

           date    param1    param2
device1       1       111       112
              2       121       122
              3       131       132
device2       1       211       212
              2       221       222
              3       231       232

Playing with pivot, stack/unstack allowed me to get closer but never reach the goal.

How can I do that?

pandas dataframe python
3 Answers

You can stack the dataframe to reshape then use groupby + cumcount to create sequential counter to identify distict rows, finally use set_index to create multilevel index and use unstack to reshape:

d = df.set_index('column_name').stack()
i = d.groupby(level=[0, 1]).cumcount()
out = d.to_frame().set_index(i, append=True)[0].unstack(0).droplevel(1)

print(out)

column_name  date  param1  param2
device1         1     111     112
device1         2     121     122
device1         3     131     132
device2         1     211     212
device2         2     221     222
device2         3     231     232
user_128331660

One possible solution:

tmp = df.melt("column_name")
tmp = tmp.set_index(['variable', 'column_name', tmp.column_name.eq('date').cumsum()])["value"].unstack(-2).droplevel(-1)
tmp.columns.name = None
tmp

         date   param1  param2
variable            
device1     1   111     112
device1     2   121     122
device1     3   131     132
device2     1   211     212
device2     2   221     222
device2     3   231     232

The difficulty was to unstack without having a unique identifier. The purpose of tmp.column_name.eq('date').cumsum() is to give an id to each group (by group I refer to a combination of a date, a param1 and a param2). This question helped me to understand this.

user_82765310

You could use melt with pivot to reshape the dataframe; like the other answers opined, you need a unique identifier:

(
    df.melt("column_name")
    .assign(unique_id=lambda x: x.groupby("column_name").cumcount())
    .pivot(index=["unique_id", "variable"], columns="column_name", values="value")
    .droplevel("unique_id")
    .rename_axis(columns=None, index=None)
)



          date  param1  param2
device1    1     111    112
device1    2     121    122
device1    3     131    132
device2    1     211    212
device2    2     221    222
device2    3     231    232
user_71757130
Related