Convert Data from Column to Row


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
# 1  
Convert Data from Column to Row

Hi

FileA.txt

Code:
E_TIM	16,
ETE	15,
EOND	26,
EEC	81,
E_1	un,
E_2	un,
E_3	un,
E_4	284,
E_TIM	17,
ETE	15,
EOND	29,
EEC	82,
E_1	un,
E_2	un,
E_3	un,
E_4	249,

Output FileB.txt

Code:
E_TIM	ETE	EOND	EEC	E_1	E_2	E_3	E_4
16,	15,	26,	81,	un,	un,	un,	284,
17,	15,	29,	82,	un,	un,	un,	249,

After 8 line data will be change but first column is same.
# 2  
In case Perl is an option, here's a program that does that:

Code:
$
$ cat -n f25
     1  E_TIM   16,
     2  ETE     15,
     3  EOND    26,
     4  EEC     81,
     5  E_1     un,
     6  E_2     un,
     7  E_3     un,
     8  E_4     284,
     9  E_TIM   17,
    10  ETE     15,
    11  EOND    29,
    12  EEC     82,
    13  E_1     un,
    14  E_2     un,
    15  E_3     un,
    16  E_4     249,

$
$ perl -ne 'BEGIN { $fmt = "%-8s %-8s %-8s %-8s %-8s %-8s %-8s %-8s\n" }
            if ($. % 8 == 1 and $. > 1) {
                printf($fmt, map { $_->[0] } @x);
                printf($fmt, map { $_->[1] } @x);
                @x = ();
            }
            /^(\S+)\s+(\S+)$/ and push @x, [ $1, $2 ];
            END {
                printf($fmt, map { $_->[0] } @x);
                printf($fmt, map { $_->[1] } @x);
            }
           ' f25
E_TIM    ETE      EOND     EEC      E_1      E_2      E_3      E_4
16,      15,      26,      81,      un,      un,      un,      284,
E_TIM    ETE      EOND     EEC      E_1      E_2      E_3      E_4
17,      15,      29,      82,      un,      un,      un,      249,
$
$

This User Gave Thanks to durden_tyler For This Post:
# 3  
Try also
Code:
awk     '/E_TIM/        {CNT++}
                        {LN[CNT]; HD[$1]; MX[CNT,$1]=$2}
         END            {               for (i in HD) printf "%10s", i; print "";
                         for (j in LN) {for (i in HD) printf "%10s", MX[j,i]; print ""}
                        }
        ' file
       ETE     E_TIM       E_1      EOND       E_2       E_3       E_4       EEC
       15,       16,       un,       26,       un,       un,      284,       81,
       15,       17,       un,       29,       un,       un,      249,       82,

This User Gave Thanks to RudiC For This Post:
# 4  
Both Code Works Perfect Thanks Guys ..
# 5  
Another awk:
Code:
awk -v n=8 'NR<=n{h=h (h?OFS:x) $1} {v=v (v?OFS:x) $2} !(NR%n) {if(h){print h; h=x} print v; v=x}' OFS='\t' file


Last edited by Scrutinizer; 03-20-2015 at 06:17 PM..
# 6  
Code:
E_TIM	16,
ETE	15,
EOND	26,
EEC	81,
E	un,
E	un,
E	249,
E	19,
E_TIM	17,
ETE	15,
EOND	29,
EEC	82,
E	un,
E	un,
E	249,
E	19,


E_TIM	ETE	EOND	EEC	E	E	E	E
16,	15,	26,	81,	un,	un,	un,	284,
17,	15,	29,	82,	un,	un,	un,	249,


Same code not working with Above input
# 7  
Your output does not correspond to the input.
The last two "E" values of each eight-pair are (249, and 19,) in the input.
But they are (un, and 284,) and (un, and 249,) in the output.

Code:
$
$ cat -n f26
     1  E_TIM   16,
     2  ETE     15,
     3  EOND    26,
     4  EEC     81,
     5  E       un,
     6  E       un,
     7  E       249,
     8  E       19,
     9  E_TIM   17,
    10  ETE     15,
    11  EOND    29,
    12  EEC     82,
    13  E       un,
    14  E       un,
    15  E       249,
    16  E       19,

$
$ perl -ne 'BEGIN { $fmt = "%-8s %-8s %-8s %-8s %-8s %-8s %-8s %-8s\n" }
            if ($. % 8 == 1 and $. > 1) {
                if (! $first) {
                    printf($fmt, map { $_->[0] } @x);
                    $first = 1;
                }
                printf($fmt, map { $_->[1] } @x);
                @x = ();
            }
            /^(\S+)\s+(\S+)$/ and push @x, [ $1, $2 ];
            END {
                printf($fmt, map { $_->[1] } @x);
            }
           ' f26
E_TIM    ETE      EOND     EEC      E        E        E        E
16,      15,      26,      81,      un,      un,      249,     19,
17,      15,      29,      82,      un,      un,      249,     19,
$
$

Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #852
Difficulty: Medium
AngularJS is a JavaScript-based open-source front-end web framework mainly maintained by Facebook.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

[Solved] Convert Row To column

Hi Folks, I am using db2 command -> db2 list tablespace show detail Tablespace ID = 10 Name = TSCDDHLMSUM Type = Database managed space Contents = All permanent data.... (5 Replies)
Discussion started by: ckwan
5 Replies

2. Shell Programming and Scripting

How to convert the row to column in Perl?

Dear Perl users, Could you help me how to convert from row to column if I've a case below: Linux 2014_01_24 CPU 10 Linux 2014_01_24 MEM 20 UNIX 2014_01_24 CPU 30 UNIX 2014_01_24 MEM ... (6 Replies)
Discussion started by: askari
6 Replies

3. Shell Programming and Scripting

Script: Convert row in to column

Hi, i need to convert SG_ERP1 SG_ERP2 SG_ERP3 in to: SG_ERP1 SG_ERP2 SG_ERP3 It's possibile? (16 Replies)
Discussion started by: elilmal
16 Replies

4. Shell Programming and Scripting

convert row to column with respect of first column.

Input file A.txt :- C2062 -117.6 -118.5 -117.5 C5145 0 0 0 C5696 0 0 0 Output file B.txt C2062 X -117.6 C2062 Y -118.5 C2062 Z -117.5... (4 Replies)
Discussion started by: asavaliya
4 Replies

5. UNIX for Advanced & Expert Users

Convert column data to row data using shell script

Hi, I want to convert a 3-column data to 3-row data using shell script. Any suggestion in this regard is highly appreciated. Thanks. (4 Replies)
Discussion started by: sktkpl
4 Replies

6. Shell Programming and Scripting

Convert row to column

Hi, I have a file like this 50 1 2 1374438 50 1 2 1682957 50 5 2 1453574 50 10 2 1985890 100 1 2 737307 100 5 2 1660204 100 10 2 2148483 and I want to convert this by... (1 Reply)
Discussion started by: gvj
1 Replies

7. Shell Programming and Scripting

Moving data from a specified column/row to another column/row

Hello, I have an input file like the following: 11_3_4 2_1_35 3_15__ _16989 Where '_' is a space. The data is in a table. Is there a way for the program to prompt the user for x1,y1 and x2,y2, where x1,y1 is the desired number (for example x=6 y=4 is a value of 4) and move to a desired spot... (2 Replies)
Discussion started by: jl487
2 Replies

8. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies

9. Shell Programming and Scripting

Convert row data to column data

Hi Guys, I have a file as follows: a 1 b 786 c 90709 d 99 a 9875 b 989 c 887 d 111 I want: a 1 9875 b 786 989 (3 Replies)
Discussion started by: npatwardhan
3 Replies

10. Shell Programming and Scripting

column to row convert - script - help

Hi, I have a file named col.txt 1.000 2.000 3.000 4.000 5.000 6.000 7.000 8.000 I should get this 1.000 5.000 2.000 6.000 3.000 7.000 (10 Replies)
Discussion started by: G0Y
10 Replies

Featured Tech Videos