Score:0

modify specific columns based on another column by using awk and gsub

bt flag

I have a file (file1) with millions of rows and columns. An example of data are:

"col1","col2","col3","col4","col5","col6"
"AAA",0,5,10,"BGB",50
"BBB",4,7,10,"BFD",76
"AAA",15,0,0,"BGB",20
"AAA",10,13,10,"DDD",23

I want to find all lines that have AAA in col1 and then get all the rows that have BGB in col5. And finally, decrease 50% of every value in col2, col3, col4, and col6 (Ignore if cell values are 0 or blank). And print all the lines of the file. So, my output will look like following:

"col1","col2","col3","col4","col5","col6"
"AAA",0,2.5,5,"BGB",25
"BBB",4,7,10,"BFD",76
"AAA",7.5,0,0,"BGB",10
"AAA",10,13,10,"DDD",23

I have been trying the following, but could not make it work (also, could not figure out how to use multiple columns in gsub)

grep AAA file1 | awk -F "," '$5~/BGB/ {gsub($6,\substr($6,1,length($6)-1)*0.50\, $6}1'
ChanganAuto avatar
us flag
What does this have to do with Ubuntu?
NotTheDr01ds avatar
vn flag
Or Windows Subsystem for Linux (as tagged)?
Score:1
cn flag

awk can match patterns like grep does, so you almost never need grep and awk in a pipeline.

You could do

 awk  '
    BEGIN {FS = OFS = ","}
    $1 ~ /AAA/ && $5 ~ /BGB/ {
        if ($2) $2 = $2 / 2
        if ($3) $3 = $3 / 2
        if ($4) $4 = $4 / 2
        if ($6) $6 = $6 / 2
    }
    1
' file

Or, if you want to make the columns to reduce more dynamic

awk -v "columns=2,3,4,6" '
    BEGIN {
        FS = OFS = ","
        n = split(columns, a, /,/)
        for (i=1; i<=n; i++) cols[a[i]]=1
    }
    $1 ~ /AAA/ && $5 ~ /BGB/ {
        for (c in cols) if ($c) $c = $c / 2
    }
    1
' file
mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.