How do I transform a non-CSV text file into a CSV using Python/Pandas?

Question

I have a text file that looks like this:

Id Number: 12345678
Location: 1234561791234567090-8.9
Street: 999 Street AVE
Buyer: john doe
Id Number: 12345688
Location: 3582561791254567090-8.9
Street: 123 Street AVE
Buyer: Jane doe @ buyer % LLC
Id Number: 12345689
Location: 8542561791254567090-8.9
Street: 854 Street AVE
Buyer: Jake and Bob: Owner%LLC: Inc

I'd like the file to look like this:

Id Number	Location	Street	Buyer
12345678	1234561791234567090-8.9	999 Street AVE	john doe
12345688	3582561791254567090-8.9	123 Street AVE	Jane doe @ buyer % LLC
12345689	8542561791254567090-8.9	854 Street AVE	Jake and Bob: Owner%LLC: Inc

I have tried the following:

# 1 Read text file and ignore bad lines (lines with extra colons thus reading as extra fields).
tr = pd.read_csv('C:\\File Path\\test.txt', sep=':', header=None, error_bad_lines=False)

# 2 Convert into a dataframe/pivot table.
ndf = pd.DataFrame(tr.pivot(index=None, columns=0, values=1))

# 3 Clean up the pivot table to remove NaNs and reset the index (line by line).

nf2 = ndf.apply(lambda x: x.dropna().reset_index(drop=True))

Here is where got the last line (#3): https://stackoverflow.com/a/62481057/10448224

When I do the above and export to CSV the headers are arranged like the following:

(index)	Street	Buyer	Id Number	Location

The data is filled in nicely but at some point the Buyer field becomes inaccurate but the rest of the fields are accurate through the entire DF.

My guesses:

When I run #1 part of my script I get the following errors 507 times:

b'Skipping line 500: expected 2 fields, saw 3\nSkipping line 728: expected 2 fields, saw 3\

At the tail end of the new DF I am missing exactly 507 entries for the Byer field. So I think when I drop my bad lines, the field is pushing my data up.

Pain Points:

The Buyer field will sometimes have extra colons and other odd characters. So when I try to use a colon as a delimiter I run into problems.

I am new to Python and I am very new to using functions. I primarily use Pandas to manipulate data at a somewhat basic level. So in the words of the great Michael Scott: "Explain it to me like I'm five." Many many thanks to anyone willing to help.

You should manually split the lines at the first colon with: `key,value = inputline.split(":",1)` save them in a dictionary and every you hit the `"Id Number"` (or end of file) append the collected dictionary into your list and start a new dictionary. You can figure out how to convert that dictionary into a dataframe easily enough. — RufusVS, Sep 29 '21 at 21:46
@RufusVS could you clarify this part: `key,value = inputline`. Sorry, I'm new to python. I know generally what a dictionary is but don't have a lot of experience using them in this way. — SupaDupa, Sep 29 '21 at 22:02
You will need to provide some example data to show the pain problems in order to get a complete answer. — Adrian Klaver, Sep 29 '21 at 22:32
@SupaDupa The key point is the split function with the 1 argument, it splits the line into two parts at the `:` — RufusVS, Sep 29 '21 at 22:49

Adrian Klaver · Answer 1 · 2021-09-29T22:59:22.870

This is a minimal example that demonstrates the basics:

cat split_test.txt 

Id Number: 12345678
Location: 1234561791234567090-8.9
Street: 999 Street AVE
Buyer: john doe
Id Number: 12345688
Location: 3582561791254567090-8.9
Street: 123 Street AVE
Buyer: Jane doe @ buyer % LLC
Id Number: 12345689
Location: 8542561791254567090-8.9
Street: 854 Street AVE
Buyer: Jake and Bob: Owner%LLC: Inc

import csv 

with open("split_test.txt", "r") as f:
    id_val = "Id Number"
    list_var = []
    for line in f:
        split_line = line.strip().split(':')
        print(split_line)
        if split_line[0] == id_val:
            d = {}
            d[split_line[0]] = split_line[1]
            list_var.append(d)
        else:
            d.update({split_line[0]: split_line[1]})

list_var

[{'Id Number': ' 12345689',
  'Location': ' 8542561791254567090-8.9',
  'Street': ' 854 Street AVE',
  'Buyer': ' Jake and Bob'},
 {'Id Number': ' 12345678',
  'Location': ' 1234561791234567090-8.9',
  'Street': ' 999 Street AVE',
  'Buyer': ' john doe'},
 {'Id Number': ' 12345688',
  'Location': ' 3582561791254567090-8.9',
  'Street': ' 123 Street AVE',
  'Buyer': ' Jane doe @ buyer % LLC'}]

with open("split_ex.csv", "w") as csv_file:
    field_names = list_var[0].keys()
    csv_writer = csv.DictWriter(csv_file, fieldnames=field_names)
    csv_writer.writeheader()
    for row in list_var:
        csv_writer.writerow(row)

score 1 · Accepted Answer · answered Sep 29 '21 at 22:59

1

Here's what I meant by reading in and using split. Very similar to other answers. Untested and I don't recall if inputline include eol, so I stripped it too.

with open('myfile.txt') as f:
    data = []  # holds database
    record = {} # holds built up record
    for inputline in f:
        key,value = inputline.strip().split(':',1)
        if key == "Id Number": # new record starting
            if len(record):
                data.append(record) # write previous record
                record = {}
        record.update({key:value})
if len(record):
    data.append(record) # out final record
df = pd.DataFrame(data)

answered Sep 29 '21 at 22:59

RufusVS

4,008
3
29
40

Wow. This is really close! Get this error message: `Traceback (most recent call last): File "filepath\interactiveshell.py", line 3441, in run_code exec(code_obj, self.user_global_ns, self.user_ns) File "", line 5, in key,value = inputline.strip().split(':',1) ValueError: not enough values to unpack (expected 2, got 1)` Tried it on a smaller subset of dataset and it worked. However when I try on the larger dataset I get the above error. – SupaDupa Sep 29 '21 at 23:17
2

Best guess there was no ':' in the string: `'Id Number'.split(":") ['Id Number']`. So there was no item in the list to unpack into `value` portion of `key,value`. That is why I asked for an example of the data. In any case you will need to add code to do data clean up to make things work. – Adrian Klaver Sep 29 '21 at 23:42

score 0 · Answer 3 · answered Sep 29 '21 at 21:31

0

I would try reading the file line by line, splitting the key-value pairs into a list of dicts to look something like:

data = [
  {
     "Id Number": 12345678,
     "Location": 1234561791234567090-8.9,
     ...
  },
  {
     "Id Number": ...
  }
]

# easy to create the dataframe from here
your_df = pd.DataFrame(data)

answered Sep 29 '21 at 21:31

danoscarmike

133
8

"I would try reading the file line by line, splitting the key-value pairs into a list..." How would I do this? Someone else mentioned splitting using the key-value pairs, but again I am new to python. Thank you. – SupaDupa Sep 29 '21 at 22:00

How do I transform a non-CSV text file into a CSV using Python/Pandas?

My guesses:

Pain Points:

3 Answers3