Transpose rows into columns into the proper format

Question

I have 3 text files as emp id.txt, before sal.txt, now sal.txt. Contents of these 3 files :-

emp id.txt

emp id ----- 1 4 7 10 13 16 19

before sal.txt

before sal ----- 23 56 78 97 105 123 145

now sal

now sal ---- 25 60 82 99 109 124 150

I used below in the shell script

awk '{ for (i=1; i<=NF; i++) RtoC[i]= (RtoC[i]? RtoC[i] FS $i: $i) } END{ for (i in RtoC) print RtoC[i] }' to convert from Row to Column then I got output like -

1 23 25
4 56 60
7 78 82
10 97 99
13 105 109
16 123 124
19 145 150
emp before now
id  sal    sal
--- -----  ----

But I want output in the below format like-

--------------------------------
|emp id  |before sal | now sal |
|--------|---------- | --------|
|1       | 23        |  25     |
|4       | 56        |  60     | 
|7       | 78        |  82     |
|10      | 97        |  99     |
|13      | 105       |  109    |
|16      | 123       |  124    |
|19      | 145       |  150    |
--------------------------------

Could you please help me on this so that i can able to get the output in above format.

Answer 1

step1:

for i in "emp_id.txt" "beforesal.txt" "nowsal.txt"; do perl -pne "s/ /\n/g" $i >> "$i"fi; done


step2:

paste emp_id.txtfi beforesal.txtfi nowsal.txtfi

output

emp before  now
id  sal sal
-----   -----   ----
1   23  25
4   56  60
7   78  82
10  97  99
13  105 109
16  123 124
19  145 150

Answer 2

define a little function that transforms the file into a column:

f2c() { tr -s '[:blank:]' '\n' < "$1"; }

then use paste to stitch the columns together, process substitutions as file arguments to paste, and column for pretty output.

paste <(f2c 'emp id.txt') <(f2c 'before sal.txt') <(f2c 'now sal.txt') | column -t

emp    before  now
id     sal     sal
-----  -----   ----
1      23      25
4      56      60
7      78      82
10     97      99
13     105     109
16     123     124
19     145     150

Answer 3

given awk command actually does very close to what you expect as output but yes break the header into separate line since of spaces (this won't be an issue if your input files was separated with Tab or something else), anyway I didn't get how header is going to print at very last lines maybe different awk versions had different sorting option as default; for example on PROCINFO["sorted_in"]="@val_num_desc" your output would be similar to below (but still have no idea how numbers sorted asc order in your expected result):

19 145 150
16 123 124
13 105 109
10 97 99
7 78 82
4 56 60
1 23 25
id sal sal
emp before now
----- ----- ----

---

let's try with below modified command and see if that's what you need or close to something you really expect:

awk '{ $2=$1"_"$2; $1=""; 
       for (i=2; i<=NF; i++) RtoC[i]= (RtoC[i]? RtoC[i] "\t" $i: $i) }
END{ PROCINFO["sorted_in"]="@unsorted";
     for (i in RtoC)  print RtoC[i];
}' emp.txt before.txt new.txt |column -t

output:

emp_id  before_sal  now_sal
-----   -----       ----
1       23          25
4       56          60
7       78          82
10      97          99
13      105         109
16      123         124
19      145         150