How do I rearrange columns in a text file?

Question

Month   Name  Marks  
2016-10 Sam   58  
2016-09 Sam   77  
2016-10 John  64  
2016-09 John  47  
2016-10 Mark  71  
2016-09 Mark  38  
2016-10 Steve 83  
2016-09 Steve 39  

I fetch this data from my DB having month in 1st column and marks in 3rd column for each student in 2nd column. Now i want to edit in such way that it contains the name in 1st column, marks for 2016-10 in 2nd column and then marks for 2016-09 in third column.

Answer 1

Assuming that your input data is in a file called `grades, try:

$ awk 'BEGIN{ PROCINFO["sorted_in"]="@ind_str_desc"} NR==1{next} {m[$1]; n[$2]; g[$2,$1]=$3} END{for (name in n) {printf "%s",name; for (month in m) printf " %s",g[name,month]; print""}}' grades | column -t
Steve  83  39
Sam    58  77
Mark   71  38
John   64  47

The output has one row per student with the grades presented in descending order of month.

For those who prefer their code spread out over multiple lines:

gawk '
    BEGIN{ PROCINFO["sorted_in"]="@ind_str_desc"}
    NR==1{
        next
    }

    {
        m[$1]
        n[$2]
        g[$2,$1]=$3
    }

    END{
        for (name in n) {
            printf "%s",name
            for (month in m)
                printf " %s", g[name,month]
                print""
        }
    }

    ' grades | column -t

How it works

• BEGIN{ PROCINFO["sorted_in"]="@ind_str_desc"}

  This tells awk that we want arrays sorted in indices. This is a GNU feature.

• NR==1{next}

  This tells awk to skip over the first line. If you want to add a header for the output file, we could do it here.

• m[$1]

  This tells awk to add an entry for the current month in associative array m. We don't need to assign a value because we will simply use this to track which months are present in the input.

• n[$2]

  This tells awk to add an entry for the student name in associative array n. We don't need to assign a value because we will simply use this to track which months are present in the input.

• g[$2,$1]=$3

  This assigns the grade as a the value under the key of student name, month in associative array g.

• END{for (name in n) {printf "%s",name; for (month in m) printf " %s",g[name,month]; print""}}

  After we have reached the end of the file, we print out all the names and grades for each student.

• column -t

  This optional step makes the output pretty.

Answer 2

If you need double month

sed '
    2~2{                                               #for even lines
        N                                              #attach next line
        s/\(\S\+ \)\(\S\+ \)[0-9]*\n\(\S\+\).*/\2\1\3/ #rearrange two line
    }
    1c\Name Month1 Month2                              #output new header
    ' file.data

Or double marks

sed '
    1!N             #from second line attach next line 
    s/\S\+ //       #remove first field (2016-10)
    s/\n.* / /      #remove 2 fields in attached line
    t               #ommit 1st line
    s/$/1 Marks2/   #arrange header
    ' file.data

Other version

echo 'Name Marks1 Marks2' ;\
paste -sd' \n' <(tail -n +2 file.data) |
cut -d' ' -f 2,3,6

Answer 3

A somewhat crude example:

Here you set the variable m to the dates you want to include and their order; as a comma separated string. As per example below that would be:

m=2016-10,2016-09

Which in turn gives:

Name 2016-10 2016-09

This require names to be unique and without spaces …

awk -v m=2016-10,2016-09 '

    NR==1{next}
    {
        # Set array x[name][month]=marks
        x[$2][$1]=$3
    }
    END {
        split(m, k, ",")
        printf "Name"
        for (v in k)
            printf "\t%s", k[v]
        for (e in x) {
            printf "\n%s", e
            for (v in k)
                printf "\t%s", x[e][k[v]]
        }
        print ""
    }
' data

Sample output:

Name    2016-10 2016-09
Steve   83  39
Mark    71  38
John    64  47
Sam 58  77

By column -t:

Name   2016-10  2016-09
Steve  83       39
Mark   71       38
John   64       47
Sam    58       77

---

If this is a one time thing, and your data is as per example (ordered, only two months etc.) then this could also do:

awk 'NR==1{next}NR%2{print $3;next}{printf "%s\t%s\t",$2,$3}' data