Shell Programming and Scripting

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.

I think you can do this using arrays. Try to learn arrays and make your attempt to create something.. we can guide you when u get stuck anywhere...

hints...

create an array with dates first.. like

Code:

DATES=$(grep data_file  | awk -F';' '{print $1}' | sort -u)

then iterate through it using the for loop and find the values for second and third column and store it to some arrays.

something like this

Code:

for dt in $DATES
do
ARRAY2=`grep '01/01/2010' data_file | awk -F';' '{print $2,3}`

Once you have this - create a third array with the times with 5 minutes intervals from 10:00-16:55 which comes to total 71 entries. Write a small function to fill the array with time entries you want.

outside the date array print the heading like date and all the time slots with your delimeter ';'. you will have to write a for loop to print that.

inside the date array loop create another loop on third array which is the time array and then check if your time matches the one stored in array2. if matches the echo output and add delimter - if dont then just echo blank space and then the delimeter. this way when you have the file ready and you import into excel - for the timeslots where entries are missing it will show blank...

I have given you the main logic of the script - get some help online and construct a script for your self... hope that helps

Moderator's Comments:

Please use code tags!