Change numeric values in rows of CSV if string in same row has certain value

Question

I have a csv that looks like below with up to 15000 lines.
The numeric values of Start and End are between 0 and 300.
I am looking for a way to parse through the file, search for rows starting with white, then check the Start value and the End value of this row with the following conditions:

if the value ≤ 150 then add 150
if the value is > 150 then subtract 150

Finally, overwrite the source file with the edits.
I am looking for a way to realize that with bash or python. Any help is much appreciated!

Raw Data:

Color, Start, End
white, 0, 1, 
black, 23, 150, 
black, 150, 24, 
white, 24, 152, 
black, 152, 25, 
black, 25, 154, 
black, 154, 81, 
white, 99, 220,
...

Final Data:

Color, Start, End
white, 150, 151, 
black, 23, 150, 
black, 150, 24, 
white, 174, 2, 
black, 152, 25, 
black, 25, 154, 
black, 154, 81, 
white, 249, 70,
...

Hello, welcome on SO. What did you try? What was the result? — Renaud Pacalet
– Renaud Pacalet, Commented Aug 5, 2021 at 11:15

Socowi · Accepted Answer · 2021-08-05 11:34:45Z

1

Sounds like a perfect job for awk:

awk -F, -v OFS=', ' '$1=="white" {for(i=2;i<=3;i++) if($i<=150) $i+=150; else $i-=150} 1' file.csv

edited Aug 5, 2021 at 11:34

answered Aug 5, 2021 at 11:12

Socowi

27.9k4 gold badges41 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

heljo Over a year ago

that worked perfectly! thanks a lot! I added a > output.csv to the end of the command to write the results in a file

Ed Morton · Accepted Answer · 2021-08-05 14:47:32Z

1

$ awk 'BEGIN{FS=" *, *"; OFS=", "; n=150} $1=="white"{ for (i=2; i<NF; i++) $i+=($i>n ? -n : n) } 1' file
Color, Start, End
white, 150, 151,
black, 23, 150,
black, 150, 24,
white, 174, 2,
black, 152, 25,
black, 25, 154,
black, 154, 81,
white, 249, 70,

edited Aug 5, 2021 at 14:47

answered Aug 5, 2021 at 14:19

Ed Morton

209k18 gold badges90 silver badges212 bronze badges

Comments

Mabbus · Accepted Answer · 2025-11-21 07:54:23Z

0

Python code that does it:

f = open("colors.csv", "r")
lines = f.read().split("\n")
f.close()
output = ""
for l in lines:
    parts = l.split(",")
    if len(parts) >= 3 and parts[0] == "white":
        if int(parts[1]) > 150:
            parts[1] = str(int(parts[1]) - 150)
        else:
            parts[1] = str(int(parts[1]) + 150)
        if int(parts[2]) > 150:
            parts[2] = str(int(parts[2]) - 150)
        else:
            parts[2] = str(int(parts[2]) + 150)
        output += (parts[0] + ", " + parts[1] + ", " + parts[2] + ",\n")
    else:
        output += (l + "\n")
f = open("colors.csv", "w")
f.write(output)
f.close()

note: If you run it twice all values will be reset, except 0, which will be 300.

edited yesterday

answered Aug 5, 2021 at 11:42

Mabbus

722 silver badges11 bronze badges

1 Comment

Mabbus Over a year ago

If it worked you should mark it as accepted answer (or mark the awk one, which was more elegant).

Collectives™ on Stack Overflow

Change numeric values in rows of CSV if string in same row has certain value

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related