How to split columns into two sub columns under their parent column using python in excel

Question

Python suggestion is required. I need to divide an Excel sheet with 20 columns into two pre and post-columns, where pre will have a value of 12 and post will have a value of 23, and so on. These columns should also be placed immediately under their respective parent columns using pandas.

Input:

| Column A | 
| -------- | 
| 12 --> 23|
| 13 --> 24|

Output

| column A |
|pre| |post|
| 12| | 23 |
| 13| | 24 |

I can't use the column name directly because there are many columns.

I tested the subsequent code If I knew the column name, it worked, but if I had to loop the columns without knowing their names, it didn't.

df = pd.read_excel('path/to/excel_file.xlsx')

for col in df.columns:
    new_cols = df[col].str.split(expand=True)
    
    df[col + '_1'] = new_cols[0]
    df[col + '_2'] = new_cols[1]
df.drop(columns=df.columns[:-2], inplace=True)

It's not working for me

Answer 1

Use str.split:

df[['pre', 'post']] = df['Column A'].str.split(r'\s*-->\s*', expand=True)
print(df)

# Output
    Column A pre post
0  12 --> 23  12   23
1  13 --> 24  13   24

So for multiple columns:

data = []

for col in df.columns:
    new_cols = df[col].str.split(r'\s*-->\s*', expand=True)
    if new_cols.shape[1] == 2:
        df = df.drop(columns=col)
        data.append(new_cols.add_prefix(col + '_'))
df = pd.concat([df, *data], axis=1)

Output:

>>> df
  Column A_0 Column A_1
0         12         23
1         13         24