Delete part of string if string contains sub-string in single column instance

Question

I have the following in a pandas DF of message A and B:

Message_A
"(Live Storage: 20.00   included in Plan for $15.00 - Exceess of 10.0   @ $6.0)" 
"(Live Storage: 5.00   included in Plan for $5.00 - Exceess of 11.0   @ $40.0)" 
"(Live Storage: 10.0   out of 150.00   included in Plan for $10.00)" 
"(Live Storage: 146.0   out of 200.00   included in Plan for $150.00)" 
"(Live Storage: 150.0   - Tier 1501 to 2000   @ $350)" 
"(PY Solution -Flat Fee- of $30.00 applied)" 
"(Live Storage: 17.0   out of 40.00   included in Plan for $20.00)" 
"(Live Storage: 67.0   @ $5.00)" 
"(Live Storage: 5.00   included in Plan for $55.00 - Exceess of 13.0   @ $6.0)" 
"(Live Storage: 741.0   @ $3.00)" 
"(Live Storage: 30.00   included in Plan for $150.00 - Exceess of 39.0   @ $6.0)" 
"(Live Storage: 65.0   - Tier 51 to 75   @ $250)" 
"(Live Storage: 567.0   - Tier 501 to 750   @ $1750)" 

Message_B
"(! Price for Live Storage not found in Pricing Plan !)" 
"(! Price for Live Storage not found in Pricing Plan !) ( ABC Storage: 141.0   @ $2.00) (Discount of 10.0% applied to storage amount)" 
"(! Price for Live Storage not found in Pricing Plan !)" 
"(! Price for Live Storage not found in Pricing Plan !) ( ABC Storage: 1.0   @ $3.00)" 
"( ABC Storage: 137.0   - Tier 1251 to 150   @ $100) (!  ABC Storage Limit of 00   Exceeded !) (Local Allocated Storage: 20.00   @ $0.40) (Live Storage: 16.0   @ $??)" 
"(Discount of 10.0% applied to storage amount) (! Price for Live Storage not found in Pricing Plan !)"
"(! Live Storage not found in Pricing Plan !) (Discount of 10.0% applied to storage amount)" 
"(! Price for Live Storage not found in Pricing Plan !) (Local Allocated Storage: 100.00   @ $0.50)" 
"(! Price for Storage not found in Pricing Plan !) (Live Storage: 18.0   @ $??)" 
"(! Price for Storage not found in Pricing Plan !)(Live Storage: 69.0   @ $??)  ( ABC Storage: 401.0   @ $1.50)" 
"(Live Storage: 6.0   @ $??) (! Price for Storage not found in Pricing Plan !)" 
"(! Price for Live Storage not found in Pricing Plan !) (Discount of 10.0% applied to storage amount)" 
"(! Price for Live Storage not found in Pricing Plan !) ( ABC Storage: 270.0   - Tier 201 to 300   @ $400)"

I am looking to remove the error messages from message_B. These are messages where some of the text changes, but all error messages contain a '!' or '?$$' in them. I then want to join to message_A for a single column message. For clarity, the middle step looks like:

Message_B
Nan
"( ABC Storage: 141.0   @ $2.00) (Discount of 10.0% applied to storage amount)" 
Nan
"( ABC Storage: 1.0   @ $3.00)" 
"( ABC Storage: 137.0   - Tier 1251 to 150   @ $100)(Local Allocated Storage: 20.00   @ $0.40)" 
"(Discount of 10.0% applied to storage amount)" 
"(Discount of 10.0% applied to storage amount)" 
 "(Local Allocated Storage: 100.00   @ $0.50)" 
Nan
"( ABC Storage: 401.0   @ $1.50)" 
Nan
"(Discount of 10.0% applied to storage amount)" 
"( ABC Storage: 270.0   - Tier 201 to 300   @ $400)"

With the final result just been a single column string (drop Nan). I have been able to split message_B by dropping the '(' and .replace ')' with '|' to give a separator to split on. I have split message_B into a (new) different dataframe, but how do I iterate over the full DF and delete the unwanted messages? (I do not want to drop full rows) I have tried df[df['Message_B'].str.contains("(Live Storage: 18.0 @ $??)")==False] But I need to do this for each type of message and the figure within the message changes. Also, I now realize that I cant use .str.contains over a full DF. Any help would be appreciated, and sorry for how I set out the DF in the message, found it was the easiest for readability. Thanks

EDIT I have been able to take out the standard error message with:

error_msg1 = "(! Price for live Storage not found in Pricing Plan !)" 
replace_with = ''
bumi_output['Message_B'] = [i.replace(error_msg1, replace_with) for i in bumi_output['Message_B']]

Is there a way to use this method to take out the error messages where a section of the messge can change?? for example: (Live Storage: 18.0 @ $??) (Live Storage: 69.0 @ $??)

Thank you.

You don't need to include the snippets, you can make things into code blocks by indenting each line (4 spaces). Or while you're writing, if you select the block and hit the {} symbol in the editor. — DilithiumMatrix
– DilithiumMatrix, Commented Dec 4, 2015 at 2:05
Thank you @DilithiumMatrix, I had something similar done, using {}, but I didn't have the rows on different lines. I had ',' separator which made it look long and hard to read. I'm not seen much interest, so I could be out of luck with this one. — Ken Lawlor
– Ken Lawlor, Commented Dec 4, 2015 at 13:01

Woody Pride · Accepted Answer · 2015-12-04 15:04:06Z

1

The following rather ugly list comprehension gets you what you want from message B by simply finding all the parentheses and excluding the ones with '!' and '$??' and then joining the remaining parts together

new_B = [' '.join([subs for subs in re.findall('\(.+?\)', val) if '!' not in subs and '$??' not in subs]) 
for val in df['Message_B']]

Then just add this to A

df['Message_A'] = df['Message_A'] + new_B

To see that this has worked:

In [26]: df['Message_A'][1]
Out[26]: '(Live Storage: 5.00   included in Plan for $5.00 - Exceess of 11.0   @ $40.0)( ABC Storage: 141.0   @ $2.00) (Discount of 10.0% applied to storage amount)'

answered Dec 4, 2015 at 15:04

Woody Pride

14k10 gold badges51 silver badges64 bronze badges

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

2 Comments

Ken Lawlor Over a year ago

Hi @Woody Pride, I have been trying to get your answer to work but it is probably something on my side. If I follow the logic, your splitting on '(' and ')', then returning anything that doesn't have '!' or '$??'.? For clarity, I am reading the df in with pandas read_csv (including header = 0, names = [x,y,z]). I have also set 'Message 3' to string with '.astype(str)'. When I run your answer, it does run (with no error) but I get no difference. I also don't get the New_B column. I have tried to figure out the problem as I am trying to learn as much as possible, but with little joy :(

Ken Lawlor Over a year ago

Hi @Woody, I have accepted your answer. I realized I was making a basic mistake. Thank you very much.

Collectives™ on Stack Overflow

Delete part of string if string contains sub-string in single column instance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related