In bash how to move row field to column in a text file

Question

I have a .txt file with this record:

field_1        value01a value01b value01c
field_2        value02
field_3        value03a value03b value03c

field_1        value11
field_2        value12a value12b 
field_3        value13

field_1        value21
field_2        value22
field_3        value23

...

field_1        valuen1
field_2        valuen2
field_3        valuen3

I would like to convert them like that:

field1                      field2            field3
value01a value01b value01c  valu02            value03a value03b value03c 
value11                     value12a value12b value13
value21                     value22           value23
...
valuen1                     valuen2           valuen3

I have tried something like:

awk '{for (i = 1; i <NR; i ++) FNR == i {print i, $ (i + 1)}}' filename

or like

awk '
{ 
   for (i=1; i<=NF; i++)  {
     a[NR,i] = $i
   }
}
NF>p { p = NF }
END {    
    for(j=1; j<=p; j++) {
       str=a[1,j]
       for(i=2; i<=NR; i++){
            str=str" "a[i,j]
       }
       print str
    }
 }'

but i can't get it to work

I would like the values to be transposed and that each tuple of values associated with a specific field is aligned with the others

Any suggestions?

Thank you in advance

On SO its highly encouraged for all users to add their efforts in form of code in their questions. So kindly do add the same in your question and let us know then. — RavinderSingh13, Sep 04 '20 at 10:54
Thanks for adding your efforts in your question. So it means you want to print values whenever there is a empty line? — RavinderSingh13, Sep 04 '20 at 11:05
I want to transpose the values associated with field1, field2 and field3 — famedoro, Sep 04 '20 at 11:10
the question is not the same as https://stackoverflow.com/questions/1729824/an-efficient-way-to-, if you try the proposed solution it doesn't work with my example transpose-a-file-in-bash — famedoro, Sep 04 '20 at 13:36

score 3 · Accepted Answer · answered Sep 04 '20 at 17:58

I have downloaded your bigger sample file. And here is what I have come up with:

awk -v OFS='\t' -v RS= '
((n = split($0, a, / {2,}| *\n/)) % 2) == 0 {
   # print header
   if (NR==1)
      for (i=1; i<=n; i+=2)
         printf "%s", a[i] (i < n-1 ? OFS : ORS)
   # print all records
   for (i=2; i<=n; i+=2)
      printf "%s", a[i] (i < n ? OFS : ORS)
}' reclamiTestFile.txt | column -t -s $'\t'

Code Demo

RavinderSingh13 · Answer 2 · 2020-09-07T10:54:40.853

Could you please try following, written and tested with shown samples in GNU awk.

awk '
{
  first=$1
  $1=""
  sub(/^ +/,"")
  if(!arr[first]++){
    ++indArr
    counter[indArr]=first
  }
  ++count[first]
  arr[first OFS count[first]]=$0
}
END{
  for(j=1;j<=indArr;j++){
    printf("%s\t%s",counter[j],j==indArr?ORS:"\t")
  }
  for(i=1;i<=FNR;i++){
     for(j=1;j<=indArr;j++){
       if(arr[counter[j] OFS i]){
          printf("%s\t%s",arr[counter[j] OFS i],j==indArr?ORS:"\t")
       }
     }
  }
}' Input_file | column -t -s $'\t'

column command is taken from @anubhava sir's answer here.

In bash how to move row field to column in a text file

2 Answers2