def weather_pred(city: str, state: str, metric=None):
    db = PostgreSQL()
    conn = db.connection
    cur = conn.cursor()
    db.adapters(np.int64, np.float64, np.datetime64)

    # If prediciton found in database
    # If metric values are desired:
    if metric == True:
        table = "feelslikec"

    else:
        table = "feelslikef"

    retrieve_pred = f"""
    SELECT month, mean
    FROM {table}
    WHERE "city"='{city}' and "state"='{state}'
    """

    cur.execute(retrieve_pred)

    result = pd.DataFrame.from_records(
        cur.fetchall(), columns=["month", "mean"])
    result.set_index("month", inplace=True)
    result.index = pd.to_datetime(result.index)

    if len(result.index) > 0:
        c = pd.Series([city] * len(result.index))
        c.index = result.index
        s = pd.Series([state] * len(result.index))
        s.index = result.index

        result = pd.concat([c, s, result], axis=1)
        result.columns = ["city", "state", "temp"]

    # If prediction not found in database
    elif len(result.index) == 0:
        retrieve_data = f"""
        SELECT date_time, FeelsLikeC
        FROM historic_weather
        Where "city"='{city}' and "state"='{state}'
        """

        cur.execute(retrieve_data)

        df = pd.DataFrame.from_records(cur.fetchall())
        df.set_index(0, inplace=True)
        series = df.resample("MS").mean()

        warnings.filterwarnings(
            "ignore",
            message="After 0.13 initialization must be handled at model creation"
        )

        result = ExponentialSmoothing(
            series.astype(np.int64),
            trend="add",
            seasonal="add",
            seasonal_periods=12
        ).fit().forecast(24)

        c = pd.Series([city] * len(result.index))
        c.index = result.index
        s = pd.Series([state] * len(result.index))
        s.index = result.index

        result = pd.concat([c, s, result], axis=1)
        result.columns = ["city", "state", "temp"]
        result.index = result.index.astype(str)

        # Converting for temperature in Fahrenheit if needed
        # Conversion Helper Function
        def to_fahr(temp: float) -> float:
            return ((temp * 9) / 5) + 32

        if metric != True:
            result["temp"] = result["temp"].apply(to_fahr)

        insert_pred = """
        INSERT INTO {table}(
            month,
            city,
            state,
            mean
        ) VALUES%s
        """.format(table=table)

        execute_values(
            cur,
            insert_pred,
            list(result.to_records(index=True))
        )
        conn.commit()
        conn.close()

    return result.to_json(indent=2)
예제 #2
0
async def predict_temperatures(city: str, state: str, metric=False):
    """
    **Input**

    `city: str`    <- city name with any capitalization

    `state: str`   <- two-letter state abbreviation with any capitalization

    `metric: bool` <- *(Optional)* default, False, will output predictions and make
    database queries for adjusted temperature in degrees Fahrenheit;

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;
    True will do so for adjusted temperature in degrees Celsius.

    **Output**

    `json object` with predictions for
    
    **monthly temperature** of `city` and up to a total of three cities
    adjusted for dew point and wind chill for 24 months
    
    from the present (September 2020)
    """
    db = PostgreSQL()
    conn = db.connection
    cur = conn.cursor()
    db.adapters(np.int64, np.float64, np.datetime64)

    # Edge Cases
    # Saint
    if city[0:3] == "St." or city[0:3] == "st.":
        city = city.replace(city[0:3], "St")
    elif city[0:5] == "Saint" or city[0:5] == "saint":
        city = city.replace(city[0:5], "St")
    # Fort
    elif city[0:3] == "Ft " or city[0:3] == "ft ":
        city = city.replace(city[0:3], "Fort ")
    elif city[0:3] == "Ft." or city[0:3] == "ft.":
        city = city.replace(city[0:3], "Fort")
    # multiple caps
    elif city[0:2] == 'Mc':
        city = city.replace(city, city[:2] + city[2:].capitalize())

    # If prediciton found in database
    # If metric values are desired:
    if metric == True:
        table = "feelslikec"

    else:
        table = "feelslikef"

    retrieve_pred = f"""
    SELECT month, mean
    FROM {table}
    WHERE "city"='{city.title()}' and "state"='{state.upper()}'
    """

    cur.execute(retrieve_pred)

    result = pd.DataFrame.from_records(cur.fetchall(),
                                       columns=["month", "mean"])
    result.set_index("month", inplace=True)
    result.index = pd.to_datetime(result.index)

    result_json = result.to_json(index=2)

    if len(result.index) > 0:
        c = pd.Series([city] * len(result.index))
        c.index = result.index
        s = pd.Series([state] * len(result.index))
        s.index = result.index

        result = pd.concat([c, s, result], axis=1)
        result.columns = ["city", "state", "temp"]

    # If prediction not found in database
    elif len(result.index) == 0:
        retrieve_data = f"""
        SELECT date_time, FeelsLikeC
        FROM historic_weather
        Where "city"='{city.title()}' and "state"='{state.upper()}'
        """

        cur.execute(retrieve_data)

        df = pd.DataFrame.from_records(cur.fetchall())

        if len(df.index) == 0:
            result_json = f"{city}, {state} not found in Historic Weather Database."

        else:
            df.set_index(0, inplace=True)
            series = df.resample("MS").mean()
            warnings.filterwarnings(
                "ignore",
                message=
                "After 0.13 initialization must be handled at model creation")

            result = ExponentialSmoothing(
                series.astype(np.int64),
                trend="add",
                seasonal="add",
                seasonal_periods=12).fit().forecast(24)

            c = pd.Series([city.title()] * len(result.index))
            c.index = result.index
            s = pd.Series([state.upper()] * len(result.index))
            s.index = result.index

            result = pd.concat([c, s, result], axis=1)
            result.columns = ["city", "state", "temp"]
            result.index = result.index.astype(str)

            # Converting for temperature in Fahrenheit if needed
            # Conversion Helper Function
            def to_fahr(temp: float) -> float:
                return ((temp * 9) / 5) + 32

            if metric != True:
                result["temp"] = result["temp"].apply(to_fahr)

            insert_pred = """
            INSERT INTO {table}(
                month,
                city,
                state,
                mean
            ) VALUES%s
            """.format(table=table)

            execute_values(cur, insert_pred,
                           list(result.to_records(index=True)))
            conn.commit()
            result_json = result.to_json(indent=2)

        conn.close()
    return result_json