WQP Harvest

wqp_harvest.py

import os,sys;
import requests,csv,codecs;

localdir = os.path.dirname(os.path.abspath(__file__));
target = localdir + os.sep + 'wqp_harvest_20230925.csv';

if os.path.exists(target):
   os.remove(target);

r = requests.get('https://www.waterqualitydata.us/Codes/countrycode?mimeType=json');
r_json = r.json();
countries = [];
for item in r_json["codes"]:
   if item["value"] != "US":
      countries.append(item["value"]);
   
r = requests.get('http://www.waterqualitydata.us/Codes/statecode?countrycode=US&mimeType=json');
r_json = r.json();
states = [];
for item in r_json["codes"]:
   states.append(item["value"]);
   
isFirst = True;

with open(target,'w',newline='',encoding='utf-8') as f:
   writer = csv.writer(f,delimiter=',');
   
   for state in states:
      payload = {'statecode':state,'mimeType':'csv','sorted':'no'};
      r = requests.get('https://www.waterqualitydata.us/data/Station/search',params=payload,stream=True);
      rd = [line.decode('utf-8') for line in r.iter_lines()];
      cr = csv.reader(rd);
      
      header = True;
      for row in cr:
      
         if header and isFirst:
            for idx,ch in enumerate(row):
               row[idx] = row[idx].replace('/','_');
               
            writer.writerow(row);
            
         else:
            for idx,ch in enumerate(row):
               row[idx] = row[idx].replace(u'\u00b6','');
               
            writer.writerow(row);
            
         header = False;
            
      isFirst = False;
      
   for country in countries:
      payload = {'countrycode':country,'mimeType':'csv','sorted':'no'};
      r = requests.get('https://www.waterqualitydata.us/data/Station/search',params=payload,stream=True);
      rd = [line.decode('utf-8') for line in r.iter_lines()];
      cr = csv.reader(rd);
      
      header = True;
      for row in cr:
      
         if header and isFirst:
            for idx,ch in enumerate(row):
               row[idx] = row[idx].replace('/','_');
               
            writer.writerow(row);
            
         else:
            for idx,ch in enumerate(row):
               row[idx] = row[idx].replace(u'\u00b6','');
               
            writer.writerow(row);
            
         header = False;
            
      isFirst = False;