trabalho.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "concat() takes 1 positional argument but 2 were given",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[19], line 95\u001b[0m\n\u001b[0;32m     89\u001b[0m     df1 \u001b[38;5;241m=\u001b[39m df_categ1\u001b[38;5;241m.\u001b[39mmerge(df_var1, left_on\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mtabela_id\u001b[39m\u001b[38;5;124m'\u001b[39m,right_on\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mTabela_id\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m     92\u001b[0m \u001b[38;5;66;03m#df=df.append(df1)\u001b[39;00m\n\u001b[0;32m     93\u001b[0m \u001b[38;5;66;03m#dfFinal = DataFrame(df1)\u001b[39;00m\n\u001b[0;32m     94\u001b[0m \u001b[38;5;66;03m#dfFinal\u001b[39;00m\n\u001b[1;32m---> 95\u001b[0m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconcat\u001b[49m\u001b[43m(\u001b[49m\u001b[43m[\u001b[49m\u001b[43mdf\u001b[49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43m[\u001b[49m\u001b[43mdf1\u001b[49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[1;31mTypeError\u001b[0m: concat() takes 1 positional argument but 2 were given"
     ]
    }
   ],
   "source": [
    "#valores para montar a base de tabelas/classificacoes/categorias/variaveis\n",
    "\n",
    "import requests\n",
    "import pandas as pd\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",
    "\n",
    "#base de tabelas/classificacoes/categorias/variaveis\n",
    "\n",
    "tabela = '2093'\n",
    "\n",
    "endereco = 'https://servicodados.ibge.gov.br/api/v3/agregados/'+tabela+'/metadados'\n",
    "\n",
    "response = requests.get(endereco)\n",
    "\n",
    "if response.status_code == 200:\n",
    "    data = response.json()\n",
    "\n",
    "#Catgoria x classificacao\n",
    "\n",
    "df_categ = pd.json_normalize(data,\n",
    "    record_path=['classificacoes','categorias'],\n",
    "    meta=[\"id\",[\"classificacoes\",\"id\"],[\"classificacoes\",\"nome\"]],\n",
    "    meta_prefix='tabela_',\n",
    "    record_prefix='categ_'\n",
    "    )\n",
    "\n",
    "#Variaveis x classificacao\n",
    "\n",
    "df_var = pd.json_normalize(data,\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",
    "\n",
    "if df_categ.empty:\n",
    "    df = df_var\n",
    "else:\n",
    "    df = df_categ.merge(df_var, left_on='tabela_id',right_on='Tabela_id')\n",
    "#print(df)\n",
    "#valores para montar a base de tabelas/classificacoes/categorias/variaveis\n",
    "\n",
    "import requests\n",
    "import pandas as pd\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",
    "\n",
    "#base de tabelas/classificacoes/categorias/variaveis\n",
    "\n",
    "tabela = '1621'\n",
    "\n",
    "endereco = 'https://servicodados.ibge.gov.br/api/v3/agregados/'+tabela+'/metadados'\n",
    "\n",
    "response = requests.get(endereco)\n",
    "\n",
    "if response.status_code == 200:\n",
    "    data = response.json()\n",
    "\n",
    "#Catgoria x classificacao\n",
    "\n",
    "df_categ1 = pd.json_normalize(data,\n",
    "    record_path=['classificacoes','categorias'],\n",
    "    meta=[\"id\",[\"classificacoes\",\"id\"],[\"classificacoes\",\"nome\"]],\n",
    "    meta_prefix='tabela_',\n",
    "    record_prefix='categ_'\n",
    "    )\n",
    "\n",
    "#Variaveis x classificacao\n",
    "\n",
    "df_var1 = pd.json_normalize(data,\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",
    "\n",
    "if df_categ1.empty:\n",
    "    df1 = df_var1\n",
    "else:\n",
    "    df1 = df_categ1.merge(df_var1, left_on='tabela_id',right_on='Tabela_id')\n",
    "\n",
    "\n",
    "#df=df.append(df1)\n",
    "#dfFinal = DataFrame(df1)\n",
    "#dfFinal\n",
    "pd.concat([df],[df1])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "import requests\n",
    "import pandas as pd\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",
    "       '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",
    "\n",
    "    for item in data:\n",
    "        id = item['id']\n",
    "        pesquisa = item['nome']\n",
    "        for agregado in item['agregados']:\n",
    "            id_df = agregado['id']\n",
    "            nome_df = agregado['nome']\n",
    "            enderecoT = 'https://servicodados.ibge.gov.br/api/v3/agregados/'+id_df+'/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",
    "            #df_var\n",
    "            if df_categ.empty:\n",
    "                dfm = df_var\n",
    "            else:\n",
    "                dfm = df_categ.merge(df_var, left_on='tabela_id',right_on='Tabela_id')\n",
    "            #print(dfm)\n",
    "            resultado = pd.concat([resultado,dfm])\n",
    "            \n",
    "        \n",
    "    print(resultado)\n",
    "else:\n",
    "    print(f\"Request failed with status code {response.status_code}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    1621\n",
      "1    2093\n",
      "2    6784\n",
      "Name: tabela, dtype: object\n",
      "https://servicodados.ibge.gov.br/api/v3/agregados/1621/metadados\n",
      "https://servicodados.ibge.gov.br/api/v3/agregados/2093/metadados\n"
     ]
    },
    {
     "ename": "TypeError",
     "evalue": "cannot concatenate object of type '<class 'list'>'; only Series and DataFrame objs are valid",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[68], line 62\u001b[0m\n\u001b[0;32m     58\u001b[0m df \u001b[38;5;241m=\u001b[39m dfm\u001b[38;5;241m.\u001b[39mloc[dfm[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mnivel_0\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39misin(options)] \n\u001b[0;32m     59\u001b[0m \u001b[38;5;66;03m#df = df.loc[:,'tabela_id']#,'var_id','tabela_classificacoes.id','categ_id','nivel_0']]\u001b[39;00m\n\u001b[0;32m     60\u001b[0m \u001b[38;5;66;03m#print(dfm)\u001b[39;00m\n\u001b[0;32m     61\u001b[0m \u001b[38;5;66;03m#print(\"muda/muda/muda\")\u001b[39;00m\n\u001b[1;32m---> 62\u001b[0m resultado \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mconcat\u001b[49m\u001b[43m(\u001b[49m\u001b[43m[\u001b[49m\u001b[43mresultado\u001b[49m\u001b[43m,\u001b[49m\u001b[43mdf\u001b[49m\u001b[43m]\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m     63\u001b[0m resultado \u001b[38;5;241m=\u001b[39m resultado\u001b[38;5;241m.\u001b[39mloc[:,[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mtabela_id\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mvar_id\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mtabela_classificacoes.id\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mcateg_id\u001b[39m\u001b[38;5;124m'\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mnivel_0\u001b[39m\u001b[38;5;124m'\u001b[39m]]\n\u001b[0;32m     65\u001b[0m resultado \u001b[38;5;241m=\u001b[39m [resultado[col_name]\u001b[38;5;241m.\u001b[39mastype(\u001b[38;5;28mstr\u001b[39m) \u001b[38;5;28;01mfor\u001b[39;00m col_name \u001b[38;5;129;01min\u001b[39;00m resultado\u001b[38;5;241m.\u001b[39mcolumns]\n",
      "File \u001b[1;32mc:\\Users\\Alexandre Rebello\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\core\\reshape\\concat.py:382\u001b[0m, in \u001b[0;36mconcat\u001b[1;34m(objs, axis, join, ignore_index, keys, levels, names, verify_integrity, sort, copy)\u001b[0m\n\u001b[0;32m    379\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m copy \u001b[38;5;129;01mand\u001b[39;00m using_copy_on_write():\n\u001b[0;32m    380\u001b[0m     copy \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;01mFalse\u001b[39;00m\n\u001b[1;32m--> 382\u001b[0m op \u001b[38;5;241m=\u001b[39m \u001b[43m_Concatenator\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    383\u001b[0m \u001b[43m    \u001b[49m\u001b[43mobjs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    384\u001b[0m \u001b[43m    \u001b[49m\u001b[43maxis\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43maxis\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    385\u001b[0m \u001b[43m    \u001b[49m\u001b[43mignore_index\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mignore_index\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    386\u001b[0m \u001b[43m    \u001b[49m\u001b[43mjoin\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mjoin\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    387\u001b[0m \u001b[43m    \u001b[49m\u001b[43mkeys\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mkeys\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    388\u001b[0m \u001b[43m    \u001b[49m\u001b[43mlevels\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mlevels\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    389\u001b[0m \u001b[43m    \u001b[49m\u001b[43mnames\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mnames\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    390\u001b[0m \u001b[43m    \u001b[49m\u001b[43mverify_integrity\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mverify_integrity\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    391\u001b[0m \u001b[43m    \u001b[49m\u001b[43mcopy\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mcopy\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    392\u001b[0m \u001b[43m    \u001b[49m\u001b[43msort\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43msort\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    393\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    395\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m op\u001b[38;5;241m.\u001b[39mget_result()\n",
      "File \u001b[1;32mc:\\Users\\Alexandre Rebello\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\core\\reshape\\concat.py:448\u001b[0m, in \u001b[0;36m_Concatenator.__init__\u001b[1;34m(self, objs, axis, join, keys, levels, names, ignore_index, verify_integrity, copy, sort)\u001b[0m\n\u001b[0;32m    445\u001b[0m objs, keys \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_clean_keys_and_objs(objs, keys)\n\u001b[0;32m    447\u001b[0m \u001b[38;5;66;03m# figure out what our result ndim is going to be\u001b[39;00m\n\u001b[1;32m--> 448\u001b[0m ndims \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_get_ndims\u001b[49m\u001b[43m(\u001b[49m\u001b[43mobjs\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    449\u001b[0m sample, objs \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_get_sample_object(objs, ndims, keys, names, levels)\n\u001b[0;32m    451\u001b[0m \u001b[38;5;66;03m# Standardize axis parameter to int\u001b[39;00m\n",
      "File \u001b[1;32mc:\\Users\\Alexandre Rebello\\AppData\\Local\\Programs\\Python\\Python311\\Lib\\site-packages\\pandas\\core\\reshape\\concat.py:489\u001b[0m, in \u001b[0;36m_Concatenator._get_ndims\u001b[1;34m(self, objs)\u001b[0m\n\u001b[0;32m    484\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(obj, (ABCSeries, ABCDataFrame)):\n\u001b[0;32m    485\u001b[0m         msg \u001b[38;5;241m=\u001b[39m (\n\u001b[0;32m    486\u001b[0m             \u001b[38;5;124mf\u001b[39m\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mcannot concatenate object of type \u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m{\u001b[39;00m\u001b[38;5;28mtype\u001b[39m(obj)\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124m; \u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    487\u001b[0m             \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124monly Series and DataFrame objs are valid\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m    488\u001b[0m         )\n\u001b[1;32m--> 489\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m(msg)\n\u001b[0;32m    491\u001b[0m     ndims\u001b[38;5;241m.\u001b[39madd(obj\u001b[38;5;241m.\u001b[39mndim)\n\u001b[0;32m    492\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m ndims\n",
      "\u001b[1;31mTypeError\u001b[0m: cannot concatenate object of type '<class 'list'>'; only Series and DataFrame objs are valid"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "import pandas as pd\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",
    "  \n",
    "    lista = pd.DataFrame({'tabela':['1621','2093','6784']})\n",
    "    print(lista['tabela'])\n",
    "    for item in lista['tabela']:\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','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",
    "        \n",
    "        resultado = [resultado[col_name].astype(str) for col_name in resultado.columns]\n",
    "        #resultado['var_id'] = resultado['var_id'].astype('Int64')\n",
    "        #resultado['categ_id'] = resultado['categ_id'].astype('Int64')\n",
    "        resultado\n",
    "    print(\"RESULTADO/RESULTADO/RESULTADO\")    \n",
    "    print(resultado)\n",
    "\n",
    "    print(f\"Request failed with status code {response.status_code}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1      População residente por cor ou raça, sexo, sit...\n",
       "11     População residente por cor ou raça, sexo, sit...\n",
       "21     População residente por cor ou raça, sexo, sit...\n",
       "31     População residente por cor ou raça, sexo, sit...\n",
       "41     População residente por cor ou raça, sexo, sit...\n",
       "                             ...                        \n",
       "591    População residente por cor ou raça, sexo, sit...\n",
       "601    População residente por cor ou raça, sexo, sit...\n",
       "611    População residente por cor ou raça, sexo, sit...\n",
       "621    População residente por cor ou raça, sexo, sit...\n",
       "631    População residente por cor ou raça, sexo, sit...\n",
       "Name: tabela_nome, Length: 64, dtype: object"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#valores para montar a base de tabelas/classificacoes/categorias/variaveis\n",
    "\n",
    "import requests\n",
    "import pandas as pd\n",
    "\n",
    "tabela = '2093'\n",
    "\n",
    "endereco = 'https://servicodados.ibge.gov.br/api/v3/agregados/'+tabela+'/metadados'\n",
    "\n",
    "response = requests.get(endereco)\n",
    "\n",
    "if response.status_code == 200:\n",
    "    data = response.json()\n",
    "\n",
    "#Catgoria x classificacao\n",
    "\n",
    "df_categ = pd.json_normalize(data,\n",
    "    record_path=['classificacoes','categorias'],\n",
    "    meta=[\"id\"],#[\"classificacoes\",\"id\"],[\"classificacoes\",\"nome\"]],\n",
    "    meta_prefix='tabela_',\n",
    "    record_prefix='categ_'\n",
    "    )\n",
    "\n",
    "#Variaveis x tabela\n",
    "\n",
    "df_var = pd.json_normalize(data,\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",
    "\n",
    "#nivel territorio x tabela\n",
    "\n",
    "df_nivel = pd.json_normalize(data,\n",
    "    record_path=['nivelTerritorial','Administrativo'],\n",
    "    meta=[\"id\"],\n",
    "    meta_prefix='tabela_',\n",
    "    record_prefix='nivel_'\n",
    "    )\n",
    "\n",
    "if df_categ.empty:\n",
    "    df = 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",
    "    df = df1.merge(df_nivel, left_on='tabela_id',right_on='tabela_id')\n",
    "\n",
    "\n",
    "\n",
    "options = ['N1','N3','N6'] \n",
    "df = df.loc[df['nivel_0'].isin(options)] \n",
    "\n",
    "df = df.loc[:,'tabela_nome']\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>nivel_0</th>\n",
       "      <th>tabela_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N15</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N1</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>N2</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>N8</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>N9</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>N6</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>N14</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>N7</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>N13</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>N3</td>\n",
       "      <td>2093</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  nivel_0 tabela_id\n",
       "0     N15      2093\n",
       "1      N1      2093\n",
       "2      N2      2093\n",
       "3      N8      2093\n",
       "4      N9      2093\n",
       "5      N6      2093\n",
       "6     N14      2093\n",
       "7      N7      2093\n",
       "8     N13      2093\n",
       "9      N3      2093"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#valores para montar a base de tabelas/classificacoes/categorias/variaveis\n",
    "\n",
    "import requests\n",
    "import pandas as pd\n",
    "\n",
    "tabela = '2093'\n",
    "\n",
    "endereco = 'https://servicodados.ibge.gov.br/api/v3/agregados/'+tabela+'/metadados'\n",
    "\n",
    "response = requests.get(endereco)\n",
    "\n",
    "if response.status_code == 200:\n",
    "    data = response.json()\n",
    "\n",
    "df_nivel = pd.json_normalize(data,\n",
    "    record_path=['nivelTerritorial','Administrativo'],\n",
    "    meta=[\"id\"],\n",
    "    meta_prefix='tabela_',\n",
    "    record_prefix='nivel_'\n",
    "    )\n",
    "df_nivel"
   ]
  }
 ],
 "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
}