How to transpose Rows and columns in unix shell?

Question

I want to transpose rows and columns of a text file by using unix script. Could anyone please suggest the Work Around ?

Input file: INPUT.txt

DM_AG_POSN_FDIC_BASE    22-MAY-2017 02:56:00    03:15:46    00:19:46 
DM_AG_POSN_FDIC_BASE    23-MAY-2017 03:26:47    03:45:33    00:18:46 
DM_EC_CONS_POSN_BASE_HELPER     22-MAY-2017 03:06:43     03:08:38   00:01:55 
DM_EC_POSN_B3_ENRICHED  22-MAY-2017 03:08:43     03:23:14   00:14:31 
DM_EC_POSN_B3_PRORATED  22-MAY-2017 03:23:27     03:30:07   00:06:40 
DM_EC_CONS_POSN_BASE_HELPER     23-MAY-2017 03:36:21     03:38:10   00:01:49 
DM_EC_POSN_B3_ENRICHED  23-MAY-2017 03:38:23     04:00:08   00:21:44 
DM_EC_POSN_B3_PRORATED  23-MAY-2017 04:00:08     04:06:30   00:06:21

Required Output file is :

DM_AG_POSN_FDIC_BASE                    DM_EC_CONS_POSN_BASE_HELPER               DM_EC_POSN_B3_ENRICHED                   DM_EC_POSN_B3_PRORATED

22-MAY-2017 02:56:00 03:15:46 00:19:46   22-MAY-2017 03:06:43 03:08:38 00:01:55             22-MAY-2017 03:08:43 03:23:14 00:14:31  22-MAY-2017 03:23:27 03:30:07 00:06:40
23-MAY-2017 03:26:47 03:45:33 00:18:46   23-MAY-2017 03:36:21 03:38:10 00:01:49        23-MAY-2017 03:38:23 04:00:08 00:21:44   23-MAY-2017 04:00:08 04:06:30 00:06:21

Note: The number of rows are not constant, but the first column name is always constant for INPUT.txt file.

Appreciate your suggestion/ workaround !

Implemented code suggested by one of the active member of this group.

sed 's/  \+/|/g;s/ $//' file |
awk -F '|' '{x=$1;$1="";a[x]=a[x]"|" substr($0, 2)} END{for(i in a) print i a[i]}' |
awk -F '|' '{for (i=1; i<=NF; i++) a[i,NR]=$i; max=(max<NF?NF:max)} END {for (i=1; i<=max; i++) {for (j=1; j<=NR; j++) printf "%s%s", a[i,j], (j<NR?"|":ORS) }}' |
column -t -s '|'

I am getting output like :

DM_AG_POSN_FDIC_BASE
22-MAY-2017 02:56:00 
-BLANK-  --> There is no output from 2nd line onwards(tried for only one record). Could you please check this ?

What is your column delimiter in INPUT.txt? Tab or space?

Cyrus
– Cyrus

2017-05-26 07:24:02 +00:00
Commented May 26, 2017 at 7:24 — Cyrus
– Cyrus, Commented May 26, 2017 at 7:24
Hi cyrus, Its a space

user7268185
– user7268185

2017-05-26 07:36:44 +00:00
Commented May 26, 2017 at 7:36 — user7268185
– user7268185, Commented May 26, 2017 at 7:36

James Brown · Accepted Answer · 2017-05-26 08:32:34Z

1

Here's one that is fragile to the input data. sort groups the records, Awk splits $1 and the rest of the record on separate lines and rs is used for transposing that into 3 rows with pretty output:

$ awk '{if(p!=$1)print $1;p=$1;sub(p,"",$0);$1=$1}1' <(sort file) | rs -e -t 3
DM_AG_POSN_FDIC_BASE                    DM_EC_CONS_POSN_BASE_HELPER             DM_EC_POSN_B3_ENRICHED                  DM_EC_POSN_B3_PRORATED
22-MAY-2017 02:56:00 03:15:46 00:19:46  22-MAY-2017 03:06:43 03:08:38 00:01:55  22-MAY-2017 03:08:43 03:23:14 00:14:31  22-MAY-2017 03:23:27 03:30:07 00:06:40
23-MAY-2017 03:26:47 03:45:33 00:18:46  23-MAY-2017 03:36:21 03:38:10 00:01:49  23-MAY-2017 03:38:23 04:00:08 00:21:44  23-MAY-2017 04:00:08 04:06:30 00:06:21

Each header item is expected to have exactly 2 lines of data like in the sample data. If this is not the case, please update the data to better reflect the real situation.

edited May 26, 2017 at 8:32

answered May 26, 2017 at 8:26

James Brown

37.7k8 gold badges52 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user7268185 Over a year ago

Thanks Sir for your response.

user7268185 Over a year ago

Hi Sir, I tried implementing your code but for "rs" is unidentified in my terminal. could you please suggest ?

James Brown Over a year ago

rs does the transposing so you needed it for this solution. Could you maybe install it and benefit from it in the future as well? rs - reshape a data array if you search for it with apt-cache: apt-cache search rs | grep ^rs.

Collectives™ on Stack Overflow

How to transpose Rows and columns in unix shell?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related