{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Introduction\nThis is my first kernel.And I challenged the analysis of time series data for the first time.\nI have learned a great deal in this competition.I also want to post something and post this Kernel.\nAs the race is over, I look forward to publishing a great solution.\n\n# Strategy\nScikit-learn implements a number of useful interpolation algorithms, but it did not seem to work in this case.\nMost weather data has a 24-hour cycle, so data around one day is useful.\nHowever, it is not suitable for interpolating short-time defects.\nTherefore, in the case of a short-time loss, Akima interpolation is performed, and for larger defects, interpolation is performed using the average of Akima interpolation with the preceding and following data."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# Import \nimport pandas as pd\nimport argparse\nimport sys\nimport numpy as np\nimport os\n\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\n\nimport matplotlib.pyplot as plt\n\nfrom datetime import date, timedelta\npath_data = \"/kaggle/input/ashrae-energy-prediction\"\npath_train = path_data + os.sep + \"train.csv\"\npath_test = path_data + os.sep + \"test.csv\"\npath_building = path_data + os.sep + \"building_metadata.csv\"\npath_weather_train = path_data + os.sep + \"weather_train.csv\"\npath_weather_test = path_data + os.sep + \"weather_test.csv\"\npath_weather_train_modified = \"weather_train_modified.csv\"\npath_weather_test_modified = \"weather_test_modified.csv\"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# reduce mem usage\ndef reduce_mem_usage(df, use_float16=False):\n    \"\"\" iterate through all the columns of a dataframe and modify the data type\n        to reduce memory usage.\n    \"\"\"\n    start_mem = df.memory_usage().sum() / 1024 ** 2\n    print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n\n    for col in df.columns:\n        if is_datetime(df[col]) or is_categorical_dtype(df[col]):\n            # skip datetime type or categorical type\n            continue\n        col_type = df[col].dtype\n\n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)\n            else:\n                if use_float16 and c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n        else:\n            df[col] = df[col].astype('category')\n\n    end_mem = df.memory_usage().sum() / 1024 ** 2\n    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Interpolate Full timestamp\ndef prep(df):\n    s = df[df[\"site_id\"] == 0][\"timestamp\"]\n    s.index = df[df[\"site_id\"] == 0][\"timestamp\"]\n    full_time = s.reindex(pd.date_range(s.min(), s.max(), freq=\"H\"))\n    ret = pd.DataFrame()\n    ret[\"timestamp\"] = full_time.index\n    # ret[\"site_id\"] = 0\n    ap = ret.copy()\n    sites = range(16)\n    for s in sites:\n        tmp = ap.copy()\n        tmp[\"site_id\"] = s\n        ret = ret.append(tmp)\n    ret = ret[ret[\"site_id\"].notna()]\n    ret = ret.merge(df, on=[\"site_id\", \"timestamp\"], how=\"left\")\n    return ret","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Fill Nan with the average of values within one week before and after.\ndef fill_mean(df, tar_col):\n    ilimit = 7\n    th = 4\n    df['{}_nan_group'.format(tar_col)] = (df[tar_col].isna() & df[tar_col].shift(1).notna()).where(\n        df[tar_col].isna()).cumsum()\n    df['{}_nan_count'.format(tar_col)] = df['{}_nan_group'.format(tar_col)].map(\n        df.groupby('{}_nan_group'.format(tar_col)).size())\n    for k in df.groupby('{}_nan_group'.format(tar_col)).groups.keys():\n        if len(df.iloc[df.groupby('{}_nan_group'.format(tar_col)).groups[k]]) < th:\n            continue\n        i = 1\n        while df.iloc[df.groupby('{}_nan_group'.format(tar_col)).groups[k] - 24 * i][[tar_col]].isna().any().values[0]:\n            i = i + 1\n        b = df.iloc[df.groupby('{}_nan_group'.format(tar_col)).groups[k] - 24 * i][tar_col].values\n        val = b\n        i = 1\n        if set(df.groupby('{}_nan_group'.format(tar_col)).groups[k] + 24 * i).issubset(df.index):\n            while df.iloc[df.groupby('{}_nan_group'.format(tar_col)).groups[k] + 24 * i][[tar_col]].isna().any().values[0]:\n                i = i + 1\n                if i > ilimit | set(df.groupby('{}_nan_group'.format(tar_col)).groups[k] + 24 * i).issubset(df.index):\n                    i = i - 1\n                    break\n            a = df.iloc[df.groupby('{}_nan_group'.format(tar_col)).groups[k] + 24 * i][tar_col].values\n            val = (a + b) / 2\n        df.loc[df.groupby('{}_nan_group'.format(tar_col)).groups[k], \"{}_mean\".format(tar_col)] = val\n    del df['{}_nan_group'.format(tar_col)]\n    del df['{}_nan_count'.format(tar_col)]\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Interpolate with average of the above average and Akima interpolation\ndef interpolate_col(df, tar_col):\n    cols = []\n    limit = 1000\n\n    m = \"mean\"\n    print(\"interpolate {} {}\".format(tar_col, m))\n    cols.append(\"{}_{}\".format(tar_col, m))\n    df = fill_mean(df, tar_col)\n\n    m = \"akima\"\n    print(\"interpolate {} {}\".format(tar_col, m))\n    cols.append(\"{}_{}\".format(tar_col, m))\n    df[\"{}_{}\".format(tar_col, m)] = df[tar_col].fillna(\n        df[tar_col].interpolate(method=m, limit_direction=\"both\", limit=limit))\n\n    df[\"{}_blend\".format(tar_col)] = df[cols].mean(axis=1)\n\n    return df, cols","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# serch modified and original feacher col \ndef search_weather_col(df, search_cols):\n    ret = []\n    cols = df.columns\n    for col in search_cols:\n        ret +=[s for s in cols if col in s]\n    return ret","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# plot modified and original feacher col \ndef plot_res(df, tar_col = 'air_temperature', year = 2016, month = 1, site = 15, t = \"train\"):\n#     out = \"wi\"\n#     os.makedirs(out, exist_ok=True)\n    cols = search_weather_col(df, [tar_col])\n    cols.sort(reverse=True)\n    s = date(year, month, 1)\n    e = s + timedelta(days=30)\n    tmp = df[\n        (df[\"timestamp\"] >= str(s)) &\n        (df[\"timestamp\"] < str(e)) &\n        (df[\"site_id\"] == site)\n    ]\n    # plt.plot(tmp.index, tmp[tar_col], label=\"original\")\n    for c in cols:\n        plt.plot(tmp.index, tmp[c], label=c)\n    plt.legend()\n    title = \"{}_y{}_m{}_site{}_{}\".format(tar_col, year, month, site, t)\n    plt.title(title)\n#     plt.savefig(out + os.sep + \"{}_y{}_m{}_site{}_{}.png\".format(tar_col, year, month, site, t))\n    plt.show()\n    plt.close(\"all\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def interpolate_weather(df):\n    df = prep(df)\n    dcols = []\n    df, cols = interpolate_col(df, 'air_temperature')\n    dcols.extend(cols)\n#     df, cols = interpolate_col(df, 'dew_temperature')\n#     dcols.extend(cols)\n#     df.loc[df[\"site_id\"] == 5, \"sea_level_pressure\"] = df[df[\"site_id\"] == 1][\"sea_level_pressure\"].values\n#     df, cols = interpolate_col(df, 'sea_level_pressure')\n#     dcols.extend(cols)\n    return df, dcols","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Debug\ndef debug(weather_test):\n    # Seems to work\n    # - air_temperature_y2017_m5_site15_test\n    # - air_temperature_y2017_m6_site15_test\n    # - air_temperature_y2018_m4_site7_test\n    # - air_temperature_y2018_m6_site15_test\n    # - air_temperature_y2018_m8_site9_test\n    # - air_temperature_y2018_m9_site1_test\n    # - air_temperature_y2018_m9_site7_test\n    print(\"Seems to work.\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2017, month=5, site=15, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2017, month=6, site=15, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=4, site=7, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=6, site=15, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=8, site=9, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=9, site=1, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=9, site=7, t=\"test\")\n    \n    # Seems to not work\n    # - air_temperature_y2017_m12_site7_test\n    # - air_temperature_y2018_m11_site1_test\n    # - air_temperature_y2018_m11_site5_test\n    # - air_temperature_y2018_m11_site7_test\n    # - air_temperature_y2018_m11_site12_test\n    print(\"Seems to not work.---\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2017, month=12, site=7, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=11, site=1, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=11, site=5, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=11, site=7, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=11, site=12, t=\"test\")\n    \n    # I can't say anything\n    # - air_temperature_y2017_m3_site7_test\n    # - air_temperature_y2018_m8_site7_test\n    # - air_temperature_y2018_m9_site5_test\n    # - air_temperature_y2018_m9_site11_test\n    print(\"I can't say anything.\")    \n    plot_res(weather_test, tar_col=\"air_temperature\", year=2017, month=3, site=7, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=8, site=7, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=9, site=5, t=\"test\")\n    plot_res(weather_test, tar_col=\"air_temperature\", year=2018, month=9, site=11, t=\"test\")\n    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# main\nweather_train = pd.read_csv(path_weather_train, parse_dates=['timestamp'])\nweather_train = reduce_mem_usage(weather_train, use_float16=True)\nweather_train, dcols = interpolate_weather(weather_train)\nweather_train.drop(dcols, axis=1, inplace=True)\nweather_train.to_csv(path_weather_train_modified, index=False)\n\nweather_test = pd.read_csv(path_weather_test, parse_dates=['timestamp'])\nweather_test = reduce_mem_usage(weather_test, use_float16=True)\nweather_test, dcols = interpolate_weather(weather_test)\nweather_test.drop(dcols, axis=1, inplace=True)\nweather_test.to_csv(path_weather_test_modified, index=False)\n\nprint(\"Debug.\")\ndebug(weather_test)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":1}