Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Data rearranging from rows to column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Data rearranging from rows to column
# 8  
Old 09-16-2015
Hello R. Singh,

I tried the code you gave me.
Code:
 
cat check_match.ksh
 awk --re-interval '{gsub(/-[0]\.[0-9]{2,3}/,"&\n",$0);gsub(/[0]{1,2}\.[0-9]{3}/,"&\n",$0);A=A?A ORS $0:$0} END{gsub("\n\n","\n",A);print A}' Input > temp_out
awk '{B=$0;A=gsub(/\./,X,B);if(A==2){C=$0;match(C,/.*\./);gsub(/\..*\./,"0.",$0);print "0" substr(C,RSTART,RLENGTH-1) ORS $0} else {print $0}}' temp_out

It seems to be working, but in some line there are this 0 and -0 added at the front and back of some values. Please check the output file for reference

Thanks,
Dinesh.N
U_ref1.txt (38.2 KB) 
# 9  
Old 09-16-2015
Try also
Code:
sed -r 's/(-*[0-9]\.)/\n\1/g; s/(\.[0-9]{3})([0-9])/\1\n\2/g; s/\n//' /tmp/U_z1.txt

This doesn't work if the number in front of the single 0 has less than 3 decimals.
This User Gave Thanks to RudiC For This Post:
dinesh.n
# 10  
Old 09-16-2015
Quote:
Originally Posted by dinesh.n
Hello R. Singh,

I tried the code you gave me.
Code:
 
cat check_match.ksh
 awk --re-interval '{gsub(/-[0]\.[0-9]{2,3}/,"&\n",$0);gsub(/[0]{1,2}\.[0-9]{3}/,"&\n",$0);A=A?A ORS $0:$0} END{gsub("\n\n","\n",A);print A}' Input > temp_out
awk '{B=$0;A=gsub(/\./,X,B);if(A==2){C=$0;match(C,/.*\./);gsub(/\..*\./,"0.",$0);print "0" substr(C,RSTART,RLENGTH-1) ORS $0} else {print $0}}' temp_out

It seems to be working, but in some line there are this 0 and -0 added at the front and back of some values. Please check the output file for reference
Thanks,
Dinesh.N
Hello Dinesh,

As you haven't mentioned how many zeros you need after point, so following code will take 3 zeros after point, let me know if this helps you.
Code:
cat check_match.ksh
awk --re-interval '{gsub(/-[0]\.[0-9]{2,3}/,"&\n",$0);gsub(/[0]{1,2}\.[0-9]{3}/,"&\n",$0);A=A?A ORS $0:$0} END{gsub("\n\n","\n",A);print A}' Input_file > out1221
awk '{B=$0;A=gsub(/\./,X,B);if(A==2){C=$0;match(C,/.*\./);gsub(/\..*\./,"0.",$0);R=substr(C,RSTART,RLENGTH-1) ORS $0;if(R !~ /^0/){Q="0" R;print substr(Q,1,5);} else {print $0}} else {print $0}}' out1221

EDIT: Adding a non one-liner form for solution now.
Code:
cat check_match.ksh
awk --re-interval '{gsub(/-[0]\.[0-9]{2,3}/,"&\n",$0);
                    gsub(/[0]{1,2}\.[0-9]{3}/,"&\n",$0);
                    A=A?A ORS $0:$0
                   }
                    END{
                                gsub("\n\n","\n",A);
                                print A
                       }
                   ' Input > out1221
awk '{B=$0;
      A=gsub(/\./,X,B);
                        if(A==2){C=$0;
                                 match(C,/.*\./);
                                 gsub(/\..*\./,"0.",$0);
                                 R=substr(C,RSTART,RLENGTH-1) ORS $0;
                                                if(R !~ /^0/){Q="0" R;print substr(Q,1,5);
                                                             }
                                                else {print $0}
                                }
                        else {print $0}
      }
    ' out1221

Thanks,
R. Singh
Last edited by RavinderSingh13; 09-16-2015 at 11:44 AM.. Reason: Added a non one-liner form of solution now.
This User Gave Thanks to RavinderSingh13 For This Post:
dinesh.n
# 11  
Old 09-17-2015
Good morning everyone,

Thanks for all your effort for helping me out. I now face a problem with a differnt data structure. Where some part of my values contains many zeros and i need them to be arranged in a seperate zeros. such as
PHP Code:
-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001 
. Where my output should look like
Code:
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001

I used the previous codes from RudiC and R.Singh and good a output(U_z27), please check the attachment. My input file is U_z2.

Thanks
Dinesh.N
U_z2.txt (31.1 KB) 
U_z27.txt (36.8 KB) 
# 12  
Old 09-17-2015
Can't be done reliably unless further info is supplied, e.g. number of zeroes.
This User Gave Thanks to RudiC For This Post:
dinesh.n
# 13  
Old 09-17-2015
Hello RudiC,

Thanks for your reply. Actually the problem is it's not possible to tell how many zeros. I am trying to automize the post processing based on the results genetrated by a CFD tool and in my future case there might be many hundres of zeros. If you could please check the outpout file U_z27 with input coming from U_z2. I used your code
Code:
sed -r 's/(-*[0-9]\.)/\n\1/g; s/(\.[0-9]{3})([0-9])/\1\n\2/g; s/\n//'

and got this output. The results looks fine but it is not arranging the zeros ( from line 2927 in the output file). I know that i might still not able to clarify your question. But if you could still help me. I will be glad.

Note: Files U_z2 and U_z27 are in my previous post

Thanks
Dinesh.N
# 14  
Old 09-17-2015
Hello Dinesh,

Could you please try following script and let me know if this helps you.
Let's say input file is as follows.
Code:
cat Input_file
-0.001-0.001-0.001-0.001-0.0010000000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001-0.001-0.001-0.001-0.001-0.00100000.0010.0010.0010.001

Then following is the code for same.
Code:
cat get_output.ksh
awk --re-interval '{gsub(/0\.00[0-9]/,"&\n");gsub(/000+/,"&\n0");print}' Input_file > output1
awk '{if($0 ~ /^0+$/){split($0, array,"");j=length(array);for(i=1;i<j;i++){print array[i]}} else {print}}' output1

Output will be as follows.
Code:
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001
-0.001
-0.001
-0.001
-0.001
-0.001
0
0
0
0
0.001
0.001
0.001
0.001

Hope this helps you.

EDIT: Changed the script into code by reading directly Input_file without writing awk 2 times now.
Code:
awk --re-interval '{gsub(/0\.00[0-9]/,"&\n");gsub(/000+/,"&\n0")split($0, A,"\n");} END{q=length(A);for(i=1;i<=q;i++){if(A[i] ~ /^0+$/){split(A[i], array,"");for(u=1;u<=length(A[i])-1;u++){print array[u]}} else {print A[i]}}}'  Input_file

Non one-liner form of solution.
Code:
awk --re-interval '{
                        gsub(/0\.00[0-9]/,"&\n");
                        gsub(/000+/,"&\n0");
                        split($0, A,"\n");
                   }
                        END{
                                q=length(A);
                                for(i=1;i<=q;i++){
                                                        if(A[i] ~ /^0+$/){
                                                                                split(A[i], array,"");
                                                                                for(u=1;u<=length(A[i])-1;u++){
                                                                                                                print array[u]
                                                                                                              }
                                                                         }
                                                        else             {
                                                                                print A[i]
                                                                         }
                                                 }
                           }
                  ' Input_file

Thanks,
R. Singh
Last edited by RavinderSingh13; 09-17-2015 at 07:40 AM.. Reason: Added one more solution with Input_file reading only
This User Gave Thanks to RavinderSingh13 For This Post:
dinesh.n
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to transpose pieces of data in a column to multiple rows?

Hello Everyone, I am very new to the world of regular expressions. I am trying to use grep/sed for the following: Input file is something like this and there are multiple such files: abc 1 2 3 4 5 ***END*** abc 6 7 8 9 ***END*** abc 10 (2 Replies)
Discussion started by: shellnewuser
2 Replies

2. UNIX for Beginners Questions & Answers

Rearranging the column

I have a text file like this, I would like to rearrange the first column (Name) according to the third column(percentage)in descending order. I mean methionine with the highest percentage should be the first one to appear under the name column. But I also want to exclude the headers from this... (2 Replies)
Discussion started by: cathum
2 Replies

3. Shell Programming and Scripting

How to separate rows of data into another column?

I have data such as below where the value in second field is the same as that in the row after. 123456,22222,John,0,xyz 234567,22222,John1,1,cde 43212,3333,Jean,3,pip 84324,3333,Abel,2,cat I'd like to rearrange the output like below to put such records beside each other and separated with... (5 Replies)
Discussion started by: james2009
5 Replies

4. Shell Programming and Scripting

Convert Column data values to rows

Hi all , I have a file with the below content Header Section employee|employee name||Job description|Job code|Unitcode|Account|geography|C1|C2|C3|C4|C5|C6|C7|C8|C9|Csource|Oct|Nov|Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep Data section ... (1 Reply)
Discussion started by: Hypesslearner
1 Replies

5. Shell Programming and Scripting

[Solved] Compare column data in all the rows

Hi.. In the below sorted input file.. I am comparing the first 3 columns of data one by one row and it is a pipeline delimitter file.. AA|BB|CC|line1 AA|BB|CC|ine4 AA|BB|CC|line2 BB|CC|DD|line3 BB|CC|DD|line5 If first 3 columns of data matches with any record in the file the... (4 Replies)
Discussion started by: NareshN
4 Replies

6. Shell Programming and Scripting

Transpose Column of Data to Rows

I can no longer find my commands, but I use to be able to transpose data with common fields from a single column to rows using a command line. My data is separated as follows: NAME=BOB ADDRESS=COLORADO PET=CAT NAME=SUSAN ADDRESS=TEXAS PET=BIRD NAME=TOM ADDRESS=UTAH PET=DOG I would... (7 Replies)
Discussion started by: docdave78
7 Replies

7. Shell Programming and Scripting

Counting and rearranging the rows

Hi, I have a file that I re-arranged using awk and unix commands to produce a file that looks like this: JOE JOE JOE JOE JOE BOB BOB HI HI HI I want to count how many of the same rows there are and print it on the second column while only maintaining the original name once. The... (5 Replies)
Discussion started by: phil_heath
5 Replies

8. Shell Programming and Scripting

Summing up rows data regarding 1st column

Dear all, I have one file like LABEL A B C D E F G H I J K L M N G02100 64651.3 25630.7 8225.21 51238 267324 268005 234001 52410.9 18598.2 10611 10754.7 122535 267170 36631.4 G02100 12030.3 8260.15 8569.91 ... (4 Replies)
Discussion started by: AAWT
4 Replies

9. Shell Programming and Scripting

column data to rows every n line

Hi every one, I am trying to organise an input text file like: input 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 into an output as following: output file 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 (5 Replies)
Discussion started by: nxp
5 Replies

10. UNIX for Dummies Questions & Answers

Find different column numbers among rows in data

I want to find the different column numbers among rows in a file. For example: A001 a b c d e ... N A002 a b c d e ... N A003 a b c d e ... N+1 A004 a b c d e ... N A005 a b c d e ... N+2 : : For most of the lines I will have N columns (say 1000 rows) in each line except the line 3... (5 Replies)
Discussion started by: AMBER
5 Replies
Login or Register to Ask a Question