Python - extract/copy delimited text from from on column to new column xlsx

Question

I have an .xlsx file which has 13 columns. I need to extract/copy text from a string in one column specifically into a new column. The string in the column is structured with underscore (_) delimiters:

TextA_TextB - TextB_TextC_TextD_TextE_TextF

I need to copy the TextA specifically into a new column. What would be the best approach here?

Also please give an example of your *actual* data (say 5 rows) since it's not absolutely clear what it looks like. See [mcve]. — jpp, Apr 28 '18 at 17:20
Reopening until we have more clarity. It's not clear regex is required here. — jpp, Apr 28 '18 at 17:38

score 0 · Answer 1 · answered Apr 28 '18 at 23:43

You could try this.

Once you have the column in a DataFrame

In [30]: df
Out[30]: 
                        a
0  test1_test2_tes3_test4

In [31]: df['a'] = df['a'].apply(lambda x: x.split('_'))

In [33]: df = pd.concat([df, df['a'].apply(pd.Series)], axis=1)

In [34]: df
Out[34]: 
                             a      0      1     2      3
0  [test1, test2, tes3, test4]  test1  test2  tes3  test4

Python - extract/copy delimited text from from on column to new column xlsx

1 Answers1