Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

View previous topic View next topic Go down

Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by Math23x7 on Tue Feb 10, 2015 8:52 pm

Okay guys, this has been something I have been working on for years, but never thought about sharing until recently.  

I imported the data from NWS Upton's list of NYC snowfall per season (http://www.erh.noaa.gov/okx/climate/records/monthseasonsnowfall.html) onto Excel and computed the mean and standard deviation of the list.  
In the "Seasons" tab, The "A" column represents the number season NYC measured snow (i.e. 2013-14 was the 145th full season of snow measurements), the "B" column gives the snow season, and the "C" column gives the snow total.  The "D" column gave each season a "status" with with terms that NOAA does with their monthly and annual temperature and precipitation maps of how far from normal the seasons were. In the "E" and "F" columns, I have the Rankings in both Snowiest and Least Snowiest, respectively, on record.  The "G" column has the Cumulative Seasonal Average and the "H" column has the change in this average (note how cell H2 has a zero next to it).  The "I" column has the Departure from the 145-year average and the "J" column has the Standardized Departure from the 145-year average.  Finally, The "K" column has the departure from normal from the 1981-2010 average (25.8").  

In the "Seasons by Rank" tab, I sorted the seasons based on value.  You can play around with this one if you want.

The "Summary" Tab includes the value of the Mean and Standard Deviation of the list.  One thing to keep in mind is that I had to choose whether to use =stdev.p (population) or =stdev.s (sample).  Since this does not include the snowfall data from January-April 1869 and since it there are no official measurements before then, I chose to use the Sample Standard Deviation.  If you think it should be Population Standard Deviation, I will make the change as necessary.

This does not include the 2014-15 season as I want to wait until it's over to put it in.

I would have shown this at the get together a month ago, but never got around to it.

Hope you enjoy this!
Attachments
NYC seasonal snow statistics.xlsx You don't have permission to download attachments.(53 Kb) Downloaded 77 times


Last edited by Math23x7 on Sat Apr 01, 2017 5:57 pm; edited 2 times in total

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 1684
Join date : 2013-01-08

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by sroc4 on Tue Feb 10, 2015 9:01 pm

Wow Mike this is amazing. Great job!

_________________
"Go hug a human, not a weather model"
WINTER 2012/2013 SNOW TOTALS 43.65"
WINTER 2013/2014 SNOW TOTALS 64.85"
WINTER 2014/2015 SNOW TOTALS 71.20"
WINTER 2015/2016 SNOW TOTALS  35.00"
WINTER 2016/2017 SNOW TOTALS 42.25"
Dec 11th = coating
Dec 17th = 4.2
Jan 5th-6th = 3"
Jan 7th = 10.4"

Jan 14th = 1.75"
Jan 31st = 2.5"
Feb 9th = 12"
March 10th = 4.8"
March 14th = 3.5"
avatar
sroc4
Admin
Admin

Posts : 4787
Join date : 2013-01-07
Location : Wading River, LI

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by Frank_Wx on Tue Feb 10, 2015 9:09 pm

Mike I can't wait to go home and download this onto my comp. Seems amazing. Thanks for this

_________________
Snow Accumulation Archive - Cranford, NJ
2013-2014: 62.1"
2014-2015: 56.0"
2015-2016: 34.0"
2016-2017: 31.5"
2017-2018:

________________________________________________________________________________________________________
CLICK HERE to view NJ Strong Snowstorm Classifications
avatar
Frank_Wx
Godzilla Seeker
Godzilla Seeker

Posts : 17017
Join date : 2013-01-05
Age : 25
Location : Cranford, NJ

View user profile http://njstrongweather.blogspot.com/

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by billg315 on Tue Feb 10, 2015 9:09 pm

Well done. And a little nostalgic. Looking through the chart reminded me of the snow drought I experienced during high school (I think we had one snow day in four years) when some of my classmates thought the days of snowy winters had passed us by (a bit of early global warming hysteria I think). Only to be followed by couple momentous snowy winters in college.
avatar
billg315
Senior Enthusiast & Loyalist
Senior Enthusiast & Loyalist

Posts : 1229
Join date : 2015-01-24
Age : 43
Location : Somerville, NJ

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by RJB8525 on Tue Feb 10, 2015 9:23 pm

Wow that's really awesome
avatar
RJB8525
Pro Enthusiast
Pro Enthusiast

Posts : 1655
Join date : 2013-02-06
Age : 31
Location : Hackettstown, NJ

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by Radz on Tue Feb 10, 2015 9:35 pm

Really great job! Thanks for sharing!
avatar
Radz
Pro Enthusiast
Pro Enthusiast

Posts : 577
Join date : 2013-01-12
Location : Cortlandt Manor NY

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by docstox12 on Wed Feb 11, 2015 9:49 am

OMG, Mike, an outstanding job! We are always in need of this chart during our winter comparisons.

CP, do you see what Mike has done here? Incredible work and patience.

Thanks, Mike!!!!
avatar
docstox12
Wx Statistician Guru
Wx Statistician Guru

Posts : 5327
Join date : 2013-01-07
Age : 66
Location : Monroe NY

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by CPcantmeasuresnow on Wed Feb 11, 2015 9:56 am

Have not had a chance yet Doc to much travel and work lately. I'm hoping to sit and take a look this weekend.
avatar
CPcantmeasuresnow
Wx Statistician Guru
Wx Statistician Guru

Posts : 4806
Join date : 2013-01-07
Age : 55
Location : OTI - Sector 4

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by Dtone on Thu Feb 12, 2015 3:55 am

billg315 wrote:Well done. And a little nostalgic. Looking through the chart reminded me of the snow drought I experienced during high school (I think we had one snow day in four years) when some of my classmates thought the days of snowy winters had passed us by (a bit of early global warming hysteria I think). Only to be followed by couple momentous snowy winters in college.

True, besides 1996 I can't remember much snowfall 6+" before college days. Snow was always N&W. 1"-3" and 2" - 4" events followed by heavy rain seem to be the standard. All snow events in NYC didn't seem nearly as common as now. Blizzard of 96 was the only snow day of my life in NY.

We averaged 30+" of snow up until the 1950s that must of been nice. Seem like we're going back toward that era during the 2000s but a dud like 2011-12 and 01-02 come along to put you in check. Still the 2000s have been amazing for snow lovers overall.

Dtone
Wx Statistician Guru
Wx Statistician Guru

Posts : 1517
Join date : 2013-08-26
Location : Bronx, NY

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by docstox12 on Thu Feb 12, 2015 4:23 am

Well told!

After going through the 70's, 80's, and 90's ( save for 93-94 and 95-96) the 2000's have been great snow years overall.Living in Fort Lee from 1956 to 1977, what you said is true. N and W of the City always had the higher totals.It was a routine statement for weather reports on the radio and tv back then...."and areas north and west of the City will have higher snow amounts."

The 60's were pretty good compared to the period from 1970-2000.There were 7 major snowstorms in that decade.
avatar
docstox12
Wx Statistician Guru
Wx Statistician Guru

Posts : 5327
Join date : 2013-01-07
Age : 66
Location : Monroe NY

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by CPcantmeasuresnow on Thu Feb 12, 2015 5:41 am

Mike:

Just had a chance to download and look through the spreadsheet. I have all of this data but never bothered to conditional color format it. It makes the deviations much more pronounced.

LOVE IT!!

Any chance you have the monthly temperature data for CPK? That's something I'd love to have but have never seen in an easily accessible format like a csv file, but what do you expect from an organization that can't seem to find a way to measure snow correctly.
avatar
CPcantmeasuresnow
Wx Statistician Guru
Wx Statistician Guru

Posts : 4806
Join date : 2013-01-07
Age : 55
Location : OTI - Sector 4

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by Math23x7 on Tue Apr 14, 2015 3:27 pm

For the last couple of months, I have considered adding additional components to the excel document including monthly snow, precipitation, and temperature data and summarizing it, but there have been issues with getting that across.  When I inputed the monthly snow totals, there were quite a number of months containing "T" (meaning trace of snow).  I originally replaced all of these with 0 to compute the averages but I want to acknowledge that it did snow in NYC during these months (especially May 1977).  And when I kept the "T"s, Excel did not count them as months.  So I did an Excel Macro which basically counts a "T" value as zero.  

But I also wanted to put in data for other locations including Boston, Philadelphia, and Washington DC and compare them month by month and year by year.  And I also wanted to add an interactive option which enables the user to input a year or month and get a summary of all the information from the time period of his or her choosing.  Here is a preview of what I have:







I included the updated NYC seasonal snow statistics and as no surprise, 2014-15 had "much above normal" snowfall and the cumulative seasonal average did go up:

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 1684
Join date : 2013-01-08

View user profile

Back to top Go down

Excel chart of NYC seasonal snow statistics (updated through the 2015-16 season)

Post by Math23x7 on Sun Apr 10, 2016 9:12 pm

I checked the 12Z EURO operational, and the 15-day EURO ensemble members and control.  None of them have any snow for NYC at any point during the run.  Yes one of them has flurries for Sussex County in NJ at day 11, big deal!  But I think it is safe to say that we are officially done with snow in NYC until next fall.

So with that in mind, I have updated my Excel snow chart.  CPK ended up with 32.1" of snow this season, slightly above normal, making it the 54th snowiest season on record and the 94th least snowiest season on record going back to 1869.  Of course, much of the snow ~83.5% of it actually, came from the 26.8" of snow from the January 22nd-23rd blizzard.  While some are skeptical at the measurements by the Central Park conservancy, I am still taking it as is since Upton still has the total at 32.1" (whereas last year they made some corrections in late-March.)  Another thing to note is that the cumulative snowfall average is creeping upwards.  14 years ago, it was at 27.98."  Now it is at 28.79."

As far as everything I mentioned I would do in the post above I made last April, I have been quite busy with other tasks so I have not really gotten around to it.  Maybe some day, I'll get to it though.  We'll see.  Also, I deleted the file attachment from last April since I was reaching my user storage cap.

And for all the newcomers on this forum, I hope you like this. Refer to my first post at the top from February 2015 for more specific details.

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 1684
Join date : 2013-01-08

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by docstox12 on Mon Apr 11, 2016 7:46 am

Great Job Mike!!!

At least you guys S and E had a normal snowfall year.Me and CP were way below normal and the guys above I-84 way way way below normal.
avatar
docstox12
Wx Statistician Guru
Wx Statistician Guru

Posts : 5327
Join date : 2013-01-07
Age : 66
Location : Monroe NY

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by CPcantmeasuresnow on Mon Apr 11, 2016 4:25 pm

It's hard to find good official snowfall measurements in the HV Doc. Although Poughkeepsie has been an official reporting station since 1949 it is just for temperature and precipitation not snowfall amounts. They did take measurements for many years but were so neglectful of it they stopped using them several years ago. They actually made the zookeeper look competent in comparison.

Anyway the point I was going to make is from anything I've read for the people between I 84 and Albany this is the most snowless winter they have ever experienced. In my 45 years or so of observing it's definitely in the top 5 probably top 3 for our section of Orange County.
avatar
CPcantmeasuresnow
Wx Statistician Guru
Wx Statistician Guru

Posts : 4806
Join date : 2013-01-07
Age : 55
Location : OTI - Sector 4

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by Math23x7 on Sat Apr 01, 2017 6:01 pm

Unless we have a surprise April snow event or if Upton does revise the snow totals from last January, the Cumulative seasonal snow total will stand.  CPK ended up with 30.2" on the season, tied with 1968-69 for the 58th snowiest on record and the 90th least snowiest on record.  I have put in this update as well as updated the 2015-16 snow total to 32.8" after the revision of the January 2016 blizzard from 26.8" to 27.5."

Here is the updated Excel file.  If updates are made, I will adjust accordingly:
Attachments
NYC seasonal snow statistics (updated through 2016-17).xlsx You don't have permission to download attachments.(54 Kb) Downloaded 1 times

Math23x7
Wx Statistician Guru
Wx Statistician Guru

Posts : 1684
Join date : 2013-01-08

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by rb924119 on Sat Apr 01, 2017 8:40 pm

Math23x7 wrote:For the last couple of months, I have considered adding additional components to the excel document including monthly snow, precipitation, and temperature data and summarizing it, but there have been issues with getting that across.  When I inputed the monthly snow totals, there were quite a number of months containing "T" (meaning trace of snow).  I originally replaced all of these with 0 to compute the averages but I want to acknowledge that it did snow in NYC during these months (especially May 1977).  And when I kept the "T"s, Excel did not count them as months.  So I did an Excel Macro which basically counts a "T" value as zero.  

But I also wanted to put in data for other locations including Red Sox Suck, Philadelphia, and Washington DC and compare them month by month and year by year.  And I also wanted to add an interactive option which enables the user to input a year or month and get a summary of all the information from the time period of his or her choosing.  Here is a preview of what I have:

I included the updated NYC seasonal snow statistics and as no surprise, 2014-15 had "much above normal" snowfall and the cumulative seasonal average did go up:

Mike, I haven't looked at this, and honestly never even knew you were working on this. However, I can just tell by your discussion it's awesome! That said, maybe I can help you with your "trace" problem, because I have run into similar issues with the project that I have been working on for the forum. As I am sure you know, a true "trace" is any liquid precipitation (rainfall) that measures less than 0.005". If it measures as 0.005" or greater, it counts as 0.01". For snowfall, the first measurement is 0.1", not 0.01" like it is for rainfall. What I did to correct this in my circumstance, which is only for snowfall, is to equate "T" to 0.01 via Find and Replace. That way, it populates the cell, but also gives me a way to still identify "trace" amounts while relatively accurately maintaining a running tally to the numbers. Really, you could use any value for "trace" amounts as long as it remains below the 0.005" rounding threshold for rain and 0.05" for snowfall. As long as you keep it consistent, you could just do a simple "Find and Replace" once any new data is imported to the spreadsheet, where you Find "T" and Replace with '0.01"' in that particular column. If you wanted to more easily distinguish "trace" amounts, you could always set up a new column where the cells of the new column contain formulas linked to the preceding column with your pesky "trace" amounts. Example: if("cellx"<0.05,"T",".") for "trace" snowfall (....<0.005....for rainfall). This way, only cells in the new column with immediately adjacent cells containing "trace" values would populate with "T" and the others would be left with a ".". Just some thoughts for ya. Hopefully they made sense and either helped or jogged your mind a bit to get you started on new ideas!! Let me know if you would like further clarification, and how it goes!!

rb924119
Meteorologist
Meteorologist

Posts : 3603
Join date : 2013-02-06
Age : 25
Location : Greentown, Pa/Fishkill, NY

View user profile

Back to top Go down

Re: Excel chart of NYC seasonal snow statistics (updated through the 2016-17 season)

Post by Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum