Bash select valid rows from file with awk

user_11135386 0
Viewed 118

I have a large data set with some invalid rows. I want to copy to another file only rows which start with valid date (regex digits).

Basically check if awk $1 is digit ([0-9]), if yes, write whole row ($0) to output file, if no skip this row, go to next row.

How I imagine it like (both versions give syntax error): 

awk '{if ($1 =~ [0-9]) print $0 }' >> output.txt
awk '$1 =~ [0-9] {print $0}' filename.txt

while this does print the first field, I have no idea how to proceed.

awk '{ print $1 }' filename.txt

19780101
19780102
19780103
a
19780104
19780105
19780106
...

Full data set:

19780101    1   1   1   1   1
19780102    2   2   2   2   2
19780103    3   3   3   3   3
a   a   a   a   a   a
19780104    4   4   4   4   4
19780105    5   5   5   5   5
19780106    6   6   6   6   6
19780107    7   7   7   7   7
19780108    8   8   8   8   8
19780109    9   9   9   9   9
19780110    10  10  10  10  10
19780111    11  11  11  11  11
19780112    12  12  12  12  12
19780113    13  13  13  13  13
19780114    14  14  14  14  14
19780115    15  15  15  15  15
19780116    16  16  16  16  16
a   a   a   a   a   a
19780117    17  17  17  17  17
19780118    18  18  18  18  18
19780119    19  19  19  19  19
19780120    20  20  20  20  20

The data set can be reproduced with R

library(dplyr)
library(DataCombine)
N  <- 20
df = as.data.frame(matrix(seq(N),nrow=N,ncol=5))
df$date = format(seq.Date(as.Date('1978-01-01'), by = 'day', len = N), "%Y%m%d")
df <- df %>% select(date, everything())

df <- InsertRow(df, NewRow = rep("a", 6), RowNum = 4)
df <- InsertRow(df, NewRow = rep("a", 6), RowNum = 18)
write.table(df,"filename.txt", quote = FALSE, sep="\t",row.names=FALSE)

Questions about reading first N rows don't address my need, because my invalid rows could be anywhere. This solution doesn't work for some reason.

dataframe awk regex
4 Answers

Based on your data, you can check if first column has 8 digits to be representing a date in YYYYMMDD format using this command:

awk '$1 ~ /^[0-9]{8}$/' file > output
user_5482250

Since you have a large data set and such a simple requirement, you could just use grep for this as it'd be faster than awk:

grep '^[0-9]' file
user_17450010

You can just go with this:

awk '/^[0-9]+/' file.txt  >> output.txt

By default awk works with lines, so you tell him (I am assuming he is a boy) to select the lines that starts (^) with at least one digit ([0-9]+), and to print them, redirecting in output.txt.

Hope helps.

user_94343230

You can also try this..

sed '/^[0-9]/!d' inputfile > outputfile
user_117421410
Related