Transpose from row to column using timestamp in first column Post: 303021758

Sponsored Content

Top Forums Shell Programming and Scripting Transpose from row to column using timestamp in first column Post 303021758 by Don Cragun on Thursday 16th of August 2018 08:27:52 AM

08-16-2018

Registered User

Since you still haven't answered several of my questions, I hate to make wild guesses at what the format of your input files really is (and the following code absolutely will not work with the sample data you provided in post #1 in this thread), but as long as each set of lines contains 6 lines, every line in each 6 line set contains the same number of fields, and there are no empty input fields or missing lines in a 6 line set (as in the file tmp1.txt that you attached to post #6); the following produces the same output as was in the file tmp2.txt that you attached to that same post and seems to me to be easier to understand:

Code:

awk '
BEGIN {	FS = OFS = ","
}
{	for(i = 2; i <= NF; i++)
		d[NR % 6, i] = $i
}
!(NR % 6) {
	for(i = 2; i <= NF; i++)
		print $1, d[1, i], d[2, i], d[3, i], d[4, i], d[5, i], d[0, i]
}' tmp1.txt

This was tested with awk on macOS High Sierra version 10.13.6, but should also work with a current gawk on a Linux system. It will also work with /usr/xpg4/bin/awk or nawk on Solaris/SunOS systems.

This User Gave Thanks to Don Cragun For This Post:

Don Cragun

View Public Profile for Don Cragun

Find all posts by Don Cragun

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Row to column transpose

Can we transpose rows to columns? Fields within row are separated by a comma.

2. Shell Programming and Scripting

How do I transpose a column of results to a row

Hi, Can anyone advise me what command I could use to display the results of the following command as a row. Thanks Gareth

3. 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. Shell Programming and Scripting

Row to column transpose between same pattern.

Hi All, I have been trying to transpose rows to column in an large file (about 15000 lines) between matching pattern. Searched all posts in this forum, but not able find the solution to my problem. Any help appreciated.!! Input /*------XXXXXX-------*/ owner: XXXX location: XXXX...

5. UNIX for Dummies Questions & Answers

Row to column transpose

Hi there, Below is sample three rows which i need transpose into multiple rows. By keeping first 2 fields static and split them into multiple rows depend following date field. Each into seperate rows. Sample code: ...

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

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

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

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

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

LEARN ABOUT CENTOS

psc

PSC(1)							      General Commands Manual							    PSC(1)

NAME

       psc - prepare sc files

SYNOPSIS

       psc [-fLkrSPv] [-s cell] [-R n] [-C n] [-n n] [-d c]

DESCRIPTION

       Psc  is used to prepare data for input to the spreadsheet calculator sc(1).  It accepts normal ascii data on standard input.  Standard out-
       put is a sc file.  With no options, psc starts the spreadsheet in cell A0.  Strings are right justified.  All data on a line is entered	on
       the  same row; new input lines cause the output row number to increment by one.	The default delimiters are tab and space.  The column for-
       mats are set to one larger than the number of columns required to hold the largest value in the column.

OPTIONS

       -f     Omit column width calculations.  This option is for preparing data to be merged with an existing spreadsheet.  If the option is  not
	      specified, the column widths calculated for the data read by psc will override those already set in the existing spreadsheet.

       -L     Left justify strings.

       -k     Keep  all  delimiters.   This  option  causes the output cell to change on each new delimiter encountered in the input stream.   The
	      default action is to condense multiple delimiters to one, so that the cell only changes once per input data item.

       -r     Output the data by row first then column.  For input consisting of a single column, this option will result in  output  of  one  row
	      with multiple columns instead of a single column spreadsheet.

       -s cell
	      Start  the  top  left  corner  of the spreadsheet in cell.  For example, -s B33 will arrange the output data so that the spreadsheet
	      starts in column B, row 33.

       -R n   Increment by n on each new output row.

       -C n   Increment by n on each new output column.

       -n n   Output n rows before advancing to the next column.  This option is used when the input is  arranged  in  a  single  column  and  the
	      spreadsheet is to have multiple columns, each of which is to be length n.

       -d c   Use the single character c as the delimiter between input fields.

       -P     Plain numbers only.  A field is a number only when there is no imbedded [-+eE].

       -S     All numbers are strings.

       -v     Print the version of psc

SEE ALSO

       sc(1)

AUTHOR

       Robert Bond

PSC 7.16							 19 September 2002							    PSC(1)

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Row to column transpose

Discussion started by: videsh77

2. Shell Programming and Scripting

How do I transpose a column of results to a row

Discussion started by: m223464

3. Shell Programming and Scripting

Transpose column to row

Discussion started by: juelillo

4. Shell Programming and Scripting

Row to column transpose between same pattern.

Discussion started by: RobP

5. UNIX for Dummies Questions & Answers

Row to column transpose

Discussion started by: ganeshd

6. Shell Programming and Scripting

Column to row Transpose

Discussion started by: tech_frk

7. Shell Programming and Scripting

To transpose row into column

Discussion started by: Anamica

8. Shell Programming and Scripting

Transpose row to column

Discussion started by: makkan

9. Shell Programming and Scripting

How transpose column in a row?

Discussion started by: Salvatore_espos

10. UNIX for Beginners Questions & Answers

Transpose the Row and column

Discussion started by: ricbha

LEARN ABOUT CENTOS

psc