Create columns of a pandas dataframe from other columns containing data in text format

Question

I have the following dataframe

data= {
    'a_index':[55, 72, 112, 55, 53, 100, 89],
    'make':['TY', 'FD', 'TA', 'HA', 'MA', 'BW', 'VN'],
    'p_index':[120, 70, 120, 128, 180, 172, 150],
    'score':['2.3,1.3,3.2,3.4','2.7,4.3, 4.2,3.4','2.3,4.3, 4.2,,3.4', '2.3,4.3, 4.2,3.4', '1.3,5.3, 7.2,3.4', '2.3,4.3, 4.2,3.4', '2.3,4.3,4.2,3.4'],
}
df = pd.DataFrame(data,
                  index=['NK', 'JN', 'NA', 'PP', 'DK', 'HA', 'CK'])
df

which gives me

    a_index make  p_index   score
NK  55      TY     120      2.3,1.3,3,2,3.4
JN  72      FD     70       2.7,4.3, 4,2,3.4
NA  112     TA     120      2.3,4.3, 4,2,,3.4
PP  55      HA     128      2.3,4.3, 4,2,3.4
DK  53      MA     180      1.3,5.3, 7,2,3.4
HA  100     BW     172      2.3,4.3, 4,2,3.4
CK  89      VN     150      2.3,4.3,4,2,3.4

What is the easiest way to the following dataframe from this dataframe

    a_index make    p_index score             sore_1    sore_2  sore_3  sore_4
NK   55     TY      120      2.3,1.3,3,2,3.4    2.3     1.3     3.2      3.4
JN   72     FD      70       2.7,4.3, 4,2,3.4   2.7     4.3     4.2      3.4
NA   112    TA      120      2.3,4.3, 4.2,3.4   2.3     4.3     4.2      3.4
PP   55     HA      128      2.3,4.3, 4.2,3.4   2.3     4.3     4.2      3.4
DK   53     MA      180      1.3,5.3, 7,2,3.4   1.3     5.3     7.2      3.4
HA   100    BW      172      2.3,4.3, 4.2,3.4   2.3     4.3     4.2      3.4
CK   89     VN      150      2.3,4.3,4.2,3.4    2.3     4.3     4.2      3.4

instead of 2.3,4.3, 4,2,,3.4 should be 2.3,4.3, 4,2, 3.4? — kederrac
– kederrac, Commented Mar 22, 2020 at 18:11
@kederrac, Ukrainian-serge and Samira Kumar thank you for your time and answering my question. Appreciate it. All three work for the latest pandas version. I have upvoted all three answers but can accept only one answer. — pi_pascal
– pi_pascal, Commented Mar 23, 2020 at 18:18

kederrac · Accepted Answer · 2020-03-22 18:33:44Z

4

you can use:

pd.concat(
    [
        df, 
        df['score'].str.split(',', expand=True).rename(
            lambda x: f'score_{x}',axis='columns')
    ], axis=1)

answered Mar 22, 2020 at 18:33

kederrac

17.4k6 gold badges36 silver badges58 bronze badges

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

4 Comments

sammywemmy Over a year ago

u could replace the rename section with ... add_prefix('score_')

kederrac Over a year ago

but is more readable using rename with axis=columns there are many ways to do it, and I guess is subjective

pi_pascal Over a year ago

Great. This worked perfectly with latest version of pandas. I liked your solution. But how can we write the same functionality in pandas 0.17? This is the version of pd I use in prod.

kederrac Over a year ago

@Ayalew thx, but as I was saying this is a task for another question

Samira Kumar · Accepted Answer · 2020-03-22 18:27:25Z

3

You can try using this.

df['score'].str.split(',').apply(pd.Series).rename(columns = {0:'score_1',1:'score_2',2:'score_3',3:'score_4'})

    score_1 score_2 score_3 score_4
NK  2.3 1.3 3.2 3.4
JN  2.7 4.3 4.2 3.4
NA  2.3 4.3 4.2 3.4
PP  2.3 4.3 4.2 3.4
DK  1.3 5.3 7.2 3.4
HA  2.3 4.3 4.2 3.4
CK  2.3 4.3 4.2 3.4

and then merge it back to original dataframe.

answered Mar 22, 2020 at 18:27

Samira Kumar

5215 silver badges19 bronze badges

Comments

Ukrainian-serge · Accepted Answer · 2020-03-22 18:29:20Z

3

Try:

exploded = df.score.apply(lambda x: pd.Series(x.split(',')))       # use explode

exploded.columns = ['score_'+str(col) for col in exploded.columns] # rename columns

pd.concat([df, exploded], axis=1)                                  # concat to original df

print(df)

    a_index make  p_index             score score_0 score_1 score_2 score_3 score_4
NK       55   TY      120   2.3,1.3,3,2,3.4     2.3     1.3       3       2     3.4
JN       72   FD       70  2.7,4.3, 4,2,3.4     2.7     4.3       4       2     3.4
NA      112   TA      120  2.3,4.3, 4,2,3.4     2.3     4.3       4       2     3.4
PP       55   HA      128  2.3,4.3, 4,2,3.4     2.3     4.3       4       2     3.4
DK       53   MA      180  1.3,5.3, 7,2,3.4     1.3     5.3       7       2     3.4
HA      100   BW      172  2.3,4.3, 4,2,3.4     2.3     4.3       4       2     3.4
CK       89   VN      150   2.3,4.3,4,2,3.4     2.3     4.3       4       2     3.4

answered Mar 22, 2020 at 18:29

Ukrainian-serge

8547 silver badges12 bronze badges

2 Comments

pi_pascal Over a year ago

This is great. Do you know how to do the same with pandas 0.17

Ukrainian-serge Over a year ago

Sorry I didn't see your version of Python. When I have time I'll create a Python 2.7 env and post solution.

Collectives™ on Stack Overflow

Create columns of a pandas dataframe from other columns containing data in text format

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related