CODEWITHSUNDEEP
bashcsvawk
HattrickNZ
HattrickNZ

Reputation: 4653

adding a column to csv and doing division + awk

this is my file 

$ cat test1
"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Device_Model","Product_Description"
"2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I","$39 Plan"
"2015-10-06","592","620","0","Apple Inc","Apple iPhone 6 (A1586)","PREPAY  STD - TRIAL - #16"
"2015-10-06","007","290","0","Apple Inc","Apple iPhone 6 (A1586)","PREPAY PLUS - $0 -"
"2015-10-06","592","050","48836832","Apple Inc","Apple iPhone 5S (A1530)","Talk and Text Connect Flexi Plan"
"2015-10-06","409","720","113755347","Samsung Korea","Samsung SM-G360G","$29 CARRYOVER PLAN"
"2015-10-06","742","620","19840943","Apple Inc","Apple iPhone S (A1530)","PREPAY STD - $0 - #2"
"2015-10-06","387","180","0","HUAWEI Technologies Co Ltd","HUAWEI HUAWEI G526-L11","PREPAY STD - $1 - #4"
"2015-10-06","731","570","2258243","Samsung Korea","Samsung SM-N910U","Business Freedom"
"2015-10-06","556","910","13332272","Samsung Korea","Samsung GT-I9505","$49 Plan"

this command adds a column to the end.

$ awk -F"," 'BEGIN { OFS = "," } {$7=($4); print}' test1
"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Device_Model","Data_Volume_Bytes"
"2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I","137765"
"2015-10-06","592","620","0","Apple Inc","Apple iPhone 6 (A1586)","0"

how do I get the following output: for simplicity this is just $7=($4/1). 

"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Device_Model","Data_Volume_Bytes"
    "2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I","137765"
    "2015-10-06","592","620","0","Apple Inc","Apple iPhone 6 (A1586)","0"

What I really want to be able to do is $7=$4/10124/1024 but for some reason I can not get the division to work.

I have tried this but I only get $7 =equal to zero.

 awk -F"," 'BEGIN { OFS = "," } {$7=($4/1024); print}' test1
"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Device_Model",0
"2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I",0

EDIT1: thsi is how I change the name of the col header in column 7. Not sure if using \" is the best or only way to keep it inside double quotes.

$ awk -F"," '{ OFS = "," } NR<=1{$7="\"Data_Volume_MB\""; print}' test1
"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Device_Model","Data_Volume_MB"

EDIT2 THIS IS WHAT i want but it involves casting from a string to an int in awk substr($4,2,(length($4)-2)). however the answer is not in double quotes 0.131383, for uniformity how do I achieve this?

$ awk -F"," '{ OFS = "," } NR>1{$7=substr($4,2,(length($4)-2))/1024/1024; print}' test1
"2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I",0.131383
"2015-10-06","592","620","0","Apple Inc","Apple iPhone 6 (A1586)",0
"2015-10-06","007","290","0","Apple Inc","Apple iPhone 6 (A1586)",0
"2015-10-06","592","050","48836832","Apple Inc","Apple iPhone 5S (A1530)",46.5744

Upvotes: 0

Views: 92

Answers (1)

karakfa
karakfa

Reputation: 67507

You're on the right track, just few minor issues

$ awk -F, -v OFS=, -v q='"' 'NR==1{$7=q"Data_Volume_MB"q}
                 NR>1{$7=$4; gsub(/"/,"",$7); $7= q $7/(1024*1024)q}1' file

"Rec_Open_Date","MSISDN","IMEI","Data_Volume_Bytes","Device_Manufacturer","Device_Model","Data_Volume_MB"
"2015-10-06","427","060","137765","Samsung Korea","Samsung SM-G900I","0.131383"
"2015-10-06","592","620","0","Apple Inc","Apple iPhone 6 (A1586)","0"
"2015-10-06","007","290","0","Apple Inc","Apple iPhone 6 (A1586)","0"
"2015-10-06","592","050","48836832","Apple Inc","Apple iPhone 5S (A1530)","46.5744"
"2015-10-06","409","720","113755347","Samsung Korea","Samsung SM-G360G","108.486"
"2015-10-06","742","620","19840943","Apple Inc","Apple iPhone S (A1530)","18.9218"
"2015-10-06","387","180","0","HUAWEI Technologies Co Ltd","HUAWEI HUAWEI G526-L11","0"
"2015-10-06","731","570","2258243","Samsung Korea","Samsung SM-N910U","2.15363"
"2015-10-06","556","910","13332272","Samsung Korea","Samsung GT-I9505","12.7146"'

-v q='"' define a variable instead of escaping the quotes

gsub(/"/,"",$7) remove quotes

$7= q $7/(1024*1024)q do the division and add quotes back

Upvotes: 1

Related Questions