Manipulate String in One Column and Append values to new rows in the DataFrame - Pandas

Question

I have data in a Pandas dataframe in the following format:

Campaign | Spend |

L003-FL-Panama | 800 |

L015, L020 CA- Rancho | 600 |

What I need is to append each value from the Campaign column that starts with a capital 'L' and ends with three digits to a new column. To make matters more complicated, if there are multiple values in this format within a string in the campaign column, then I need each value to be listed in its own new row.

The output would be the following:

Campaign | Spend | Store

L003-FL-Panama | 800 | L003

L015, L020 CA- Rancho | 600 | L015

L015, L020 CA- Rancho | 600 | L020

Apologies if this doesn't make sense, let me know if I can clarify.

Can you show us the code for your latest attempts, including it in your question? — jpp
– jpp, Commented Aug 29, 2018 at 17:35

Onyambu · Accepted Answer · 2018-08-29 18:10:05Z

2

You could use extractall then merge to the original dataframe

 df.reset_index().merge(df.Campaign.str.extractall('(\\bL\\d{3})').reset_index(),
           left_on='index',right_on  = 'level_0').drop(['index','level_0','match'],
           axis = 1).rename({0:'store'},axis = 1)

Out[65]: 
                 Campaign  Spend store
0         L003-FL-Panama     800  L003
1  L015, L020 CA- Rancho     600  L015
2  L015, L020 CA- Rancho     600  L020

edited Aug 29, 2018 at 18:10

answered Aug 29, 2018 at 18:04

Onyambu

80.3k3 gold badges29 silver badges65 bronze badges

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

1 Comment

Billy Hansen Over a year ago

Yes! Thank you so much

Collectives™ on Stack Overflow

Manipulate String in One Column and Append values to new rows in the DataFrame - Pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related