Find and replace in special CSV column with Python

Question

Normally i do find and replace with the following code, which works like expected:

text = open("input.csv", "r")
text = ''.join([i for i in text]) \
    .replace("\"-\"", "\"\"")
x = open("output.csv","w")
x.writelines(text)
x.close()

Now i want do the same but in the special column. How can i setup the column (like column number or name), where find and replace things?

Test data: to delete is a --sign in the column Referer

+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| URL                                        | Timestamp           | Remote Host  | Method | Response Code | Bytes | Time Taken(ms) | User Agent                                                                                                                                                                                             | Referer | Verification Status |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:00 | 66.249.64.92 | GET    | 301           | 4     | 261            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | -       | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:01 | 66.249.64.90 | GET    | 200           | 15688 | 296            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) |         | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:00 | 66.249.64.92 | GET    | 301           | 4     | 261            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | -       | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:01 | 66.249.64.90 | GET    | 200           | 15688 | 296            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) |         | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:00 | 66.249.64.92 | GET    | 301           | 4     | 261            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | -       | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:01 | 66.249.64.90 | GET    | 200           | 15688 | 296            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) |         | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:00 | 66.249.64.92 | GET    | 301           | 4     | 261            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) |         | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:01 | 66.249.64.90 | GET    | 200           | 15688 | 296            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) |         | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| http://example.com/krankenversicherung.php | 18.05.2019 00:00:00 | 66.249.64.92 | GET    | 301           | 4     | 261            | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/41.0.2272.96 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) |         | Verified            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+

post a testable "input.csv" content

RomanPerekhrest
– RomanPerekhrest

2019-11-27 18:04:23 +00:00
Commented Nov 27, 2019 at 18:04 — RomanPerekhrest
– RomanPerekhrest, Commented Nov 27, 2019 at 18:04
now you see it.

Evgeniy
– Evgeniy

2019-11-27 18:38:56 +00:00
Commented Nov 27, 2019 at 18:38 — Evgeniy
– Evgeniy, Commented Nov 27, 2019 at 18:38

Stan van Rooy · Accepted Answer · 2019-11-29 12:51:32Z

1

I would suggest you use the csv library. That makes this really easy:

import csv
input_file = open('input.csv', 'r')
output_file = open('output.csv', 'w+', newline='')

reader = csv.reader(input_file, quotechar='|')
writer = csv.writer(output_file, quoting=csv.QUOTE_NONE, delimiter=',', quotechar='|')
for line in reader:
    line[7] = line[7].replace('-', '')  # change to correct replace and index values
    writer.writerow(line)

If you want to learn more about the csv library, I'd suggest reading my blogpost.

edited Nov 29, 2019 at 12:51

answered Nov 27, 2019 at 18:10

Stan van Rooy

3462 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Evgeniy Over a year ago

Where in your code is a name or an index of column?

Stan van Rooy Over a year ago

line[1] = line[1], you can change the index.

Evgeniy Over a year ago

It works, but not as expected. I need to delete - between "". But if i setup the replacement like replace('\"-\"', '\"\"') or even without escaping, like replace('"-"', '""') , the code replaces "-" with empty place. Quote signs are replaced too. In the code i cited above escaping of quote signs let them remain - in your code under all circumstances they are away.

Stan van Rooy Over a year ago

@Evgeniy replace('"-"', '""') with this you replace all "-" with just "".

Evgeniy Over a year ago

No, it works even not consistently. Look at screenshot: easycaptures.com/fs/uploaded/1432/4557320379.png

|

Collectives™ on Stack Overflow

Find and replace in special CSV column with Python

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related