Stack data based on substring in column name

user_16584118 0
Viewed 62

I'm a bit new to Python and Pandas and I do not understand how to stack data based on part of the column name.

My data looks like below: (part of sample data)

ID Region Rep Item_Loop1 Units_A_Loop1 Type_col_X_Loop1 Unit_Cost_Type_New_Loop1xyz Total_Loop1ABCD Item_Loop2 Units_A_Loop2 Type_col_X_Loop2 Unit_Cost_Type_New_Loop2xyz Total_Loop2ABCD
1 Central Andrews Pencil 75 A 1,99 149,25 Pencil 66 Q 1,99 131,34
4 Central Jardine Pen Set 50 C 4,99 249,5 Pen Set 50 V 4,99 249,5
5 East Jones Pencil 95 B 1,99 189,05 Binder 60 A 4,99 299,4
6 Central Kivell Binder 50 X 19,99 999,5 Pen Set 96 G 4,99 479,04

Except first 3 columns (ID, Region, Rep) all other columns contain the same substring "Loop" with number of loop, e.g. loop1, loop2, loop3 --> Item_Loop1, Item_Loop2, Item_Loop3

I need to change this data to the one stacked by ID. So for example for ID = 1 there will be 2 additional rows for each loop:

ID Region Rep Loop Item_ Units_A_ Type_col_X_ Unit_Cost_Type_New_xyz Total_ABCD
1 Central Andrews Loop1 Pencil 75 A 1,99 149,25
1 Central Andrews Loop2 Pencil 66 Q 1,99 131,34
1 Central Andrews Loop3 Pencil 14 X 1,29 18,06
4 Central Jardine Loop1 Pen Set 50 C 4,99 249,5
4 Central Jardine Loop2 Pen Set 50 V 4,99 249,5
4 Central Jardine Loop3 Binder 11 Y 4,99 54,89
5 East Jones Loop1 Pencil 95 B 1,99 189,05
5 East Jones Loop2 Binder 60 A 4,99 299,4
5 East Jones Loop3 Pencil 35 Q 4,99 174,65
5 East Jones Loop4 Pen Set 16 A 15,99 255,84
5 East Jones Loop5 Binder 60 G 8,99 539,4

What I've tried doing us to split columns by _ and stacking it with 'loop'. The problem that I found was that my columns have multiple _ in it and after 'loopX' there is additional text. I've tried slicing column names with rfind/slice, but I couldn't execute reasonable results.

My general idea was more or less:

df.columns = df.columns.str.split('_', expand=True) 
#I've tried to use rfind and slicing column name to -> find index of last '_' and get 5 next characters, but I couldn't execute it without errors
df = df.stack(dropna=False).rename_axis(['ID','Region','Rep','Loop']).reset_index()

I'm also not sure why in preview tables look good, but when posted it's not :(

pandas dataframe python
3 Answers

Normalize the column names in such a way that each column name starts with the prefix LoopN_, then split and expand the col names around the delimiter _ to create a multiindex columns, then stack the frame on level=0 to reshape

df = df.set_index(["ID", 'Region', 'Rep'])
df.columns = df.columns.str.replace(r'(.*)(Loop\d+)(.*)', r'\2_\1\3')\
                       .str.split('_', n=1, expand=True)
df = df.stack(0)

                            Item_ Total_ABCD Type_col_X_ Unit_Cost_Type_New_xyz  Units_A_
ID Region  Rep                                                                           
1  Central Andrews Loop1   Pencil     149,25           A                   1,99        75
                   Loop2   Pencil     131,34           Q                   1,99        66
4  Central Jardine Loop1  Pen Set      249,5           C                   4,99        50
                   Loop2  Pen Set      249,5           V                   4,99        50
5  East    Jones   Loop1   Pencil     189,05           B                   1,99        95
                   Loop2   Binder      299,4           A                   4,99        60
6  Central Kivell  Loop1   Binder      999,5           X                  19,99        50
                   Loop2  Pen Set     479,04           G                   4,99        96
user_128331660

Use pd.MultiIndex to split your columns. level0 contains LoopX while level1 are the column names without LoopX. Replace current index withe the two combined levels and stack level 0.

df = df.set_index(['ID', 'Region', 'Rep'])
level0 = df.columns.str.extract(r'(Loop\d+)', expand=False)
level1 = df.columns.str.replace(r'_Loop\d+', '', regex=True)
df.columns = pd.MultiIndex.from_arrays([level0, level1])
out = df.stack(level=0).reset_index()

>>> out
   ID   Region      Rep level_3     Item TotalABCD Type_col_X Unit_Cost_Type_Newxyz  Units_A
0   1  Central  Andrews   Loop1   Pencil    149,25          A                  1,99       75
1   1  Central  Andrews   Loop2   Pencil    131,34          Q                  1,99       66
2   4  Central  Jardine   Loop1  Pen Set     249,5          C                  4,99       50
3   4  Central  Jardine   Loop2  Pen Set     249,5          V                  4,99       50
4   5     East    Jones   Loop1   Pencil    189,05          B                  1,99       95
5   5     East    Jones   Loop2   Binder     299,4          A                  4,99       60
6   6  Central   Kivell   Loop1   Binder     999,5          X                 19,99       50
7   6  Central   Kivell   Loop2  Pen Set    479,04          G                  4,99       96

Details:

>>> level0
Index(['Loop1', 'Loop1', 'Loop1', 'Loop1', 'Loop1',
       'Loop2', 'Loop2', 'Loop2', 'Loop2', 'Loop2'],
      dtype='object')

>>> level1
Index(['Item', 'Units_A', 'Type_col_X', 'Unit_Cost_Type_Newxyz', 'TotalABCD',
       'Item', 'Units_A', 'Type_col_X', 'Unit_Cost_Type_Newxyz', 'TotalABCD'],
      dtype='object')
user_152399510

Another solution:

df.columns = df.columns.str.replace(
    r"(.*)_Loop(\d+)(.*)", r"\1_\3_Loop\2", regex=True
)
stubnames = set(c.rsplit("_", maxsplit=1)[0] for c in df.columns if "Loop" in c)
df = (
    pd.wide_to_long(df, stubnames, i="ID", j="xxx", sep="_Loop")
    .reset_index()
    .drop(columns="xxx")
    .sort_values(by="ID")
)
print(df)

Prints:

   ID   Region      Rep  Units_A Type_col_X Total_ABCD     Item Unit_Cost_Type_New_xyz
0   1  Central  Andrews       75          A     149,25   Pencil                   1,99
4   1  Central  Andrews       66          Q     131,34   Pencil                   1,99
1   4  Central  Jardine       50          C      249,5  Pen Set                   4,99
5   4  Central  Jardine       50          V      249,5  Pen Set                   4,99
2   5     East    Jones       95          B     189,05   Pencil                   1,99
6   5     East    Jones       60          A      299,4   Binder                   4,99
3   6  Central   Kivell       50          X      999,5   Binder                  19,99
7   6  Central   Kivell       96          G     479,04  Pen Set                   4,99
user_100359850
Related