Saturday, August 16, 2008

Row to column transpose - bash scripting


I already discussed about transpose using awk in one of my older post. Now a different awk solution for the same.

Input file:

$ cat mtf.txt
a:b:cde:f:g
1:2
I:II:III:IV

Awk code:


$ awk 'BEGIN {FS=OFS=":"}
{
for (i=1;i<=NF;i++)
{
arr[NR,i]=$i;
if(big <= NF)
big=NF;
}
}

END {
for(i=1;i<=big;i++)
{
for(j=1;j<=NR;j++)
{
printf("%s%s",arr[j,i], (j==NR ? "" : OFS));
}
print "";
}
}' mtf.txt



One more solution:

$ awk -F ":" '{
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 a[r, f] (r==NR ? RS : FS)
}' mtf.txt


Output:
a:1:I
b:2:II
cde::III
f::IV
g::

7 comments:

Walter said...

amazing

well done, great job, saved me a problem in openoffice column limit

Jadu Saikia said...

@Walter, great to hear that. thanks for reading unstableme.

potzilov said...

Thank you for useful piece of code. While trying to execute this code, I've encountered the following error:

awk: program limit exceeded:maximum number of fields size=32767 FILENAME="filename.csv" FNR=1 NR=1.

I do have a file with 2 rows and around 40,000 columns which I would like to get transposed. How can I do it?

Jadu Saikia said...

@potzilov, thanks for reading unstableme.

On my ubuntu machine with GNU Awk 3.1.6, I can transpose a file with 40000 columns and 2 rows (using the same above codes).


Please try this python alternative and let me know the results:

e.g.

$ cat file.txt
1 2 3 4
5 6 7 8
1 2 3 4

$ python -c "import sys; print('\n'.join(' '.join(c) for c in zip(*(l.split() for l in sys.stdin.readlines() if l.strip()))))" < file.txt

o/p:

1 5 1
2 6 2
3 7 3
4 8 4

potzilov said...

@Jadu Saikia, thank you for the quick reply. Your python code does the job, thank you. I also found out the reason for awk program giving the error: on my Ubuntu machine the installed version is mawk 1.3.3, which has a max NF value of 32767. That, it seems, explains the problem I encountered.

Jadu Saikia said...

@potzilov great, thanks,

tommycarstensen said...

What are the memory requirements of this method?

© Jadu Saikia www.UNIXCL.com