{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Imports"},{"metadata":{"trusted":false},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\n\nimport xgboost as xgb\n\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import matthews_corrcoef\nfrom sklearn.metrics import confusion_matrix\n\nimport traceback\n\nimport re\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"df_names = [\"S\"+str(i) for i in range(52)]\n[df_names.append(\"L\"+str(i)) for i in range(4)] \ndf_names.append(\"all\")\ndatabase_file = \"bosch_data.h5\"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Load Data from CSV Files"},{"metadata":{},"cell_type":"markdown","source":"The idea is, since the read_csv function in pandas is very slow, and it gets even slower if you want to engenieer features, to store the dataset in a HDF5 table in a file called data.h5\n\nThe HDF5 table will have 57 subtables. One for each of the 52 stations, one for each of the 4 lines and one for all stations. In the sub tables only the engineered data is stored. "},{"metadata":{},"cell_type":"markdown","source":"To load the data from the csv files, three helper functions were needed. Each of these functions gets a chunk from the according table and a station or line name. It can also be \"all\".\nThen each function processes the sub_chunk (e.g. only the chunk with station S0) accordingly\n\n* process_numeric_chunk: adds the min, max, and mean value of the numerical data for the station or line. The Response will be stored in the \"all\" table .\n* process_date_chunk: adds min, max, first, last, number of NAs and duration to each line. Each timedata will also be stored in the station\n* process_cateforical: just removes empty rows and returns the data "},{"metadata":{"trusted":false},"cell_type":"code","source":"def process_numeric_chunk(chunk_numeric, name):\n    # select feature columns \n    if name == \"all\":\n        sub_columns = chunk_numeric.columns[chunk_numeric.columns.str.contains(r'L\\d+_S\\d+_F\\d+.*')] \n    else:\n        sub_columns = chunk_numeric.columns[chunk_numeric.columns.str.contains(r'.*'+name+'_.*')]\n\n\n    # select correct subchunk and drop empty rows from subchunk\n    sub_chunk = chunk_numeric[sub_columns]\n    sub_chunk.dropna(how=\"all\", inplace=True)\n\n    # engineer features\n    sub_chunk[name+\"_numerical_min\"] = sub_chunk.min(axis=1)\n    sub_chunk[name+\"_numerical_max\"] = sub_chunk.max(axis=1)\n    sub_chunk[name+\"_numerical_mean\"] = sub_chunk.mean(axis=1)\n\n    # only keep engineered features for lines and all\n    if \"L\" ==name[0] or name==\"all\":\n        \n        sub_chunk.drop(sub_chunk.columns[sub_chunk.columns.str.contains(r'L\\d+_S\\d+_F\\d+.*')], inplace = True, axis=1)\n\n    # add Response Column to all\n    if name == \"all\":\n        sub_chunk[\"Response\"] = chunk_numeric[\"Response\"]\n\n    return sub_chunk\n\n\ndef process_row(row):\n    first = row[row.first_valid_index()]\n    last = row[row.last_valid_index()]\n    NAs = row.isnull().sum()\n    return first, last, NAs\n\n\ndef process_date_chunk(chunk_date,name):\n    if name == \"all\":\n        sub_columns = chunk_date.columns[chunk_date.columns.str.contains(r'L\\d+_S\\d+_D\\d+.*')] \n    else:\n        sub_columns = chunk_date.columns[chunk_date.columns.str.contains(r'.*'+name+'_.*')]\n\n    # select correct subchunk and drop empty rows from subchunk \n    sub_chunk = chunk_date[sub_columns]\n    sub_chunk.dropna(how=\"all\", inplace=True)\n    if sub_chunk.empty:\n        return sub_chunk\n    if name[0] == \"S\":\n        return sub_chunk\n\n    sub_chunk[name+\"_date_min\"] = sub_chunk.min(axis=1)\n    sub_chunk[name+\"_date_max\"] = sub_chunk.max(axis=1)    \n    sub_chunk[name+\"_first\"], sub_chunk[name+\"_last\"], sub_chunk[name+\"_NAs\"] = zip(*sub_chunk[sub_columns].apply(process_row, axis=1))\n    sub_chunk[name+\"_time\"]=sub_chunk[name+\"_date_max\"]-sub_chunk[name+\"_date_min\"]\n    sub_chunk.drop(sub_chunk.columns[sub_chunk.columns.str.contains(r'L\\d+_S\\d+_D\\d+.*')], inplace = True, axis=1)\n    return sub_chunk\n\n\ndef process_categorical_chunk(chunk_categorical, name):\n    if name[0] == \"S\":\n        sub_columns = chunk_categorical.columns[chunk_categorical.columns.str.contains(r'.*'+name+'_.*')]\n        sub_chunk = chunk_categorical[sub_columns]\n        sub_chunk.dropna(how=\"all\", inplace=True)\n        return sub_chunk\n    return None\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In the following block the programm will always take a chunk out of each dataset process it, join the processed data and append it to the according HDF table. **This will take quite some time. But will make future experiments much easier and faster to load the data.** It took me on my machine around half an hour. The resulting file will have a size of approximatly 10 GB."},{"metadata":{"trusted":false},"cell_type":"code","source":"store =  pd.HDFStore(database_file)\n\nchunksize = 10 ** 5\n\nnumber_of_lines_to_load=12*10**5\n\ndata_dir = \"input/\"\n\nheader_numeric = pd.read_csv(data_dir+\"train_numeric.csv.zip\", nrows= 1 ,engine=\"c\",compression=\"zip\").columns\nheader_date = pd.read_csv(data_dir+\"train_date.csv.zip\", nrows= 1 ,engine=\"c\",compression=\"zip\").columns\nheader_categorical = pd.read_csv(data_dir+\"train_categorical.csv.zip\", nrows= 1 ,engine=\"c\",compression=\"zip\").columns\n\nconvert = lambda error: float(error[1:]) if error != \"\"  else np.nan\nconverter = {cat:convert for cat in header_categorical[1:]}\ntry: \n    for loaded in range(1,number_of_lines_to_load,chunksize):\n        \n\n        # load data\n        chunk_numeric = pd.read_csv(data_dir+\"train_numeric.csv.zip\", skiprows = loaded, nrows= chunksize ,engine=\"c\", names = header_numeric, index_col=\"Id\",compression=\"zip\")\n        chunk_date = pd.read_csv(data_dir+\"train_date.csv.zip\", skiprows = loaded, nrows= chunksize, engine=\"c\", names = header_date, index_col=\"Id\",compression=\"zip\")\n        chunk_categorical = pd.read_csv(data_dir+\"train_categorical.csv.zip\", skiprows = loaded, nrows= chunksize , \n            names = header_categorical,index_col=\"Id\",dtype=\"object\",engine=\"c\", converters=converter,compression=\"zip\")# dtype=\"float\",  sep = r',T*')\n\n        for name in df_names:            \n            \n            # process data\n            numeric_processed = process_numeric_chunk(chunk_numeric, name)\n            date_processed = process_date_chunk(chunk_date, name)\n            categorical_processed = process_categorical_chunk(chunk_categorical, name)\n            \n            # join processed data\n            processed_joined = numeric_processed.join(date_processed, on=\"Id\")\n            if categorical_processed is not None:\n                processed_joined = processed_joined.join(categorical_processed, on=\"Id\")\n\n            # append to previously loaded data\n            if processed_joined is not None:\n                store.append(name,processed_joined.iloc[:-1,:])\n        # early stopping criteria if loading takes too long\n        print(loaded)\n        if loaded>=number_of_lines_to_load:\n            break\n\n    store.close()\nexcept Exception as e:\n    traceback.print_exc()\n\n    store.close()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next there will be some additional features added. First the time difference between each part the last (time_dt) and the next(time_idt) is measured. Then same is done with the difference in NAs.\nThe column P1 ord and group_len are connected. P1 is a bool value and describes wether the row is part of a bigger part. For example if the next three parts have the same starting time P1 will be True for all of them. How many parts are in this bigger part is represented by the attribute group_len. And ord describes what number the part has in a subpart."},{"metadata":{"trusted":false},"cell_type":"code","source":"store =  pd.HDFStore(database_file)\ntry:\n    for name in df_names[-5:]:\n        chunk = store.get(name)\n        chunk[name+\"_time_dt\"] = chunk[name+\"_time\"]- chunk[name+\"_time\"].shift(1)\n        chunk[name+\"_time_idt\"]= chunk[name+\"_time\"]- chunk[name+\"_time\"].shift(-1)\n        chunk[name+\"_NAs_dt\"] = chunk[name+\"_NAs\"]- chunk[name+\"_NAs\"].shift(1)\n        chunk[name+\"_NAs_idt\"]= chunk[name+\"_NAs\"]- chunk[name+\"_NAs\"].shift(-1)\n        \n        store.put(name,value=chunk, format=\"table\")\n\n    table_date = store.get(\"all\")\n\n    table_date[\"P1\"] = np.logical_or(table_date[\"all_first\"] == table_date[\"all_first\"].shift(1), table_date[\"all_first\"] == table_date[\"all_first\"].shift(-1))\n\n    i = 1\n    p1 = table_date[table_date[\"P1\"]==True]\n    p1[\"ord\"] = np.logical_and(table_date[\"P1\"],table_date[\"P1\"].shift(1)==0)*1\n\n    while(len(p1[(p1[\"ord\"]==0)])>0):\n        left = p1[(p1[\"ord\"]==0)&(p1[\"ord\"].shift(1) ==i)]\n        left[\"ord\"]=i+1\n        p1.update(left)\n        i+=1\n    p1[\"group_len\"] = np.nan\n    p1[\"group_len\"] = p1[p1[\"ord\"].shift(-1)==1][\"ord\"]\n    p1.fillna(method=\"bfill\", inplace=True)\n\n    table_date[\"ord\"] = 0\n    table_date[\"group_len\"] = 0\n    table_date.update(p1)\n    table_date[\"P1\"]=table_date[\"P1\"].astype(bool)\n    store.put(\"all\",value=table_date, format=\"table\")\nexcept Exception as e:\n    traceback.print_exc()\n    store.close()\n\n    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Loading data"},{"metadata":{},"cell_type":"markdown","source":"When we stored all the data in the HDF table we have to load it again. Because all of the HDF will probably be too big for memory. We need to select which tables we load first, then we can only load the columns of interest and concat to a DataFrame."},{"metadata":{"trusted":false},"cell_type":"code","source":"columns = ['Response','P1', 'ord', 'group_len', 'all_first','L3_time',\n                   #'S32_mean', 'S33_mean', 'S38_mean',\n                   'L3_first', 'L3_last', 'L3_time_dt', 'L3_time_idt',\n                   'L3_NAs_dt', 'L3_NAs_idt',\n                   'L3_S32_F3851', 'L3_S32_F3853', 'L3_S32_F3854',\n                   'L1_S24_F1846', 'L3_S32_F3850', 'L1_S24_F1695', \n                   'L1_S24_F1632', 'L3_S33_F3855', 'L1_S24_F1604',\n                   'L3_S29_F3407', 'L3_S33_F3865', 'L3_S38_F3952', \n                   'L1_S24_F1723']\n\n# select which table to load from HDF\nload = {\"all\":[]}\nfor col in columns:\n    if col in ['Response','P1', 'ord', 'group_len']:\n        load[\"all\"].append(col)\n    for name in df_names:\n        # if feature is from station get the station \n        if name +\"_\" in col:\n            if name not in load.keys():\n                load[name]=[]\n            load[name].append(col)\n\nstore =  pd.HDFStore(database_file)\ntable = pd.DataFrame()\ntry:\n    for name in load.keys():\n\n        if table.empty:\n            table = store.select(name, columns = load[name])\n        else:\n            table = pd.concat([table,store.select(name, columns=load[name])], axis=1)\n    store.close()\nexcept Exception as e:\n    traceback.print_exc()\n    store.close()\ntable\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Training with xgboost"},{"metadata":{},"cell_type":"markdown","source":"First we need to spilt out data in Training and test data and initialize the DMatrices for xgboost."},{"metadata":{"trusted":false},"cell_type":"code","source":"X_train, X_test, y_train, y_test = train_test_split(table.drop([\"Response\"], axis=1),table[\"Response\"],test_size=0.2, random_state=4, shuffle=True)\nX_train, X_val, y_train, y_val = train_test_split(X_train,y_train,test_size=0.2, random_state=4, shuffle=True)\n\ndtrain  = xgb.DMatrix(X_train,y_train)\ndval = xgb.DMatrix(X_val, y_val)\ndtest = xgb.DMatrix(X_test, y_test)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Next we need to set the hyper parameters for xgboost and start training."},{"metadata":{"trusted":false},"cell_type":"code","source":"num_round = 65\nevallist = [ (dtrain, 'train'),(dval, 'eval')]\nparam = {\"nthread\": 8, \"max_depth\" : 10, \"eta\":0.1, \"subsample\" : 0.9, \"colsample_bytree\" : 0.5,\n                  \"objective\": \"binary:hinge\", \"booster\":\"gbtree\"}\nbst = xgb.train(param, dtrain, num_round,evallist,early_stopping_rounds = 5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Last the prediction is calculated as well as the matthews correlation coefficiant and the confusion matrix"},{"metadata":{"trusted":false},"cell_type":"code","source":"y_pred = bst.predict(dtest)\n#prob = np.sort(y_pred)[-int(len(y_pred)*0.006)]\n#y_pred = y_pred>prob\nprint(\"Matthews Correlation Coeffinciant:\",matthews_corrcoef(y_test,y_pred))\nc = confusion_matrix(y_test,y_pred)\nc","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"xgb.plot_importance(bst, max_num_features=20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"","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":4}