ParametrosAPI_IBGE.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  tabela\n",
      "0   2093\n",
      "https://servicodados.ibge.gov.br/api/v3/agregados/tabela/metadados\n"
     ]
    },
    {
     "ename": "KeyError",
     "evalue": "\"Key 'id' not found. To replace missing values of 'id' with np.nan, pass in errors='ignore'\"",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "File \u001b[1;32mc:\\Users\\Alexandre Rebello\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\json\\_normalize.py:398\u001b[0m, in \u001b[0;36mjson_normalize.<locals>._pull_field\u001b[1;34m(js, spec, extract_record)\u001b[0m\n\u001b[0;32m    397\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m--> 398\u001b[0m         result \u001b[38;5;241m=\u001b[39m \u001b[43mresult\u001b[49m\u001b[43m[\u001b[49m\u001b[43mspec\u001b[49m\u001b[43m]\u001b[49m\n\u001b[0;32m    399\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m e:\n",
      "\u001b[1;31mKeyError\u001b[0m: 'id'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[7], line 43\u001b[0m\n\u001b[0;32m     41\u001b[0m  dataT \u001b[38;5;241m=\u001b[39m responseT\u001b[38;5;241m.\u001b[39mjson()\n\u001b[0;32m     42\u001b[0m  \u001b[38;5;66;03m#print(dataT)\u001b[39;00m\n\u001b[1;32m---> 43\u001b[0m  df_categ \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mjson_normalize\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdataT\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m     44\u001b[0m \u001b[43m     \u001b[49m\u001b[43mrecord_path\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mclassificacoes\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mcategorias\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m     45\u001b[0m \u001b[43m     \u001b[49m\u001b[43mmeta\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mid\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mclassificacoes\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mid\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mclassificacoes\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m,\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mnome\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m     46\u001b[0m \u001b[43m     \u001b[49m\u001b[43mmeta_prefix\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mtabela_\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\n\u001b[0;32m     47\u001b[0m \u001b[43m     \u001b[49m\u001b[43mrecord_prefix\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mcateg_\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\n\u001b[0;32m     48\u001b[0m \u001b[43m     \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m     49\u001b[0m \u001b[38;5;66;03m# print(df_categ)\u001b[39;00m\n\u001b[0;32m     50\u001b[0m          \u001b[38;5;66;03m#Variaveis x classificacao\u001b[39;00m\n\u001b[0;32m     52\u001b[0m  df_var \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mjson_normalize(dataT,\n\u001b[0;32m     53\u001b[0m      record_path\u001b[38;5;241m=\u001b[39m[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mvariaveis\u001b[39m\u001b[38;5;124m'\u001b[39m],\n\u001b[0;32m     54\u001b[0m      meta\u001b[38;5;241m=\u001b[39m[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mid\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnome\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mURL\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mpesquisa\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124massunto\u001b[39m\u001b[38;5;124m\"\u001b[39m,[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mperiodicidade\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfrequencia\u001b[39m\u001b[38;5;124m\"\u001b[39m],[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mperiodicidade\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124minicio\u001b[39m\u001b[38;5;124m\"\u001b[39m],[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mperiodicidade\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mfim\u001b[39m\u001b[38;5;124m\"\u001b[39m],[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnivelTerritorial\u001b[39m\u001b[38;5;124m\"\u001b[39m,\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mAdministrativo\u001b[39m\u001b[38;5;124m\"\u001b[39m]],\n\u001b[0;32m     55\u001b[0m      meta_prefix\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mtabela_\u001b[39m\u001b[38;5;124m'\u001b[39m,\n\u001b[0;32m     56\u001b[0m      record_prefix\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mvar_\u001b[39m\u001b[38;5;124m'\u001b[39m\n\u001b[0;32m     57\u001b[0m              )\n",
      "File \u001b[1;32mc:\\Users\\Alexandre Rebello\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\json\\_normalize.py:517\u001b[0m, in \u001b[0;36mjson_normalize\u001b[1;34m(data, record_path, meta, meta_prefix, record_prefix, errors, sep, max_level)\u001b[0m\n\u001b[0;32m    514\u001b[0m                 meta_vals[key]\u001b[38;5;241m.\u001b[39mappend(meta_val)\n\u001b[0;32m    515\u001b[0m             records\u001b[38;5;241m.\u001b[39mextend(recs)\n\u001b[1;32m--> 517\u001b[0m \u001b[43m_recursive_extract\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdata\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mrecord_path\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m{\u001b[49m\u001b[43m}\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mlevel\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;241;43m0\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[0;32m    519\u001b[0m result \u001b[38;5;241m=\u001b[39m DataFrame(records)\n\u001b[0;32m    521\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m record_prefix \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n",
      "File \u001b[1;32mc:\\Users\\Alexandre Rebello\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\json\\_normalize.py:494\u001b[0m, in \u001b[0;36mjson_normalize.<locals>._recursive_extract\u001b[1;34m(data, path, seen_meta, level)\u001b[0m\n\u001b[0;32m    492\u001b[0m         \u001b[38;5;28;01mfor\u001b[39;00m val, key \u001b[38;5;129;01min\u001b[39;00m \u001b[38;5;28mzip\u001b[39m(_meta, meta_keys):\n\u001b[0;32m    493\u001b[0m             \u001b[38;5;28;01mif\u001b[39;00m level \u001b[38;5;241m+\u001b[39m \u001b[38;5;241m1\u001b[39m \u001b[38;5;241m==\u001b[39m \u001b[38;5;28mlen\u001b[39m(val):\n\u001b[1;32m--> 494\u001b[0m                 seen_meta[key] \u001b[38;5;241m=\u001b[39m \u001b[43m_pull_field\u001b[49m\u001b[43m(\u001b[49m\u001b[43mobj\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mval\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;241;43m-\u001b[39;49m\u001b[38;5;241;43m1\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    496\u001b[0m         _recursive_extract(obj[path[\u001b[38;5;241m0\u001b[39m]], path[\u001b[38;5;241m1\u001b[39m:], seen_meta, level\u001b[38;5;241m=\u001b[39mlevel \u001b[38;5;241m+\u001b[39m \u001b[38;5;241m1\u001b[39m)\n\u001b[0;32m    497\u001b[0m \u001b[38;5;28;01melse\u001b[39;00m:\n",
      "File \u001b[1;32mc:\\Users\\Alexandre Rebello\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\io\\json\\_normalize.py:408\u001b[0m, in \u001b[0;36mjson_normalize.<locals>._pull_field\u001b[1;34m(js, spec, extract_record)\u001b[0m\n\u001b[0;32m    406\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m np\u001b[38;5;241m.\u001b[39mnan\n\u001b[0;32m    407\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m--> 408\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(\n\u001b[0;32m    409\u001b[0m             \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mKey \u001b[39m\u001b[38;5;132;01m{\u001b[39;00me\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m not found. To replace missing values of \u001b[39m\u001b[38;5;132;01m{\u001b[39;00me\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m with \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    410\u001b[0m             \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnp.nan, pass in errors=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mignore\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    411\u001b[0m         ) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01me\u001b[39;00m\n\u001b[0;32m    413\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m result\n",
      "\u001b[1;31mKeyError\u001b[0m: \"Key 'id' not found. To replace missing values of 'id' with np.nan, pass in errors='ignore'\""
     ]
    }
   ],
   "source": [
    "#lista de parametros para API\n",
    "\n",
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "pd.set_option('future.no_silent_downcasting', True)\n",
    "\n",
    "response = requests.get('https://servicodados.ibge.gov.br/api/v3/agregados')\n",
    "\n",
    "if response.status_code == 200:\n",
    "    data = response.json()\n",
    "\n",
    "    resultado = pd.DataFrame({'categ_id':[], 'categ_nome':[], 'categ_unidade':[], 'categ_nivel':[], 'tabela_id':[],\n",
    "       'tabela_classificacoes.id':[], 'tabela_classificacoes.nome':[],                      \n",
    "       'var_id':[], 'var_nome':[], 'var_unidade':[], 'var_sumarizacao':[], 'tabela_id':[],\n",
    "       'Tabela_nome':[], 'Tabela_URL':[], 'Tabela_pesquisa':[], 'Tabela_assunto':[],\n",
    "       'Tabela_periodicidade.frequencia':[], 'Tabela_periodicidade.inicio':[],\n",
    "       'Tabela_periodicidade.fim':[], 'Tabela_nivelTerritorial.Administrativo':[]})\n",
    "    \n",
    "    data_list = []\n",
    "\n",
    "    for item in data:\n",
    "        id = item['id']\n",
    "        pesquisa = item['nome']\n",
    "        for agregado in item['agregados']:\n",
    "            tabela = agregado['id']\n",
    "            nome_tabela = agregado['nome']\n",
    "            data_list.append([id, pesquisa, tabela, nome_tabela])\n",
    "            if 'SU' == item['id'] : df = pd.DataFrame(data_list, columns=['id', 'pesquisa', 'tabela', 'nome_tabela'])\n",
    "    nada = df['tabela']\n",
    "\n",
    "    lista = pd.DataFrame({'tabela':['2093']})\n",
    "    print(lista)\n",
    "    for item in lista:\n",
    "        tab = item\n",
    "        #print(IdTabela)\n",
    "        enderecoT = 'https://servicodados.ibge.gov.br/api/v3/agregados/'+tab+'/metadados'\n",
    "        print(enderecoT)\n",
    "        responseT = requests.get(enderecoT)\n",
    "        dataT = responseT.json()\n",
    "        #print(dataT)\n",
    "        df_categ = pd.json_normalize(dataT,\n",
    "            record_path=['classificacoes','categorias'],\n",
    "            meta=[\"id\",[\"classificacoes\",\"id\"],[\"classificacoes\",\"nome\"]],\n",
    "            meta_prefix='tabela_',\n",
    "            record_prefix='categ_'\n",
    "            )\n",
    "       # print(df_categ)\n",
    "                #Variaveis x classificacao\n",
    "\n",
    "        df_var = pd.json_normalize(dataT,\n",
    "            record_path=['variaveis'],\n",
    "            meta=[\"id\",\"nome\",\"URL\",\"pesquisa\",\"assunto\",[\"periodicidade\",\"frequencia\"],[\"periodicidade\",\"inicio\"],[\"periodicidade\",\"fim\"],[\"nivelTerritorial\",\"Administrativo\"]],\n",
    "            meta_prefix='tabela_',\n",
    "            record_prefix='var_'\n",
    "                    )\n",
    "        #nivel territorio x tabela\n",
    "\n",
    "        df_nivel = pd.json_normalize(dataT,\n",
    "            record_path=['nivelTerritorial','Administrativo'],\n",
    "            meta=[\"id\"],\n",
    "            meta_prefix='tabela_',\n",
    "            record_prefix='nivel_'\n",
    "            )\n",
    "\n",
    "        if df_categ.empty:\n",
    "            dfm = df_var.merge(df_nivel,left_on='tabela_id',right_on='tabela_id')\n",
    "        else:\n",
    "            df1 = df_categ.merge(df_var, left_on='tabela_id',right_on='tabela_id')\n",
    "            dfm = df1.merge(df_nivel, left_on='tabela_id',right_on='tabela_id')\n",
    "        \n",
    "        options = ['N1','N2','N3','N6'] \n",
    "        df = dfm.loc[dfm['nivel_0'].isin(options)] \n",
    "        #df = df.loc[:,'tabela_id']#,'var_id','tabela_classificacoes.id','categ_id','nivel_0']]\n",
    "        #print(dfm)\n",
    "        #print(\"muda/muda/muda\")\n",
    "        resultado = pd.concat([resultado,df])\n",
    "        resultado = resultado.loc[:,['tabela_id','var_id','tabela_classificacoes.id','categ_id','nivel_0']]\n",
    "        # resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']] = resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']].fillna(-1)\n",
    "        # resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']] = resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']].astype(int)\n",
    "        # resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']] = resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']].astype(str)\n",
    "        # resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']] = resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']].replace('-1', np.nan)\n",
    "        # resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']] = resultado[['tabela_id','var_id','tabela_classificacoes.id','categ_id']].fillna('').astype(str)\n",
    "\n",
    "    print(\"RESULTADO/RESULTADO/RESULTADO\")    \n",
    "    #print(resultado.dtypes)\n",
    "    print(resultado)\n",
    "    print(f\"Request failed with status code {response.status_code}\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}