mardi 22 octobre 2019

Map rows containing strings to grouping labels if contains keywords

I am trying to read a column with 100k+ rows of free-text strings, search for specific keywords, and populate a new column with a string relating to the original input. I have a df as follows.

Keyword                                        Mapping
Email: >>                                   Outgoing Email
Email: <<                                   Incoming Email
Submitted Form                              Formfill
Opened                                      Formfill
LinkedIn                                    LinkedIn
call                                        Phonecall
called                                      Phonecall
phone                                       Phonecall
voicemail                                   Phonecall

I would like to read another column 'activity_detail', which has many free text strings for the Keyword in the mapping df, and if the string contains the Keyword, populate the 'activity_mapped' with the Mapping word.

activity_detail
Email: << Whitepages Pro 
Company & Client Prep Call
January Newsletter - send
Email: <<  summary and next steps
Email: >> Re: summary and next steps
Called, no response lvm
Left a voicemail
Email: >> See You at the Event 2019
Delivered Gift [via Sendoso]
Submitted Form "Product Demo"
Opened Protect Your Data Webinar

I've tried np.where with str.contains, but the Keywords are more than 500 so this isn't feasible. I've tried using a for loop with np.where str.contains but cannot get it to work either.

I'd like to see the output as such:

activity_detail                     activity_mapped
Email: << Whitepages Pro            Incoming Email
Company & Client Prep Call          Phonecall
January Newsletter - Send 2         NAN
Email: << summary and next steps    Incoming Email
Email: >> Re:summary and next steps     Outgoing Email
Called, no response lvm             Phonecall
Left a voicemail                    Phonecall
Email: >> See You at the Event 2019     Outgoing Email
Delivered Bundle Gift [via Sendoso]       Sendoso
Submitted Form "Product Demo"         Formfill
Opened Protect Your Data Webinar      Formfill

Aucun commentaire:

Enregistrer un commentaire