CODEWITHSUNDEEP
pythonexcelpandas
Eric Shreve
Eric Shreve

Reputation: 186

python separate text into different column with comma

I'm pulling data from a database and writing to a new Excel file for a report. My issue is that the last column of data has data that is separated by commas and needs to be separated into separate columns.

As an example I have data like the following:

Name  Info
Mike  "a, b, c, d"
Joe  "a, f, z"

I need to break these letters out into separate columns. The a's, b's, etc. don't have to line up so that each letter is in the "correct" column. They just need to be broken out into separate columns.

I'm doing this in Python. I'm open to using other libraries like Pandas. There will be other columns included, not just two. I made a simple example.

Any help is appreciated.

Upvotes: 3

Views: 3853

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

IIUC:

df.assign(**df['Info'].str.split(',', expand=True).add_prefix('Info_'))

Output:

   Name        Info Info_0 Info_1 Info_2 Info_3
0  Mike  a, b, c, d      a      b      c      d
1   Joe     a, f, z      a      f      z   None

Note: You can also use join instead of assign (Using @coldspeed \s* to elimate spaces):

df.join(df['Info'].str.split('\s*,\s*', expand=True).add_prefix('Info_'))

Upvotes: 5

BENY
BENY

Reputation: 323226

From pandas str.split

df=pd.concat([df,df.Info.str.split(',',expand=True)],1)
df
Out[611]: 
   Name        Info  0   1   2     3
0  Mike  a, b, c, d  a   b   c     d
1   Joe     a, f, z  a   f   z  None

Upvotes: 2

Related Questions