From Convert pandas dataframe from wide to long. how can I extend the solutions to work on a database of the form:
a_1 ab_col_1 ac_1 a_2 ab_col_2 ac_2
2 3 4 5 6 7
The issue here is the repeating separator in some columns
You can use rsplit with parameter n=1 for MultiIndex and then reshape by stack and last use reset_index for remove MultiIndex:
df.columns = df.columns.str.rsplit('_', expand=True, n=1)
df = df.stack().reset_index(drop=True)
print (df)
a ab_col ac
0 2 3 4
1 5 6 7
df.columns = df.columns.str.rsplit('_', expand=True, n=1)
df = df.stack().reset_index(level=0, drop=True)
print (df)
a ab_col ac
1 2 3 4
2 5 6 7
