Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Transpose columns to row

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Transpose columns to row
# 1  
Old 08-30-2016
Transpose columns to row
Gents

Using the attached file

and using this code.
Code:
awk '{print substr($0,4,2)}' input.txt | sort -k1n | awk '{a[$1]++}END{for(i in a) print i,a[i]}' | sort -k1 > output

i got the this output.
Code:
00 739
01 807
02 840
03 735
04 782
05 850
06 754
07 295
08 388
09 670
10 669
11 762
12 587
13 647
14 723
15 837
16 797
17 867
18 781
19 840
20 795
21 809
22 928
23 898

But i would like to change the format transposing the columns like this.

Code:
  00   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23
 739  807  840  735  782  850  754  295  388  670  669  762  587  647  723  837  797  867  781  840  795  809  928  898

Please can you help.

Thanks for your help.Smilie
input.txt (191.2 KB) 
# 2  
Old 08-30-2016
Have you searched the forums for this already. This type of question has been asked before.



Robin
# 3  
Old 08-30-2016
Hi rabatte1,

yes I did ,, but i would like to keep the format as the output.

Basically it is counting the total by hrs for 24hrs... It is possible to fill from 00 to 23 and count the numbers according to the file attached,, and if there is nothing for some hour write 0? .
# 4  
Old 08-30-2016
Hello jiam912,

Could you please check following if this helps you.
Code:
awk '{A[substr($0,4,2)+0]++} END{for(i=0;i<=NR;i++){if(A[i]){Q=Q?Q OFS sprintf("%02d",i):sprintf("%02d",i);V=V?V OFS A[i]:A[i]}};print Q ORS V}' OFS="\t"  Input_file

Thanks,
R. Singh
# 5  
Old 08-30-2016
Hi RavinderSingh13,

It works perfect...

Please if I remove the hrs 01 and 02 in the input file i got. (using the code ).

Code:
00	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23
739	735	782	850	754	295	388	670	669	762	587	647	723	837	797	867	781	840	795	809	928	898

Kindly can you modify it to get this:
Code:
00	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23
739	00	00	735	782	850	754	295	388	670	669	762	587	647	723	837	797	867	781	840	795	809	928	898

Thanks a lot
Last edited by rbatte1; 08-30-2016 at 12:34 PM.. Reason: Corrected person to thank
# 6  
Old 08-30-2016
Hi RavinderSingh13,

Attached the new input file.
input4.txt (173.5 KB) 
Last edited by rbatte1; 08-30-2016 at 12:34 PM.. Reason: Corrected person to thank
# 7  
Old 08-30-2016
Hello jiam912,

In case you wanted each number which is missed starting from 1 to till last line of Input_file, then following may help you in same(Not tested though).
Code:
awk '{A[substr($0,4,2)+0]++} END{for(i=0;i<=NR;i++){Q=Q?Q OFS sprintf("%02d",i):sprintf("%02d",i);V=V?V OFS (A[i]=A[i]?A[i]:0):(A[i]=A[i]?A[i]:0)};print Q ORS V}' OFS="\t"  Input_file

EDIT: Adding a non-one liner form of solution on same.
Code:
awk '{
        A[substr($0,4,2)+0]++
     }
     END{
                for(i=0;i<=NR;i++){
                                        Q=Q?Q OFS sprintf("%02d",i):sprintf("%02d",i);
                                        V=V?V OFS (A[i]=A[i]?A[i]:0):(A[i]=A[i]?A[i]:0)
                                  };
                print Q ORS V
        }
    ' OFS="\t"   Input_file

Thanks,
R. Singh
Last edited by RavinderSingh13; 08-30-2016 at 01:11 PM.. Reason: Added a non-one liner form of solution now.
This User Gave Thanks to RavinderSingh13 For This Post:
jiam912
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Transpose the Row and column

Hi, I have data in form of A ram B shyam C seeta D geeta A bob B methew C Richad D Mike and i want it in this form. A B C D ram shyam seeta geeta bob methew Richard Mike. please help by providing the scripting for this. (3 Replies)
Discussion started by: ricbha
3 Replies

2. Shell Programming and Scripting

How transpose column in a row?

Hello guys, First of all happy holidays and happy new year. I'm new in bioinformatic and also it is my first time that I write in this forum. Therefore, sorry if I make some mistakes. I'm writing to ask your help to fix a problem: I have a file like this: gene1 GO:0016491|GO:0055114... (8 Replies)
Discussion started by: Salvatore_espos
8 Replies

3. UNIX for Beginners Questions & Answers

Transpose a sequence of symbols from row to one columns (no separators))

Hi all, I have a data file. It contains one header line followed by a new line which is one row of different characters without separators (charahters can be dots, dash, capital and small letters). What I need is ignoring header line to transpose these characters so they form a column. So, from... (4 Replies)
Discussion started by: kush
4 Replies

4. Shell Programming and Scripting

Transpose row to column

I'm using the testawk.awk from the following thread https://www.unix.com/shell-programming-and-scripting/18897-row-column-transpose.htmlI'm getting the following output fieldname1 data1 fieldname2 data2 fieldname3 data3 How can I get like this instead 1 fieldname1 data1 2 fieldname2 data2... (1 Reply)
Discussion started by: makkan
1 Replies

5. Shell Programming and Scripting

To transpose row into column

Hi All, In shell, I have below data coming from some some text file as below: . 351706 5861.8 0.026 0.012 12.584 0.026 0.012 12.582 0.000 0.000 0.000 Now i need the above data to be transposed as below 351706... (16 Replies)
Discussion started by: Anamica
16 Replies

6. Shell Programming and Scripting

Column to row Transpose

Hi Folks, Iam a kinda newbie to unix shell scripting, the scenario is i have a text file containing the following info Charlie chicago 15 Charlie newyork 26 jonny chicago 14 jonny newyork 15 joe chicago 15 joe newyork 18output should be Name chicago ... (3 Replies)
Discussion started by: tech_frk
3 Replies

7. UNIX for Dummies Questions & Answers

Select 2 columns and transpose row by row

Hi, I have a tab-delimited file as follows: 1 1 2 2 3 3 4 4 a a b b c c d d 5 5 6 6 7 7 8 8 e e f f g g h h 9 9 10 10 11 11 12 12 i i j j k k l l 13 13 14 14 15 15 16 16 m m n n o o p p The output I need is: 1 1 a a 5 5 e e 9 9 i i 13... (5 Replies)
Discussion started by: mvaishnav
5 Replies

8. Shell Programming and Scripting

Transpose column to row

Hi i have a file which has values seperated by "," as shown below and I want to transpose for every doc_id in one row. Input: DOC_ID,KEYWORD 105,REGISTROS 105,GEOLOGIA 105,NUCLEOS 105,EXPEDIENTE 105,PROGRAMAS 10025,EXPEDIENTE 10025,LOCALIZACIONES 10025,OFICIOS 10025,PROGRAMAS... (4 Replies)
Discussion started by: juelillo
4 Replies

9. Shell Programming and Scripting

Transpose - Multiple row to Multiple columns

I have seen many posts to transpose rows into columns, but not for multiple rows to columns..so please help me how to do this.. I have around 1000 rows and 1000 columns, i want to transpose it.. A1 A2 A3 B1 B2 B3 C1 C2 C3 D1 D2 D3 I want output to be like A1 B1 C1 D1 A2 B2 C2 D2... (5 Replies)
Discussion started by: ganeshss
5 Replies

10. Shell Programming and Scripting

Row to column transpose

Can we transpose rows to columns? Fields within row are separated by a comma. (10 Replies)
Discussion started by: videsh77
10 Replies
Login or Register to Ask a Question