0

I am dealing with a tab separated file with nearly 200MM rows on Linux. In one the columns which contains binary values, I noticed the data type is not consistent and there is a large number of missing values. Here is an example:

input:

timestamp val 1589205592 0 1589205593 0.0 1589205594 1589205595 1 1589205595 1.0

I tried what was suggested here using awk, but seems it can be really slow since the file size is large. I am trying to fill these values with 0, make the data types consistent i.e., convert all float to int, and overwrite the current file.

output:

timestamp val 1589205592 0 1589205593 0 1589205594 0 1589205595 1 1589205595 1
Improve this question
3
  • Will the tab always be present, even if you don't have data in the second field? Or do you have lines with no tab, just the first field? Do you want all decimals removed, or only .0? What if you have 1.00? Should that also become 1? I assume you don't want to change if the value is 1.06, right? Or do you want to round the numbers to the closest integer value? What is the "data type" and how is it not consistent? Please edit your question and clarify. Commented May 11, 2020 at 14:18
  • @terdon- Thanks for your suggestion and comment. I edited the post. Commented May 11, 2020 at 15:22
  • @Kusalananda. I wish I could:) Commented May 11, 2020 at 15:23

4 Answers 4

Reset to default
1 
awk 'BEGIN { FS=OFS="\t" } NR > 1 { $2 = sprintf("%d", $2) }; 1' file >file.new

This would read your data and write it to a new file with all the number in the second column converted to integers (by rounding down, missing data would be turned into zeros).

If you want floating point output, e.g. with two decimal places, use %.2f in place of %d as the format string in the call to sprintf().

The output will be tab-delimited.

Once you've inspected the new file by eye, simply mv it in place of the old file.

Improve this answer
1

Or inline with sed (just add the -i though it has the temp file overhead too)

sed -e "s/\.0$//" -e "s/\t$/\t0/" file
Improve this answer
0

simply do:

gawk -i inplace -F'\t' 'NR>1{ $2*=1 }1' OFS='\t' infile

however -i inplace doesn't do such magic and it creates a temp file then replaces after process ends. so you can also do

awk -F'\t' 'NR>1{ $2*=1 }1' OFS='\t' infile > newfile

then either remove original infile or rename newfile with original infile.

Improve this answer
0

You could try Miller

mlr --csvlite --fs tab put '$val = is_empty($val) ? 0 : int($val)' file

Miller provides an in-place mode should you wish to try it.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.