<commit_before>"""
 Check over the database and make sure we have what we need there to
 make the climodat reports happy...
"""
from __future__ import print_function
import sys

import pandas as pd
from pandas.io.sql import read_sql
from pyiem.network import Table as NetworkTable
from pyiem.util import get_dbconn


def main(argv):
    """Go Main"""
    state = argv[1]
    nt = NetworkTable("%sCLIMATE" % (state,))
    pgconn = get_dbconn('coop')
    cursor = pgconn.cursor()
    df = read_sql("""
        SELECT station, year, day from alldata_""" + state + """
        ORDER by station, day
    """, pgconn, index_col=None, parse_dates=['day'])

    for station, gdf in df.groupby('station'):
        if station not in nt.sts:
            print("station: %s is unknown to %sCLIMATE network" % (station,
                                                                   state))
            continue
        days = pd.date_range(gdf['day'].min(), gdf['day'].max())
        missing = [x for x in days.values if x not in gdf['day'].values]
        print(("station: %s has %s rows between: %s and %s, missing %s/%s days"
               ) % (station, len(gdf.index), gdf['day'].min(),
                    gdf['day'].max(), len(missing), len(days.values)))
        for day in missing:
            now = pd.Timestamp(day).to_pydatetime()
            cursor.execute("""
                INSERT into alldata_""" + state + """ (station, day, sday,
                year, month) VALUES (%s, %s, %s, %s, %s)
            """, (station, now,
                  "%02i%02i" % (now.month, now.day), now.year, now.month))

    cursor.close()
    pgconn.commit()
<commit_msg>faster climodat hole filler and some QC to remove data poor stations<commit_after>"""Rectify climodat database entries."""
from __future__ import print_function
from io import StringIO
import subprocess
import sys

import pandas as pd
from pandas.io.sql import read_sql
from pyiem.network import Table as NetworkTable
from pyiem.util import get_dbconn


def main(argv):
    """Go Main"""
    state = argv[1]
    nt = NetworkTable("%sCLIMATE" % (state,))
    pgconn = get_dbconn('coop')
    df = read_sql("""
        SELECT station, year, day from alldata_""" + state + """
        ORDER by station, day
    """, pgconn, index_col=None, parse_dates=['day'])

    for station, gdf in df.groupby('station'):
        if station not in nt.sts:
            print("station: %s is unknown to %sCLIMATE network" % (station,
                                                                   state))
            continue
        # Make sure that our data archive starts on the first of a month
        minday = gdf['day'].min().replace(day=1)
        days = pd.date_range(minday, gdf['day'].max())
        missing = [x for x in days.values if x not in gdf['day'].values]
        print(("station: %s has %s rows between: %s and %s, missing %s/%s days"
               ) % (station, len(gdf.index), gdf['day'].min(),
                    gdf['day'].max(), len(missing), len(days.values)))
        coverage = len(missing) / float(len(days.values))
        if coverage > 0.33:
            cmd = ("python ../dbutil/delete_station.py %sCLIMATE %s"
                   ) % (state, station)
            print(cmd)
            subprocess.call(cmd, shell=True)
            cursor = pgconn.cursor()
            cursor.execute("""
            DELETE from alldata_""" + state + """ WHERE station = %s
            """, (station, ))
            print("Removed %s database entries" % (cursor.rowcount, ))
            cursor.close()
            pgconn.commit()
            continue
        sio = StringIO()
        for day in missing:
            now = pd.Timestamp(day).to_pydatetime()
            sio.write(("%s,%s,%s,%s,%s\n"
                       ) % (station, now, "%02i%02i" % (now.month, now.day),
                            now.year, now.month))
        sio.seek(0)
        cursor = pgconn.cursor()
        cursor.copy_from(
            sio, "alldata_%s" % (state, ),
            columns=('station', 'day', 'sday', 'year', 'month'), sep=','
        )
        del sio
        cursor.close()
        pgconn.commit()

{'fraction_non_alphanumeric': 0.13948446157552397, 'fraction_numerical': 0.0036135870874488074, 'mean_word_length': 2.0196363636363635, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1061311', 'n_tokens_mistral': 1257, 'n_tokens_neox': 1217, 'n_words': 363}