Rows to columns with awk-last line issue

Question

I have several folders, each containing a text file, which I would to transpose (convert lines to columns) in Bash. I have set this up with awk inside a for loop, however I have an issue with the last line, which is never transposed correctly.

This is my input.txt

ID1 11208   13391   16070   19383
ID2 6691    8489    8723    7493
ID3 5768    6004    7754    7614

This is my output.txt with the error.

ID1 ID2 ID3
11208   6691    5768
13391   8489    6004
16070   8723    7754
19383       
7493    
7614

This is what I want

ID1 ID2 ID3
11208   6691    5768
13391   8489    6004
16070   8723    7754
19383   7493    7614

My awk one liner is :

input.txt | awk '{for(i=1; i<=NF; i++) A[i]=A[i] (NR>1?OFS:x) $i} END{for(i=1; i<=NR; i++) print A[i]}' OFS="\t" > output.txt

Thank you all in advance for your time

Welcome to SO, please wrap up your code in CODE TAGS a button `{}` while editing your post. — RavinderSingh13, May 24 '18 at 08:47
If you can use GNU `datamash`, then `datamash transpose < input.txt` is the way to go. Otherwise, see https://stackoverflow.com/questions/1729824/an-efficient-way-to-transpose-a-file-in-bash for many, many suggestions. — Wintermute, May 24 '18 at 08:52
Possible duplicate of [An efficient way to transpose a file in Bash](https://stackoverflow.com/questions/1729824/an-efficient-way-to-transpose-a-file-in-bash) — Sundeep, May 24 '18 at 08:59

RavinderSingh13 · Accepted Answer · 2018-05-24T11:09:15.697

1

Following awk may help you here.

awk '
!a[$1]++{
  val=val?val OFS $1:$1}
{
  for(i=2;i<=NF;i++){
    b[FNR,i]=$i}
}
END{
  print val;
  for(j=2;j<=NF;j++){
    for(k=1;k<=FNR;k++){
      printf("%s%s",b[k,j],k==FNR?ORS:OFS)}
}}
'   Input_file

EDIT: Thanks to @jas for pointing this point here:

@OP: Check here if you have DOS ending lines by doing:

cat -v Input_file

If you see control M characters then do following to remove them.

tr -d '\r' < Input_file > temp_file && mv temp_file Input_file

edited May 24 '18 at 11:09

answered May 24 '18 at 08:56

RavinderSingh13

130,504
14
57
93

Sorry I am a newbie here haven't followed previous threads, will do so thanks! – Antonis G May 24 '18 at 08:59
Thanks RavinderSingh13, I have seen this also in another answer will give it a try! – Antonis G May 24 '18 at 09:03
@AntonisG, yes in case your input is coming from another command then do like `your_command | my awk script` let me know how it goes then?? – RavinderSingh13 May 24 '18 at 09:49
@RavinderSingh13 I tried the code above yet sadly it gives the same problematic output. The first row value is transposed properly while the remaining ones are left as column and are shifted one line below – Antonis G May 24 '18 at 10:18
@AntonisG, it is working well for me for shown examples, could you please post in code tags output what is not working?? – RavinderSingh13 May 24 '18 at 10:28
1

Almost certainly OP has DOS line endings (extra Ctl-M being attached to last numbers in each line). Use `cat -t input.txt` to verfiy. – jas May 24 '18 at 11:07
@jas, cool, thanks jas for pointing it out, added that codes too to remove the control M characters here. – RavinderSingh13 May 24 '18 at 11:09
1

@RavinderSingh13 Just got it right, thank you very much for your time and help!! – Antonis G May 24 '18 at 11:46

Rows to columns with awk-last line issue

1 Answers1