Change Specific Value in CSV File Via Python

Change specific value in CSV file via Python

This is the solution opening the csv file, changing the values in memory and then writing back the changes to disk.

r = csv.reader(open('/tmp/test.csv')) # Here your csv file
lines = list(r)

Content of lines:

[['Ip', 'Sites'],
 ['127.0.0.1', '10'],
 ['127.0.0.2', '23'],
 ['127.0.0.3', '50']]

Modifying the values:

lines[2][1] = '30'

Content of lines:

[['Ip', 'Sites'],
 ['127.0.0.1', '10'],
 ['127.0.0.2', '30'],
 ['127.0.0.3', '50']]

Now we only have to write it back to a file

writer = csv.writer(open('/tmp/output.csv', 'w'))
writer.writerows(lines)

Change values of a column in CSV file using python

It worked for me... Try this.

res=[]
with open('hi.csv') as f:
    content=csv.reader(f,delimiter='|')
    for row in content:
        for str in range (len(row)):
            row[str]=row[str].replace('\'','')
        res.append(row)
    f.close()
with open('hi.csv','wb') as ff:  # python 3 => 'wb' => 'w',newline=''
    sw=csv.writer(ff,delimiter='|',quoting=csv.QUOTE_MINIMAL)
    for rows in res:
        sw.writerow(rows)
ff.close()

Python - Replacing a specific value in a CSV file while keeping the rest

This is how it would look like using pandas

import pandas as pd
from io import StringIO

# Things you'll get from a user
globalUsername = "Den1994"
field = 'Fav Artist'
new_value = 'Linkin Park'

# Things you'll probably get from a data file
data = """
         Username,Password,Name,DOB,Fav Artist,Fav Genre
         Den1994,Denis1994,Denis,01/02/1994,Eminem,Pop
         Joh1997,John1997,John,03/04/1997,Daft Punk,House
       """

# Load your data (e.g. from a CSV file)
df = pd.read_csv(StringIO(data)).set_index('Username')

print(df)

# Now change something
df.loc[globalUsername][field] = new_value

print(df)

Here df.loc[] allows you to access a row by the index. In this case Username is set as index. Then, [field] selects the column in that row.

Also, consider this:

df.loc[globalUsername][['Fav Artist', 'Fav Genre']] = 'Linkin Park', 'Nu Metal'

In case you have a my-data.csv file you can load it with:

df = pd.read_csv('my-data.csv') 

---

The code above will return

           Password   Name         DOB Fav Artist Fav Genre
Username                                                   
Den1994   Denis1994  Denis  01/02/1994     Eminem       Pop
Joh1997    John1997   John  03/04/1997  Daft Punk     House

and

           Password   Name         DOB   Fav Artist Fav Genre
Username                                                     
Den1994   Denis1994  Denis  01/02/1994  Linkin Park       Pop
Joh1997    John1997   John  03/04/1997    Daft Punk     House

open CSV & replace all 'numbers greater than 100' in specific column

You're not opening the CSV files properly for Python 3 (see the csv module's documentation). The results you're getting are because you're using a list comprehension to change a single element of each row (which itself is a list).

Here's what I would suggest. I've changed many variable names to better reflect the type of data they contain. The code also follows the PEP 8 - Style Guide for Python Code guidelines to make it more readable. I suggest you read that document and start following its advice.

import csv

COL = 1  # Second column.
LIMIT = 100
REPLACEMENT = str(LIMIT)

with open('INPUT.csv', 'r', newline='') as f_input, \
     open('OUTPUT.csv', 'w', newline='') as f_output:
    csv_reader = csv.reader(f_input)
    csv_writer = csv.writer(f_output)
    csv_writer.writerow(next(csv_reader))  # Copy header row.

    for row in csv_reader:
        row[COL] = REPLACEMENT if int(row[COL]) > LIMIT else row[COL]
        csv_writer.writerow(row)

print('done')

Python - Replacing value of a row in a CSV file

Here's something to get you going in the right direction.

with open('path/to/filename') as filehandler_name:
    # this is how you open a file for reading

with open('path/to/filename', 'w') as filehandler_name:
    # this is how you open a file for (over)writing
    # note the 'w' argument to the open built-in

import csv
# this is the module that handles csv files

reader = csv.reader(filehandler_name)
# this is how you create a csv.reader object
writer = csv.writer(filehandler_name)
# this is how you create a csv.writer object

for line in reader:
    # this is how you read a csv.reader object line by line
    # each line is effectively a list of the fields in that line
    # of the file.
    # # XXXX-XXXX, 0 --> ['XXXX-XXXX', '0']

For small files, you could do something like:

import csv

with open('path/to/filename') as inf:
    reader = csv.reader(inf.readlines())

with open('path/to/filename', 'w') as outf:
    writer = csv.writer(outf)
    for line in reader:
        if line[1] == '0':
            writer.writerow(line[0], '1')
            break
        else:
            writer.writerow(line)
    writer.writerows(reader)

For large files that inf.readlines will kill your memory allocation since it's pulling the whole file into memory at once, and you should do something like:

import csv, os

with open('path/to/filename') as inf, open('path/to/filename_temp', 'w') as outf:
    reader = csv.reader(inf)
    writer = csv.writer(outf)
    for line in reader:
        if line[1] == '0':
           ...
        ... # as above

os.remove('path/to/filename')
os.rename('path/to/filename_temp', 'path/to/filename')

Replacing specific data in a csv file

from collections import OrderedDict
user_data = OrderedDict() # dict to hold all matching rows for the user, keyed off of line number
data_to_write = []
with open(path, 'r') as csvfile:  # read-write mode for file
    ver_read = csv.reader(csvfile, delimiter =",")
    for x, row in enumerate(ver_read):
        if user == row[0]:
            user_data[x] = row
        else:
            data_to_write.append(row)   # store it for afterwards
    if len(user_data) >= 3:
        keys = user_data.keys()[-2:] # Grab the last two scores in the file
        for x in keys:
            data_to_write.append(user_data[x])
        # Write the contents of the new score here:
        data_to_write.append(. . . . .)
with open(path, 'w') as csvfile:
    # finally write the changes to file
    ver_write = csv.writer(csvfile, delimiter=",")
    ver_write.writerows(data_to_write)

How to replace a word in a csv file in specific column with python

A working example would be better (output is open as file2 but writer is given outfile)

But in any case your problem is replace

• replace returns a copy of the string (with replaced text if the criteria matches)

But you are discarding the returned value. You are actually changing nothing in row[0]

Solution:

replaced = row[0].replace('word','changed word')  # I want to replace in first column=row[0] 'word' with 'changed word'
row[0] = replaced
writer.writerow(row)

How to replace specific words from entire csv file?

Read your text file in as a Series that looks like

s

0    mag:magnitude
1        shf:shaft
2          gr:gear
3      bat:battery
4      ext:exhaust
5       ml:mileage
Name: 0, dtype: object

Split on colon and convert the series into a dictionary mapping key to its replacement:

dict(s.str.split(':').tolist())

# {'bat': 'battery',
#  'ext': 'exhaust',
#  'gr': 'gear',
#  'mag': 'magnitude',
#  'ml': 'mileage',
#  'shf': 'shaft'}

Use this to perform a replace operation with regex=True:

df['messages'].replace(dict(s.str.split(':').tolist()), regex=True)

0    we need to fix the car magnitude but we can't
1            we need a shaft to perform eng change
2                         gear is needed to change
3                             battery needs change
4                     car towed for exhaust change
5                              car mileage is high
Name: messages, dtype: object

---

Note that if these are strictly whole word replacements, you can extend this solution by converting the key strings into regular expressions that use word boundaries. For good measure, escape the string as well:

import re

mapping = {fr'\b{re.escape(k)}\b': v for k, v in s.str.split(':').tolist()}
df['messages'].replace(mapping, regex=True)

0    we need to fix the car magnitude but we can't
1            we need a shaft to perform eng change
2                         gear is needed to change
3                             battery needs change
4                     car towed for exhaust change
5                              car mileage is high
Name: messages, dtype: object

---

Related Topics