Weekly Challenges
Solve the challenge, share your solution and summit the ranks of our Community!Also available in | Français | Português | Español | 日本語
IDEAS WANTED
Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA
Cheated a little bit... Changed the URL to this one which only has the last 7 days of data
Just made it way quicker to download a much smaller data set that didn't need half the filtering.
A challenging and very interesting exercise - learned a lot, especially the value of caching data - yikes!
Spoiler
Process:
- Extract sensor IDs (first split columns on "(" then removed ")" - there are probably better ways)
- Add Record ID
- Count number of sensors
- Generate rows for each sensor
- Append with URL for data
- Replace "303" in URL with each sensor ID
- Download data for each sensor
- Split to rows (on "\n")
- Cache data - the total was approaching 1GB!
- Filter out data beginning with "#"
- Filter data to capture most recent 7 days (I used 2018-01-23 so I could compare my results to the "real answer")
-- Something went wrong with the filters but I still got there - no time to debug as the overall solution still worked - and I should be working right now too!)
- Text to columns on ","
- Flag the most recent 7 days of data (since my filter failed somewhere)
- Multi row tool to add 7 days of snow accumulation
- Filter for 2018-01-23
- Sort by Snow accumulation (descending)
- Select first record
- Add points on Lat and Long
- Append with Ski Area data
- Calculate distance from sensor to Ski Area
- Sort by distance (ascending)
- Select first record
- Sensor 840 (Upper San Juan) had 1.4 inches of snow accumulation and was closest to Wolf Creek
Not quite the same (1.4 vs 1.3) as official answer, but I think I got the process reasonably there
- Extract sensor IDs (first split columns on "(" then removed ")" - there are probably better ways)
- Add Record ID
- Count number of sensors
- Generate rows for each sensor
- Append with URL for data
- Replace "303" in URL with each sensor ID
- Download data for each sensor
- Split to rows (on "\n")
- Cache data - the total was approaching 1GB!
- Filter out data beginning with "#"
- Filter data to capture most recent 7 days (I used 2018-01-23 so I could compare my results to the "real answer")
-- Something went wrong with the filters but I still got there - no time to debug as the overall solution still worked - and I should be working right now too!)
- Text to columns on ","
- Flag the most recent 7 days of data (since my filter failed somewhere)
- Multi row tool to add 7 days of snow accumulation
- Filter for 2018-01-23
- Sort by Snow accumulation (descending)
- Select first record
- Add points on Lat and Long
- Append with Ski Area data
- Calculate distance from sensor to Ski Area
- Sort by distance (ascending)
- Select first record
- Sensor 840 (Upper San Juan) had 1.4 inches of snow accumulation and was closest to Wolf Creek
Not quite the same (1.4 vs 1.3) as official answer, but I think I got the process reasonably there
DURANGO MOUNTAIN RESORT would be the place to be on 11/24/2019 if there was any snow happening this early in the season.
