Message Boards Message Boards


Importing tipping bucket raingage data

Posted 6 years ago
7 Replies
1 Total Likes

Hi There

I am a very new Mathematica user and have only just been making my way through the Hands-On book.

I am a hobbyist and I have a home weather system which uses a tipping bucket to measure rainfall. Basically, the bucket tips whenever it collects 0.01" of rain, so the time series is irregular and not a constant interval, i.e. the bucket tip times are irregular but the volume is always the same. Right now the data is in Excel.

Can someone guide me to how I import the irregular date and time series of constant volumes from excel and work with it, i.e. make a plot. Also, is there a way to make it a regular series of data by interpolating to say, make it a 5 or 10 minute interval and have Mathematica calculate the volume over that regular time series. I would like to calculate rainfall intensities for various periods and also have Mathematica look at the time series with a forward moving average to pick out the maximum intensity for various time periods by going stepwise through the data series to find which 5 minute interval (for example) had the highest intensity for a particular storm event.

Any advice on where to begin?

Thanks for any thoughts. Roger

7 Replies

Hi Roger,

that shouldn't be too difficult. There is even built in functionality to make time series more regular. But importing and cleaning the data depends a little on the exact format it is in. Could you post a sample file, i.e. attach the Excel file to your post?



Yes, thank you. Attached is an Excel file with bucket tips since 1993. Looking through the data again, I see that the standard bucket tip volume is 0.04 inches (not 0.01) and there are a number of tips amounts that are greater, some 0.08 and a few 0.12 and one 0.15 so the volume is not always the same (must be periods of very intense rainfall). And of course, there are mostly report-out values of "0" as the raingage reports out at specific intervals whether or not it is raining.

This is pretty typical of the output. There is two date/time columns but I can use either one, although the first column of reading time is probably the better one to use (they are mostly the same).

Thanks for any help, looking forward to learning how its done.



Dear Roger,

ok. Thank you. I will look into that this night. Reading and plotting the data is quite straight forward:

data = Import["/Users/thiel/Desktop/Novato_ALERT_tips_1993_2012.xlsx"];

where the directory/folder would have to be adjusted. A DatelistPlot works with the data quite directly:

DateListPlot[data[[1, 3 ;;, {1, 3}]], PlotRange -> All]

enter image description here



Great, thanks. Quick simple question. What do the 1,3 and the two semicolons in a row mean? i.e. the "1,3;;,{1,3}" part of your answer. I was looking at the reference guide but couldnt quite find it. Is it part of the Import function or DateListPlot?

Thanks for your help, Roger

Hi Roger,

no that's to part of DateListPlot. Your data after import has these dimensions:

data // Dimensions
(*{1, 21579, 5}*)

That means that it is a list of 21579 lists of five entries. The outer list (corresponding to the 1) comes from the excel spreadsheet. Sometimes these things have multiple pages, but yours doesn't. If I now print out a couple of days it looks like that:

data[[1, 1 ;; 10]] // TableForm

enter image description here

As you see the first two rows are just headers and I don't want to plot them. so I only choose the one from 3 to the end. So if I write:


that mans take the fist (and only) page of the spreadsheet. Then use rows 3 to the end. If I knew that there are 21579 lines I could also write


but you see that the the first choice is somewhat shorter and requires less knowledge. It just means take rows 3 to the end. The table also shows that per row there are five entries. I was interested in the first and the third. That is where the {1,3} comes from.

Ok. Once we have done this we can do:

ts = TimeSeries[data[[1, 3 ;;, {1, 3}]]];
rainperday = MovingMap[Total, ts, Quantity[1, "Days"]];

to get

enter image description here

It is easy to see that Mathematica cuts off at about 3.5 so that most of the time series can easily be seen. But you can force it to plot everything:

DateListPlot[rainperday, PlotRange -> All]

enter image description here

There is obviously a very large peak at the beginning of 2008 (3rd January?). Given your geolocation, I assume that this is the January 2008 North American storm complex, but that's only a guess. An alternative explanation would be that somebody sprayed it with a watering hose.

It is a beautiful dataset. It could be a challenge for this community to find out where your rain gauge probably was - that can of course only be done with quite some error, but it would be fun...



Thanks Marco! You are a great explainer. And yes, the January 2008 storm may have been big but not that big, so its either a garden hose or someone peeing into my rain gage as a practical joke. Either way, I wouldn't have seen it without this plot. And you have given me two new functions to look up "TimeSeries" and "MovingMap" so I may have questions on what these functions do.

Another question, if I had another rain gage in the same excel file as another column, how do I get Mathematica to recognize there are two different time series y variables with the same date/time stamp? I tried with another data set using DataListPlot and it didnt work, it didnt recognize another column as a new time series with the same date/time stamp so I got an error. When I deleted the second column it worked

thanks, Roger

Hi Roger,

if you have a file with more time series from different rain gauges, you might want to use the Dimensions function that I used above. There should be a change in one of the dimensions or an additional entry. That would tell you how to extract the data you want.

You can post the data with two entries and I can give it a shot.



Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
or Discard

Group Abstract Group Abstract