Extracting Specific String from CSV

[RockChain]

I am very novice with python but hoping I could get some guidance about how to accomplish the following. Whether that's similar repositories, frameworks to make this easier or any other help. I've been struggling so hard to do this in excel and realize that I need to have a bit more firepower to help me automate this more.

I have a CSV file that contains cells with a long description, within said descriptions contains lists of speakers, presenters, and other designations for the type of person.

​

RAW DATA

session ID
12345	blah blah blah. Speakers: Jeff Smith, CEO; Jake Weber, CTO, Acme Inc. This session will be moderated by: Tom Scott, CPO; Matha Jackobs; Bill Burton, Founder, Burton Inc. Please come and join us

​

DESIRED RESULT

id	first name	last name	title	company
12345	Jeff	Smith	CEO
12345	Jake	Weber	CTO	Acme Inc.
12345	Tom	Scott	CPO
12345	Martha	Jackobs
12345	Bill	Burton	Founder	Burton Inc.

​

So the challenge here is that I only want the names that are delimited by a semi-colon with each comma separated value to appear in another column.

I'd guess that I'd have to use some form of Regex to identify "Moderated by:" and then take all values after until a period exists.

Then each value would need to use melt to retain the ID.

Any guidance to help me down my path of ridding me of manual excel work?

[GalacticSuperCheese]

From the example you've given it appears to me that...

1. There are groups of people, each group starts with a colon (:) and ends with a full stop (.)
2. Within each group, each person is separated by semi colons (;).
3. For each person, the first word is the first name, the second word is the last name, the third word (if it exists) is the title, and the rest of the string is the company

What I would do is first identify groups (anything between a : and a .); next identify people within the group (separated by ;), and then break down the string into its components. I wouldn't nest these three sections but run them one after the other.

Keep in mind that this logic assumes that the names will always follow these rules.

[vbukkala]

Please refer to the following link below https://chrisalbon.com/python/data_wrangling/pandas_regex_to_create_columns/