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

Ihos

11/29/22, 6:47 PM

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'

137

1 + 2

xubuntu

bash

windows-subsystem-for-linux

linux-headers

ChanganAuto

11/29/22, 6:49 PM

What does this have to do with Ubuntu?

NotTheDr01ds

11/29/22, 10:12 PM

Or Windows Subsystem for Linux (as tagged)?

Score:1

Ubuntu

glenn jackman

11/29/22, 7:01 PM

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

0 + 0

Elon Musk

I sit in a Tesla and translated this thread with Ai:

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

TH: แก้ไขคอลัมน์เฉพาะตามคอลัมน์อื่นโดยใช้ awk และ gsub

RO: modificați anumite coloane pe baza unei alte coloane utilizând awk și gsub

RU: изменить определенные столбцы на основе другого столбца с помощью awk и gsub

VI: sửa đổi các cột cụ thể dựa trên một cột khác bằng cách sử dụng awk và gsub

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.