String case operations in CSVs, Python

Question

I want to fix my CSV file's case issues as simply as possible. I already have pandas imported if that simplifies the code.

So I want to replace

 Name          City            State
 FOO BAR       los angeles     ca
 guy's naME    PHILADELPHIA    Pa

With (note the ')

 Name          City            State
 Foo Bar       Los Angeles     CA
 Guy's Name    Philadelpha     PA

And does someone want to tell me what to put in my question to ensure the proper syntax highlighting for a table? — Xodarap777, Jan 08 '14 at 23:30

Jeff C Johnson · Accepted Answer · 2021-02-02T19:54:15.480

3

This is hard-coded to match your example file but should do what you want:

import csv
import sys
import string

reader = csv.reader(sys.stdin, delimiter='\t')
writer = csv.writer(sys.stdout, delimiter='\t')

# write the header as-is
writer.writerow(reader.next())

for row in reader:
    row[0] = string.capwords(row[0])
    row[1] = string.capwords(row[1])
    row[2] = row[2].upper()
    writer.writerow(row)

Example usage:

cat test.csv | python fix_case.py
Name    City    State
Foo Bar Los Angeles CA
Guy's Name  Philadelphia    PA

edited Feb 02 '21 at 19:54

answered Jan 09 '14 at 00:18

Jeff C Johnson

187
2
9

Perfect for what I wanted. I just needed to understand how to apply the string operations to the dataframes/series, but I guess that's as simple as it is for strings. For row[0] - row[0].capwords() should solve several problems at once, I expect! – Xodarap777 Jan 09 '14 at 00:28
I edited my answer to use capwords per your suggestion, that fixed the problem I missed where it capitalized the 's' in Guy's Name. Thanks! – Jeff C Johnson Jan 09 '14 at 00:41

score 2 · Answer 2 · answered Jan 09 '14 at 00:10

This will do what you want minus the all capital states and cases like (Guys'S Name) with special characters:

with open("output.txt",'w') as O:
    with open("input.txt") as I:
        for line in I:
            O.write(line.title())

Before:

Name          City            State
FOO BAR       los angeles     ca
guy's naME    PHILADELPHIA    Pa

After:

Name          City            State
Foo Bar       Los Angeles     Ca
Guy'S Name    Philadelphia    Pa

score 0 · Answer 3 · answered Jan 08 '14 at 23:45

First of all, you need to learn some of python rules Then, you have to install ipython, and use it instead of python. Then, you have to run ipython, and try some really basic problems.

Read csv file to DataFrame object(google it, or learn) Then, if you want to UPPERCASE Series(State):

s1 = pd.core.series.Series(['foo', 'bar'])
s1 = s1.apply(lambda x: x.upper())
s1

Then if you want to lowercase and capitalize Series(City)

s2 = pd.core.series.Series(['FOO', 'fOo'])
s2 = s2.apply(lambda x: x.lower().capitalize())
s2

The if you want to capitalize each word, you have to google for it.

And then you have to save you csv file with you new changed DataFrame.

"Google or learn" is a generic attitude that could apply to all forums, especially Q&A-style forums. However, I'm obviously here for a reason. "Vote Down requires 125 reputation." — Xodarap777, Jan 08 '14 at 23:52

String case operations in CSVs, Python

3 Answers3