How to overwrite a particular column of a csv file using pandas or normal python?

Question

I am new to python. I have a .csv file which has 13 columns. I want to round off the floating values of the 2nd column which I was able to achieve successfully. I did this and stored it in a list. Now I am unable to figure out how to overwrite the rounded off values into the same csv file and into the same column i.e. column 2? I am using python3. Any help will be much appreciated.

My code is as follows:

Import statements for module import:

import csv

Creating an empty list:

list_string = []

Reading a csv file

with open('/home/user/Desktop/wine.csv', 'r') as csvDataFile:

    csvReader = csv.reader(csvDataFile, delimiter = ',')
    next(csvReader, None)                              
    for row in csvReader:
        floatParse = float(row[1])               
        closestInteger = int(round(floatParse))
        stringConvert = str(closestInteger)
        list_string.append(stringConvert)
print(list_string)

Writing into the same csv file for the second column (Overwrites the entire Excel file)

with open('/home/user/Desktop/wine.csv', 'w') as csvDataFile:

    writer = csv.writer(csvDataFile)
    next(csvDataFile)
    row[1] = list_string
    writer.writerows(row[1])

PS: The writing into the csv overwrites the entire csv and removes all the other columns which I don't want. I just want to overwrite the 2nd column with rounded off values and keep the rest of the data same.

score 0 · Accepted Answer · answered Sep 22 '17 at 17:54

0

this might be what you're looking for.

import pandas as pd
import numpy as np
#Some sample data
data = {"Document_ID": [102994,51861,51879,38242,60880,76139,76139],
    "SecondColumnName":    [7.256,1.222,3.16547,4.145658,4.154656,6.12,17.1568],
   }
wine = pd.DataFrame(data)

#This is how you'd read in your data
#wine = pd.read_csv('/home/user/Desktop/wine.csv')

#Replace the SecondColumnName with the real name
wine["SecondColumnName"] = wine["SecondColumnName"].map('{:,.2f}'.format)

#This will overwrite the sheet, but it will have all the data as before
wine.to_csv(/home/user/Desktop/wine.csv')

Pandas is way easier than read csv...I'd recommended checking it out.

answered Sep 22 '17 at 17:54

Tim Gottgetreu

483
1
8
21

Hi Tim. Thanks for the help. But in the csv file the second coloumn is still not rounding off the floating point number to the closest integer. Can you please help me out with this? – user2604339 Sep 22 '17 at 18:38
1

Hi Tim. Thanks for the help. This code worked and i just modified the map to apply(np.floor) for rounding off. Thanks once again. – user2604339 Sep 22 '17 at 18:45
.map('{:,.0f}'.format) would also give you no decimals if that's what you're after. Glad it worked! – Tim Gottgetreu Sep 22 '17 at 20:20

score 0 · Answer 2 · answered Sep 22 '17 at 19:45

I think this better answers the specific question. The key to this is to define an input_file and an output_file during the with part.

The StringIO part is just there for sample data in this example. newline='' is for Python 3. Without it, blank lines between each row appears in the output. More info.

import csv
from io import StringIO

s = '''A,B,C,D,E,F,G,H,I,J,K,L
1,4.4343,3,4,5,6,7,8,9,10,11
1,8.6775433,3,4,5,6,7,8,9,10,11
1,16.83389832,3,4,5,6,7,8,9,10,11
1,32.2711122,3,4,5,6,7,8,9,10,11
1,128.949483,3,4,5,6,7,8,9,10,11'''

list_string = []

with StringIO(s) as input_file, open('output_file.csv', 'w', newline='') as output_file:
  reader = csv.reader(input_file)
  next(reader, None)
  writer = csv.writer(output_file)
  for row in reader:
    floatParse = float(row[1]) + 1
    closestInteger = int(round(floatParse))
    stringConvert = str(closestInteger)
    row[1] = stringConvert
    writer.writerow(row)

How to overwrite a particular column of a csv file using pandas or normal python?

2 Answers2