challenge3_kent.py

import matplotlib.pyplot as plt
from matplotlib import ticker, gridspec
import pandas as pd

import sageodata


wl_key = "STANDING_WATER_LEVEL"
# wl_key = "DEPTH_TO_WATER"

with sageodata.Connection() as gd:
    df = gd.pandas_query("""
        select d.unit_no, wl.obs_date, wl.{wl_key}
        from dhdb.wa_water_level_vw wl
        left outer join dhdb.dd_drillhole_vw d
        on wl.drillhole_no = d.drillhole_no
        where d.drillhole_no=128013 or d.drillhole_no=128014
        """.format(wl_key=wl_key)
        )

print(df.columns)

df = df.dropna()

def difference_func(group):
    wl_1 = group[group["UNIT_NO"] == 702901196][wl_key].mean()
    wl_2 = group[group["UNIT_NO"] == 702901197][wl_key].mean()
    print(group)
    print(wl_1 - wl_2)
    return pd.DataFrame({
        "obs_date": [group["OBS_DATE"].iloc[0]],
        "diff": [wl_1 - wl_2],
        })

df["MONTHLY"] = df["OBS_DATE"].dt.to_period("M")
monthly_data = df.groupby("MONTHLY")
good_months = monthly_data.filter(lambda group: len(set(group["UNIT_NO"])) > 1)
diffs = good_months.groupby("MONTHLY").apply(difference_func)

if True:
    fig = plt.figure()
    gs = gridspec.GridSpec(2, 1, height_ratios=(2, 1), hspace=0.18)
    ax_dtw = fig.add_subplot(gs[0])
    ax_diff = fig.add_subplot(gs[1], sharex=ax_dtw)
    colours = {
        702901196: "r",
        702901197: "b",
        }
    labels = {
        702901196: "PAG081",
        702901197: "PAG082",
    }
    for unit_no in set(df["UNIT_NO"]):
        sub_df = df[df["UNIT_NO"] == unit_no]
        ax_dtw.plot(
            sub_df["OBS_DATE"], sub_df[wl_key], marker=".", 
            color=colours[unit_no], ls="none", label="")

        sub_good_months = good_months[good_months["UNIT_NO"] == unit_no]
        ax_dtw.plot(
            sub_good_months["OBS_DATE"], sub_good_months[wl_key], marker="o", 
            color=colours[unit_no], ls="none", mfc="none", label=labels[unit_no])

        ax_dtw.legend()

    plt.setp(ax_dtw.get_xticklabels(), visible=False)

    ax_diff.axhline(0, color="gray", lw=0.5)
    ax_diff.plot(diffs["obs_date"].values, diffs["diff"], ls="none", marker="o", color="k", mfc="grey")

    ax_diff.yaxis.set_major_locator(ticker.MultipleLocator(0.5))
    ax_diff.yaxis.set_minor_locator(ticker.MultipleLocator(0.1))

    for ax in (ax_dtw, ):
        ax.set_ylim(*ax.get_ylim()[::-1])
    ax_dtw.set_ylabel(wl_key + " (m)")
    ax_dtw.set_title("Monitoring data:", fontsize="medium")

    ax_diff.set_title("Comparison of monthly averages:", fontsize="medium")
    text_kws = dict(ha="right", fontsize="small", transform=ax_diff.transAxes)
    ax_diff.text(0.98, 0.95, "PAG82 shallower than PAG81", color="b", va="top", **text_kws)
    ax_diff.text(0.98, 0.05, "PAG82 deeper than PAG81", color="r", va="bottom", **text_kws)

fig.savefig("kent_normal.png", dpi=120, bbox_inches="tight")
plt.show()