Can't Remove White Spaces from CSV Headers with Pandas

can definition
can verb
can synonym
can.'' acronym
meaning of can in hindi
can band
can pronunciation
can is a verb or noun

I'm trying to rename headers in a csv that have white spaces. Using these lines from the Pandas API reference is not working. The headers still have white spaces instead of underscores.

import pandas as pd

df = pd.read_csv("my.csv",low_memory=False)
df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')

Try using a list comprehension.

df.columns = [c.strip().lower().replace(' ', '_') for c in df.columns]

Can, VerbEdit. can (third-person singular simple present can, present participle -, simple past could, past participle (obsolete except in adjectival use) couth). Can definition, to be able to; have the ability, power, or skill to: She can solve the problem easily, I'm sure. See more.

Tried using rename?

df.rename(index=str, columns={"A space": "a", "B space ": "c"})

Can, can meaning: 1. to be able to: 2. used to say that you can and will do something: 3. to be allowed to: . Learn more. Define can. can synonyms, can pronunciation, can translation, English dictionary definition of can. to be able to, have the power or skill to: I can take a bus to the

I ditched Pandas and just used the CSV module in Python 2.7.

import csv
import re
import tempfile
import sys
import os
if sys.version_info >= (3, 3):
    from os import replace
elif sys.platform == "win32":
    from osreplace import replace
else:
    from os import rename as replace

newHeaderList = []

with tempfile.NamedTemporaryFile(dir='.', delete=False) as tmp, \
    open('myFile.txt', 'rb') as f:
    r = csv.reader(f, delimiter = '\t')
    w = csv.writer(tmp, delimiter = '\t', quoting=csv.QUOTE_NONNUMERIC)
    header = next(r)
    for h in header:
        headerNoSpace = re.sub("\s+", "_", h.strip())
        newHeaderList.append(headerNoSpace)
    w.writerow(newHeaderList)
    for row in r:
        w.writerow(row)

os.rename(tmp.name, new_text_filepath)


new_txt = csv.reader(open('newFile.txt', "rb"), delimiter = '\t')
out_csv = csv.writer(open('myFile.csv', 'wb'))
out_csv.writerows(new_txt)

can, Can was the leading avant-garde rock group of the 70s. Can experimented with noise, synthesizers, non-traditional music, cut-and-paste techniques, and, most  can definition: 1. to be able to: 2. used to say that you can and will do something: 3. to be allowed to: . Learn more.

CAN, 1.4Used to indicate that something is typically the case. 'antique clocks can seem out of place in modern homes'. More example sentences. 110 synonyms of can from the Merriam-Webster Thesaurus, plus 97 related words, definitions, and antonyms. Find another word for can. Can: to bring (as an action or operation) to an immediate end.

The Official CAN / Spoon Records Website, can definition: The definition of a can is a container normally made of metal with a lid. (noun) An example of can is what tuna comes in. A Controller Area Network (CAN bus) is a robust vehicle bus standard designed to allow microcontrollers and devices to communicate with each other's applications without a host computer. It is a message-based protocol , designed originally for multiplex electrical wiring within automobiles to save on copper, but can also be used in many other

Can, can. (kæn). n. 1. a container, esp for liquids, usually of thin sheet metal: a petrol can;  Can (stylised as CAN) was a German experimental rock band formed in Cologne in 1968 by the core quartet of Holger Czukay (bass, tape editing), Irmin Schmidt (keyboards), Michael Karoli (guitar), and Jaki Liebezeit (drums).

Comments
  • See discussion below, this is a Python 2 related question.
  • Strange, because I tried this with one of my dataframes and it's working here. Maybe you can re-check your code and if it still doesn't work, provide the version number of pandas & python you are using. I'm testing with python 3.6 & pandas 0.23.4
  • So when I just print df.columns and don't try to replace anything, I get this list (I've shortened it). The first header already has an underscore in its name. Index([u'DWH_ID', u'ID', u'Actual Retirement', u'In Service', u'Gross Area (Imperial)', u'Gross Area', u'Area (Imperial)', u'Area (Metric)', u'Area', u'Area Units'])
  • It also says dtype='object' at the end of the index. Could that be why string replace isn't working?
  • No. From the 'u'prefix it seems you are using the outdated python2 instead of the current python3, so you may be using an outdated pandas as well. Please post your version numbers.
  • Yes, Python 2.7.13 from an ArcGIS install. Pandas version 0.23.4