0

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 ?
user7268185
  • 57
  • 1
  • 1
  • 8

1 Answers1

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.

James Brown
  • 36,089
  • 7
  • 43
  • 59
  • Thanks Sir for your response. – user7268185 May 26 '17 at 10:00
  • Hi Sir, I tried implementing your code but for "rs" is unidentified in my terminal. could you please suggest ? – user7268185 May 30 '17 at 09:00
  • `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`. – James Brown May 30 '17 at 10:33