Link to URL/File – issue with .isum and .sortwith functions for missing data

[Jean-Yves Le StangParticipant]

Hi,

In the graphic below, the “Raw data” section is a link from an URL providing weather data every 3 hours, from which I extract the date and the rainfall during the last 3 hours.

In order to be able to calculate the rainfall between two selected dates I need to elaborate the running sum of the 3 hours cumulated rainfalls

The downloaded data are not sorted by date  and some rainfall values are missing. Therefore after sorting using “.sortwith” I come-up with “Bad index” errors in the sorted list of rainfalls, and,  as mentioned in the documentation, the incremental sum issued from the “.isum” function is reset to “0” each time a piece of data is missing.

Therefore, my rainfall curve looks pretty much like a real rainfall on the screen ! (see red curve).

When I download the same file on my computer and drop it in the “Data Side Panel”, I can then select the missing rows using the search menu and fill them with “0” or delete them, and produce the blue curve, which is what I want. I use provisionally this workaround by “copy as Text” and “Paste” of the “Link to URL/File” columns, but the interest of linking to URL is then null as i have to do this operation for each update. I could not find any automatic workaround and appreciate if you suggest any.

But the real good thing could be to introduce in the “Link to URL/File” column definition a “missing” drop down menu like it is in the Expressions Columns.

The whole file is available with the link below

https://www.dropbox.com/scl/fo/7j1tqsspxdeh4ecfgzvlq/h?rlkey=qjtuj07jryk4e72zbljtcpyxg&dl=0

[Jean-Yves Le StangParticipant]

I found a simple automatic workaround by creating an expression from the sorted rainfall column and selecting “set as 0” for the “Missing” option. This was suggested to me by in #12405 ( with many thanks to Petercreate)

I Keep my proposal to integrate this possibility in the “Link to URL/File” command.

[dgteamModerator]

Thanks for posting this example. One thing is that the isum should probably not reset to zero when you specify the missing values to be zero. Either way, glad you found a work around for the missing data.  Strange that they have rows with nothing in them.

Also this looks like a great source of data that you can dynamically to link to that others might be intersted in cheching out.

https://public.opendatasoft.com/api/explore/v2.1/console

 

[Jean-Yves Le StangParticipant]

Your proposal makes sense, better than mine, unless there is a specific reason for the isum property to reset when a piece of data is missing ?

Regarding the data set, more than fourty records are collected every 3 hours by around 60 weather stations in France, for a worldwide message/file system coordinated by UN/WMO and called SYNOP. In each station, some sensors may be accidentally or periodically out of service and their records remain empty while other sensor are still recorded and reported. This is the reason why in rows, some columns are empty (may this would not happen with a “flattened” database)

Please note the following:

• https://public.opendatasoft.com/explore/?sort=modified
  links to more than 580 data sets and a lot of different subjects than weather (COVID, Health, housing…), but mainly related to France.
• Vessels at sea are also involved in regular SYNOP messaging, as part of the IMO organisation, but I don’t know if their records are available to public and their interest for non-professional.
• SYNOP system may be soon obsolete or updated due to the contribution of weather satellites to the worldwide climate information.

Many thanks to DGTeam for your support.

 

[Author]

Posts