1
$\begingroup$

Using names = df['Name and Location'].str.split(',', expand=True) I am able to split this dense data at delimiters like colons.

I'm stuck on how to recombine the data into a flatter record. I've tried:

pd.concat([df, names])

Records end at "complaint #", and begin at date: which is in another column.

Date: 1999/12/29
**Last_Name , First_Name**
City: City_Name
County: OUT_OF_STATE
Zip Code: 00000
License #: AA0000000
Complaint # AA00000000000

Date: 1999/03/01
**Company:** Company_Name,_INC
City: City_Name
County: County_Name
Zip Code: 00000
Company: Company_Name LIC AA0000
City: City_Name
County: County_Name
Zip Code: 00000
License: string_or_int
Complaint # AA00000000000

Date: 1999/05/04
**Last_Name**, First_Name
Company: Company_Name
City: City_Name
County: County_Name
Zip Code: 00000
License #: AA00000000000
Complaint # AA00000000000

Ideally, each "record" would ultimately be flat, like:

First Name Last Name Company City County Zip Code License Complaint Date  

Last_name_1 First_name_1 Company_Name_1 City_1 County_1 00001 AA000000 1999/12/29
Improve this question
$\endgroup$
5
  • $\begingroup$ Welcome to DataScienceSE. I would read all of this as a text file, parse it and store every record as a dictionary in a list. Once this is done, it should be easy to recreate a clean dataframe. $\endgroup$
    – Erwan
    Commented Mar 24, 2022 at 19:39
  • $\begingroup$ Thank you for the welcome, @Erwan Would that method work for something that is multiple columns wide? $\endgroup$
    – Megan Perry
    Commented Mar 24, 2022 at 21:21
  • $\begingroup$ Well, doing the code yourself gives you full control over how the data should be parsed. You can split the lines according to a separator, and then interpret the content directly in whatever way makes sense.Personally I tend to go back to good old manual parsing in this kind of case, but there might be other options. $\endgroup$
    – Erwan
    Commented Mar 24, 2022 at 21:35
  • $\begingroup$ @Erwan there are 8,000 rows of this, and I am far too lazy of a coder to parse by hand. I'm in awe and deeply respectful that you would though. It certainly is called for sometimes. I think I have found a solution in Pandas, though. Is there a best etiquette for answering one's own question? $\endgroup$
    – Megan Perry
    Commented Mar 25, 2022 at 1:18
  • $\begingroup$ Good that you found a solution :) Writing your own answer is encouraged, there's no particular etiquette for it. $\endgroup$
    – Erwan
    Commented Mar 25, 2022 at 10:50

1 Answer 1

Reset to default
0
$\begingroup$

To split at a delimiter, and also create and combine a new column with the existing df, use:

df = pd.concat((df, df['Column_to_Split'].str.split('String_to_Go:', expand=True)), axis=1, ignore_index=True)

Any delimiter can be used, including an empty string. The key here is expand = True as it creates a new column, which was the goal.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.