Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Script to compact information !

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script to compact information !
# 1  
Old 11-04-2009
Question Script to compact information !
Hello Group,

Could you please help me with a shell script that condense in this case each 12 rows of hour information in only one row per day as following:

Final Fields in each row
1.Ticker Name
2. D (Daily)
3. Date
4. Open Price (The open price of the first record of the day)
5. High Price (The high price of all 12 rows of each day)
6. Low Price (The low price of all 12 rows of each day)
7. Close (The close price of the last record of the day)
8. Volume (The sum of all volume fields per day)

Final Result of the rows:
<ticker>,<per>,<date>,<open>,<high>,<low>,<close>,<vol>
EUR_USD,D,11/02/2009,1.4783,1.4844,1.4727,1.4772,11633
EUR_USD,D,11/03/2009,1.4732,1.4631,1.4631,1.4654,12887


The information that I want to process is in the following format:

<ticker>,<per>,<date>,<time>,<open>,<high>,<low>,<close>,<vol>
EUR_USD,I,11/02/2009,12:00,1.4783,1.4784,1.4766,1.4774,824
EUR_USD,I,11/02/2009,13:00,1.4773,1.4775,1.4746,1.4754,854
EUR_USD,I,11/02/2009,14:00,1.4753,1.4779,1.4739,1.4777,1320
EUR_USD,I,11/02/2009,15:00,1.4778,1.4844,1.4778,1.4817,2389
EUR_USD,I,11/02/2009,16:00,1.4816,1.4834,1.4813,1.4827,1302
EUR_USD,I,11/02/2009,17:00,1.4828,1.4831,1.4761,1.4762,1053
EUR_USD,I,11/02/2009,18:00,1.4761,1.4778,1.4731,1.4733,1148
EUR_USD,I,11/02/2009,19:00,1.4734,1.4771,1.4727,1.4751,1112
EUR_USD,I,11/02/2009,20:00,1.4752,1.4770,1.4747,1.4765,936
EUR_USD,I,11/02/2009,21:00,1.4764,1.4775,1.4763,1.4773,296
EUR_USD,I,11/02/2009,22:00,1.4772,1.4774,1.4760,1.4772,219
EUR_USD,I,11/02/2009,23:00,1.4773,1.4778,1.4769,1.4772,180
EUR_USD,I,11/03/2009,12:00,1.4635,1.4659,1.4631,1.4654,1207
EUR_USD,I,11/03/2009,13:00,1.4655,1.4668,1.4643,1.4651,1219
EUR_USD,I,11/03/2009,14:00,1.4652,1.4662,1.4633,1.4657,1690
EUR_USD,I,11/03/2009,15:00,1.4658,1.4696,1.4642,1.4676,2365
EUR_USD,I,11/03/2009,16:00,1.4675,1.4681,1.4631,1.4655,1592
EUR_USD,I,11/03/2009,17:00,1.4656,1.4695,1.4645,1.4685,1104
EUR_USD,I,11/03/2009,18:00,1.4686,1.4710,1.4678,1.4709,1006
EUR_USD,I,11/03/2009,19:00,1.4710,1.4732,1.4702,1.4702,988
EUR_USD,I,11/03/2009,20:00,1.4701,1.4722,1.4700,1.4711,723
EUR_USD,I,11/03/2009,21:00,1.4710,1.4732,1.4710,1.4721,371
EUR_USD,I,11/03/2009,22:00,1.4721,1.4727,1.4706,1.4716,395
EUR_USD,I,11/03/2009,23:00,1.4717,1.4732,1.4715,1.4731,227


I really appreciate your help and support

Sincerely,

Carlos S
EUR_USD-1H.txt (92.4 KB) 
# 2  
Old 11-04-2009
It looks homework, I saw similar csv before.

So tell us, what did you try already?
# 3  
Old 11-05-2009
Code:
while(<DATA>){
	my @tmp = split(",",$_);
	my $key = $tmp[0].",D,".$tmp[2];
	$hash{$key}->{open}=$tmp[4] if $hash{$key}->{open}=="";
	$hash{$key}->{low}=$tmp[6] if $hash{$key}->{low}=="";
	$hash{$key}->{high}=($hash{$key}->{high}<$tmp[5])?$tmp[5]:$hash{$key}->{high};
	$hash{$key}->{low}=($hash{$key}->{low}>$tmp[6])?$tmp[6]:$hash{$key}->{low};
	$hash{$key}->{close}=$tmp[7];
	$hash{$key}->{vol}+=$tmp[8];
}
foreach my $key(keys %hash){
	print $key,",",$hash{$key}->{open},",",$hash{$key}->{high},",",$hash{$key}->{low},",",$hash{$key}->{close},",",$hash{$key}->{vol},"\n";
}
__DATA__
EUR_USD,I,11/02/2009,12:00,1.4783,1.4784,1.4766,1.4774,824
EUR_USD,I,11/02/2009,13:00,1.4773,1.4775,1.4746,1.4754,854
EUR_USD,I,11/02/2009,14:00,1.4753,1.4779,1.4739,1.4777,1320
EUR_USD,I,11/02/2009,15:00,1.4778,1.4844,1.4778,1.4817,2389
EUR_USD,I,11/02/2009,16:00,1.4816,1.4834,1.4813,1.4827,1302
EUR_USD,I,11/02/2009,17:00,1.4828,1.4831,1.4761,1.4762,1053
EUR_USD,I,11/02/2009,18:00,1.4761,1.4778,1.4731,1.4733,1148
EUR_USD,I,11/02/2009,19:00,1.4734,1.4771,1.4727,1.4751,1112
EUR_USD,I,11/02/2009,20:00,1.4752,1.4770,1.4747,1.4765,936
EUR_USD,I,11/02/2009,21:00,1.4764,1.4775,1.4763,1.4773,296
EUR_USD,I,11/02/2009,22:00,1.4772,1.4774,1.4760,1.4772,219
EUR_USD,I,11/02/2009,23:00,1.4773,1.4778,1.4769,1.4772,180
EUR_USD,I,11/03/2009,12:00,1.4635,1.4659,1.4631,1.4654,1207
EUR_USD,I,11/03/2009,13:00,1.4655,1.4668,1.4643,1.4651,1219
EUR_USD,I,11/03/2009,14:00,1.4652,1.4662,1.4633,1.4657,1690
EUR_USD,I,11/03/2009,15:00,1.4658,1.4696,1.4642,1.4676,2365
EUR_USD,I,11/03/2009,16:00,1.4675,1.4681,1.4631,1.4655,1592
EUR_USD,I,11/03/2009,17:00,1.4656,1.4695,1.4645,1.4685,1104
EUR_USD,I,11/03/2009,18:00,1.4686,1.4710,1.4678,1.4709,1006
EUR_USD,I,11/03/2009,19:00,1.4710,1.4732,1.4702,1.4702,988
EUR_USD,I,11/03/2009,20:00,1.4701,1.4722,1.4700,1.4711,723
EUR_USD,I,11/03/2009,21:00,1.4710,1.4732,1.4710,1.4721,371
EUR_USD,I,11/03/2009,22:00,1.4721,1.4727,1.4706,1.4716,395
EUR_USD,I,11/03/2009,23:00,1.4717,1.4732,1.4715,1.4731,227

# 4  
Old 11-05-2009
Hello rdcwayx,

The final purpose of transform the attached file is to use this data in order to take decisions of trade currencies. I want to convert or condense all rows of each day of the file in one row per day. My intention is to use Ubuntu and a shell script for this case.

Thanks for your prompt response.

Carlos
# 5  
Old 11-05-2009
Code:
$ awk -F, '{
{date[$3]=$3}
{if (open[$3]=="") open[$3]=$5}
{if (high[$3]<$6) high[$3]=$6}
{if (low[$3]=="") low[$3]=$7}
{if (low[$3]>$7) low[$3]=$7}
{closep[$3]=$8}
{vol[$3]+=$9}
}
END {for (i in open) {print "EUR_USD,D",date[i],open[i],high[i],low[i],closep[i],vol[i]}}
' OFS="," EUR_USD-1H.txt |sort -t, -k3r |head

EUR_USD,D,<date>,<open>,<high>,<low>,<close>,0
EUR_USD,D,11/04/2009,1.4764,1.4805,1.4740,1.4796,3046
EUR_USD,D,11/03/2009,1.4635,1.4732,1.4631,1.4731,12887
EUR_USD,D,11/02/2009,1.4783,1.4844,1.4727,1.4772,11633
EUR_USD,D,11/01/2009,1.4728,1.4741,1.4703,1.4708,1297
EUR_USD,D,10/30/2009,1.4804,1.4820,1.4711,1.4715,10863
EUR_USD,D,10/29/2009,1.4728,1.4858,1.4718,1.4838,10865
EUR_USD,D,10/28/2009,1.4779,1.4815,1.4690,1.4716,12978
EUR_USD,D,10/27/2009,1.4876,1.4887,1.4770,1.4821,12250
EUR_USD,D,10/26/2009,1.5024,1.5035,1.4844,1.4859,10336

Last edited by rdcwayx; 11-05-2009 at 09:38 PM..
# 6  
Old 11-06-2009
Hello rdcwayx and Everybody.

I tried the awk script with the ascii attached file and working fine but the output is in incorrect date order.

Could you please help me for solve that the final output will in ascending date order like the original input file

Thanks in advance

Carlos

---------- Post updated at 04:17 PM ---------- Previous update was at 04:17 PM ----------

Hello rdcwayx and Everybody.

I tried the awk script with the ascii attached file and working fine but the output is in incorrect date order.

Could you please help me for solve that the final output will in ascending date order like the original input file

Thanks in advance

Carlos
# 7  
Old 11-07-2009
change above command from:
Code:
sort -t, -k3r

to
Code:
sort -t, -k3

And no need to use head command.
Login or Register to Ask a Question

Previous Thread | Next Thread

6 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compact script with array - Good idea?

Hi, I have a shell script where a lot of the code is repeated. I wanted to make the code much more compact so I spoke to a guy and he suggested using arrays, like follows: #!/bin/bash readonly -a nginx=('nginx' '--prefix=/opt' '-j 4' 'http://nginx.org/download/nginx-1.2.2.tar.gz' )... (2 Replies)
Discussion started by: Spadez
2 Replies

2. Ubuntu

Format a compact flash card in Ubuntu

I need some assistance formatting a compact flash card in Ubtunu. I connect up the CF card through a USB reader. Ubuntu recognizes the reader usb device, but does not "mount" the CF card as a device. The CF card was formatted in FAT32 format. Any help would be greatly appreciated.... (3 Replies)
Discussion started by: genesis211
3 Replies

3. Shell Programming and Scripting

Compact 3-D output from a big input like:

Hi Experts!!!! Tried a lot but in vein,no success, please help to achieve this LOGIC behind is first field is reffrance row field, the second field shoud be made titles of coulmn and the the third field is made as matrix value. And if they dont match the matrix value should be 0. (Unix (Shell,... (1 Reply)
Discussion started by: ashis.tewari
1 Replies

4. UNIX for Dummies Questions & Answers

User information script

Hi Guys, I just started learning unix and was wondering if anyone can assist me with my User info script.The script file lets the user input their personal information (Their name, address, phone number etc...). The script will then ask the user to input the info again if the input is incorrect... (2 Replies)
Discussion started by: rc1138
2 Replies

5. Linux

How to Burn .gp Oracle 9.2.0.4.0 to Compact Disc Media on Windows Box

Hi All, I Downloaded Oracle 9.2.0.4.0 for Linux AS 4 and the file format is Directions to extract the files 1. Run "gunzip <filename>" on all the files. Eg. ship_9204_linux_disk1.cpio.gz 2. Extract the cpio archives with the command "cpio -idmv < <filename>" Eg. cpio -idmv... (0 Replies)
Discussion started by: prakashpichika
0 Replies

6. Windows & DOS: Issues & Discussions

formatting a Compact Flash in DOS format

:confused: I tryied to use the mount Command (and the msdos.utils) in Darwin 1.4 (OS X) to format a Compact Flash in DOS Format (I had already this native format but I changed it with Disk Utility in MacOS standard and the PC Card slot of a Windows 98-Laptop can't nore read the card) I want to... (1 Reply)
Discussion started by: dreamsurfer
1 Replies
Login or Register to Ask a Question