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

0 votes

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

Apr 7, 2023 in Others by narikkadan
• 63,420 points
1,212 views

1 answer to this question.

0 votes

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
answered Apr 7, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Excel: How to merge two columns into one (from different sheets or separated columns)

This equation is completely adjustable. Your two ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
541 views
0 votes
1 answer

How to paste an Excel chart into PowerPoint placeholder using Python?

Very near indeed! Copy and Paste are ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
3,400 views
0 votes
1 answer

How to change two different date format into single date format in excel

With data in A2, in B2 enter: =IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))) and apply your format ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
880 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,853 views
+1 vote
2 answers

View onto a numpy array?

 just index it as you normally would. ...READ MORE

answered Oct 18, 2018 in Python by roberto
693 views
0 votes
1 answer
0 votes
1 answer

Printing a large numpy array

numpy.set_printoptions(threshold='nan') READ MORE

answered Jul 20, 2018 in Python by Nietzsche's daemon
• 4,260 points
1,541 views
0 votes
1 answer

How to detect value in change in excel using python?

You might keep a txt file with ...READ MORE

answered Mar 24, 2023 in Others by Kithuzzz
• 38,010 points
389 views
0 votes
1 answer

How to read an Excel CSV file in Python?

The csv module or the pandas library ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
510 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP