Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Transpose with two newlines as delimiter

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transpose with two newlines as delimiter
# 1  
Old 01-22-2010
Transpose with two newlines as delimiter
Hi Guys,

I have data in a file as follows:

Code:
 
a 1 2 3
b 4 5 6 
 
 
a 6 7 8 
 
 
a 4 7 9 
b 6 8 5 
c 0 8 7

So the number of rows which have data is variable (2 for the first group, one for the second group and three for the third group), but the delimiters between the groups is always two newlines.

I want something like this:

Code:
a b c 
1 4 0
6 6
4

I tried the following transpose with awk and it is giving me:
Code:
 
a 1 2 3         b 4 5 6          a 6 7 8     a 4 7 9           b 6 8 5         c 0 8 7

There are tabs between different groups. Here is the awk statement I am using:

Code:
 
$ awk ' BEGIN {FS="\n\n"} { for (i=1;i<=NF;i++) { arr[NR,i]=$i; if(nf<= NF) nf=NF; } nr=NR } END { for(i=1;i<=nf;i++) { for(j=1;j<=nr;j++) { printf("%s\t",arr[j,i]); } printf("\n"); } }' try.txt > try1.txt

Any ideas what may be wrong?

Thanks.
# 2  
Old 01-22-2010
can you explain the logic behind this o/p
Quote:
a b c
1 4 0
6 6
4
# 3  
Old 01-23-2010
The logic is I want all the a values from multiple groups to appear under the a column, all b values under the b column and all the c values under the c column.


So from the original file, the first group has a as 1 (from the 1 2 3, a = 1), the second group has a = 6 and the third group has a = 4. Same thing for b and c. That is why the modified file should have 1,6 and 4 under a.

I call a group as the values separated by two newlines. Eg.
Code:
a 1 2 3
b 4 5 6

is a group and so on.

---------- Post updated at 10:28 PM ---------- Previous update was at 04:58 PM ----------

Anybody?
# 4  
Old 01-23-2010
CAVEAT: If there are any files in the script's current working directory with the same name as a first column's value, that file will have data appended before being deleted. If uncertain, best to run it from an empty directory.

It's probably best to wait for an AWK or perl solution Smilie

Code:
$ ./npatwardhan.sh data
a b c
1 4 0
6 6 
4

$ cat npatwardhan.sh 
#!/bin/sh

while read k v w; do
    f="$f $k"
    echo $v >>$k
done <"$1" 2>/dev/null

f=$(echo $f | tr ' ' '\n' | sort -u)

echo $f
paste -d' ' $f
rm $f

$ cat data
 
a 1 2 3
b 4 5 6 
 
 
a 6 7 8 
 
 
a 4 7 9 
b 6 8 5 
c 0 8 7

In the code: k=key, v=value, w=waste (unwanted data), f=file list.

Regards,
Alister
# 5  
Old 01-24-2010
Here's one way to do it with Perl:

Code:
$ 
$ # show the contents of the input file: f1
$ cat -n f1                                
     1  a 1 2 3                            
     2  b 4 5 6                            
     3                                     
     4                                     
     5  a 6 7 8                            
     6                                     
     7                                     
     8  a 4 7 9                            
     9  b 6 8 5                            
    10  c 0 8 7                            
$                                          
$
$ ##
$ perl -ane '
>   if (!/^\s*$/) {
>     # if hash value of $F[0] exists, then push $F[1] to the array
>     # referenced by $x[$y{$F[0]}]. Here, @x is an array, each of whose
>     # element is a reference to another array
>     if (defined $y{$F[0]}) {
>       $x[$y{$F[0]}] = [@{$x[$y{$F[0]}]}, $F[1]];
>       $max = $#{$x[$y{$F[0]}]} if $#{$x[$y{$F[0]}]} > $max;
>     }
>     # otherwise, push the reference to the array ($F[0], $F[1]) in @x
>     else {
>       push @x, [$F[0], $F[1]];
>       $y{$F[0]} = $#x;
>     }
>   }
>   END { # now just print the transpose of the two-dimensional array @x
>     foreach $c(0..$max){foreach $r(0..$#x){print $x[$r][$c]," "} print "\n"}
>   }' f1
a b c
1 4 0
6 6
4
$
$

tyler_durden
# 6  
Old 01-24-2010
use below they are 2 awks solution

Code:
awk '
NF{ a[$1]=a[$1]" "$2 ; next }
END{for (i in a) {print i,a[i] } }
'    infile.txt | awk '
   BEGIN{ FS=" " }

   {
      for (f = 1; f <= NF; f++)
         a[NR, f] = $f
   }
   NF > nf { nf = NF }
   END {
      for (f = 1; f <= nf; f++)
         for (r = 1; r <= NR; r++)
            
                printf"%s%s",a[r, f],(r==NR ? "\n" : FS)

   } 
'

Code:
i/p
a 1 2 3
b 4 5 6 
 
 
a 6 7 8 
 
 
a 4 7 9 
b 6 8 5 
c 0 8 7


Code:
o/p
a b c 
1 4 0
6 6
4

SmilieSmilieSmilie
# 7  
Old 01-24-2010
Ok I got the previous request working so I am editing this post.

@ahmad.diab,
I have one more modification to my request (I could probably post a new thread but decided to use the same thread as it is relevant). I want the output as :

Code:
a b c 
1 4 
6 
4 6 0

So where there are no entries for a,b and c, I want to see a blank. Can I modify your script so that it prints a blank space if there is no data?

Appreciate your help.
Last edited by npatwardhan; 01-27-2010 at 08:06 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Remove newlines

Hi buddy's my file are like this: s.no,name,band,sal 1,"suneel",,10 2,"bargav sand",,20 30," ebdug gil",,4 but i want s.no,name,band,sal 1,"suneel",,10 2,"bargav sand",,20 30,"ebdug gil",,4 any command or Shell script for this. please help me it's urgent to implement (33 Replies)
Discussion started by: Suneelbabu.etl
33 Replies

2. Shell Programming and Scripting

Perl Code to change file delimiter (passed as argument) to bar delimiter

Hi, Extremely new to Perl scripting, but need a quick fix without using TEXT::CSV I need to read in a file, pass any delimiter as an argument, and convert it to bar delimited on the output. In addition, enclose fields within double quotes in case of any embedded delimiters. Any help would... (2 Replies)
Discussion started by: JPB1977
2 Replies

3. Shell Programming and Scripting

Shell script to put delimiter for a no delimiter variable length text file

Hi, I have a No Delimiter variable length text file with following schema - Column Name Data length Firstname 5 Lastname 5 age 3 phoneno1 10 phoneno2 10 phoneno3 10 sample data - ... (16 Replies)
Discussion started by: Gaurav Martha
16 Replies

4. Shell Programming and Scripting

Replace commas with newlines

Good afternoon, I am trying to read user input. Here is what I have so far: echo "Type the Container ID for every container that you want subnets exported" echo "for (with comma between each one, for example... 1,45,98)" echo -n "if you want every one listed, then just type ALL in caps... (2 Replies)
Discussion started by: brianjb
2 Replies

5. Shell Programming and Scripting

How to cut by delimiter, and delimiter can be anything except numbers?

Hi all, I have a number of strings like below: //mnt/autocor/43°13'(33")W/ and i'm trying to get the numbers in this string, for example 431333 please help thanks ahead (14 Replies)
Discussion started by: sunnydanniel
14 Replies

6. Shell Programming and Scripting

Need help with eliminating newlines with Perl

Good morning, I need some help with getting rid of newlines with the output from a MYSQL query and putting the information into the right format that I need. Here is the script as it is today: #!/usr/bin/perl my $uda = system("/opt/incontrol/mysql/bin/mysql -u root -ppassword... (2 Replies)
Discussion started by: brianjb
2 Replies

7. Shell Programming and Scripting

Delete newlines after every one space

Hi All, I have a file which looks like this: abc 3456 computer 3214 printer 0.9823 computer 3214 Can anyone please let me know how I can format my text like this? abc 3456 computer 3214 printer 0.9823 computer 3214 I know how to space to newlines using tr but don't know how to do... (4 Replies)
Discussion started by: shoaibjameel123
4 Replies

8. Shell Programming and Scripting

Extract pattern before two newlines

Hi All, My file looks like this: 1 2 3 3 4 5 6 7 8 8 7 6 3 4 5 3 6 7 3 4 5 1 2 4 3 4 6 2 4 6 As you can see there are two newlines after the next pattern of numbers begin. (4 Replies)
Discussion started by: shoaibjameel123
4 Replies

9. Shell Programming and Scripting

Substring based on delimiter, finding last delimiter

Hi, I have a string like ABC.123.XYZ-A1-B2-P1-C4. I want to delimit the string based on "-" and then get result as only two strings. One with string till last hyphen and other with value after last hyphen... For this case, it would be something like first string as "ABC.123.XYZ-A1-B2-P1" and... (6 Replies)
Discussion started by: gupt_ash
6 Replies

10. OS X (Apple)

Add CRs (newlines)

I have a long file originally created with vi but at some point saved with MS Word. At another time I substituted all occurrences of ^M with XXX. Now I'd like to get this back to vi but with the XXX converted to newline. I'm using whatever version of vim Apple employs. Thanks, Gale (10 Replies)
Discussion started by: Gale Gorman
10 Replies
Login or Register to Ask a Question