<jupyter_start><jupyter_text>Critcal Loads: Dockerised workflow (November 2018)This notebook begins the process of transferring the Critical Loads workflow out of RESA and into Docker, so that it can eventually be moved onto the new data platform. A workflow is provided for creating a new PostGIS database in Docker and populating it with the key datasets required for the critical loads calculations. Some data exploration and checking is also presented.In brief, the workflow looks like this: 1. Create a local Docker container running PostGIS  2. Use the [Data Science Toolkit](https://github.com/NIVANorge/niva_datasci_toolkit) to transfer all relevant datasets into the new PostGIS database  3. Create an identical (empty) PostGIS container running on Kubernetes in Google Cloud  4. Use `pg_dump` to transfer all the data from the local container to the cloud container  5. Expose the cloud container to allow connections/queries from e.g. the Toolkit or QGIS 1. Create a PostGIS database running on Docker**Update 23/11/2018:** I initially used the `kartoza/postgis:9.6-2.4` image for my testing, but it seems that `timescale/timescaledb-postgis:latest-pg10` is more compatible with our current setup on GCP. My original code for creating the PostGIS container was      Create container    docker run --name=postgis_crit_lds -d -e POSTGRES_USER=admin -e POSTGRES_PASS=<PASSWORD> -e POSTGRES_DBNAME=critical_loads -e ALLOW_IP_RANGE=0.0.0.0/0 -p 25432:5432 -v pg_data_crit_lds:/var/lib/postgresql kartoza/postgis:9.6-2.4    but this has now been changed in favour of the workflow described below. 1.1. Create data volumeThe following code creates a data volume on the (local) Docker host.     Create data volume    docker volume create pg_data_crit_lds 1.2. Create container     Create container    docker run -d --name postgis_crit_lds -p 25432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=<PASSWORD> -e POSTGRES_DB=critical_loads -e PGDATA=/var/lib/postgresql/data/pg10 -e POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL -v pg_data_crit_lds:/var/lib/postgresql/data timescale/timescaledb-postgis:latest-pg10    **Note:** Once the container has been created, it can be stopped/started at any time using     Start    docker start postgis_crit_lds         Stop    docker stop postgis_crit_lds    We can now start the DS Toolkit and establish connections to the following databases: 1. **RESA2**. A centralised NIVA database containing most of the old (non-spatial) data relating to the Critical Loads project  2. **PostGIS running on `localhost`**. My local PostGIS installation, which stores some of the new spatial datasets required for the updated calculations  3. **PostGIS running on Docker**. The new database created for the updated workflow. We wish to transfer all the Critical Loads datasets (both spatial and non-spatial) to this database<jupyter_code># Connect to the NIVABASE
ora_eng = nivapy.da.connect()
# Connect to local PostGIS
loc_pg_eng = nivapy.da.connect(src='postgres',
                               db_name='niva_work',
                               port=5432)
# Connect to Docker PostGIS
doc_pg_eng = nivapy.da.connect(src='postgres',
                               db_name='critical_loads',
                               port = 25432)<jupyter_output>Connection successful.<jupyter_text>1.3. Activate the PostGIS extension**Note:** This step is required for the `timescale/timescaledb-postgis` container, but *not* for the `kartoza/postgis:9.6-2.4` container.<jupyter_code># Activate PostGIS extension
sql = "CREATE EXTENSION IF NOT EXISTS postgis"
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>2. Deposition dataFirst, create a new schema to store deposition data.<jupyter_code># Create deposition schema
sql = "CREATE SCHEMA deposition"
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>2.1. Transfer BLR and 0.1 degree deposition gridsDeposition data from NILU is now supplied on a 0.1 degree grid, which is currently stored in my local PostGIS installation. The code below transfers it to the new schema in Docker.<jupyter_code># Get 0.1 degree dep grid from PostGIS
sql = ("SELECT * FROM public.dep_grid_0_1deg")
dep_gdf = gpd.read_postgis(sql, loc_pg_eng)

# Write to new db
nivapy.da.gdf_to_postgis(dep_gdf, 
                         'dep_grid_0_1deg', 
                         'deposition', 
                         doc_pg_eng,
                         'dep_dep_grid_0_1deg_spidx',
                         if_exists='replace',
                         index=False)

# Use 'cell_id' col as primary key
sql = ("ALTER TABLE deposition.dep_grid_0_1deg "
       "ADD CONSTRAINT dep_grid_0_1deg_pk "
       "PRIMARY KEY (cell_id)")
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>In previous years, data have been supplied using the BLR grid, which is available as a shapefile. The code below loads this into the database as well.<jupyter_code># Read BLR .shp
blr_path = (r'../../../data/vector/blrgrid_uten_grums.shp')
dep_gdf = gpd.read_file(blr_path)
dep_gdf.rename({'BLR':'blr'},
               axis=1,
               inplace=True)

# Write to new db
nivapy.da.gdf_to_postgis(dep_gdf, 
                         'dep_grid_blr', 
                         'deposition', 
                         doc_pg_eng,
                         'dep_dep_grid_blr_spidx',
                         if_exists='replace',
                         index=False)

# Use 'blr' col as primary key
sql = ("ALTER TABLE deposition.dep_grid_blr "
       "ADD CONSTRAINT dep_grid_blr_pk "
       "PRIMARY KEY (blr)")
doc_pg_eng.execute(sql)<jupyter_output>WARNING! The dataframe contains mixed geometries:
  {'Polygon', 'MultiPolygon'}
These will be cast to "Multi" type. If this is not what you want, consider using gdf.explode() first<jupyter_text>2.2. Transfer "Deposition Series Definitions" from the NIVABASEIn RESA2, the table `DEP_SERIES_DEFINITIONS` identifies the datasets previously supplied by NILU. Each dataset has a separate row (and ID) in this table. For consistency, I will transfer this table directly and use the same dataset IDs in the new workflow.<jupyter_code># Read data from RESA
sql = "SELECT * FROM resa2.dep_series_definitions"
df = pd.read_sql(sql, ora_eng)

# Tidy
df.rename({'dep_series_id':'series_id'},
          axis=1,
          inplace=True)
df.sort_values('series_id', inplace=True)
df.tail()
# Write to new db
df.to_sql('dep_series_defs', 
          doc_pg_eng,
          'deposition',
          if_exists='replace',
          index=False)

# Use 'dep_series_id' col as primary key
sql = ("ALTER TABLE deposition.dep_series_defs "
       "ADD CONSTRAINT dep_series_defs_pk "
       "PRIMARY KEY (series_id)")
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>2.3. Transfer "Deposition Parameter Definitions" from the NIVABASEIn RESA2, the table `AIR_PARAMETER_DEFINITIONS` identifies various deposition parameters. As far as I can see, only the first three or four entries in this table are currently relevant. Many of the columns also seem unnecessary at present<jupyter_code># Read data from RESA
sql = ("SELECT * FROM resa2.air_parameter_definitions "
       "WHERE parameter_id < 5")
df = pd.read_sql(sql, ora_eng)

# Tidy
df.sort_values('parameter_id', inplace=True)
df.drop(['formula', 'category', 'function', 'entered_by', 'entered_date'],
        axis=1,
        inplace=True)
df.rename({'parameter_id':'param_id'}, 
          inplace=True,
          axis=1)
df
# Write to new db
df.to_sql('dep_param_defs', 
          doc_pg_eng,
          'deposition',
          if_exists='replace',
          index=False)

# Use 'dep_series_id' col as primary key
sql = ("ALTER TABLE deposition.dep_param_defs "
       "ADD CONSTRAINT dep_param_defs_pk "
       "PRIMARY KEY (param_id)")
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>2.4. Data for "Deposition Values" 2.4.1. Create tablesThe old workflow used the coarse BLR grid for deposition values. Deposition `series_ids` from 1 to 27 are all based on this grid, so these values will be transferred to a new table named `'dep_values_blr_grid'`. Deposition `series_id=28` uses the new 0.1 degree grid, so this dataset (and subsequent ones) will need adding to a separate table (`'dep_values_0_1deg_grid'`). As future data will also be delivered using this grid, it will be useful to have a function for processing and adding raw data to this table.Additionally, both these tables need constraints to ensure that relevant series and parameter IDs are defined before uploading data. Note that I have not included a constraint on the BLR/cell ID, because the data supplied by NILU often includes cells that are outside of terrestrial Norway.<jupyter_code># Delete if already exist
sql = ("DROP TABLE IF EXISTS deposition.dep_values_blr_grid, "
       "  deposition.dep_values_0_1deg_grid")
doc_pg_eng.execute(sql)

# Create table for BLR data
sql = ("CREATE TABLE deposition.dep_values_blr_grid "
       "( "
       "  series_id integer NOT NULL, "
       "  blr integer NOT NULL, "
       "  param_id integer NOT NULL, "
       "  value numeric, "
       "  PRIMARY KEY (series_id, blr, param_id), "
       "  CONSTRAINT series_id_fkey FOREIGN KEY (series_id) "
       "      REFERENCES deposition.dep_series_defs (series_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION, "
       "  CONSTRAINT param_id_fkey FOREIGN KEY (param_id) "
       "      REFERENCES deposition.dep_param_defs (param_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION "
       ")")
doc_pg_eng.execute(sql)

# Create table for 0.1 degree data
sql = ("CREATE TABLE deposition.dep_values_0_1deg_grid "
       "( "
       "  series_id integer NOT NULL, "
       "  cell_id integer NOT NULL, "
       "  param_id integer NOT NULL, "
       "  value numeric, "
       "  PRIMARY KEY (series_id, cell_id, param_id), "
       "  CONSTRAINT series_id_fkey FOREIGN KEY (series_id) "
       "      REFERENCES deposition.dep_series_defs (series_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION, "
       "  CONSTRAINT param_id_fkey FOREIGN KEY (param_id) "
       "      REFERENCES deposition.dep_param_defs (param_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION "
       ")")
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>2.4.2. Transfer "old" dataThe old data using the BLR grid are stored in RESA2 in the table `DEP_BLR_VALUES`. We'll just transfer the data for `param_ids` 1 to 4.<jupyter_code># Read data from RESA
sql = ("SELECT * FROM resa2.dep_blr_values "
       "WHERE parameter_id < 5")
df = pd.read_sql(sql, ora_eng)

# Tidy
df.rename({'dep_series_id':'series_id',
           'parameter_id':'param_id'},
          axis=1,
          inplace=True)

# Write to new db
df.to_sql('dep_values_blr_grid', 
          doc_pg_eng,
          'deposition',
          if_exists='append',
          index=False)<jupyter_output><empty_output><jupyter_text>2.4.3. Import "new" dataIn 2017, NILU supplied raw data for the 0.1 degree grid in `.dat` format. I previously wrote some code ([here](http://nbviewer.jupyter.org/github/JamesSample/critical_loads/blob/master/notebooks/critical_loads_workflow_new_grid.ipynb1.1.-Upload-new-data-to-database)) to process this data. I have now generalised this and moved it into a new `.py` file, which will eventually become a Python package/module for the Critical Loads workflow.**Note:** Double-check with Kari that the NILU file `tot_s_combined-emep_grid.dat` contains data for *non-marine* S. We assumed this in the 2018 analysis, but worth checking.<jupyter_code># Process NILU data and add to db
nilu_fold = r'../../../data/raw/nilu_dep/2012-2016'
df = cl.upload_nilu_0_1deg_dep_data(nilu_fold, 
                                    doc_pg_eng,
                                    28)
df.head()<jupyter_output>207000 new rows added successfully.<jupyter_text>3. Vegatation dataFirst, create a new schema for data relating to the vegetation calculations.<jupyter_code># Create veg schema
sql = "CREATE SCHEMA vegetation"
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>3.1. Transfer critical loads table for vegetationThe Excel file     new_workflow_nov_2018\data\raw\veg_cl_classes\sat_veg_land_use_classes.xlsx    contains critical load values for various vegetation classes. The original is in the sheet named `'EUNIS_tilGIS'`, but I have also created a tidied version (with e.g. lower case column names) in the sheet named `'eunis_lower_case'`.As above, if this table is likely to change, it should be normalised and loaded into the database more carefully. For now, the "flat" Excel format is convenient, but I nevertheless want to store it in the database with the rest of the data.<jupyter_code># Read Excel data
xl_path = r'../../../data/raw/veg_cl_classes/sat_veg_land_use_classes.xlsx'
df = pd.read_excel(xl_path, sheet_name='eunis_lower_case')

# Write to new db
df.to_sql('land_class_crit_lds', 
          doc_pg_eng,
          'vegetation',
          if_exists='replace',
          index=False)

# Use 'norut_code' as primary key
sql = ("ALTER TABLE vegetation.land_class_crit_lds "
       "ADD CONSTRAINT veg_land_class_crit_lds_pk "
       "PRIMARY KEY (norut_code)")
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>3.2. Transfer vector vegetation dataAnalyses undertaken for the periods 2002-2006 and 2007-2011 used a vector vegetation dataset. From 2012-2016 onwards, this has been replaced by a more detailed raster dataset (Section 3.3, below). The code in this section adds the old vector dataset to PostGIS for future reference.<jupyter_code># Read veg .shp
shp_path = (r'../../../data/vector/nye_2010_talegrenser_veg.shp')
veg_gdf = gpd.read_file(shp_path)

# Tidy
del veg_gdf['ID_1'], veg_gdf['GRIDCODE'], veg_gdf['EUNIS_1']
veg_gdf.columns = [i.lower() for i in veg_gdf.columns]

# Reproject to WGS84
veg_gdf = veg_gdf.to_crs({'init':'epsg:4326'})

# Write to new db
nivapy.da.gdf_to_postgis(veg_gdf, 
                         'vector_veg_pre_2012', 
                         'vegetation', 
                         doc_pg_eng,
                         'vector_veg_pre_2012_spidx',
                         if_exists='replace',
                         index=False)

# Use 'id' col as primary key
sql = ("ALTER TABLE vegetation.vector_veg_pre_2012 "
       "ADD CONSTRAINT vector_veg_pre_2012_pk "
       "PRIMARY KEY (id)")
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>3.3. Transfer vegetation gridsThe code in Section 1.3 of [this notebook](http://nbviewer.jupyter.org/github/JamesSample/critical_loads/blob/master/notebooks/critical_loads_workflow_new_grid.ipynb1.3.-Reclassify) converted 30 m resolution satellite vegetation data into a grid of critical loads (based on the Excel table above - Section 3.1). Unless we update the vegetation data, this calculation does not need to be performed again, but it would be useful to store the resulting raster grid (`sat_veg_30m_cr_lds_div100.tif`) and the original vegetation grid (`sat_veg_30m_all.tif`) in the PostGIS database. As GeoTiffs, these grids are large: `sat_veg_30m_cr_lds_div100.tif` is around 2.3 GB uncompressed. PostGIS compresses the grids when they are uploaded, and it is possible to get the final raster size in the database using the folowing SQL:    SELECT pg_size_pretty(pg_total_relation_size('vegetation.sat_veg_30m_cr_lds_div100'));    This query returns 121 MB i.e. a vast reduction in file size. This is great for data storage, but terrible for computational performance, because the raster needs to be uncompressed each time before it can be used. For any data processing, it is therefore *much* faster to store the grids locally as uncompressed GeoTiff files. In the data analysis workflow, I will therefore include functions for exporting grids from PostGIS to GeoTiff format. This step only needs to be done once - all subsequent steps can then make use of the local GeoTiff files.**Note:** It is possible to load raster data into PostGIS using NivaPy (via `raster2pgsql`). For reference, a brief explanation of the underlying command systax is given below.           raster2pgsql -s 32633 -d -C -I -M -l 2,4,8,16,32,64,128,256,512 C:\Data\James_Work\Staff\Kari_A\Critical_Loads\GIS\raster\sat_veg_30m_cr_lds_div100.tif -t 100x100 vegetation.sat_veg_30m_cr_lds_div100 | psql -h localhost -U postgres -p 25432 -d critical_loads        You will be prompted to enter the password for the Docker database. This command specifies the following options (more details [here](https://postgis.net/docs/using_raster_dataman.html)):         * **-s**. EPSG code for spatial reference     * **-d**. Drop ratser if already exists in database     * **-C**. Apply raster constraints -- srid, pixelsize etc. to ensure raster is properly registered     * **-I**. Build a spatial index     * **-M**. Vaccum analyze after loading     * **-l**. Build pyramids (9 levels)     * **-t**. Cut raster into tiles to be inserted one per table row     The part after `psql` then specifies the connection details for the Docker database.      For the vegetation exceedance calculations, the code below uploads the following grids: * `sat_veg_30m_all.tif` * `sat_veg_60m_all.tif` * `sat_veg_120m_all.tif` * `sat_veg_30m_cr_lds_div100.tif` * `sat_veg_60m_cr_lds_div100.tif` * `blr_land_mask.tif` * `blr_land_mask_60m.tif`<jupyter_code># Add 30m veg to PostGIS
nivapy.da.raster_to_postgis('../../../data/raster/sat_veg_30m_all.tif',
                            'critical_loads',
                            'vegetation',
                            'sat_veg_30m_all', 
                            32633)
# Add 60m veg to PostGIS
nivapy.da.raster_to_postgis('../../../data/raster/sat_veg_60m_all.tif',
                            'critical_loads',
                            'vegetation',
                            'sat_veg_60m_all', 
                            32633)
# Add 120m veg to PostGIS
nivapy.da.raster_to_postgis('../../../data/raster/sat_veg_120m_all.tif',
                            'critical_loads',
                            'vegetation',
                            'sat_veg_120m_all', 
                            32633)
# Add 30m CL data to PostGIS
nivapy.da.raster_to_postgis('../../../data/raster/sat_veg_30m_cr_lds_div100.tif',
                            'critical_loads',
                            'vegetation',
                            'sat_veg_30m_cr_lds_div100', 
                            32633)
# Add 60m CL data to PostGIS
nivapy.da.raster_to_postgis('../../../data/raster/sat_veg_60m_cr_lds_div100.tif',
                            'critical_loads',
                            'vegetation',
                            'sat_veg_60m_cr_lds_div100', 
                            32633)
# Add 60m land mask to PostGIS
nivapy.da.raster_to_postgis('../../../data/raster/blr_land_mask_60m.tif',
                            'critical_loads',
                            'vegetation',
                            'blr_land_mask_60m', 
                            32633)
# Add 30m land mask to PostGIS
nivapy.da.raster_to_postgis('../../../data/raster/blr_land_mask.tif',
                            'critical_loads',
                            'vegetation',
                            'blr_land_mask_30m', 
                            32633)<jupyter_output>Processing data for vegetation.blr_land_mask_30m...

Raster loaded successfully.<jupyter_text>3.4. Create tables to store exceedancesSummary exceedance statistics for each grid cell are stored in the database in the following tables.<jupyter_code># Delete if already exist
sql = ("DROP TABLE IF EXISTS vegetation.exceedance_stats_blr_grid, "
       "  vegetation.exceedance_stats_0_1deg_grid")
doc_pg_eng.execute(sql)

# Create table for BLR data
sql = ("CREATE TABLE vegetation.exceedance_stats_blr_grid "
       "( "
       "  series_id integer NOT NULL, "
       "  blr integer NOT NULL, "
       "  exceeded_area_km2 numeric, "
       "  total_area_km2 numeric, "
       "  pct_exceeded numeric, "
       "  PRIMARY KEY (series_id, blr), "
       "  CONSTRAINT series_id_fkey FOREIGN KEY (series_id) "
       "      REFERENCES deposition.dep_series_defs (series_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION, "
       "  CONSTRAINT blr_fkey FOREIGN KEY (blr) "
       "      REFERENCES deposition.dep_grid_blr (blr) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION "
       ")")
doc_pg_eng.execute(sql)

# Create table for BLR data
sql = ("CREATE TABLE vegetation.exceedance_stats_0_1deg_grid "
       "( "
       "  series_id integer NOT NULL, "
       "  cell_id integer NOT NULL, "
       "  exceeded_area_km2 numeric, "
       "  total_area_km2 numeric, "
       "  pct_exceeded numeric, "
       "  PRIMARY KEY (series_id, cell_id), "
       "  CONSTRAINT series_id_fkey FOREIGN KEY (series_id) "
       "      REFERENCES deposition.dep_series_defs (series_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION, "
       "  CONSTRAINT cell_id_fkey FOREIGN KEY (cell_id) "
       "      REFERENCES deposition.dep_grid_0_1deg (cell_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION "
       ")")
doc_pg_eng.execute(sql)

# Create table for exceedance per land class data
sql = ("CREATE TABLE vegetation.exceedance_stats_land_class "
       "( "
       "  series_id integer NOT NULL, "
       "  norut_code integer NOT NULL, "
       "  exceeded_area_km2 numeric, "
       "  total_area_km2 numeric, "
       "  pct_exceeded numeric, "
       "  PRIMARY KEY (series_id, norut_code), "
       "  CONSTRAINT series_id_fkey FOREIGN KEY (series_id) "
       "      REFERENCES deposition.dep_series_defs (series_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION, "
       "  CONSTRAINT norut_code_fkey FOREIGN KEY (norut_code) "
       "      REFERENCES vegetation.land_class_crit_lds (norut_code) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION "
       ")")
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>4. Water dataFirst, create a new schema for data relating to the water calculations.<jupyter_code># Create water schema
sql = "CREATE SCHEMA water"
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>4.1. MAGIC model output for regressionKari has a spreadsheet here    K:\Avdeling\317 Klima- og miljømodellering\KAU\Focal Centre\Data\bc0regresjonNOK_TL2005-rapport_KAU.xls    containing output from the MAGIC model. This is used to generate parameters for the calculation of critical loads to water. It is unclear whether this data will be updated in the future. If so, the dataset should be properly normalised before adding to the database. However, for now I'm assuming that this is a static dataset, so I'm just athe essential data to the database as a "flat" table for the purposes of data storage.<jupyter_code># Read tidied Excel data
xl_path = r'../../../../water/update_nov_2018/regression_data_tidied.xls'
df = pd.read_excel(xl_path, sheet_name='tidied')

# Write to new db
df.to_sql('magic_regression_data', 
          doc_pg_eng,
          'water',
          if_exists='replace',
          index=False)

# Use ('resa_stn_id', 'sim_yr') as primary key
sql = ("ALTER TABLE water.magic_regression_data "
       "ADD CONSTRAINT water_magic_regression_data_pk "
       "PRIMARY KEY (resa_stn_id, sim_yr)")
doc_pg_eng.execute(sql)<jupyter_output><empty_output><jupyter_text>4.2. Perform regressionIn the MAGIC data table, values for `bc_x_k` in 1860 are used to define $BC^*_0$, and values from 1986 are used to define $BC^*$. The regression uses $BC^*$ as the x-variable and $BC^*_0$ as the y-variable.<jupyter_code># Get data
sql = ("SELECT resa_stn_id, sim_yr, bc_x_k "
       "FROM water.magic_regression_data")
df = pd.read_sql(sql, doc_pg_eng)
df.index = df['resa_stn_id']
del df['resa_stn_id']

# Split by year
bc0_df = df.query('sim_yr == 1860')
del bc0_df['sim_yr']
bc0_df.columns = ['BC0']

bc_df = df.query('sim_yr == 1986')
del bc_df['sim_yr']
bc_df.columns = ['BC']

# Join
df = bc0_df.join(bc_df)

# Regression
res = sm.ols(formula='BC0 ~ BC', data=df).fit()

print (res.summary())<jupyter_output><empty_output><jupyter_text>This is the same result as in Kari's Excel spreadsheet. Note, however, that the intercept is not significantly different from zero. This implies an alternative, "slope-only" model *might* be better.<jupyter_code># Regression
res = sm.ols(formula='BC0 ~ BC - 1', data=df).fit()
print (res.summary())<jupyter_output><empty_output><jupyter_text>This model is better (lower AIC and BIC) than the original, and it's also simpler. I therefore propose using$$BC^*_0 = 0.9481BC^*_t$$instead of $$BC^*_0 = 0.9431BC^*_t + 0.2744$$  XXXX. Create summary tables<jupyter_code># Create water schema
sql = "CREATE SCHEMA summaries"
doc_pg_eng.execute(sql)
# Create table for overall summary
sql = ("CREATE TABLE summaries.national_summary "
       "( "
       "  series_id integer NOT NULL, "
       "  medium varchar NOT NULL, "
       "  total_area_km2 numeric, "
       "  exceeded_area_km2 numeric, "
       "  exceeded_area_pct numeric, "
       "  PRIMARY KEY (series_id, medium), "
       "  CONSTRAINT series_id_fkey FOREIGN KEY (series_id) "
       "      REFERENCES deposition.dep_series_defs (series_id) "
       "      ON UPDATE NO ACTION ON DELETE NO ACTION "
       ")")
doc_pg_eng.execute(sql)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0870761130103344, 'fraction_numerical': 0.01968643168690322, 'mean_word_length': 3.1578173501061917, 'pattern_counts': {'":': 0, '<': 81, '<?xml version=': 0, '>': 79, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '643700', 'license': 'MIT', 'n_tokens_mistral': 8654, 'n_tokens_neox': 7986, 'n_words': 2443, 'path': 'notebooks/migrate_to_docker_test.ipynb', 'repo_name': 'JamesSample/critical_loads_2', 'size': '25441'}