Pandas - How to replace a single quotation mark followed by a comma ( ", )

user_11422689 0
Viewed 459

I have a text file that looks like this:

,Symbol,Date"+
",DRI,23/04/2021"+
",CQN,28/04/2021"+
",TGG,29/01/2021"+

and I want the result that looks like this:

"Symbol,Date"+
"DRI,23/04/2021"+
"CQN,28/04/2021"+
"TGG,29/01/2021"

I'm using pandas to convert csv to the text file above but I got this single double quote with comma right after (",) that I want to replace with only one single double quote (").

In the code below:

df = pd.read_csv(in_csv, na_filter=False, encoding='utf-8-sig')

    # dict of replacements
replacements = {
    '"",': '"'
}

    # using the replace() method
df1 = df.replace(replacements, regex=True)

I tried many possible solutions like

1. '\"' to '"' 
2. """," to """"
3. '\""," to '"'
...

but none seems to work. Doing this with Find and Replace in notepad is easy, but I need to automate this task by using code only. I'm also stuck at how to remove only that first comma and the last (+) sign without affecting the rest.

Could you suggest a solution to this problem I'm having?

pandas csv python replace
3 Answers

No need for pandas here. The file is not a csv file but a plain text file so you could just use:

  • the re module to change the beginning of the lines and the end of a last line. Compiling the regex before looping over the lines is slightly more efficient
  • the common read and write on temporary file pattern commonly used to modify text files. Keeping the previous line is a simple way to easily identify the last line.

It could be:

import re
import os

infile = 'ess.txt'
tmpfile = infile + '.tmp'

# pre-compile the regular expressions
init = re.compile(r'^"?,')
last = re.compile(r'"\+\s*$')

with open(infile) as fd, open(tmpfile, 'w') as out:
    old = None
    for line in fd:
        if len(line.strip()) == 0:  # skip empty lines
            continue
        if old is not None:
            out.write(old)
        old = init.sub('"', line)
    out.write(last.sub('"\n', old))

os.remove(infile)
os.rename(tempfile, infile)
user_35452730

Another option is to use a pattern with a lambda to do the replacement, and load the string using StringIO

If group 1 is present (the last plus sign) then return an empty string. Else return a double quote.

^"?,+|(\+)\Z
  • ^ Start of line
  • "?,+ Match an optional double quote and 1 or more comma's
  • | Or
  • (\+) Capture a plus sign in group 1
  • \Z End of string

Regex demo

For example

import re
import pandas as pd
from io import StringIO

with open('file', 'r') as file:
    lines = file.read()
    pattern = r"^\"?,+|(\+)\Z"
    data = re.sub(pattern, lambda m: "" if m.group(1) else '"', lines, 0, re.MULTILINE)
    df = pd.read_csv(StringIO(data), na_filter=False, encoding='utf-8-sig')
    print(df)

Output

      Symbol,Date+
0  DRI,23/04/2021+
1  CQN,28/04/2021+
2   TGG,29/01/2021
user_54249880

If you are open to non-python solutions, sed can do this job easily :)

sed -e 's/^",/,/g' -e 's/^,/"/g' -e '$ s/+$//g' test.txt
  1. 's/^",/,/g' replaces the first line starting ", with ,
  2. 's/^,/"/g' replaces all starting , by ".
  3. '$ s/+$//g' removes the + sign on the last line, at end of line. sed has the concept of addresses and $ means the last line.

On some systems, you might have to use sed -i -e.

user_84916420
Related