Replace excessive spaces to format a csv

Question

I have a TON of data in a .txt file formatted like so...

WOODY, Harlan Fred                 S2c        USN
WOOD, Earl A.                      PVT        USAR
WOOD, Frank                        S2c        USN
WOOD, Harold Baker                 BM2c       USN
WOOD, Horace Van                   S1c        USN
WOOD, Roy Eugene                   F1c        USN
WOOLF, Norman Bragg                CWTP       USN
WORKMAN, Creighton Hale            F1c        USN

I would like to get it into a csv format like so...

WOODY,Harlan Fred,S2c,USN

I see in Python I can use regex and/or split, but I need to preserve the spaces between the first and last names. As you can see, the space count is different between most of the entries and the occasional tab may appear as well (I think).

Possible duplicate of [How to strip all whitespace from string](http://stackoverflow.com/questions/3739909/how-to-strip-all-whitespace-from-string) — PseudoAj, Nov 11 '16 at 23:11
Are these actual service members names? You probably shouldn't be posting actual names if they are. — kylieCatt, Nov 11 '16 at 23:43
@PseudoAj note that solutions from that page will remove whitespace from `Harlan Fred`. — vaultah, Nov 13 '16 at 05:24

Moses Koledoye · Answer 1 · 2016-11-11T23:30:39.840

2

Here's one way to do it - splitting on a comma first and then splitting on multiple spaces to avoid splitting the singly spaced names. Then joining all the items with a comma with str.join:

with open(textfile) as f, open(csvfile, 'w') as fc:
    for line in f:
        first, others = line.split(',')
        row = [first] + [i.strip() for i in others.split('   ') if i]
        fc.write(','.join(row) + '\n')

edited Nov 11 '16 at 23:30

answered Nov 11 '16 at 23:16

Moses Koledoye

77,341
8
133
139

score 1 · Answer 2 · answered Nov 11 '16 at 23:40

Use pandas read_csv with a regex seperator. Pandas will be faster than a solution written in pure python.

import pandas as pd
pd.read_csv('./s.dat',header=None, delimiter=r"\s+") 
          0          1       2     3     4
0    WOODY,     Harlan    Fred   S2c   USN
1     WOOD,       Earl      A.   PVT  USAR
2     WOOD,      Frank     S2c   USN   NaN
3     WOOD,     Harold   Baker  BM2c   USN
4     WOOD,     Horace     Van   S1c   USN
5     WOOD,        Roy  Eugene   F1c   USN
6    WOOLF,     Norman   Bragg  CWTP   USN
7  WORKMAN,  Creighton    Hale   F1c   USN

score 0 · Answer 3 · answered Nov 11 '16 at 23:16

0

line = 'WOODY, Harlan Fred                 S2c        USN'
print(line[:35].rstrip().split(', ') + line[35:].split())

Output:

['WOODY', 'Harlan Fred', 'S2c', 'USN']

answered Nov 11 '16 at 23:16

Alex Hall

34,833
5
57
89

score 0 · Answer 4 · answered Nov 12 '16 at 00:13

Another way of doing so would be to replace each double space with a comma, then split on the commas, then stripping the non-null values, finally joining with a comma. Apply the following to each line in the textfile:

','.join([x.strip() for x in line.replace('  ',',').split(',') if x])

Replace excessive spaces to format a csv

4 Answers4