1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Temperature circle as seen on LinkedIn?

Discussion in 'Discuss Data Visualizations and Charting' started by Diederik, Jul 15, 2018.

  1. Diederik

    Diederik New Member

    Messages:
    4
    today, when reading on my LinkIn feed I found an animation from a series of global temparature points per country since 1880 and I was stunned by the graphics. The animation was created by a Finnish meteo scientist, Annti Lipponen, and I felt immediately challenged to see if Ii could re-create it in Excel. (link to the linkedin feed: https://www.flickr.com/photos/150411108@N06/35471910724/in/photostream/)
    At first I thought about a sunburst chart but that is definitely not the answer. More like a filled radar plot. The animation could be done with VBA.
    But I got stuck! First in finding the used data (no luck with that) but most important with the chart itself. Even with a dummy set (all countries per continent, 1 year of fake temperature data) I'm not getting any closer. And I've done my share of custom graphs like vertical waterfalls with subtotals and the usual gannt charts with progress bars..... I even understand the Wall Mart animation from Jorge Camoes his book "Data at Work". So it's not like I don't know how to combine datasets to a chart.
    Any thoughts? Or can I find the answer somewhere else on the forum....
  2. p45cal

    p45cal Well-Known Member

    Messages:
    1,231

    Attached Files:

    Thomas Kuriakose likes this.
  3. Diederik

    Diederik New Member

    Messages:
    4
    Hi P45Cal, thanks for the reply. This is exactly how far I had come with my filled radar plot (identical :) ). So it proves I was on the right track and didn't miss any other Excel feature so far.
    I like the idea of the "spoke chart" for the call centre report (hourly calls) and it looks promising to try and get all the features of the temperature circle into this type of chart.
    Maybe somebody has options on how to color the spokes according to their value.
    Ik know you cab change color when inverted/negative but this requires a more subtle approach. From blue towards red.
    Kind regards, Diederik (Holland)
  4. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    …only if I haven't missed any; I've only installed 2016 recently and haven't explored new charts available.


    I don't know the base values, nor do I know the colours you want. In the attached I've added colour, but it is only a random colour. You'll need to change the line:
    Code (vb):
      xColours(i - 1) = RGB(Application.RandBetween(0, 255), Application.RandBetween(0, 255), Application.RandBetween(0, 255))
    to a calculated colour based on the value of RotatedData(i, 1), perhaps in a separate function.
    It's not very fast.
    upload_2018-7-16_10-49-34.png

    Attached Files:

    Thomas Kuriakose likes this.
  5. Diederik

    Diederik New Member

    Messages:
    4
    p45cal:
    thanks, this is a growing thing :)
    the color will change according to the absolute delta to the average. So below average it wil be a blue-ish color (cold) and above average it will get red (warmth)
    I will look into this tonight after office hours. My boss wants me to do his work during these hours :)
    Thanks for this next step
  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    Does anybody here know how to extract the country/year data from the file ?

    The rest is fairly simple

    I'm going to use a technique similar to this post
    https://chandoo.org/wp/how-to-make-a-spoke-chart/
    But I will use Named Formula and a little bit of vba to do the animation
  7. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    Attached.
    (I thought I'd posted this yesterday)

    Attached Files:

  8. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    Could you indicate which file?
  9. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    Last edited: Jul 17, 2018
  10. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    Yes , I went there earlier but couldn't find ERSSTv4 version.
    upload_2018-7-17_11-47-11.png

    I downloaded the
    upload_2018-7-17_11-51-25.png
    instead but after unzipping it turns out to be a NetCDF file, a format I've never heard of before (it seems to be specific to climate people). If I get time (and inclination) I'll dig a bit deeper. (Why couldn't they supply this in a more friendly format?)
    There's more information on the format here:
    https://www.unidata.ucar.edu/publications/factsheets/current/factsheet_netcdf.pdf
    and here:
    https://www.unidata.ucar.edu/software/netcdf/docs_rc/
    and here's an online converter (not tried):
    https://mygeodata.cloud/converter/netcdf-to-xlsx
    and perhaps more promising for Excel is:
    https://code.google.com/archive/p/netcdf4excel/
  11. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    I think that file referred to is an SBBX file not a .NC file type which the NetCDF4 programs use?

    I also suspect that the data is in 2deg blocks around the globe
    So it will probably need consolidation to countries first

    Power Pivot could probably do that if we can get it to a CSV or XLSX file type
  13. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    I think the file you highlighted is probably an SBBX file (whatever that is) but I went for the closest to the cited file:
    Land-Ocean Temperature Index ERSSTv4, 1200km smoothing
    which was (two files above your highlight):
    Land-Ocean Temperature Index ERSSTv5, 1200km smoothing
    which has a .nc extension.
    Or am I missing something?
  14. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    xlsx version of Land-Ocean Temperature Index ERSSTv5, 1200km smoothing:
    https://www.dropbox.com/s/fggmypxirycon7a/Land Ocean Temperature Index ERSSTv5 1200km smoothing.xlsx?dl=0
    Installed NetCDF4Excel on an old Office 2010 machine using the NetCDF4Excel_3_3_setup.exe file at https://github.com/NetCDF4Excel/project/releases and converted the .nc file.
    Most of the sheets are mapping/lookup sheets. Most of the data is 32.767 (one thousandth of the Integer data type max) so I guess these may as well be blank. Not sure what the other values mean. Times/dates seem to tally more or less with Excel dates.
    I'll try and lighten the file so it only contains meaningful data, then it'll be a case of mapping lat/lon to country names.
    Thomas Kuriakose likes this.
  15. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    FYI - "ERSSTv5" is just the latest version of ERSST (Extended Reconstructed Sea Surface Temperature) data.

    *.nc is netCDF format file.
    https://www.unidata.ucar.edu/software/netcdf/docs_rc/

    SBBX is binary file, that's used to produce the NetCDF file.

    data set meta data contains following in 'gistemp1200_ERSSTv5.nc'
    File read using netCDF4 module in Python3.6
    Code (vb):
    root group (NETCDF3_CLASSIC data model, file format NETCDF3):
        title: GISTEMP Surface Temperature Analysis
        institution: NASA Goddard Institute for Space Studies
        source: http://data.giss.nasa.gov/gistemp/
        Conventions: CF-1.6
        history: Created 2018-07-12 10:36:22 by SBBX_to_nc 2.0 - ILAND=1200, IOCEAN=NCDC/ER5, Base: 1951-1980
        dimensions(sizes): lat(90), lon(180), time(1662), nv(2)
        variables(dimensions): float32 lat(lat), float32 lon(lon), int32 time(time), int32 time_bnds(time,nv), int16 tempanomaly(time,lat,lon)
        groups:
    And these are meta data for variables.
    Code (vb):
    <class 'netCDF4._netCDF4.Variable'>
    float32 lat(lat)
        standard_name: latitude
        long_name: Latitude
        units: degrees_north
    unlimited dimensions:
    current shape = (90,)
    filling off

    <class 'netCDF4._netCDF4.Variable'>
    float32 lon(lon)
        standard_name: longitude
        long_name: Longitude
        units: degrees_east
    unlimited dimensions:
    current shape = (180,)
    filling off

    <class 'netCDF4._netCDF4.Variable'>
    int32 time(time)
        long_name: time
        units: days since 1800-01-01 00:00:00
        bounds: time_bnds
    unlimited dimensions:
    current shape = (1662,)
    filling off

    <class 'netCDF4._netCDF4.Variable'>
    int32 time_bnds(time, nv)
    unlimited dimensions:
    current shape = (1662, 2)
    filling off

    <class 'netCDF4._netCDF4.Variable'>
    int16 tempanomaly(time, lat, lon)
        long_name: Surface temperature anomaly
        units: K
        scale_factor: 0.01
        cell_methods: time: mean
        _FillValue: 32767
    unlimited dimensions:
    current shape = (1662, 90, 180)
    filling off
    Haven't got time today to study the data set more in detail. But will play with it when I find time.
    Thomas Kuriakose likes this.
  16. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    Ok, I've finished studying the data set. One thing to note is that data starts from Jan, 1880 and is monthly.

    This poses issue for Excel as Excel isn't well equipped to deal with dates prior to 1900.

    As well, data is structured as matrix (lat, lon), so there's bit of transformation needed.

    I've extracted out from year 2000 to current year, and flattened the data.

    https://drive.google.com/file/d/1-KLIa3axR2ohxjGz7PWoZqp9KUk1ez_4/view?usp=sharing
    Thomas Kuriakose likes this.
  17. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    I've been struggling with this. One way I came across that seems to look good is google maps API, the only problem being it's not so easily available since (just a few days ago) they changed its availability to those who have provided Google with a payment method (it's still free but they want this information). Given the recent mass hacking of personal data, even from reputable companies, that sort of information is not something I'm ready to give to Google just yet, especially as this is not even my project, nor do I want to buy anything else from Google.

    So, I've taken various .nc files from that NASA web site, converted them to Excel, then extracted only those points on the Earth which have data associated with them and it comes to about 77k of them. Plotted on 3DMaps within Excel produces this sort of thing:
    upload_2018-7-20_18-16-1.png

    So, perhaps we can do some collaborative work here (I'll atttach that list of lat/longs here); anyone with a Google account that does have access to Google APIs could perhaps process those lat/longs and produce a lookup table so that we can assign a country and continent to many of those points?

    So Hui was quite right with:
    but I'm not familiar enough with Power Pivot yet to do this. See linked-to xlsx file in my message #14.

    Haven't heard from @Diederik for a while…

    Attached Files:

    Last edited: Jul 20, 2018
    Thomas Kuriakose likes this.
  18. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    Thomas Kuriakose likes this.
  19. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,571
    I have some code to Reverse Geocode Data to get Countries

    But was going to attempt it in Power Map also
    Will post it later

    After getting countries
    Filter out the oceans
    Then consolidate country data
  20. Diederik

    Diederik New Member

    Messages:
    4
    Hi everybody, there has been done a lot of work by you. I've been sort of busy with my job lately so my apologies for being "silent". I was puzzled by the dataset myself with all the Nasa files and couldn't get it in the right format, like "Country";"continent";"year";"temp"
    Since 1880 is just a number the Excel date issue is actually not an issue. But anyway I got nowhere near a dataset like I described. And seeing the struggle from others to derive data from the Nasa pages I'm not even a little embarrassed that I failed :)
    The original animation was done in Phyton I think and I have no knowledge of that language. I also emailed the Finnish scientist for tips on his dataset, but no answer. The next weeks I'll be on a holiday trip to Italy so I'm afraid i will have to let is rest for I while. But as soon as I can get my hands on a workable dataset I'll try to pick it up again.
  21. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    Excel isn't well suited for visualizing this data. Due to size and how the data is structured.

    You can use Python (Anaconda is probably the easiest distribution to work with), as previously mentioned to read contents of NetCDF using netCDF4 module.

    Played a little with MapBox API using various Python modules.
    Currently I haven't adjusted color scale. So, I believe it's using plotly's default 0 to 1 (0 to 100%) scale.

    You can download file from link below.
    https://drive.google.com/file/d/1xxRt-4WTCNKD1CStIZG3XoAN4s84aGhB/view?usp=sharing

    Code (vb):
    from netCDF4 import Dataset
    import xarray as xr
    import numpy as np
    f = Dataset('C:/Test/nc/gistemp1200_ERSSTv5.nc', 'r')
    ds = xr.open_dataset(xr.backends.NetCDF4DataStore(f))
    df = ds.to_dataframe()

    df_sub = df.iloc[(df.index.get_level_values('time') == '2017-12-15') & (df.index.get_level_values('nv') == 0)]

    step = 1.0
    to_bin = lambda x: np.floor(x / step) * step
    df_sub["latbin"] = df_sub.index.get_level_values('lat').map(to_bin)
    df_sub["lonbin"] = df_sub.index.get_level_values('lon').map(to_bin)
    groups = df_sub.groupby(("latbin", "lonbin"))
    df_flat = df_sub.drop_duplicates(subset=['latbin', 'lonbin'])
    df_no_nan = df_flat[np.isfinite(df_flat['tempanomaly'])]
    import plotly.offline as py_off
    import plotly.graph_objs as go
    mapbox_access_token = 'Use API key obtained from Mapbox site here'
    data = []
    data.append(
        go.Scattermapbox(
            lon=df_no_nan['lonbin'].values,
           lat=df_no_nan['latbin'].values,
           mode='markers',
           marker=go.Marker(
                color=df_no_nan['tempanomaly'].values,
               colorscale='Bluered'
           ),
        )
    )
         
    layout = go.Layout(
        margin=dict(t=0,b=0,r=0,l=0),
        autosize=True,
        hovermode='closest',
       showlegend=False,
        mapbox=dict(
            accesstoken=mapbox_access_token,
            bearing=0,
            center=dict(
                lat=38,
                lon=-94
            ),
            pitch=0,
            zoom=0,
            style='light'
       ),
    )
    fig = dict(data=data, layout=layout)
    py_off.plot(fig, filename='Da_Weather_2.html')
    upload_2018-7-23_10-7-42.png
  22. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    To aggregate it to country level, you'll first calculate/find boundaries for each country of interest (E, W, N, S limit of lat & lon value). Then average out tempanomaly values. Or have Lookup table/dictionary listing each lat/lon coordinate (at 2 degree square) and corresponding country/geographic location that it falls under.

    Edit: Here's link to csv file that contains min & max values for lat/lon for each country. I believe wrapped means it crosses +/- threshold.
    https://data.humdata.org/dataset/bounding-boxes-for-countries
    Last edited: Jul 23, 2018
  23. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    Did some more digging. Found Python code used by Antti Lipponen used to generate temperature circle via Python.
    https://gist.github.com/anttilipp/6b572512ef53cfc6bf949afdc8eb6720
    Note: He hard coded data values.

    To get country via lat/lon in Python, you can use number of methods.
    1. Import below to your python project/environment
    https://github.com/che0/countries
    Then download world border shape file from...
    http://thematicmapping.org/downloads/world_borders.php
    Or file from below.
    http://gis-lab.info/qa/vmap0-eng.html

    2. Use Google API to retrieve country name from lat/lon coordinate.
  24. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,006
    I messed up few times transforming Pandas dataframe. But I think I got the right data and mapping to country and continent now.

    FYI - Added "Arctic" as continent/region.

    Average yearly temperature anomaly from 1880-2017 (there is 2018 in there but it's partial), by country.

    Attached Files:

  25. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    Nor have I; perhaps it's a dialect of Klingon or Ferengi.

Share This Page