How to save pandas textmanipulation as csv in the correct form

July 6, 2022

I have a *.txt file with numbers. I want to eliminate the spaces.
The raw data looks like this

12 12345 1234
23 23456 234

If I use the following

data=data[0].str.replace(" ","")
data.update('\'' + data + '\',')

I get

 '1234123451234',
 '2323456234',

which I want. But if I save it to csv with

data.to_csv("/Users/k/file.txt", header=None, index=None, mode='a')

I get as file values:

 "'1234123451234',"
 "'2323456234',"

If I use the quoating = csv.None or 3 (same)

data.to_csv("Users/k/file.txt", header=None, index=None, quoting=3, escapechar="\\", mode='a')

The file looks like:

 '1234123451234'\,
 '2323456234'\,

Just using space or nothing as escapechar does not work.

If I just remove the spaces without adding quotes or commas and then save via:

data.to_csv("Users/k/file.txt", header=None, index=None, mode='a', quoting=1, sep=",")

I get:

 "1234123451234"
 "2323456234"

missing the comma.

Adding only the comma and saving as above gets me

 "1234123451234,"
 "2323456234,"

wrong place 🙂

As you can see, I am getting mad over missing my target by inches, while it is most likely super easy.
I probably will switch to regex 🙂

>Solution :

I would suggest not using Pandas at all, given that your transformation is pretty simple.

Something like this should work:

with open('input.txt', 'rt') as f_in:
    with open('output.txt', 'wt') as f_out:
        for line in f_in:
            line = line.strip()
            line = line.replace(' ', '')
            line = '\'' + line + '\',\n'
            f_out.write(line)