CODEWITHSUNDEEP
pythoncsvreplace
Evgeniy
Evgeniy

Reputation: 2595

Find and replace in special CSV column with Python

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            |
+--------------------------------------------+---------------------+--------------+--------+---------------+-------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+

Upvotes: 0

Views: 49

Answers (1)

Stan van Rooy
Stan van Rooy

Reputation: 346

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.

Upvotes: 1

Related Questions