Thursday, April 19, 2012

Getting Historical Weather Data in R and SAP HANA

For many of my latest data blogs, I needed historical weather data to perform data mash-ups to pin-point the cause.  For example, for my continued exploration into the airlines/airports historical data using SAP HANA and R, I wanted to find out whether the weather was behind the extreme delay experienced out of a particular airport for a particular day/hour.  So I needed to mash-up the weather data with the airlines data for this analysis.

I looked around but could not find a better way to get the weather data.  So I turned to R.  Now, to get historical weather data, I am using Weather Underground's REST APIs and I put together a simple program in R to get the weather data in a data.frame.  This R module gets called from SAP HANA and it inserts a new table into HANA with the right weather information.  Once, I have the data in HANA, I performed mash-ups in HANA and off I go on my intellectual pursuit.

Weather Underground returns the data in both XML and JSON file formats.  The program logic is very simple, [once you have spent hours cracking it, the end product looks simple anyways :-)] and there are appropriate comments in the code below for self-learning.

I want to mention that you are not limited to just getting the historical view on weather data.  You can get the weather forecast for next 10 days, perform your analysis and predict future!

Make sure to register with Weather Underground (API documentation link), comply with their rules and get your own key to access their APIs.
getHistoricalWeather <- function(airport.code="SFO", date="Sys.Date()")
  base.url <- '{your key here}/'
  # compose final url
  final.url <- paste(base.url, 'history_', date, '/q/', airport.code, '.json', sep='')

  # reading in as raw lines from the web service
  conn <- url(final.url) <- readLines(conn, n=-1L, ok=TRUE)
 # Convert to a JSON <- fromJSON(paste(, collapse=""))

# get data for 10 days - restriction by Weather Underground for free usage
date.range <- seq.Date(from=as.Date('2006-1-01'), to=as.Date('2006-1-10'), by='1 day')

# Initialize a data frame
hdwd <- data.frame()

# loop over dates, and fetch weather data
for(i in seq_along(date.range)) { <- getHistoricalWeather('SFO', format(date.range[i], "%Y%m%d"))                 
      hdwd <- rbind(hdwd, ldply($history$dailysummary, 
          function(x) c('SJC', date.range[i], x$fog, x$rain, x$snow,  x$meantempi, x$meanvism, x$maxtempi, x$mintempi)))
colnames(hdwd) <- c("Airport", "Date", 'Fog', 'Rain', 'Snow','AvgTemp', 'AvgVisibility','MaxTemp','MinTemp')

# save to CSV
write.csv(hdwd, file=gzfile('SFC-Jan2006.csv.gz'), row.names=FALSE)

Results - 

Airport Date Fog Rain Snow AvgTemp AvgVisibility MaxTemp MinTemp
SFO 13149 0 1 0 55 14 62 47
SFO 13150 0 1 0 53 11 55 50
SFO 13151 0 1 0 51 14 56 46
SFO 13152 0 0 0 56 16 62 50
SFO 13153 0 0 0 54 14 60 48
SFO 13154 0 1 0 52 14 59 45
SFO 13155 0 1 0 56 14 61 50
SFO 13156 0 0 0 51 16 57 45
SFO 13157 0 0 0 49 16 56 41
SFO 13158 0 0 0 54 10 61 46

Happy Analyzing!


  1. Is this a world wide data base?
    How far back in time can you get data?

    1. I don't recall Gustaf, please check it out here:

  2. Cool thanks, now I just need to dig in to the API and find out what I can get.

    You should add that you need:


    for the script to work.

  3. R works well for REST calls! For production work, I would add 2 things to your code:
    options(timeout=600) so your code doesn't crash with slow network response times;
    wrap your call to readLines() in try() so you can trap issues such as missing date/airport code combinations (e.g., errors in their backend data service).

    The WeatherUnderground data is simplest for your particular use, as you can query it via airport codes. For other uses and larger hunks and further back in time, the NCDC historic weather products are mostly free for everyone now, and will become free by the end of Sept 2012 (see These include global data in ghcn, and hourly data as well as daily data with many more attributes. To grab these data, you need to look up the station index for your site, then build the simple urls, then grab the data with read.fwf() (in some cases looping over year-specific urls).

    A harder case is grabbing the NOAA 6-minute historic tidal height data, as it comes back as a web page embedded in (varying) html, so it requires try(Text<-readlines(Q)) and grep so distinguish data lines. Still, its wonderful that nearly all historic NOAA data is becoming freely available on the web, and R has the tools for easy assimilation.

  4. Solid points tomp. I did look at NCDC's website but since there were no API's offered and I am not a big fan of screen scrapping, I decided to stick with W-Underground. I am pretty sure that I will run into issues with free APIs.

    I put a sleep for 60 seconds before making another batch request and I can only make 500 calls per days. What I don't like about W-Underground's API is that I can only download data for a single day in one API call.

    Thanks for your insights!

  5. This post has a lot of importance to the people…I hope you can continue to inspire and post more of this…Thanks

  6. Data Visualization Software
    SQIAR ( is a leading Business Intelligence company and provides Tableau Software consultancy across United Kingdom and USA

  7. Hi, sir I have seen your info on sap.. Especially on sap hana. The info you provided is very useful for any interested on sap. I am very much satisfied with your info. I wish I can get furthered info from you.I am very glad to say thanks for this info.
    thank you very much sir.
    sap hana online training

  8. Well appreciated keep updates nice post see sap global consultants.

  9. Excellent piece of information on sap hana admin, I feel it is right place to get info on SAP HANA. This was really something, I had been looking for days. Too excited to view your other blogs on HANA. we provide SAP HANA ONLINE TRAINING.

  10. If you are using SAP HANA in conjunction with other SAP business applications, it is possible to integrate with SAP Solution Manager.

    The SAP HANA ADMIN Configuration Check Tool allows you to check the interoperability of SAP HANA with your existing enterprise storage in production environments.


  11. Your blog was excellent. Posted best topics for the readers help them to get to know things. Also helps the readers to choose the better career. SAP HANA ADMINISTRATION ONLINE TRAINING

  12. This SAP HANA ADMINISTRATION ONLINE TRAINING course not only gives an overview but provides you with a thorough in-depth knowledge about how the SAP HANA works. we also provide online training on SAP HANA ADMINISTRATION ONLINE TRAINING.


  13. Best Post about SAP Courses and the Training. Helps the readers to get better knowledge on Current SAP Technologies.

  14. Thanks so very much for taking your time to create this very useful and informative site. I have learned a lot from your site. Thanks!!

    Hadoop Course in Chennai

  15. there is another way of doing this here

  16. . Your blog was excellent. Posted best topics for the readers help them to get to know things. Also helps the readers to choose the better career reading this. sap bods online training

  17. Thank you very much for giving this information about msbi training in hyderabadplease keep updates on this and i searched many websites for this information.

  18. Hi ! my very first comment on your site. ,I have been reading your blog for a while and thought.

    statistical data analysis services

  19. You have really done some great work here and coming up with this kind statistical information. You may also like to think of considering creating blog for statistical analysis of financial data.

  20. Hey , I read your post this is really very informative, don't have any ideas about this but now know this. Thanks again
    Dash Inspectorate

  21. hey i need to create a weather forecast system which will take inputs like the past present and future temperature and i have to predict what will be the weather for the next one week..i have to use r to do so....can anybody help?

  22. Great post I would like to thank you for the efforts you have made in writing this interesting and knowledgeable article.

  23. how will get the the weather information for the future
    for example I need to know the weather for the coming one month.
    So what do I do?

  24. Great information, thansk for this.Its very informative and i am sure it will help many other people like the way it helps me. Thanks for the information.

    Bigdata Analytics

  25. Thanks for the use full topic. Very useful information.
    We IT hub Online Training are good in giving the sap hana Training

  26. can you help me to get data from same source but for one particular day and i want hourly data like 12:10 to 12:40 then 12:40 to 1:10..

  27. Pretty section of content. I simply stumbled upon your site and in accession capital to say that I get actually loved to account your blog posts.
    SQL Server Training in Chennai

  28. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

  29. thanks! this is super helpful. But I found that your date format pulled up the incorrect date in history. It seems to me, that to get the correct date, you need to enter the date format "yyyymmdd".

  30. Its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    interview preparation

  31. This concept is a good way to enhance the knowledge.thanks for sharing. please keep it up selenium Online Training Hyderabad

  32. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    Digital Marketing Training in Chennai

    Digital Marketing Training in Bangalore

    digital marketing training in tambaram

    digital marketing training in annanagar

  33. Thanks a lot for sharing us about this update. Hope you will not get tired on making posts as informative as this. 
    python training institute in chennai
    python training in Bangalore
    python training institute in chennai

  34. Great thoughts you got there, believe I may possibly try just some of it throughout my daily life.
    Blueprism training in marathahalli

    Blueprism training in btm

    Blueprism online training

  35. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.

    Data Science training in Chennai
    Data science training in bangalore
    Data science online training
    Data science training in pune

  36. Some us know all relating to the compelling medium you present powerful steps on this blog and therefore strongly encourage contribution from other ones on this subject while our own child is truly discovering a great deal. Have fun with the remaining portion of the year.
    java training in chennai | java training in bangalore

    java online training | java training in pune

  37. Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 
    angularjs Training in chennai
    angularjs-Training in pune

    angularjs-Training in chennai

    angularjs Training in chennai

    angularjs-Training in tambaram

  38. myTectra a global learning solutions company helps transform people and organization to gain real, lasting benefits.Join Today.Ready to Unlock your Learning Potential !Read More...

  39. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.

    AWS Training in Bangalore | Amazon Web Services Training in bangalore , india

    AWS Training in pune | Amazon Web Services Training in Pune, india

    AWS Training in Chennai|Amazon Web Services Training in Chennai,India

    aws online training and certification | amazon web services online training ,india

  40. Good job! Fruitful article. I like this very much. It is very useful for my research. It shows your interest in this topic very well. I hope you will post some more information about the software. Please keep sharing!!
    SEO Training in Chennai
    SEO Training
    SEO Course in Chennai
    Digital Marketing Course in Chennai
    Digital Marketing Course
    Digital Marketing Training in Chennai

  41. Greetings. I know this is somewhat off-topic, but I was wondering if you knew where I could get a captcha plugin for my comment form? I’m using the same blog platform like yours, and I’m having difficulty finding one? Thanks a lot.
    iosh course in chennai