{"cells":[{"metadata":{},"cell_type":"markdown","source":"### V19: RCC (team + rafa) + SE + CENSO (team v2)"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import pandas as pd\nimport re\nimport gc","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def keep_matching_cols(df1, df2):\n    keep_cols = list(set(df1.columns).intersection(set(df2.columns)))\n    df1 = df1[keep_cols]\n    df2 = df2[keep_cols]\n    print(len(set(df1.columns) - set(df2.columns)) , len(set(df2.columns) - set(df1.columns)))\n    return df1, df2","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Pre-Procesamiento RCC"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"rcc_train = pd.read_csv(\"/kaggle/input/interbank20/rcc_train.csv\")\nrcc_test = pd.read_csv(\"/kaggle/input/interbank20/rcc_test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"bins = [-1, 0, 10, 20, 30, 60, 90, 180, 360, 720, float(\"inf\")]\nrcc_train[\"condicion_bins\"] = pd.cut(rcc_train.condicion, bins)\nrcc_test[\"condicion_bins\"] = pd.cut(rcc_test.condicion, bins)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def makeCt(df, c, aggfunc=sum):\n    try:\n        ct = pd.crosstab(df.key_value, df[c].fillna(\"N/A\"), values=df.saldo, aggfunc=aggfunc)\n    except:\n        ct = pd.crosstab(df.key_value, df[c], values=df.saldo, aggfunc=aggfunc)\n    ct.columns = [f\"{c}_{aggfunc.__name__}_{v}\" for v in ct.columns]\n    return ct","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = []\ntest = []\naggfuncs = [len, sum, min, max]\nfor c in rcc_train.drop([\"codmes\", \"key_value\", \"saldo\", \"condicion\"], axis=1):\n    print(\"haciendo\", c)\n    train.extend([makeCt(rcc_train, c, aggfunc) for aggfunc in aggfuncs])\n    test.extend([makeCt(rcc_test, c, aggfunc) for aggfunc in aggfuncs])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = pd.concat(train, axis=1)\ntest = pd.concat(test, axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train, test = keep_matching_cols(train, test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test = test.rename(columns = lambda x:re.sub('[^A-Za-z0-9_-]+', '', x))\ntrain = train.rename(columns = lambda x:re.sub('[^A-Za-z0-9_-]+', '', x))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def pre_rcc(df):\n    agg_key_mes = {\n        'condicion_max'     : ('condicion', 'max'),\n        'condicion_menos30' : ('condicion', lambda x: int(x.max() <= 30)),\n        'condicion_menos60' : ('condicion', lambda x: int(x.max() <= 60 & x.max() > 30)),\n        'condicion_menos90' : ('condicion', lambda x: int(x.max() <= 90 & x.max() > 60)),\n        'condicion_mas90'   : ('condicion', lambda x: int(x.max() > 90)),\n        'ifinan_count'      : ('cod_instit_financiera', 'count'),\n        'rdirecto_max'      : ('RIESGO_DIRECTO', 'max'),\n        'cdeudor_max'       : ('COD_CLASIFICACION_DEUDOR', 'max'),\n        'saldo_sum'         : ('saldo', lambda x: x.abs().sum()),\n    }\n\n    agg_key = {\n        'condicion_max_count'     : ('condicion_max', 'cumsum'),\n        'condicion_menos30_count' : ('condicion_menos30', 'cumsum'),\n        'condicion_menos60_count' : ('condicion_menos60', 'cumsum'),\n        'condicion_menos90_count' : ('condicion_menos90', 'cumsum'),\n        'condicion_mas90_count'   : ('condicion_mas90', 'cumsum'), \n        'ifinan_count_sum'        : ('ifinan_count_diff', 'cumsum'), \n    }\n    \n    df.drop(columns=['tipo_credito', 'PRODUCTO'], inplace=True)\n    df['codmes'].replace(sorted(df.codmes.unique()), list(range(1,13)), inplace=True)\n\n    df = df.groupby(['key_value', 'codmes']).agg(**agg_key_mes).reset_index()\n\n    df['riesgo_diff']        = df.rdirecto_max ** 2 - df.cdeudor_max ** 2\n    df['condicion_max_diff'] = df.groupby(['key_value']).condicion_max.diff().fillna(df['condicion_max'])\n    df['ifinan_count_diff']  = df.groupby(['key_value']).ifinan_count.diff().abs().fillna(df['ifinan_count'])\n\n    df2 = df.groupby(['key_value']).agg(**agg_key)\n\n    df = pd.concat([df, df2], axis = 1).pivot(index = 'key_value', columns = 'codmes').reset_index().set_index('key_value')\n    \n    df.columns = [f'{f}_{s}' if s != '' else f'{f}'  for f, s in df.columns]    \n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = pre_rcc(rcc_train).join(train)\ntest = pre_rcc(rcc_test).join(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del rcc_train, rcc_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Pre-Procesamiento SUNAT"},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"sunat_train = pd.read_csv(\"/kaggle/input/interbank20/sunat_train.csv\")\nsunat_test= pd.read_csv(\"/kaggle/input/interbank20/sunat_test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def pre_sunat(df):\n    agg_key = {\n        'tipcontribuyente_count'    : ('tipcontribuyente', 'nunique'),\n        'tippersona_count'          : ('tippersona', 'nunique'),\n        'ciiu_count'                : ('ciiu', 'nunique'),\n        'ubigeo_count'              : ('ubigeo', 'nunique'),\n        'condiciondomicilio_count'  : ('condiciondomicilio', 'nunique'),       \n        'estadocontribuyente_count' : ('estadocontribuyente', 'nunique'),\n        'codvia_count'              : ('codvia', 'nunique'),\n        'codzona_count'             : ('codzona', 'nunique'),       \n        'contabilidad_count'        : ('contabilidad', 'nunique'),  \n        'facturacion_count'         : ('facturacion', 'nunique'),       \n        'domiciliado_count'         : ('domiciliado', 'nunique'),  \n        'comercioexterior_count'    : ('comercioexterior', 'nunique'),       \n        'cargorele_count'           : ('cargorele', 'nunique'),          \n        'codentidadtributo_count'   : ('codentidadtributo', 'nunique'),\n        'fecalta_min'               : ('fecalta', 'min'),\n        'actividad_vigente_flag'    : ('fecbaja', lambda x: int(x.isna().count() > 0)),        \n        'actividad_finalizada_flag' : ('fecbaja', lambda x: int(x.count() > 0)),       \n     }\n    \n    df1 = df.groupby(['key_value']).agg(**agg_key)\n    df1['actividad_count'] = df1.iloc[:, 6:14].sum(axis=1)\n    \n#     df2 = pd.crosstab(df.key_value, df.estadotributo).add_prefix('estadotributo_')\n#     df3 = pd.crosstab(df.key_value, df.ciiu).add_prefix('ciiu_')\n\n#     df = df1.join(df2).join(df3)\n    return df1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = train.join(pre_sunat(sunat_train))\ntest = test.join(pre_sunat(sunat_test))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del sunat_train, sunat_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train, test = keep_matching_cols(train, test)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Incluir SE & Censo"},{"metadata":{"trusted":true},"cell_type":"code","source":"se_train = pd.read_csv(\"/kaggle/input/interbank20/se_train.csv\", index_col=\"key_value\")\nse_test = pd.read_csv(\"/kaggle/input/interbank20/se_test.csv\", index_col=\"key_value\")\ncenso_train = pd.read_csv(\"/kaggle/input/interbank20/censo_train.csv\", index_col=\"key_value\")\ncenso_test = pd.read_csv(\"/kaggle/input/interbank20/censo_test.csv\", index_col=\"key_value\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"se_train.drop(columns=['cod_ubi'], inplace=True)\nse_test.drop(columns=['cod_ubi'], inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = train.join(se_train).join(censo_train)\ntest = test.join(se_test).join(censo_test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del se_train, se_test, censo_train, censo_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('Train Columns:', len(set(train.columns)), '- Test Columns:', len(set(test.columns)))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Genero Output"},{"metadata":{"trusted":true},"cell_type":"code","source":"train.to_csv('train-v20.gz', compression = 'gzip')\ntest.to_csv('test-v20.gz', compression = 'gzip')","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}