{"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_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# ok submission is so hard\n# if you are here. it would be great :)","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import pandas as pd\n\n# assume you have output like this\npredict_df = pd.DataFrame(\n    index=[\n        (\"Bangkok\",    pd.to_datetime(\"2020-03-18 12:00:00+07:00\")), # index[0] = city, index[1] = time\n        (\"Bangkok\",    pd.to_datetime(\"2020-03-18 18:00:00+07:00\")),\n        (\"Chiangmai\",  pd.to_datetime(\"2020-03-18 12:00:00+07:00\")),\n        (\"Chiangmai\",  pd.to_datetime(\"2020-03-18 18:00:00+07:00\")),\n        (\"Chiangmai\",  pd.to_datetime(\"2020-04-18  0:00:00+07:00\")),\n        (\"Chiangmai\",  pd.to_datetime(\"2020-04-18  6:00:00+07:00\"))\n    ],\n    data = {\n        # columns name not matter, order is matter \n        \"pm25_next0hour\": [30, 40, 50, 60, 70, 80], # column 0 is not used. but for easy calculate time\n        \"pm25_next1hour\": [31, 41, 51, 61, 71, 81], # predict_df[1] = pm2.5 of +1 hour from index[1]\n        \"pm25_next2hour\": [31, 41, 51, 61, 71, 81], # predict_df[2] = pm2.5 of +2 hour from index[1]\n        # .\n        # . until next 72 hour\n        # .\n        \"pm25_next72hour\": [31, 41, 51, 61, 71, 81],\n    }\n)\n\n# expect column in order 0 hour, 1 hour, 2 hour ... 72 hour\n# bacuase we will use as index=i mean time[i] + i hour\npredict_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import datetime # pd.datetime is datetime module\n\n# extract city and date \n# you must seperate city and date for calculate time addition\ndef parse_submission(df: pd.DataFrame, city_and_time_column: str) -> pd.DataFrame:\n    list_city: pd.Series[str] = df[city_and_time_column].map(lambda s: s[:-19]) # extract only city\n    list_time: pd.Series[datetime] = pd.to_datetime(df[city_and_time_column].map(lambda s: s[-19:None])) # extract only time\n    return pd.DataFrame(\n        index=pd.MultiIndex.from_arrays([list_city, list_time], names=('city', 'time')),\n        data={'idx': df[\"Unnamed: 0\"].to_numpy()}\n    )\n\nDATA_PATH = \"../input/2110446-data-science-and-data-engineering-2021/DS_kaggle_edited_v3/\"\nmissing_df = parse_submission(pd.read_csv(f\"{DATA_PATH}/missing_index.csv\"), 'Missing date')\noutput_df = parse_submission(pd.read_csv(f\"{DATA_PATH}/output_index.csv\"), 'predicted timestamp')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# you can see (city, time) is index now\n# it easy to map with predict_df above\nmissing_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# output index same as mission\noutput_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# we will map output_df to predict_df\n\n# allocate predict column on output_df\nPM25_OUTPUT_FIELD = 'pm25'\noutput_df[PM25_OUTPUT_FIELD] = 30.0\nindex_of_pm25 = list(output_df.columns).index(PM25_OUTPUT_FIELD)\n\n# output_df gurantee will be chunk of 72 hour consecutive start from (t+1 ... t+72)\n# loop in chunk of 72 hour\nfor i in range(0, len(output_df), 72):\n    expected_output_index = list(output_df.index[i: i+72].to_numpy())\n    \n    \n    city: str = expected_output_index[0][0] # all city will be same\n    \n    # time is in incrasing order start from t+1, t+2, ..., t+72. but we need t+0 so (t+1)-1 = t\n    time: datetime = expected_output_index[0][1] - pd.DateOffset(hours=1) \n    \n    # predict_df.loc[(city, time), 1:72] = [[time+1hour, time+2hour, ..., time+72hour]], read column axis\n    # output_df.iloc[i:i+72, index_of_pm25], write on row axis\n    output_df.iloc[i:i+72, index_of_pm25] = predict_df.loc[(city, time), 1:72].to_numpy() \n    \n    # if you predict not contain all expected output. it will show error as below","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import numpy as np\n\n# time to remove mission columns\nremove_missing_output_df = output_df[~output_df.index.isin(missing_df.index)] # remove all output that match missing index\nassert 448554 == remove_missing_output_df.shape[0]\n\n# rename column to match kaggle\nkaggle_output_df = pd.DataFrame(\n    index=np.arange(448554), \n    data={'Predicted': remove_missing_output_df[PM25_OUTPUT_FIELD].to_numpy()\n})\nkaggle_output_df.to_csv('final_submission.csv')\nkaggle_output_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]}]}