Convert Data from Column to Row


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert Data from Column to Row
# 1  
Old 03-20-2015
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  
Old 03-20-2015
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  
Old 03-20-2015
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  
Old 03-20-2015
Both Code Works Perfect Thanks Guys ..
# 5  
Old 03-20-2015
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 05:17 PM..
# 6  
Old 03-20-2015
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  
Old 03-20-2015
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 to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. Shell Programming and Scripting

Convert column values into row

hi, I have a requirement where in I read the values from a file using awk. The resulting data should be converted into row format from column format. For ex: My log file login.lst contains the following SERVER1 DB1 SERVER2 DB2 SERVER3 DB3 SERVER4 DB4 I use awk to grep only the server... (6 Replies)
Discussion started by: senthil3d
6 Replies

9. 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

10. UNIX for Dummies Questions & Answers

convert column into row with some modifier

A file content have 1 1:-0.289433 2:0.833778 3:0.314471 4:-0.289433 5:-0.81876 6:-0.456693 7:-0.17511 8:-0.644555 9:-0.00666341 10:-1.13603 I will like to have that column into row with numbers to be printed (red color) only after colon output shud be like that -0.289433... (1 Reply)
Discussion started by: cdfd123
1 Replies
Login or Register to Ask a Question