0

I used JotForm Configurable list widget to collect data, but having troubles parsing the resulting data correctly. When I use

testdf = pd.read_csv ("TestLoad.csv")

The data is read in as two records and the details are stored in the "Information" column. I understand why it is parsed the way it is, but I would like to break out the details into multiple records as noted below.

Any help would be appreciated.

Sample CSV

"Date","Information","Type"
"2015-12-06","First: Tom, Last: Smith, School: MCAA; First: Tammy, Last: Smith, School: MCAA;","New"
"2015-12-06","First: Jim, Last: Jones, School: MCAA; First: Jane, Last: Jones,  School: MCAA;","New"

Current Result

Date        Information                                                                      Type
2015-12-06  First: Tom, Last: Smith, School: MCAA; First: Tammy, Last: Smith, School: MCAA;  New
2015-12-06  First: Jim, Last: Jones, School: MCAA; First: Jane, Last: Jones,  School: MCAA;  New

Desired Result

Date        First  Last   School Type
2015-12-06  Tom    Smith  MCAA   New
2015-12-06  Tammy  Smith  MCAA   New
2015-12-06  Jim    Jones  MCAA   New
2015-12-06  Jane   Jones  MCAA   New
Improve this question

2 Answers 2

Reset to default
3

This is useless text that is required to keep an answer from being downvoted by the moderators. Here is the data I used:

"Date","Information","Type"
"2015-12-07","First: Jim, Last: Jones, School: MCAA; First: Jane, Last: Jones,  School: MCAA;","Old"
"2015-12-06","First: Tom, Last: Smith, School: MCAA; First: Tammy, Last: Smith, School: MCAA;","New"

import pandas as pd
import numpy as np
import csv
import re
import itertools as it
import pprint
import datetime as dt

records = [] #Construct a complete record for each person

colon_pairs = r"""
    (\w+)   #Match a 'word' character, one or more times, captured in group 1, followed by..
    :       #A colon, followed by...
    \s*     #Whitespace, 0 or more times, followed by...
    (\w+)   #A 'word' character, one or more times, captured in group 2.
"""

colon_pairs_per_person = 3

with open("csv1.csv", encoding='utf-8') as f:
    next(f) #skip header line
    record = {}

    for date, info, the_type in csv.reader(f):
        info_parser = re.finditer(colon_pairs, info, flags=re.X)

        for i, match_obj in enumerate(info_parser):
            key, val = match_obj.groups()
            record[key] = val

            if (i+1) % colon_pairs_per_person == 0: #then done with info for a person
                record['Date'] = dt.datetime.strptime(date, '%Y-%m-%d') #So that you can sort the DataFrame rows by date.
                record['Type'] = the_type

                records.append(record)
                record = {}

pprint.pprint(records)
df = pd.DataFrame(
        sorted(records, key=lambda record: record['Date'])
)
print(df)
df.set_index('Date', inplace=True)
print(df)

--output:--
[{'Date': datetime.datetime(2015, 12, 7, 0, 0),
  'First': 'Jim',
  'Last': 'Jones',
  'School': 'MCAA',
  'Type': 'Old'},
 {'Date': datetime.datetime(2015, 12, 7, 0, 0),
  'First': 'Jane',
  'Last': 'Jones',
  'School': 'MCAA',
  'Type': 'Old'},
 {'Date': datetime.datetime(2015, 12, 6, 0, 0),
  'First': 'Tom',
  'Last': 'Smith',
  'School': 'MCAA',
  'Type': 'New'},
 {'Date': datetime.datetime(2015, 12, 6, 0, 0),
  'First': 'Tammy',
  'Last': 'Smith',
  'School': 'MCAA',
  'Type': 'New'}]

        Date  First   Last School Type
0 2015-12-06    Tom  Smith   MCAA  New
1 2015-12-06  Tammy  Smith   MCAA  New
2 2015-12-07    Jim  Jones   MCAA  Old
3 2015-12-07   Jane  Jones   MCAA  Old

            First   Last School Type
Date                                
2015-12-06    Tom  Smith   MCAA  New
2015-12-06  Tammy  Smith   MCAA  New
2015-12-07    Jim  Jones   MCAA  Old
2015-12-07   Jane  Jones   MCAA  Old
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

7stud - thanks for the solution. This is the method approach I ended up using since the number of people within a record could be 1:n
1

I used regex separators with the python engine so I could specify multiple separators. Then, I used the usecols parameter to specify which columns in the csv file you want in your dataframe. The header will not be read from file, and I skipped the first row since it doesn't have any data. I read in the first and second set of records into 2 dataframes, and then concatenate the 2 data frames.

a = pd.read_csv('sample.csv', sep=',|:|;', skiprows = 1, usecols = (0,2,4,6, 14), header = None, engine='python')
b = pd.read_csv('sample.csv', sep=',|:|;', skiprows = 1, usecols = (0,8,10,12,14), header = None, engine='python')
a.columns = ['Date', 'First', "Last", 'School', 'Type']
b.columns = ['Date', 'First', "Last", 'School', 'Type']
final_data = pd.concat([a,b], axis = 0)

If you need the order preserved, such that the second names appear right below the first name, you can sort using the indices. I use mergesort, because it is a stable sort and this ensures that the first Information record (record on the right) will be above the Information record on the left.

final_data.sort_index(kind='mergesort', inplace = True)
>>>final_data
        Date        First  Last     School  Type
0   "2015-12-06"    Tom    Smith    MCAA    "New"
0   "2015-12-06"    Tammy  Smith    MCAA    "New"
1   "2015-12-06"    Jim    Jones    MCAA    "New"
1   "2015-12-06"    Jane   Jones    MCAA    "New"

Edit: Including the second set of the record into the data. Changed the axis to 0.

Improve this answer

3 Comments

Thanks for the approach. I was able to replicate, however when I tried it, the code didn't pick up the second names in each of the rows (e.g. Tammy Smith and Jane Jones). Is there something I need to do differently to iterate through the text in the "Information" column?
@Zymurgist66 Do the records have to appear such that Tom Smith has to appear right above Timmy Smith? Anyways, I edited my response to read both set of names and provided an option so the order can be maintained.
user1435522 - no the order is not relevant. The initial examples I was testing only had 2 people per record. As I tried to use on the entire dataset, I found that the number of people could be 1:n so I ended up needing to iterate through the people.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.