{"cells":[{"metadata":{"_uuid":"19255f3d4046137d5a54a0c8f40e35038eab7f80"},"cell_type":"markdown","source":"![](https://www.ibge.gov.br/templates/novo_portal_internas/imagens/logo_mobile.png)\n\n## The Brazilian Institute of Geography and Statistics or IBGE (Portuguese: Instituto Brasileiro de Geografia e Estatística) is the agency responsible for official collection of statistical, geographic, cartographic, geodetic and environmental information in Brazil. IBGE performs a decennial national census; questionnaires account for information such as age, household income, literacy, education, occupation and hygiene levels.\n\n### So get some statistic from [IBGE](https://www.ibge.gov.br/en/np-statistics/full-list-statistics.html)"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd \nimport os, glob","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"!wget -q -r ftp://ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/IPCA_15/Resultados_por_Subitem/2017/\n!wget -q -r ftp://ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/IPCA_15/Resultados_por_Subitem/2018/\n!mkdir cache\n!for z in ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/IPCA_15/Resultados_por_Subitem/2017/*.zip; do unzip -qq \"$z\" -d cache; done\n!for z in ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/IPCA_15/Resultados_por_Subitem/2018/*.zip; do unzip -qq \"$z\" -d cache; done\n!rm -r ftp.ibge.gov.br","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"d79da36c9d6b3b4c6285048b2d429af40cab57a0"},"cell_type":"code","source":"filenames = glob.glob('./cache/*.xls')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0337b65ec3e0e6d83e0b08f5b1a0f8b8c98cb3cf"},"cell_type":"markdown","source":"### Extended National Consumer Price Index 15 - IPCA-15\nThe National System of Consumer Price Indexes - SNIPC produces - continuously and systematically - consumer price indexes and, in this production, has, as data collection units, commercial and service-rendering establishments, concessionaires of public Internet services."},{"metadata":{"trusted":true,"_uuid":"9250b052fde325ebb0d4109e850a4469acf21546"},"cell_type":"code","source":"ipca15 = pd.read_excel(filenames[0], index_col=[0], header=[4], skiprows=[5])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b627b14ffbbcec17dc67c17f17282838c341e1bd"},"cell_type":"code","source":"# Select some rows\ncols_cat = [' ÍNDICE GERAL',       # 'GENERAL INDEX'\n        ' ALIMENTAÇÃO E BEBIDAS',     # 'FOOD AND BEVERAGES'\n        ' HABITAÇÃO',                 # 'HOUSING',\n        ' IOGURTE E BEBIDAS LÁCTEAS', # 'YOGURT AND DAIRY BEVERAGES',\n        ' ARTIGOS DE RESIDÊNCIA',     # 'RESIDENCE ARTICLES',\n        ' VESTUÁRIO',                 # 'CLOTHING',\n        ' TRANSPORTES',               # 'TRANSPORTES',\n        ' SAÚDE E CUIDADOS PESSOAIS', # 'HEALTH AND PERSONAL CARE',\n        ' DESPESAS PESSOAIS',         # 'PERSONAL EXPENSES',\n        ' EDUCAÇÃO',                  # 'EDUCATION',\n        ' COMUNICAÇÃO',               # 'COMMUNICATION'\n        ]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"499c87af1d6e75754fb58c091aabb63b7f8fbe5d"},"cell_type":"code","source":"mth = filenames[0].split('_')[1][:6]\nprint(f\"Month: {mth[:4]+'-'+mth[-2:]}\")\n# Cols meaning :\n# Monthly variation by groups (%)\n# Rio de Janeiro, Porto Alegre, Belo Horizonte, Recife,\n# São Paulo, Brasília, Belém, Fortaleza, Salvador, Curitiba, Goiânia\nipca15.drop_duplicates().loc[cols_cat, :]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4591b742155495d373f1df67e51428bf49fa4c3d"},"cell_type":"code","source":"# And now collect colomn 'NACIONAL' in DataFrame by month\nIPCA15 = pd.DataFrame(index=pd.DatetimeIndex(freq='M', start='2017-01-01', end='2018-12').to_period('M')\n                          , columns=cols_cat)\n\nfor file in filenames:\n    mth = file.split('_')[1][:6]\n    idx = pd.to_datetime(mth[:4]+'-'+mth[-2:]).to_period('M')\n    tmp = pd.read_excel(file, index_col=[0], header=[4], skiprows=[5])\n    IPCA15.loc[idx,:] = tmp.drop_duplicates().loc[cols_cat, 'NACIONAL'].values\nIPCA15[cols_cat] = IPCA15[cols_cat].astype(np.float32)\nIPCA15.index.name='eval month'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"eb8613c9c29f80fbed39353fc34d7a623af33953"},"cell_type":"code","source":"IPCA15.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"39856ddcab179af36b9ba3a784f10afef086b34b"},"cell_type":"code","source":"# read the data\ndf = pd.read_csv('../input/train.csv', parse_dates=['first_active_month'])\ntrns = pd.read_csv('../input/historical_transactions.csv',\n                   parse_dates=['purchase_date'], infer_datetime_format=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6e9ae5c31cc5446afc6ead4ecaded57095c5bfd0"},"cell_type":"code","source":"cols = ['card_id', 'month_lag', 'purchase_date']\ndf = pd.merge(df, trns[cols].groupby('card_id').first(), on='card_id', left_index=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1861a29c1bbf08b7522eab9120f623e8756095bb"},"cell_type":"code","source":"df['eval month'] = df.purchase_date - df.month_lag.astype('timedelta64[M]')\ndf['eval month'] = df['eval month'].dt.to_period('M')\ndf.drop(['month_lag', 'purchase_date'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9760d9fce6a05d6d6dd3831828f6e0266006367f"},"cell_type":"code","source":"df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f6863702d7862f79944eb930be914bfea302a6bd"},"cell_type":"code","source":"df_stats = pd.merge(df, IPCA15.reset_index(), on='eval month', left_index=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"0df0582e8bd73bed6ff27434a5be6955635eea74"},"cell_type":"code","source":"# check correlation with target, it's 99% coincidence, but mb usefull\ndf_stats.corr().iloc[4:,3:4]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"47de575f0c72b75d76fe422dabc3ecaeecbb8857"},"cell_type":"code","source":"IPCA15.to_csv('IPCA15.csv')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"_kg_hide-input":true,"trusted":true,"_uuid":"752ad1a2b78dff20d78357f08546b6fd5715f24b"},"cell_type":"code","source":"!rm -r cache\n!wget -q -r -l 1 ftp://ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/INPC/Resultados_por_Subitem/ -A zip\n!wget -q -r ftp://ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/INPC/Resultados_por_Subitem/2017    \n!mkdir cache\n!for z in ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/INPC/Resultados_por_Subitem/2017/*.zip; do unzip -qq \"$z\" -d cache; done\n!for z in ftp.ibge.gov.br/Precos_Indices_de_Precos_ao_Consumidor/INPC/Resultados_por_Subitem/*.zip; do unzip -qq \"$z\" -d cache; done\n!rm -r ftp.ibge.gov.br","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"894092d34f03da7eb4932b36294bab3b6a6c94ff"},"cell_type":"markdown","source":"### National Consumer Price Index - INPC\nThe National System of Consumer Price Indexes - SNIPC continuously and systematically produces the National Consumer Price Index - INPC, aimed at the correction of the purchasing power of salaries by means of the measurement of price changes in the basket of the lowest-income salaried population. That income range was created in order to guarantee a 50% coverage of families with a salaried reference person and living in urban areas included in the National System of Consumer Price Indexes - SNIPC . "},{"metadata":{"trusted":true,"_uuid":"0da539d02edd3c8016833d26c110ebc7f6251243"},"cell_type":"code","source":"filenames = glob.glob('./cache/*.xls')\n# And now collect colomn 'NACIONAL' in DataFrame by month\nINPC = pd.DataFrame(index=pd.DatetimeIndex(freq='M', start='2017-01-01', end='2018-12').to_period('M')\n                          , columns=cols_cat)\n\nfor file in filenames:\n    mth = file.split('_')[1][:6]\n    idx = pd.to_datetime(mth[:4]+'-'+mth[-2:]).to_period('M')\n    tmp = pd.read_excel(file, index_col=[0], header=[4], skiprows=[5])\n    INPC.loc[idx,:] = tmp.drop_duplicates().loc[cols_cat, 'NACIONAL'].values\nINPC[cols_cat] = INPC[cols_cat].astype(np.float32)\nINPC.index.name='eval month'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"10551f5d6ccb703013f8865038527101a5e3e912"},"cell_type":"code","source":"df_stats = pd.merge(df, INPC.reset_index(), on='eval month', left_index=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"55300340ac832e1d19f62c7af3bd4da40820e0da","scrolled":false},"cell_type":"code","source":"df_stats.corr().iloc[4:,3:4]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0a341c4b78c8b980c3c65780f12f2bb87150aac2"},"cell_type":"code","source":"INPC.to_csv('INPC.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4702d2fd419dafbb9493f6e2014159ed65295c2b","_kg_hide-input":true},"cell_type":"code","source":"!rm -r cache","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"54697013228ac5e1272478824f9b33426befa7af"},"cell_type":"markdown","source":"## If you finded some worthwhile method using stats - inform please !"}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}