Sponsored Content
Top Forums Shell Programming and Scripting Row to columns - special case Post 302613781 by jmarmitt on Tuesday 27th of March 2012 08:34:44 PM
Old 03-27-2012
Row to columns - special case

Hi. Let me start saying that i am kinda new to bash, and have few skills in programming. I've been advised to use bash to manipulate large .csv files. I've been able to do some data filtering using fors, grep and tail commands. That was kinda easy seeing examples. But now i need to do some hard stuff (for me, at least). I need to switch one entire row to columns. I have this:

Code:
01/01/2010;10:00;SomePrice
01/01/2010;10:05;SomePrice
01/01/2010;10:10;SomePrice
...
01/01/2010;16:55;SomePrice
02/01/2010;10:00;SomePrice
02/01/2010;10:05:SomePrice
...
12/31/2010;16:45;SomePrice
12/31/2010;16:50;SomePrice
12/31/2010;16:55;SomePrice

Each line, at each day, represents a price at each five-minute period. It stars at 10:00AM and ends 16:55PM.So there is like 96 observations per day. The problem is, there is a lot of missing observations, it's not always 96 per day (in fact, just a few cases have all 96 observations).

So i need to switch to columns matching each day:

Code:
                  ;10:00        ;10:05        ;10:10...                   
01/01/2010;SomePrice;SomePrice;SomePrice;...
02/01/2010;SomePrice;SomePrice;SomePrice;...
...
12/31/2010;SomePrice;SomePrice;SomePrice;...

So 10:00 goes to first columns, 10:05 to second, and so on. The time of day doesn't need to be switched too, i just added to make it clear. I can remove it from the data. I took 2010 as example (I have 5 more years with this kind of data). So, putting other way, using only one year as example, i need to transform 365 times 96 = 35040 rows in to a 365 rows to 96 columns matrix (but this numbers are just to ilustrate, since i don't have 96 obs per day all the time, nor 365 days of negotiation, so it will be an incomplete matrix). I've tried to solve this with some rows to columns codes, but it was a complete failure and i realized i needed help.

Anyone have any idea how to do this? Thanks in advance.

Last edited by jmarmitt; 03-28-2012 at 12:19 PM..
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Row to Columns

Hi, I have a file like this. 1,1,1,0,0,0 1,1,2,1,0,0 1,1,3,0,0,0 1,1,4,0,0,0 ........... ........... 1,1,24,0,0,0 1,1,25,0,0,0 1,1,26,1,0,0 1,1,27,0,0,0 1,2,1,0,0,0 1,2,2,0,0,0 1,2,3,0,0,0 1,2,4,0,0,0 1,2,5,1,0,0 1,2,6,1,0,0 (4 Replies)
Discussion started by: vskr72
4 Replies

2. UNIX for Dummies Questions & Answers

how to delete whole directory in special case

Hello, Today, as a root user, i want to copy recursively all files and diretories in a source directory to a destination directory using the following command, cp -r /home/smith/* /home/bob/ However, I carelessly missed the '*' out when I executed the command. Now, i noticed a... (1 Reply)
Discussion started by: cy163
1 Replies

3. SCO

Avoiding duplicates with some special case

Hi Gurus, I had a question regarding avoiding duplicates.i have a file abc.txt abc.txt ------- READER_1_1_1> HIER_28056 XML Reader: Error occurred while parsing:; line number ; column number READER_1_3_1> Sun Mar 23 23:52:48 2008 READER_1_3_1> HIER_28056 XML Reader: Error occurred while... (0 Replies)
Discussion started by: pssandeep
0 Replies

4. UNIX for Dummies Questions & Answers

rename more files special case

hello all i have a big problems for me if i have more files as example test.ghrt.part01.rar test.ghrt.part02.rar test.ghrt.part03.rar test.ghrt.part04.rar test.ghrt.part05.rar test.ghrt.part06.rar test.ghrt.part07.rar test.ghrt.part08.rar test.ghrt.part09.rar test.ghrt.part10.rar... (13 Replies)
Discussion started by: ateya
13 Replies

5. Shell Programming and Scripting

Check input for lenght, special characters and letter case

I made menu script for users so they can run other script without going in shell just from menu. But i must control their input. These are criteria: Input must have 4 signs First two signs are always lower case letters Input shall not have some special signs just letters and numbers ... (1 Reply)
Discussion started by: waso
1 Replies

6. Shell Programming and Scripting

help [[row and columns]]

i ask to do ,,program that convert the last row to be the first row ,,,and after that exchange the the columns ex,, 1 2 3 4 5 6 7 8 9 to be 7 8 9 4 5 6 1 2 3 and then to be 9 8 7 6 5 4 3 2 1 give mee the code .... (0 Replies)
Discussion started by: khaled1989kh
0 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

awk special parse case

I have a special case that awk could be used but I do not have the skill. Trying to create a final output file (indel_parse.txt) that is created from using some information from each of the two files (attached). parse rules: The header is skipped FNR>1 1. 4 zeros after the NC_ (not... (2 Replies)
Discussion started by: cmccabe
2 Replies

9. Shell Programming and Scripting

Text to column starting/ending with special character in each row

Hello, Here is my text data excerpted from the webpage: input My target is to get: What i tried is: sed 's/.*\(connector\)/1/' input > output but all characters coming before the word "connector" are deleted which is not good for me. My question: (9 Replies)
Discussion started by: baris35
9 Replies

10. Shell Programming and Scripting

Special case to skip function in bash menu

In the bash menu below if the variant that is inputted is in the format NM_004004.3:c.274G>T the below works perfectly. My question is if the variant inputted isNM_004004.3:-c.274G>T or NM_004004.3:+c.274G>T then the code as is will throw an error due to a biological issue. Is it possible to to... (1 Reply)
Discussion started by: cmccabe
1 Replies
HTML::CalendarMonth::DateTool(3pm)			User Contributed Perl Documentation			HTML::CalendarMonth::DateTool(3pm)

NAME
HTML::CalendarMonth::DateTool - Base class for determining which date package to use for calendrical calculations. SYNOPSIS
my $date_tool = HTML::CalendarMonth::DateTool->new( year => $YYYY_year, month => $one_thru_12_month, weeknum => $weeknum_mode, historic => $historic_mode, datetool => $specific_datetool_if_desired, ); DESCRIPTION
This module attempts to utilize the best date calculation package available on the current system. For most contemporary dates this usually ends up being the internal Time::Local package of perl. For more exotic dates, or when week number of the years are desired, other methods are attempted including DateTime, Date::Calc, Date::Manip, and the linux/unix 'ncal' or 'cal' commands. Each of these has a specific subclass of this module offering the same utility methods needed by HTML::CalendarMonth. METHODS
new() Constructor. Takes the following parameters: year Year of calendar in question (required). If you are rendering exotic dates (i.e. dates outside of 1970 to 2038) then something besides Time::Local will be used for calendrical calculations. month Month of calendar in question (required). 1 through 12. weeknum Optional. When specified, will limit class excursions to those that are currently set up for week of year calculations. historic Optional. If the the ncal or cal commands are available, use one of them rather than other available date modules since these utilities accurately handle some specific historical artifacts such as the transition from Julian to Gregorian. datetool Optional. Mostly for debugging, this option can be used to indicate a specific HTML::CalendarMonth::DateTool subclass for instantiation. The value can be either the actual utility class, e.g., Date::Calc, or the name of the CalendarMonth handler leaf class, e.g. DateCalc. Use 'ncal' or 'cal', respectively, for the wrappers around those commands. There are number of methods automatically available: month() year() weeknum() historical() datetool() Accessors for the parameters provided to "new()" above. dow1st() Returns the day of week number for the 1st of the "year" and "month" specified during the call to "new()". Relies on the presence of "dow1st_and_lastday()". Should be 0..6 starting with Sun. lastday() Returns the last day of the month for the "year" and "month" specified during the call to "new()". Relies on the presence of "dow1st_and_lastday()". Overridden methods Subclasses of this module must provide at least the "day_epoch()" and "dow1st_and_lastday()" methods. dow1st_and_lastday() Required. Provides a list containing the day of the week of the first day of the month (0..6 starting with Sun) along with the last day of the month. day_epoch() Optional unless interested in epoch values for wacky dates. For a given day, and optionally "month" and "year" if they are different from those specified in "new()", provide the unix epoch in seconds for that day at midnight. If the subclass is expected to provide week of year numbers, three more methods are necessary: dow() For a given day, and optionally "month" and "year" if they are different from those specified in "new()", provide the day of week number. (1=Sunday, 7=Saturday). add_days($days, $delta, $day, [$month], [$year]) For a given day, and optionally "month" and "year" if they are different from those specified in "new()", provide a list of year, month, and day once "delta" days have been added. week_of_year($day, [$month], [$year]) For a given day, and optionally "month" and "year" if they are different from those specified in "new()", provide a list with the week number of the year along with the year. (some days of a particular year can end up belonging to the prior or following years). AUTHOR
Matthew P. Sisk, <sisk@mojotoad.com> COPYRIGHT
Copyright (c) 2010 Matthew P. Sisk. All rights reserved. All wrongs revenged. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself. SEE ALSO
HTML::CalendarMonth(3), Time::Local(3), DateTime(3), Date::Calc(3), Date::Manip(3), cal(1) perl v5.12.4 2011-08-26 HTML::CalendarMonth::DateTool(3pm)
All times are GMT -4. The time now is 07:29 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy