Restructure the dataframe - dividing one column into multiple and align between rows

user_19803657 0
Viewed 51

I would like to change the structure of the dataset. (I did not come up with code for this yet...)

This is data that I have↓↓

My data below background:

  • I set pandas dataframe into series(list of lists), but I guess I can use pandas dataframes as well (Sorry, I am a beginner, so I don't know...)
  • Columns_A's data is equal to Columns_B's data (Columns_A=Columns_B)
  • Each Columns_C&Columns_D has the same value repeated
Columns_A Columns_B Columns_C Columns_D
1 A X Y
1 A X Y
1 A X Y
2 B X Y
2 B X Y
3 C X Y
3 C X Y
3 C X Y
3 C X Y
11 D Z Q
12 E Z Q
12 E Z Q
12 E Z Q
13 F Z Q
13 F Z Q

This is what I would like to make based on original data↓↓

What I would like to do:

  • Update new columns (Create additional columns for original Columns_A and Columns_B, original Columns_C and Columns_D can remain the same)
  • Assign Columns_A&Columns_B's values into different columns
  • based on the longst value; →repeat pasting Columns_C&Columns_D values →add blank if the column doesn't have any more values
Columns_A_1 Columns_B_1 Columns_A_2 Columns_B_2 Columns_A_3 Columns_B_3 Columns_C Columns_D
1 A 2 B 3 C X Y
1 A 2 B 3 C X Y
1 A Blacnk Blacnk 3 C X Y
Blacnk Blacnk Blacnk Blacnk 3 C X Y
11 D 12 E 13 F Z Q
Blank Blank 12 E 13 F Z Q
Blank Blank 12 E Blank Blank Z Q

I am thinking if I can use "loop & condition" approach, since the data is list of lists. (Sorry I put the example data as dataset just to make it easier to read)

Please let me know if my explanation does not make sense. Any advice would be appreciated.

data-science pandas data-manipulation dataframe python
1 Answers

This is a complex pivot. You first need to set helper columns.

NB. I named the columns A/B/C/D for clarity:

g = df.groupby(['C', 'D'], as_index=False, group_keys=False)

out = (df
 .assign(idx2=g.apply(lambda g: g.groupby(['A', 'B']).cumcount()),
         col=g.apply(lambda g: g.groupby(['A', 'B']).ngroup().add(1))
        )
 .pivot(index=['C', 'D', 'idx2'], columns='col')
 .pipe(lambda d: d.set_axis(d.columns.map(lambda x: f'{x[0]}_{x[1]}'), axis=1))
 .reset_index(['C', 'D'])
)

output:

      C  D   A_1   A_2   A_3  B_1  B_2  B_3
idx2                                       
0     X  Y   1.0   2.0   3.0    A    B    C
1     X  Y   1.0   2.0   3.0    A    B    C
2     X  Y   1.0   NaN   3.0    A  NaN    C
3     X  Y   NaN   NaN   3.0  NaN  NaN    C
0     Z  Q  11.0  12.0  13.0    D    E    F
1     Z  Q   NaN  12.0  13.0  NaN    E    F
2     Z  Q   NaN  12.0   NaN  NaN    E  NaN
user_163434640
Related