How Do We Get This Data?

[alyoshenka]

In light of the below information, figure out how to get the "Desired Data Output" dataframe

What Our Data Looks Like At Present:

Data Inputs:

• Historical Danger Rating

Date	Area	Danger Rating
Apr 15 2023	Stevens Pass	LOW

• Historical weather (from NWAC; there's gotta be a better way to get it)

Date PDT	Temp F	RH %	Min mph	Spd mph	Gust mph	Dir deg
Apr 24 6:00	23	98	7	18	31	W

• Historical SNOTEL (can add more/different metrics potentially?)

Date	Station Id	Station Name	Snow Water Equivalent (in)	Snow Depth (in)	Precipitation Accumulation (in)	Air Temperature Observed (degF)	Elevation (ft)	Latitude	Longitude
2022-11-01 00:00	702	Potato Hill	1.6	4	3.9	34.2	4510	46.34963	-121.51435

Date	Station Id	Station Name	Snow Water Equivalent (in)	Snow Depth (in)	Precipitation Accumulation (in)	Air Temperature Observed (degF)	Elevation (ft)	Latitude	Longitude
2022-11-01 00:00	804	Surprise Lakes	0.0	0	6.5	34.5	4290	46.09497	-121.76345

etc,etc for all desired snotel sites

Combining SNOTEL Data

For all the snotel sites we use to calculate our metrics, we want to combine them with both mean and median aggregation. So, for every individual hourly (or daily) metric, we want the mean/median of all values for all sites.

Desired Data Output:

Date	Max Air Temp 24hr	Max Air Temp 72hr	Total Snowfall 24hr	Total Snowfall 72hr	Max Windspeed 24hr	Weighted Snowfall 96hr	Min Temp Diff 48hr	Was Heavy Snowfall 24hr	Was High Winds 24hr	Sum Max Temp 72hr	Delta SWE 24hr	Yesterday Danger	Danger Rating
1/1/2000	32.0	33.0	13	23	27	40	-10	True/1	False/0	63	0.5	MODERATE/2	LOW/1

(These are absolutely random values)

Data Points and Why They Are Necessary:

Nov 1 2022 - Apr 30 2023

1. Max Air Temp 24hr: Maximum air temperature in the past 24 hours
   • Really warm (significantly above freezing) causes wet slides, really really cold causes other problems.
2. Max Air Temp 72hr: Maximum air temperature in the past 72 hours
   • This is similar to 1, but shows warm temperatures over a longer time frame.
3. Yesterday Danger: Previous day's danger rating
   • If the previous day had a high danger rating, the current day will likely have a high(ish) danger rating.
     • Ideally we wouldn't use this, because it would be cool to generate predictions based only on weather, but we should make our prediction good before optimizing completely.
4. Total Snowfall 24hr: Total snowfall in the past 24 hours
   • Lots of new snow is a red flag.
5. Total Snowfall 72hr: Total snowfall in the past 72 hours
   • Prolonged high snowfall adds a lot of stress to the snow. Imagine putting a brick onto a pile of packing peanuts that's sitting on an incline: the heavier the brick the more peanuts it will displace when the friction gives out and the pile (of peanuts/snow) begins to slide.
6. Max Windspeed 24hr: Maximum wind speed in the past 24 hours
   • High wind creates "wind slabs": blocks of cohesive snow that stick together. These blocks of snow sit on top of older snow, and as in the above example, they are another "brick on top of packing peanuts".
7. Was Heavy Snowfall 24hr: Threshold snowfall: was there > 7cm (convert to in) in the past 24 hours
   • This shifts us from linear/numerical to categorical data. We might not be able to use both, so this is a less important metric. Basically, there are "thresholds" that if they are passed it is an immediate red flag. This is one of them.
   • We should try with both a 12 in threshold, which is the standard for avalanche forecasting, and a 7 cm threshold, which is the value that one of the DS projects identified as being the best measure.
8. Was High Winds 24hr: Did the wind speed exceed 20 mph in the past day?
   • This is another boolean value like the one above that we may or may not be able to use.
   • As like the max windspeed metric, high winds can be a bad sign
   • It would also be cool to try this with:
     • Use 25 mph instead
     • Use maximum gust instead of speed
9. Delta SWE 24hr: Water content of new snow (use SWE)
   • How much did the SWE change in the past 24 hours?
   • Snow Water Equivalent (SWE) measures how much water actually fell from the sky. This is a measure of the density of new snow. 10" of super light snow could weigh the same as 2" of super dense snow, so this might actually be a better metric to use than actual snowfall, which is just the depth of snow.
10. Sum Max Temp 72hr: Sum of maximum temperature of the past 3 days
    • indicative of a warm spell
11. Min Temp Diff 48hr: Difference in minimum temperature from previous day
    • This can tell us if there are large temperature swings (rapid warming leads to rapid thawing which creates rivers under the snow that destabilize the snowpack, rapid cooling does some bad stuff too but I can't remember what exactly).
    • Today's temperature - yesterday's temperature
12. Weighted Snowfall 96hr: Weighted sum of snow for the past 4 days
    • 1.0 yesterday snowfall + 0.75 two days ago snowfall + 0.5 three days ago snowfall + 0.25 four days ago snowfall
    • This is similar to total snowfall, but gives a better view of the past few days, with more recent snowfall being more "important" than snowfall 4 days ago.

How Do We Get These Data Points:

• mean/median: Can we do this with SNOTEL Report Generator? Or do we have to do ourselves?

It seems that Snotel doesn't like to do both Delta from Previous and Mean/Median, so we have to pick one and deal with the other. I think it's easier to generate reports for every site and combine them ourselves than to generate the mean/median and calculate deltas ourselves.

1. air temperature maximum Value None Daily
2. Same as above, then calculate max of 3 days ourselves
3. Probably have to calculate ourselves (shouldn't be terribly hard)
4. snow depth Delta from Previous Value None Daily
5. Same as above, then calculate total of 3 days ourselves
6. Have to calculate ourselves
7. Use 4 to calculate
8. Ourselves, probably
9. snow water equivalent Delta from Previous Value None Daily
10. Use 1 to calculate
11. air temperature minimum Delta from Previous Value None Daily
12. Use 4 to calculate

[alyoshenka]

NWAC Weather Data Portal

[alyoshenka]

putting in review so i can refer back to it