Divide DataFrame Column on (,) into two new columns

Question

I have a pandas DataFrame called data_combined with the following structure:

    index   corr_year   corr_5d
0   (DAL, AAL)  0.873762    0.778594
1   (WEC, ED)   0.851578    0.850549
2   (CMS, LNT)  0.850028    0.776143
3   (SWKS, QRVO)    0.850799    0.830603
4   (ALK, DAL)  0.874162    0.744590

Now I am trying to divide the column named index into two columns on the (,). The desired output should look like this:

    index1  index2   corr_year  corr_5d
0   DAL    AAL   0.873762   0.778594
1   WEC    ED    0.851578   0.850549
2   CMS    LNT   0.850028   0.776143
3   SWKS   QRVO  0.850799   0.830603
4   ALK    DAL   0.874162   0.744590

I have tried using pd.explode() with the following code

data_results_test = data_results_combined.explode('index')
data_results_test

Which leads to the following output:

    index   corr_year   corr_5d
0   DAL 0.873762    0.778594
0   AAL 0.873762    0.778594
1   WEC 0.851578    0.850549
1   ED  0.851578    0.850549

How can I achieve the split with newly added columns instead of rows. pd.explode does not seem to have any option to choose wether to add new rows or columns

Alex · Accepted Answer · 2022-11-17 15:37:42Z

2

How about a simple apply? (Assuming 'index' column is a tuple)

data_results_combined['index1'] = data_results_combined['index'].apply(lambda x: x[0])
data_results_combined['index2'] = data_results_combined['index'].apply(lambda x: x[1])

answered Nov 17, 2022 at 15:37

Alex

741 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

crashMOGWAI · Accepted Answer · 2022-11-17 15:37:05Z

0

df[['index1','index2']] = df['index'].str.split(',',expand=True)

answered Nov 17, 2022 at 15:37

crashMOGWAI

7021 gold badge5 silver badges23 bronze badges

2 Comments

Niko Over a year ago

I tried your answer however I am getting the error: ValueError: Columns must be same length as key Which is weird because the length of the data frame is 96 x 3 so there is no column longer than the other

crashMOGWAI Over a year ago

You may be having a col name dupe/collision. As best practice I try not to use 'index' as a col name since sometimes pandas internals wants to use it when trying to preserve the numbered/named index during operations.

Collectives™ on Stack Overflow

Divide DataFrame Column on (,) into two new columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related