I need to write a programme in MATLAB to convert my daily rainfall data into average rainfal per month. I have thousands of entries so doing this manually in Excel is a no go. Does anybody know how I can go about this? I am stumped!
programme to...
- Thread starter SoSolid
- Start date
Can't help you to do it in MATLAB, but it would be easy to acheive using MS Access and the original data (I'm assuming it's in excel) as the source.
If that's any use, let me know and I'll post some examples of how to do it.
If that's any use, let me know and I'll post some examples of how to do it.
ok great I could use access.
Ok, assuming the data is in a format something like.
Location - Date - Rainfall
First thing is to link the data as a table. Go to tables, right click, link table and select the data source.
Then go into query builder and create a new query with the following fields.
Location - =month(date) - rainfall
This will give you the location, the month and the rainfall for each day.
Then create a second query (you can use the query wizard for the one) and create a summary query based on the query you have just made. Choose average for rainfall in summary options, and then group by location and month.
Run that query, and you'll produce a list of average monthly data for each location.
If that's ok, let me know, if you don't understand, let me know and I'll try and break it down simpler, or let me know the data structure and I'll see what I can put together for you.
Location - Date - Rainfall
First thing is to link the data as a table. Go to tables, right click, link table and select the data source.
Then go into query builder and create a new query with the following fields.
Location - =month(date) - rainfall
This will give you the location, the month and the rainfall for each day.
Then create a second query (you can use the query wizard for the one) and create a summary query based on the query you have just made. Choose average for rainfall in summary options, and then group by location and month.
Run that query, and you'll produce a list of average monthly data for each location.
If that's ok, let me know, if you don't understand, let me know and I'll try and break it down simpler, or let me know the data structure and I'll see what I can put together for you.
Thanks a lot. My data is in this format: date (in column 1) and rainfall (in column 2). Would this alter it much?
No, just ignore the location part and do the rest of it the same.
Perhaps not the most elegant solution, but it will work
at me If you could send me the SQL that would be great.
Code:
SELECT DISTINCTROW Format$([FR94300].[F1],'mmmm yyyy') AS [F1 By Month], Avg(FR94300.F2) AS [Avg Of F2]
FROM FR94300
GROUP BY Format$([FR94300].[F1],'mmmm yyyy'), Year([FR94300].[F1])*12+DatePart('m',[FR94300].[F1])-1;Is the sql from the query, fr94300 is the spreadsheet linked as a table
Soldato
- Joined
- 15 Nov 2008
- Posts
- 5,060
- Location
- In the ether
Rather than messing about with Access you can do this really simply in Perl.
(putting this here just in case anyone reads the thread in the future).
So say you have your data as:
DD/MM/YYYY, Daily Rain Fall
open(my $datafile, "my matlab file.csv") or die "Can't find my data
";
my $running_month;
my @seen_months;
my $running_count;
my $total;
my @monthy_rainfall;
while($current_line = <$datafile>){
# This is just saying skip the line if it starts with a Date I.e. the column names line
next if $current_line =~ /^Date/;
($date,$rain) = split /,/,$current_line;
$total = $total+$rain;
($day,$month,$year) = split /\//,$date;
if $running_month eq "" $running_month = $month;
if($running_month ne "month"){
$running_month = $month;
}
$running_count{$month} = $running_count{$month} + $rain;
if($true = grep /$month/ @seen_months){
}else{
@seen_months = (@seen_months,$month);
}
}
foreach $bitty (@seen_months){
$avg = $running_count{$bitty}/$total;
print "$bitty \t $avg \n";
}
PERL IS FUN!
(putting this here just in case anyone reads the thread in the future).
So say you have your data as:
DD/MM/YYYY, Daily Rain Fall
open(my $datafile, "my matlab file.csv") or die "Can't find my data
";my $running_month;
my @seen_months;
my $running_count;
my $total;
my @monthy_rainfall;
while($current_line = <$datafile>){
# This is just saying skip the line if it starts with a Date I.e. the column names line
next if $current_line =~ /^Date/;
($date,$rain) = split /,/,$current_line;
$total = $total+$rain;
($day,$month,$year) = split /\//,$date;
if $running_month eq "" $running_month = $month;
if($running_month ne "month"){
$running_month = $month;
}
$running_count{$month} = $running_count{$month} + $rain;
if($true = grep /$month/ @seen_months){
}else{
@seen_months = (@seen_months,$month);
}
}
foreach $bitty (@seen_months){
$avg = $running_count{$bitty}/$total;
print "$bitty \t $avg \n";
}
PERL IS FUN!