sed: truncate decimals in csv file column

Question

I am trying to remove the entire decimal part of the numbers of a csv file, with sed. All I can do is truncate all the decimals (that are .0) from the whole columns of the file and I don't know how to do it only in the sixth column.

Input:

PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
343,No,2,"Collander, Mr. Erik Gustaf",male,28.0,0,0,248740,13.0,,S
76,No,3,"Moen, Mr. Sigurd Hansen",male,25.0,0,0,348123,7.65,F G73,S

Expected Output:

PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
343,No,2,"Collander, Mr. Erik Gustaf",male,28,0,0,248740,13.0,,S
76,No,3,"Moen, Mr. Sigurd Hansen",male,25,0,0,348123,7.65,F G73,S

My try:

sed -r ‘s/\.0//g’ file.csv

Answer 1

A bit awkward, but I think it gets the job done.

The csvkit Python package contains command line tools for working with CSV files. Among these is csvcut, which works like the standard cut command, but which is CSV aware, which means that it allows for fields that may contain embedded field delimiters and newlines, as long as they are properly quoted.

With csvcut, we can cut out the column that we want to modify, modify it, and then put it back in. I'm using paste to put the fields back together again, with a comma as delimiter. The paste utility gets three input streams corresponding to the first few fields (unmodified), the field that we want to fix up (modified with a simple sed script), and the last few fields (unmodified).

paste -d, \
    <( csvcut -c -5  file.csv ) \
    <( csvcut -c  6  file.csv | sed '1!s/\..*//' ) \
    <( csvcut -c  7- file.csv )

The sed expression 1!s/\..*// removes a dot and everything after it on all lines except from the first line.

This is slightly inefficient as it reads the whole input file three times.

Result:

PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
343,No,2,"Collander, Mr. Erik Gustaf",male,28,0,0,248740,13.0,,S
76,No,3,"Moen, Mr. Sigurd Hansen",male,25,0,0,348123,7.65,F G73,S

Answer 2

I wasn't able to do this with sed because the use of commas as the delimiter as well as the comma in the Name field not being a delimiter made it too complicated so I used awk with the gsub option:

awk -F ',' 'NR==1; NR>1{gsub("\.0","",$7);print} ' OFS="," file

This is somewhat messy given the circumstances but it sets a comma as the delimiter, print the first line, and then on any line after the first line, substitutes .0 with nothing in the 7th field as the comma in the name effectively makes the age the 7th field, and then sets the output field separator as a comma again and then prints:

PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
343,No,2,"Collander, Mr. Erik Gustaf",male,28,0,0,248740,13.0,,S
76,No,3,"Moen, Mr. Sigurd Hansen",male,25,0,0,348123,7.65,F G73,S

Answer 3

When working with CSV files, the main problem that you are facing is that your column separator may reside in the column value itself, if the column is of type string and the string is enclosed in (double) quotes.

So, the following command:

sed -r 's/^(([^,]*,){6})([^\.,]*)[^,]*(,.*)$/\1\3\4/g'

would work, but only if you do not have a comma in a text string. If you are dealing with such a case, thn a tool such as the one suggested by the previous poster is needed.

Answer 4

I tried to do it by using the longest regex from beginning of line to the last double quotes, used the following field as is, preserved the whole digits prior to decimal, and removed .0. Here is the solution

sed 's/\(.*\",[^,]*,[[:digit:]]*\)\.0\(.*\)/\1\2/'

Answer 5

A simple awk approach can be to get the age field looking at from the end bcoz from that end the fields don't have delimiters inside:

awk -F, -v OFS=, '
  NR>1{$(NF-6) = int($(NF-6))}1
' file

---

Using sed with extended regex -r we count the fields from the end

sed -r '
  s/\.0((,[^,]*){6})$/\1/
' file

Output:

PassengerId,Survived,Pclass,Name,Sex,Age,SibSp,Parch,Ticket,Fare,Cabin,Embarked
343,No,2,"Collander, Mr. Erik Gustaf",male,28,0,0,248740,13.0,,S
76,No,3,"Moen, Mr. Sigurd Hansen",male,25,0,0,348123,7.65,F G73,S

Answer 6

With csvkit tools:

csvjoin -I -c PassengerId <(csvcut -c -5 file.csv) <(csvcut -c 1,6 file.csv | awk '{sub(/[.][0-9]+/, ""); print}') <(csvcut -c 1,7- file.csv)

For introduction of csvkit and csvcut, please see the other answer.

csvcut takes fields from csv files with -c HeaderName or -c HeaderNumber argument. -C does the exact opposite.

In our file use like this: csvcut -c 6 file.csv or csvcut -c Age file.csv

csvcut -c 1,6 file.csv takes first and sixth field of data file. Output of this command is piped to awk '{sub(/[.][0-9]+/, ""); print}') to remove dot and digits after that dot. The period . is used in [] character set because in regex expression without this, It would match any single character but newline.

csvcut -c -5 file.csv is short writing for csvcut -c 1-5 file.csv. That means we want to take first five fields. Similarly csvcut -c 7- file.csv takes fields from seventh field to the end.

csvjoin can join three streams by columns with -c argument.In this case column we used to join is PassengerId. To use PassengerId for join, we took first field in every stream.

We have used -I or --no-inference because without this argument string "No" or zeros would have been replaced by False.