Downloading and extracting data from CSV file

Configuration
1

I want to extract and display some data from a CSV file that needs to be downloaded from the internet.

https://api.melbournewater.com.au/rainfall-river-level/228357A/river-level/live.csv

I want to extract the lastest date/time and number in second field from the second line of the file, and also whether the water level is rising or falling by comparing to the next line.

Contents of the CSV file

Date/Time,Mean level (m)
“2020-09-22 09:06:00”,0.162
“2020-09-22 09:00:00”,0.162
“2020-09-22 08:54:00”,0.162
“2020-09-22 08:48:00”,0.162
“2020-09-22 08:42:00”,0.162
“2020-09-22 08:36:00”,0.162
“2020-09-22 08:30:00”,0.160
“2020-09-22 08:24:00”,0.160

2

Hi @dazza25,

Did you find a solution to your issue?
If yes, could you please share it here. Indeed, I want to do the same :wink:

3

G’day fellow Aussies,

Was really hoping someone would help you out on this, as I am not confident my solution is the best (though it works :wink: )

End Result

image

Steps

  1. create this file from the root of your HA configurations folder:
./scripts/melbournewater.py

Obviously create the script directory as it is non-standard.

  1. paste this into said file:
import requests 

url = "https://api.melbournewater.com.au/rainfall-river-level/228357A/river-level/live.csv"

r = requests.get(url, stream = True)
line = r.content[0:128].decode("utf-8").splitlines()[1].split(',')
r.close

print('{"date": '+line[0]+',"level": "'+line[1]+'"}')
  1. Create the following sensor:
sensor:
  - platform: command_line
    name: MelbourneWater
    json_attributes:
      - date
      - level
    command: 'python3 /config/scripts/melbournewater.py'
    value_template: '{{ value_json.level }}'
  1. Watch the water

Re:

This is out of scope for this sensor. You can easily do that with an automation in Home Assistant; That is what it’s there for!

Something along the lines of (in psuedo-code):

if change in MelbourneWater.date:
  condition:
    level.now > level.last
  do:
    something

Let me know if you need me to explain any of the code

4

I ended up discovering that although the Melbourne Water api is not supposedly available to the public, it is not access-controlled at all, and found the correct URLS to access the data directly.

  - platform: rest
    name: heathmont_dandenong_creek_level
    resource: https://api.melbournewater.com.au/rainfall-river-level/228357A/river-level/live
    value_template: '{{ value_json.liveRiverLevelsData[0].meanRiverLevel }}'
    unit_of_measurement: 'm'
    scan_interval: 900        

  - platform: rest
    name: heathmont_dandenong_creek_flow
    resource: https://api.melbournewater.com.au/rainfall-river-level/228357A/river-flow/live
    value_template: '{{ value_json.liveRiverFlowsData[0].meanRiverFlow }}'
    unit_of_measurement: 'Ml/day'
    scan_interval: 900        

  - platform: rest
    name: heathmont_dandenong_creek_rain
    resource: https://api.melbournewater.com.au/rainfall-river-level/228357A/rain/live
    value_template: '{{ value_json.liveRainfallLevelsData[0].cumulativeRainfallLevel }}'
    unit_of_measurement: 'mm'
    scan_interval: 900
1 Like
5

This is a much better solution.
Good on ya.

Just wrap your code in a code fence so others can copy it and mark it as the solution!

6

Done, thanks.

1 Like