Remove cell in a CSV file if a certain value is th

Question

I have two types of CSV files, and I want to merge them together. To do that, I want to locate certain values in each row and remove them if they are there.

I tried using list.Index or list.Remove functions but I get an error when the values are not in the specific file.

For example, the two rows are (I cut the rest of the rows for better display):

CSV 1950    1300    1180    48  48  400 2   PASS        0   51:31.5
CSV 3270    2500    1950    1300    1180                        48

I want to locate the cells with the values "3270" and "2500" so the two files will align... After that I want to remove the empty cells so again - they will align...

Can you please help me understand the way to do this?

thanks, Nimrod.

Answer 1

I would advice you to loop each value from the file and then put some conditions deleting the elements and then merge the values into a new output file

Step1 Reading the file

import sys
import csv
updatedlist = []
for val in csv.reader(open('input1.csv' , 'rb')) :
    print val

## val will be a list of each row , 
## So in this case you will have to 
## select the elements while you will be looping 
## then replace or removing the elements you want to remove
## and make a new updated list which you will then have 
## to append to a new output.csv file
## then do the same the to the other file and append to the output.csv file    


for Values  in  updatedlist :

##looping thru the values and then make a string of the values separated by commas
        f  = open('output.csv' , 'a')
        f.writelines(updatestring) ##updated list with a string of comma separated values 
        f.close()

Answer 2

It's hard to tell exactly what you're hoping to accomplish, but I think this ought to get you started.

#!/usr/bin/env python

import csv    

myfile = '/path/to/untitled.csv'
newfile = '/path/to/untitled_new.csv'

reader = csv.reader(open(myfile))

remove_me = {'3270','2500'}

print('Before:')
print(open(myfile).read())

for row in reader:
    new_row = []
    for column in row:
        if column not in remove_me:
            new_row.append(column)

    csv.writer(open(newfile,'a')).writerow(new_row)

print('\n\n')
print('After:')
print(open(newfile).read())

Output:

Before:
1950,1300,1180,48,48,400
3270,2500,1950,1300,1180



After:
1950,1300,1180,48,48,400 
1950,1300,1180 

Make sure that you're not using list.remove while you're iterating over that same list, or you'll likely mess yourself up. My code above uses a safer strategy of copying the values that pass your if test (column is not one of your values that you want to get rid of) to a new list, then writing that new list to a new .csv file.

Is this more or less what you're intending to do?

To get rid of blank cells, I presume you could add '' to remove_me.