2

I have copied a partial csv file.

publish_date,headline_text,likes_count,comments_count,shares_count,love_count,wow_count,haha_count,sad_count,thankful_count,angry_count
20030219,aba decides against community broadcasting licence,1106,118,109,155,6,5,2,0,6
20030219,act fire witnesses must be aware of defamation,137,362,67,0,0,0,0,0,0
20030219,a g calls for infrastructure protection summit,357,119,212,0,0,0,0,0,0
20030219,air nz staff in aust strike for pay rise,826,254,105,105,21,45,7,0,90
20030219,air nz strike to affect australian travellers,693,123,153,17,113,4,103,0,7
20030219,ambitious olsson wins triple jump,488,57,161,0,0,0,0,0,0
20030219,antic delighted with record breaking barca,386,60,80,3,4,0,93,0,68
20030219,aussie qualifier stosur wastes four memphis match,751,45,297,0,0,0,0,0,0
20030219,aust addresses un security council over iraq,3847,622,141,1,0,0,0,0,0
20030219,australia is locked into war timetable opp,1330,205,874,0,0,0,0,0,0
20030219,australia to contribute 10 million in aid to iraq,3530,130,0,23,16,4,1,0,0
20030219,barca take record as robson celebrates birthday in,13875,331,484,0,0,0,0,0,0
20030219,bathhouse plans move ahead,11202,450,2576,433,51,20,4,0,34
20030219,big hopes for launceston cycling championship,3988,445,955,0,0,0,0,0,0
20030219,big plan to boost paroo water supplies,460,101,92,0,0,0,0,0,0
20030219,blizzard buries united states in bills,303,223,193,0,0,0,0,0,0

I would like to find a shell command that will help me able to make a new column that will add up each entries (likes_count+ love_count + thankful_count) - (angry_count + sad_count) and name the column emotional_polarity.

I have tried

awk -F , {$12=$3+$6+$10-$11-$9;}{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12} file

but it does not work for some reason the columns become mixed together. i think this may be because I am losing the comma when I perform this

1
  • Beware of commas in the headlines. – berndbausch May 3 at 15:58
7

set OFS (Output Field Separator) too so that you don't lose commas. It loses the commas when you do $12=$3+$6+$10-$11-$9, i.e, setting/updating any column's value which in this case awk does the field splitting on the current line based on the OFS internal variable, which is Space character by default, so setting it to a comma will keep those on output when printing.

awk 'BEGIN{ FS=OFS="," }
          { $(NF+1)=(NR==1? "emotional_polarity" : $3+$6+$10-$11-$9); print }' infile

or simply append the new updates to the current input line:

awk -F, '{ $0=$0 FS (NR==1? "emotional_polarity" : $3+$6+$10-$11-$9); print }' infile

from the awk manual:

FS
The input field separator (see section Specifying How Fields Are Separated). The value is a single-character string or a multicharacter regular expression that matches the separations between fields in an input record.

OFS
The output field separator (see section Output Separators). It is output between the fields printed by a print statement. Its default value is " ", a string consisting of a single space.

0
3

If it'd be useful to refer to the fields by their names (e.g. if the order of the columns can change):

$ cat tst.awk
BEGIN { FS=OFS="," }
NR == 1 {
    $(NF+1) = "emotional_polarity"
    for (i=1; i<=NF; i++) {
        f[$i] = i
    }
}
NR > 1 {
    $(f["emotional_polarity"]) = \
        ( $(f["likes_count"]) + $(f["love_count"]) + $(f["thankful_count"]) ) \
            - ( $(f["angry_count"]) + $(f["sad_count"]) )

}
{ print }

$ awk -f tst.awk file
publish_date,headline_text,likes_count,comments_count,shares_count,love_count,wow_count,haha_count,sad_count,thankful_count,angry_count,emotional_polarity
20030219,aba decides against community broadcasting licence,1106,118,109,155,6,5,2,0,6,1253
20030219,act fire witnesses must be aware of defamation,137,362,67,0,0,0,0,0,0,137
20030219,a g calls for infrastructure protection summit,357,119,212,0,0,0,0,0,0,357
20030219,air nz staff in aust strike for pay rise,826,254,105,105,21,45,7,0,90,834
20030219,air nz strike to affect australian travellers,693,123,153,17,113,4,103,0,7,600
20030219,ambitious olsson wins triple jump,488,57,161,0,0,0,0,0,0,488
20030219,antic delighted with record breaking barca,386,60,80,3,4,0,93,0,68,228
20030219,aussie qualifier stosur wastes four memphis match,751,45,297,0,0,0,0,0,0,751
20030219,aust addresses un security council over iraq,3847,622,141,1,0,0,0,0,0,3848
20030219,australia is locked into war timetable opp,1330,205,874,0,0,0,0,0,0,1330
20030219,australia to contribute 10 million in aid to iraq,3530,130,0,23,16,4,1,0,0,3552
20030219,barca take record as robson celebrates birthday in,13875,331,484,0,0,0,0,0,0,13875
20030219,bathhouse plans move ahead,11202,450,2576,433,51,20,4,0,34,11597
20030219,big hopes for launceston cycling championship,3988,445,955,0,0,0,0,0,0,3988
20030219,big plan to boost paroo water supplies,460,101,92,0,0,0,0,0,0,460
20030219,blizzard buries united states in bills,303,223,193,0,0,0,0,0,0,303
0
1

I would make two changes in what you have tried. This is your command:

awk -F , '{$12=$3+$6+$10-$11-$9;}{print }' file

When OFS="," is used in BEGIN block, half of our work is done. This is how fields are separated when printed. Next if(NR==1) $NF="emotional_polarity" does the other half. Though use of $(NF+1) is better than $12, I would use here $12. $12=$a+..$b add another field to $0. This increments NF value by one. Therefore if statement changes last filed of line 1(NR ==1) to "emotional_polarity". I, now, place these two expressions in your command.

awk -F , 'BEGIN{OFS=","}{$12=$3+$6+$10-$11-$9; if(NR==1) $NF="emotional_polarity"}{print }' file

I tried it with an array like this:

awk -F',' 'BEGIN{OFS=","}
{arr[NR][1]=$0; arr[NR][2]=$3+$6+$10-$11-$9;}
END {
arr[1][2]="emotional_polarity";
for(i=1;i<=NR;i++) print arr[i][1], arr[i][2] }' file

arr[NR][1] takes all $0 output whereas arr[NR][2] takes calculation.

In END block, we set arr[1][2] to "emotional_polarity" because we wanted to name the field emotional_polarity. Then we tell awk to print.

New contributor
Prabhjot Singh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.