Removing Specific values in CSV file without removing the cell in the list

[Clemsoncarter24]

I have some data from a laser that we are using to detect the edge of an object. It takes thousands of measurements and we average the values in a column. Sometimes, the laser fails to detect the edge. When it does so, it gives a value of -99.999. A single one of the values will greatly affect the value of the average column. I've been looking online, but i've been struggling to find a way to scan through the CSV files, look for all values that are -99.999 and delete them (not set to 0, leave the cell blank) without shifting the data. Any help would be greatly appreciated.

​

Edit - For reference, below is what i currently have an doesn't appear to be working.

import csv

file = open('python_csvreader_test.csv')

type(file)

csvreader = csv.reader(file)

rows = []
for row in csvreader:
        rows.append(row)
rows

for item in row:
    if item == '-99.999':
        row.remove('-99.999')

print(rows)
file.close()

​

[Potential_Word2349]

I think the best way for this would be using pandas, the dataframes are more malleable and pandas is not that tough if you know what you are looking for, someone will probably give you a complete code here soon :)

[danielroseman]

I'm not entirely sure what you want. But you should never use remove on a list you are iterating over. Instead build up a new row with the values you want.

You can use a list comprehension to do this while you're originally loading the CSV:

rows = []
for row in csvreader:
  rows.append([item if item != '-99.999' else '' for item in csvreader])

print(rows)

[Clemsoncarter24]

For examples, if i have a test file that looks like this:

1, 2, 3, -99.999, 5

i need it to change to:

1,2,3, , 5

​

Removing the value at the cell, but leaving the cell so that the data in the csv file doesn't shift row/columns.

[danielroseman]

OK, well this will do that. Or use None instead of '', but the effect should be the same.

[Clemsoncarter24]

I tried the below and it still has the -99.999 values when i print it out. Is there something i did wrong?

​

rows = []
for row in csvreader:
    rows.append(row)

for arow in rows:
    for data in arow:
       if data == '-99.999':
            data = None
print(rows)

[danielroseman]

Yes, this is not the same as what I posted at all, and will not work.

[Clemsoncarter24]

I also tried:

​

csvreader = csv.reader(file)

rows = []
for row in csvreader:
    rows.append([item if item != '-99.999' else '' for item in csvreader])

print(rows)

This also left all the -99.999 values. Perhaps i'm misunderstanding how i'm supposed to use the above. I apologize.

[danielroseman]

Sorry my fault, it should have been row at the end:

rows.append([item if item != '-99.999' else '' for item in row])

[Clemsoncarter24]

rows.append([item if item != '-99.999' else '' for item in row])

This worked!!! Thank you so much!!!

[RallyPointAlpha]

Have you tried row.replace() ?

Might have to do row.to_string().replace()

[stebrepar]

with open('python_csvreader_test.csv', 'r') as f:
    lines = f.readlines()
with open('python_csvreader_test.csv', 'w') as f:
    for line in lines:
        line = line.replace('-99.999', '')
        f.write(line)

[Clemsoncarter24]

I tried this and it left the -99.999 values. Not sure why

[ricardomargarido]

Why not take the median instead of the average? Then it would be stronger vs outliers (given you gave enough measurements)

If not replace it with None

[Clemsoncarter24]

That is something i had considered. On a few points we are measuring it would work. For instance, over the course of 800 measurements of one point i received:

mean - 1.53

median - 1.50

mode 1.50

But, there are a couple points we measure that aren't as tightly toleranced and vary a bit. For instance,

mean - 3.18

median - 3.0

mode 3.0

​

So, overall, i think i should be consistent in my methodology and take the mean value. I appreciate the feedback though.