'add a column to a dataset from the split of two other columns

I have the following dataset in ubuntu and I would like to make an iteration (while or for) in bash in order to generate a new column with the quotient between failed and passed subjects.

id, name, country, Continent, grade, passed, failed
1, Louise Smith, UK, Europe, 7, 5, 1
2, Okio Kiomoto, Japan, Asia, 9, 5, 0
3, Ralph Watson, USA, Northern America, 5.6, 5, 2
4, Mary Mcaann, South Africa, Africa, 4.7, 5, 3
5, Jack Thomson, Australia, Oceania, 10, 5, 0
6, N'dongo Mbaye, Senegal, Africa, 7.9, 5, 1

To do this I have tried to use the following code in a script. But I can't get any result as I can't find any way to add this new generated column to the current dataset.. Any ideas?

while IFS=, read _ _ _ _ _ passed failed; do
newcolumn=$($passed/$failed |bc)

done

As a guideline, the desired output would be as follows.

id, name, country, Continent, grade, passed, failed, new
1, Louise Smith, UK, Europe, 7, 5, 1, 0.2
2, Okio Kiomoto, Japan, Asia, 9, 5, 0, 0
3, Ralph Watson, USA, Northern America, 5.6, 5, 2, 0.4
4, Mary Mcaann, South Africa, Africa, 4.7, 5, 3, 0.6
5, Jack Thomson, Australia, Oceania, 10, 5, 0, 0
6, N'dongo Mbaye, Senegal, Africa, 7.9, 5, 1, 0.2

Thank you

bashubuntuc#recursionvisual-studio-2019stack-overflow


Solution 1:[1]

Using awk

$ awk  'BEGIN { FS=OFS=", " } NR == 1 { $8="new" } NR > 1 { $8=$NF/$(NF-1) }1' input_file
id, name, country, Continent, grade, passed, failed, new
1, Louise Smith, UK, Europe, 7, 5, 1, 0.2
2, Okio Kiomoto, Japan, Asia, 9, 5, 0, 0
3, Ralph Watson, USA, Northern America, 5.6, 5, 2, 0.4
4, Mary Mcaann, South Africa, Africa, 4.7, 5, 3, 0.6
5, Jack Thomson, Australia, Oceania, 10, 5, 0, 0
6, N'dongo Mbaye, Senegal, Africa, 7.9, 5, 1, 0.2

Solution 2:[2]

Tested and confirmed working on gawk 5.1.1, mawk 1.3.4, mawk 1.9.9.6, and macos nawk

                ______ # this pair of empty double quotes
              /        # is *** essential ***, since it forces string
             /         # compare, allowing rows getting "0" value in new  
            /          # column to print out properly
            \ 
{m,n,g}awk '"" ($(_=NF += !+FS) = !/[0-9]/ ? "new"  : \
                         ($--_) / (+$--_ ? $_ : --_^--_^_))' FS = '[,][ \t]*' 
                                                            OFS = ', '

——————————————————————————————————

id, name, country, Continent, grade, passed, failed, new
1, Louise Smith, UK, Europe, 7, 5, 1, 0.2
2, Okio Kiomoto, Japan, Asia, 9, 5, 0, 0
3, Ralph Watson, USA, Northern America, 5.6, 5, 2, 0.4
4, Mary Mcaann, South Africa, Africa, 4.7, 5, 3, 0.6
5, Jack Thomson, Australia, Oceania, 10, 5, 0, 0
6, N'dongo Mbaye, Senegal, Africa, 7.9, 5, 1, 0.2

——————————————————————————————————

(condensed version :::) 

mawk '""($(_=NF+=!+FS)=!/[0-9]/?"new":($--_)/(+$--_?$_:--_^--_^_) )' FS='[,][ \t]*' OFS=', '

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 HatLess
Solution 2

Related Questions