!pip install wikitables
!pip install sparqlwrapper
!pip install lxml
!pip install requests
Requirement already satisfied: wikitables in /srv/paws/lib/python3.6/site-packages
Requirement already satisfied: requests>=2.9.1 in /srv/paws/lib/python3.6/site-packages (from wikitables)
Requirement already satisfied: mwparserfromhell>=0.4.3 in /srv/paws/lib/python3.6/site-packages (from wikitables)
Requirement already satisfied: certifi>=2017.4.17 in /srv/paws/lib/python3.6/site-packages (from requests>=2.9.1->wikitables)
Requirement already satisfied: urllib3<1.24,>=1.21.1 in /srv/paws/lib/python3.6/site-packages (from requests>=2.9.1->wikitables)
Requirement already satisfied: idna<2.8,>=2.5 in /srv/paws/lib/python3.6/site-packages (from requests>=2.9.1->wikitables)
Requirement already satisfied: chardet<3.1.0,>=3.0.2 in /srv/paws/lib/python3.6/site-packages (from requests>=2.9.1->wikitables)
Requirement already satisfied: sparqlwrapper in /srv/paws/lib/python3.6/site-packages
Requirement already satisfied: rdflib>=4.0 in /srv/paws/lib/python3.6/site-packages (from sparqlwrapper)
Requirement already satisfied: pyparsing in /srv/paws/lib/python3.6/site-packages (from rdflib>=4.0->sparqlwrapper)
Requirement already satisfied: isodate in /srv/paws/lib/python3.6/site-packages (from rdflib>=4.0->sparqlwrapper)
Requirement already satisfied: six in /srv/paws/lib/python3.6/site-packages (from isodate->rdflib>=4.0->sparqlwrapper)
Requirement already satisfied: lxml in /srv/paws/lib/python3.6/site-packages
Requirement already satisfied: requests in /srv/paws/lib/python3.6/site-packages
Requirement already satisfied: urllib3<1.24,>=1.21.1 in /srv/paws/lib/python3.6/site-packages (from requests)
Requirement already satisfied: certifi>=2017.4.17 in /srv/paws/lib/python3.6/site-packages (from requests)
Requirement already satisfied: chardet<3.1.0,>=3.0.2 in /srv/paws/lib/python3.6/site-packages (from requests)
Requirement already satisfied: idna<2.8,>=2.5 in /srv/paws/lib/python3.6/site-packages (from requests)
import re

from wikitables import import_tables

tables = import_tables('List_of_largest_cities_in_Brazil', 'en')
pattern = '\[\[([\w+ --()./]+)(\|.*)?]\]'


def get_item_for_name(name):
    page = pywikibot.Page(site, name)
    return page.data_item()

wikipedia_ids = []
for row in tables[0].rows:
    result = re.search(pattern, str(row['City'].raw))
    print(row.keys())
    if result:
        page_name = result.group(1)
        item = get_item_for_name(page_name)
        print(page_name, item, row['2010Census'])
        wikipedia_ids.append(item.id)
    else:
        print("--- WARNING ---")
        print("could not match ", format(row))
        print(row['Landkreis/Kreis'].raw)
        print("--- END WARNING ---")
dict_keys(['2018rank', 'City', 'State', '2018Estimate'])
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-9-990118944764> in <module>()
     18         page_name = result.group(1)
     19         item = get_item_for_name(page_name)
---> 20         print(page_name, item, row['2010Census'])
     21         wikipedia_ids.append(item.id)
     22     else:

KeyError: '2010Census'
 
from SPARQLWrapper import SPARQLWrapper, JSON

endpoint_url = "https://query.wikidata.org/sparql"

query = """SELECT DISTINCT ?landkreis ?landkreisLabel ?coordinates WHERE {
  ?landkreis (wdt:P31/wdt:P279*) wd:Q106658.
  OPTIONAL { ?landkreis wdt:P576 ?dissolved. }
  OPTIONAL { ?landkreis wdt:P625 ?coordinates. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de, en". }
  FILTER(!BOUND(?dissolved))
  
}"""


def get_results(endpoint_url, query):
    sparql = SPARQLWrapper(endpoint_url)
    sparql.setQuery(query)
    sparql.setReturnFormat(JSON)
    return sparql.query().convert()

def extract_id(url):
    [*rest, q_id] = url.split('/')
    return q_id

results = get_results(endpoint_url, query)

wikidata_ids = [result['landkreis']['value'] for result in results["results"]["bindings"]]
print("Landkreise ohne Auflösungsdatum (P576), die nicht in der Wikipedia Liste der Landkreise auftauchen.")
print("Hier muss recherchiert und das entsprechende P576 gesetzt werden.")
for item in wikidata_ids:
    if extract_id(item) not in wikipedia_ids:
        print(item)
Landkreise ohne Auflösungsdatum (P576), die nicht in der Wikipedia Liste der Landkreise auftauchen.
Hier muss recherchiert und das entsprechende P576 gesetzt werden.
http://www.wikidata.org/entity/Q1421347
http://www.wikidata.org/entity/Q1598267
http://www.wikidata.org/entity/Q1787204
http://www.wikidata.org/entity/Q1787568
http://www.wikidata.org/entity/Q1320758
http://www.wikidata.org/entity/Q1483352
http://www.wikidata.org/entity/Q1621433
http://www.wikidata.org/entity/Q1366141
http://www.wikidata.org/entity/Q1664105
http://www.wikidata.org/entity/Q1563834
http://www.wikidata.org/entity/Q1787540
http://www.wikidata.org/entity/Q1803071
http://www.wikidata.org/entity/Q1664009
http://www.wikidata.org/entity/Q18022911
http://www.wikidata.org/entity/Q1803073
http://www.wikidata.org/entity/Q1470516
http://www.wikidata.org/entity/Q49594
http://www.wikidata.org/entity/Q1803351
http://www.wikidata.org/entity/Q1281905
http://www.wikidata.org/entity/Q1662276
http://www.wikidata.org/entity/Q1803357
tables[0].rows
[{'2018rank': 1,
  'City': São Paulo,
  'State': São Paulo,
  '2018Estimate': 12176866 10659386},
 {'2018rank': 2,
  'City': Rio de Janeiro,
  'State': Rio de Janeiro,
  '2018Estimate': 6688927 5940224},
 {'2018rank': 3,
  'City': Brasília,
  'State': Distrito Federal,
  '2018Estimate': 2974703 2469489},
 {'2018rank': 4,
  'City': Salvador,
  'State': Bahia,
  '2018Estimate': 2857329 2480790},
 {'2018rank': 5,
  'City': Fortaleza,
  'State': Ceará,
  '2018Estimate': 2643247 2315116},
 {'2018rank': 6,
  'City': Belo Horizonte,
  'State': Minas Gerais,
  '2018Estimate': 2501576 2258096},
 {'2018rank': 7,
  'City': Manaus,
  'State': Amazonas,
  '2018Estimate': 2145444 1718584},
 {'2018rank': 8,
  'City': Curitiba,
  'State': Paraná,
  '2018Estimate': 1917185 1678965},
 {'2018rank': 9,
  'City': Recife,
  'State': Pernambuco,
  '2018Estimate': 1637834 1472202},
 {'2018rank': 10,
  'City': Goiânia,
  'State': Goiás,
  '2018Estimate': 1495705 1256514},
 {'2018rank': 11,
  'City': Belém,
  'State': Pará,
  '2018Estimate': 1485732 1351618},
 {'2018rank': 12,
  'City': Porto Alegre,
  'State': Rio Grande do Sul,
  '2018Estimate': 1479101 1365039},
 {'2018rank': 13,
  'City': Guarulhos,
  'State': São Paulo,
  '2018Estimate': 1365899 1176804},
 {'2018rank': 14,
  'City': Campinas,
  'State': São Paulo,
  '2018Estimate': 1194094 1024912 	},
 {'2018rank': 15,
  'City': São Luís,
  'State': Maranhão,
  '2018Estimate': 1094667 966989},
 {'2018rank': 16,
  'City': São Gonçalo,
  'State': Rio de Janeiro,
  '2018Estimate': 1077687 945752},
 {'2018rank': 17,
  'City': Maceió,
  'State': Alagoas,
  '2018Estimate': 1012382 917086},
 {'2018rank': 18,
  'City': Duque de Caxias,
  'State': Rio de Janeiro,
  '2018Estimate': 914383 818432},
 {'2018rank': 19,
  'City': Campo Grande,
  'State': Mato Grosso do Sul,
  '2018Estimate': 885711 766461},
 {'2018rank': 20,
  'City': Natal,
  'State': Rio Grande do Norte,
  '2018Estimate': 877640 785722},
 {'2018rank': 21,
  'City': Teresina,
  'State': Piauí,
  '2018Estimate': 861442 797029},
 {'2018rank': 22,
  'City': São Bernardo do Campo,
  'State': São Paulo,
  '2018Estimate': 833240 746718},
 {'2018rank': 23,
  'City': Nova Iguaçu,
  'State': Rio de Janeiro,
  '2018Estimate': 818875 767505},
 {'2018rank': 24,
  'City': João Pessoa,
  'State': Paraíba,
  '2018Estimate': 800323 716042},
 {'2018rank': 25,
  'City': Santo André,
  'State': São Paulo,
  '2018Estimate': 716109 654354 		},
 {'2018rank': 26,
  'City': São José dos Campos,
  'State': São Paulo,
  '2018Estimate': 713943 597425},
 {'2018rank': 27,
  'City': Jaboatão dos Guararapes,
  'State': Pernambuco,
  '2018Estimate': 697636 623471},
 {'2018rank': 28,
  'City': Osasco,
  'State': São Paulo,
  '2018Estimate': 696850 637617},
 {'2018rank': 29,
  'City': Ribeirão Preto,
  'State': São Paulo,
  '2018Estimate': 694534 583842},
 {'2018rank': 30,
  'City': Uberlândia,
  'State': Minas Gerais,
  '2018Estimate': 683247 579005},
 {'2018rank': 31,
  'City': Sorocaba,
  'State': São Paulo,
  '2018Estimate': 671186 570434},
 {'2018rank': 32,
  'City': Contagem,
  'State': Minas Gerais,
  '2018Estimate': 659070 568640},
 {'2018rank': 33,
  'City': Aracaju,
  'State': Sergipe,
  '2018Estimate': 648939 552365},
 {'2018rank': 34,
  'City': Feira de Santana,
  'State': Bahia,
  '2018Estimate': 609913 542476},
 {'2018rank': 35,
  'City': Cuiabá,
  'State': Mato Grosso,
  '2018Estimate': 607153 530308},
 {'2018rank': 36,
  'City': Joinville,
  'State': Santa Catarina,
  '2018Estimate': 583144 509293},
 {'2018rank': 37,
  'City': Aparecida de Goiânia,
  'State': Goiás,
  '2018Estimate': 565957 442978},
 {'2018rank': 38,
  'City': Juiz de Fora,
  'State': Minas Gerais,
  '2018Estimate': 564310 497778},
 {'2018rank': 39,
  'City': Londrina,
  'State': Paraná,
  '2018Estimate': 563943 493358},
 {'2018rank': 40,
  'City': Ananindeua,
  'State': Pará,
  '2018Estimate': 525566 456316},
 {'2018rank': 40,
  'City': Porto Velho,
  'State': Rondônia,
  '2018Estimate': 519531 410520},
 {'2018rank': 42,
  'City': Niterói,
  'State': Rio de Janeiro,
  '2018Estimate': 511786 441078},
 {'2018rank': 43,
  'City': Belford Roxo,
  'State': Rio de Janeiro,
  '2018Estimate': 508614 455598},
 {'2018rank': 44,
  'City': Serra,
  'State': Espírito Santo,
  '2018Estimate': 507598 384469},
 {'2018rank': 45,
  'City': Caxias do Sul,
  'State': Rio Grande do Sul,
  '2018Estimate': 504069 427664},
 {'2018rank': 46,
  'City': Campos dos Goytacazes,
  'State': Rio de Janeiro,
  '2018Estimate': 503424 442363},
 {'2018rank': 47,
  'City': Macapá,
  'State': Amapá,
  '2018Estimate': 493634 387539},
 {'2018rank': 48,
  'City': Florianópolis,
  'State': Santa Catarina,
  '2018Estimate': 492977 404224},
 {'2018rank': 49,
  'City': Vila Velha,
  'State': Espírito Santo,
  '2018Estimate': 486208 393941},
 {'2018rank': 50,
  'City': São João de Meriti,
  'State': Rio de Janeiro,
  '2018Estimate': 471888 439497},
 {'2018rank': 51,
  'City': Mauá,
  'State': São Paulo,
  '2018Estimate': 468148 394698},
 {'2018rank': 52,
  'City': São José do Rio Preto,
  'State': São Paulo,
  '2018Estimate': 456245 406220},
 {'2018rank': 53,
  'City': Mogi das Cruzes,
  'State': São Paulo,
  '2018Estimate': 440769 366216},
 {'2018rank': 53,
  'City': Santos,
  'State': São Paulo,
  '2018Estimate': 432957 407506},
 {'2018rank': 55,
  'City': Betim,
  'State': Minas Gerais,
  '2018Estimate': 432575 367044},
 {'2018rank': 56,
  'City': Diadema,
  'State': São Paulo,
  '2018Estimate': 420934 370184 	},
 {'2018rank': 57,
  'City': Maringá,
  'State': Paraná,
  '2018Estimate': 417010 349860},
 {'2018rank': 58,
  'City': Jundiaí,
  'State': São Paulo,
  '2018Estimate': 414810 357909},
 {'2018rank': 59,
  'City': Campina Grande,
  'State': Paraíba,
  '2018Estimate': 407472 383941},
 {'2018rank': 60,
  'City': Montes Claros,
  'State': Minas Gerais,
  '2018Estimate': 404804 355401},
 {'2018rank': 61,
  'City': Rio Branco,
  'State': Acre,
  '2018Estimate': 401155 319825},
 {'2018rank': 62,
  'City': Piracicaba,
  'State': São Paulo,
  '2018Estimate': 400949 356179},
 {'2018rank': 63,
  'City': Carapicuíba,
  'State': São Paulo,
  '2018Estimate': 398611 344878},
 {'2018rank': 64,
  'City': Olinda,
  'State': Pernambuco,
  '2018Estimate': 391835 357965},
 {'2018rank': 65,
  'City': Anápolis,
  'State': Goiás,
  '2018Estimate': 381970 324303},
 {'2018rank': 66,
  'City': Cariacica,
  'State': Espírito Santo,
  '2018Estimate': 378603 335984},
 {'2018rank': 67,
  'City': Boa Vista,
  'State': Roraima,
  '2018Estimate': 375374 277684},
 {'2018rank': 68,
  'City': Bauru,
  'State': São Paulo,
  '2018Estimate': 374272 335888},
 {'2018rank': 69,
  'City': Itaquaquecetuba,
  'State': São Paulo,
  '2018Estimate': 366519 314843},
 {'2018rank': 70,
  'City': Caucaia,
  'State': Ceará,
  '2018Estimate': 363982 286446},
 {'2018rank': 71,
  'City': São Vicente,
  'State': São Paulo,
  '2018Estimate': 363173 316324},
 {'2018rank': 72,
  'City': Vitória,
  'State': Espírito Santo,
  '2018Estimate': 358267 297489},
 {'2018rank': 73,
  'City': Caruaru,
  'State': Pernambuco,
  '2018Estimate': 356872 306788},
 {'2018rank': 74,
  'City': Blumenau,
  'State': Santa Catarina,
  '2018Estimate': 352460 299159},
 {'2018rank': 75,
  'City': Franca,
  'State': São Paulo,
  '2018Estimate': 350400 318239},
 {'2018rank': 76,
  'City': Ponta Grossa,
  'State': Paraná,
  '2018Estimate': 348043 305545},
 {'2018rank': 77,
  'City': Canoas,
  'State': Rio Grande do Sul,
  '2018Estimate': 344957 317945},
 {'2018rank': 78,
  'City': Petrolina,
  'State': Pernambuco,
  '2018Estimate': 343865 287233},
 {'2018rank': 79,
  'City': Pelotas,
  'State': Rio Grande do Sul,
  '2018Estimate': 341648 321818},
 {'2018rank': 80,
  'City': Vitória da Conquista,
  'State': Bahia,
  '2018Estimate': 338885 295277},
 {'2018rank': 81,
  'City': Ribeirão das Neves,
  'State': Minas Gerais,
  '2018Estimate': 331045 290727},
 {'2018rank': 82,
  'City': Uberaba,
  'State': Minas Gerais,
  '2018Estimate': 330361 288235},
 {'2018rank': 83,
  'City': Paulista,
  'State': Pernambuco,
  '2018Estimate': 329117 278651},
 {'2018rank': 84,
  'City': Cascavel,
  'State': Paraná,
  '2018Estimate': 324476 283193},
 {'2018rank': 85,
  'City': Praia Grande,
  'State': São Paulo,
  '2018Estimate': 319146 251526},
 {'2018rank': 86,
  'City': Guarujá,
  'State': São Paulo,
  '2018Estimate': 318107 260477},
 {'2018rank': 87,
  'City': São José dos Pinhais,
  'State': Paraná,
  '2018Estimate': 317476 254556},
 {'2018rank': 88,
  'City': Taubaté,
  'State': São Paulo,
  '2018Estimate': 311854 275218},
 {'2018rank': 89,
  'City': Petrópolis,
  'State': Rio de Janeiro,
  '2018Estimate': 305687 277816},
 {'2018rank': 90,
  'City': Limeira,
  'State': São Paulo,
  '2018Estimate': 303682 274100},
 {'2018rank': 91,
  'City': Santarém,
  'State': Pará,
  '2018Estimate': 302667 291122},
 {'2018rank': 92,
  'City': Suzano,
  'State': São Paulo,
  '2018Estimate': 294638 256502},
 {'2018rank': 93,
  'City': Mossoró,
  'State': Rio Grande do Norte,
  '2018Estimate': 294076 254032},
 {'2018rank': 94,
  'City': Camaçari,
  'State': Bahia,
  '2018Estimate': 293723 225510},
 {'2018rank': 95,
  'City': Palmas,
  'State': Tocantins,
  '2018Estimate': 291855 223817},
 {'2018rank': 96,
  'City': Taboão da Serra,
  'State': São Paulo,
  '2018Estimate': 285570 225053},
 {'2018rank': 97,
  'City': Várzea Grande,
  'State': Mato Grosso,
  '2018Estimate': 282009 248130},
 {'2018rank': 98,
  'City': Santa Maria,
  'State': Rio Grande do Sul,
  '2018Estimate': 280505 259004},
 {'2018rank': 99,
  'City': Gravataí,
  'State': Rio Grande do Sul,
  '2018Estimate': 279398 253060},
 {'2018rank': 100,
  'City': Governador Valadares,
  'State': Minas Gerais,
  '2018Estimate': 278685 255475},
 {'2018rank': 101,
  'City': Sumaré,
  'State': São Paulo,
  '2018Estimate': 278571 236358},
 {'2018rank': 102,
  'City': Marabá,
  'State': Pará,
  '2018Estimate': 275086 224014},
 {'2018rank': 103,
  'City': Volta Redonda,
  'State': Rio de Janeiro,
  '2018Estimate': 271998 246210},
 {'2018rank': 104,
  'City': Juazeiro do Norte,
  'State': Ceará,
  '2018Estimate': 271926 244701},
 {'2018rank': 105,
  'City': Barueri,
  'State': São Paulo,
  '2018Estimate': 271306 228962},
 {'2018rank': 106,
  'City': Embu das Artes,
  'State': São Paulo,
  '2018Estimate': 270843 223622},
 {'2018rank': 107,
  'City': Ipatinga,
  'State': Minas Gerais,
  '2018Estimate': 261344 224636},
 {'2018rank': 108,
  'City': Foz do Iguaçu,
  'State': Paraná,
  '2018Estimate': 258823 250918},
 {'2018rank': 109,
  'City': Imperatriz,
  'State': Maranhão,
  '2018Estimate': 258016 245509},
 {'2018rank': 110,
  'City': Parnamirim,
  'State': Rio Grande do Norte,
  '2018Estimate': 255793 195274},
 {'2018rank': 111,
  'City': Viamão,
  'State': Rio Grande do Sul,
  '2018Estimate': 254101 236999},
 {'2018rank': 112,
  'City': Macaé,
  'State': Rio de Janeiro,
  '2018Estimate': 251631 194497},
 {'2018rank': 113,
  'City': São Carlos,
  'State': São Paulo,
  '2018Estimate': 249415 219865},
 {'2018rank': 114,
  'City': Indaiatuba,
  'State': São Paulo,
  '2018Estimate': 246908 188639},
 {'2018rank': 115,
  'City': Novo Hamburgo,
  'State': Rio Grande do Sul,
  '2018Estimate': 246452 237044},
 {'2018rank': 116,
  'City': Cotia,
  'State': São Paulo,
  '2018Estimate': 244694 190488},
 {'2018rank': 117,
  'City': Magé,
  'State': Rio de Janeiro,
  '2018Estimate': 243657 218307},
 {'2018rank': 118,
  'City': São José,
  'State': Santa Catarina,
  '2018Estimate': 242927 203384},
 {'2018rank': 119,
  'City': Colombo,
  'State': Paraná,
  '2018Estimate': 240840 208805},
 {'2018rank': 120,
  'City': Itaboraí,
  'State': Rio de Janeiro,
  '2018Estimate': 238695 210780},
 {'2018rank': 121,
  'City': Sete Lagoas,
  'State': Minas Gerais,
  '2018Estimate': 237286 211094},
 {'2018rank': 122,
  'City': Marília,
  'State': São Paulo,
  '2018Estimate': 237130 214742},
 {'2018rank': 123,
  'City': Americana,
  'State': São Paulo,
  '2018Estimate': 237112 209230},
 {'2018rank': 124,
  'City': Divinópolis,
  'State': Minas Gerais,
  '2018Estimate': 235977 206867},
 {'2018rank': 125,
  'City': São Leopoldo,
  'State': Rio Grande do Sul,
  '2018Estimate': 234947 212279},
 {'2018rank': 126,
  'City': Itapevi,
  'State': São Paulo,
  '2018Estimate': 234352 192184},
 {'2018rank': 127,
  'City': Araraquara,
  'State': São Paulo,
  '2018Estimate': 233744 204934},
 {'2018rank': 128,
  'City': Jacareí,
  'State': São Paulo,
  '2018Estimate': 231863 208934},
 {'2018rank': 129,
  'City': Arapiraca,
  'State': Alagoas,
  '2018Estimate': 230417 212216},
 {'2018rank': 130,
  'City': Rio Verde,
  'State': Goiás,
  '2018Estimate': 229651 169611},
 {'2018rank': 131,
  'City': Rondonópolis,
  'State': Mato Grosso,
  '2018Estimate': 228857 189975},
 {'2018rank': 132,
  'City': Hortolândia,
  'State': São Paulo,
  '2018Estimate': 227353 186461},
 {'2018rank': 133,
  'City': Presidente Prudente,
  'State': São Paulo,
  '2018Estimate': 227072 205340},
 {'2018rank': 134,
  'City': Maracanaú,
  'State': Ceará,
  '2018Estimate': 226128 200797},
 {'2018rank': 135,
  'City': Cabo Frio,
  'State': Rio de Janeiro,
  '2018Estimate': 222528 171551},
 {'2018rank': 136,
  'City': Dourados,
  'State': Mato Grosso do Sul,
  '2018Estimate': 220965 191638},
 {'2018rank': 137,
  'City': Santa Luzia,
  'State': Minas Gerais,
  '2018Estimate': 218147 198773},
 {'2018rank': 138,
  'City': Chapecó,
  'State': Santa Catarina,
  '2018Estimate': 216654 182809},
 {'2018rank': 139,
  'City': Itajaí,
  'State': Santa Catarina,
  '2018Estimate': 215895 182484},
 {'2018rank': 140,
  'City': Juazeiro,
  'State': Bahia,
  '2018Estimate': 215183 194327},
 {'2018rank': 141,
  'City': Criciúma,
  'State': Santa Catarina,
  '2018Estimate': 213023 191473},
 {'2018rank': 142,
  'City': Itabuna,
  'State': Bahia,
  '2018Estimate': 212740 202359},
 {'2018rank': 143,
  'City': Rio Grande,
  'State': Rio Grande do Sul,
  '2018Estimate': 210005 192582},
 {'2018rank': 144,
  'City': Alvorada,
  'State': Rio Grande do Sul,
  '2018Estimate': 209213 194837},
 {'2018rank': 145,
  'City': Cachoeiro de Itapemirim,
  'State': Espírito Santo,
  '2018Estimate': 207324 180984},
 {'2018rank': 146,
  'City': Águas Lindas de Goiás,
  'State': Goiás,
  '2018Estimate': 207070 157323},
 {'2018rank': 147,
  'City': Sobral,
  'State': Ceará,
  '2018Estimate': 206644 181010},
 {'2018rank': 148,
  'City': Cabo de Santo Agostinho,
  'State': Pernambuco,
  '2018Estimate': 205112 179912},
 {'2018rank': 149,
  'City': Luziânia,
  'State': Goiás,
  '2018Estimate': 205023 165492},
 {'2018rank': 150,
  'City': Rio Claro,
  'State': São Paulo,
  '2018Estimate': 204797 185777},
 {'2018rank': 151,
  'City': Parauapebas,
  'State': Pará,
  '2018Estimate': 202882 149411},
 {'2018rank': 152,
  'City': Passo Fundo,
  'State': Rio Grande do Sul,
  '2018Estimate': 201767 181299},
 {'2018rank': 153,
  'City': Angra dos Reis,
  'State': Rio de Janeiro,
  '2018Estimate': 200407 148294},
 {'2018rank': 154,
  'City': Castanhal,
  'State': Pará,
  '2018Estimate': 198294 168559},
 {'2018rank': 155,
  'City': Araçatuba,
  'State': São Paulo,
  '2018Estimate': 195874 178927},
 {'2018rank': 156,
  'City': Lauro de Freitas,
  'State': Bahia,
  '2018Estimate': 195095 154245},
 {'2018rank': 157,
  'City': Santa Bárbara d'Oeste,
  'State': São Paulo,
  '2018Estimate': 192536 178290},
 {'2018rank': 158,
  'City': Ferraz de Vasconcelos,
  'State': São Paulo,
  '2018Estimate': 191993 164591},
 {'2018rank': 159,
  'City': Nova Friburgo,
  'State': Rio de Janeiro,
  '2018Estimate': 190084 173989},
 {'2018rank': 160,
  'City': Barra Mansa,
  'State': Rio de Janeiro,
  '2018Estimate': 183976 172484},
 {'2018rank': 161,
  'City': Nossa Senhora do Socorro,
  'State': Sergipe,
  '2018Estimate': 181503 158470},
 {'2018rank': 162,
  'City': Teresópolis,
  'State': Rio de Janeiro,
  '2018Estimate': 180886 160205},
 {'2018rank': 163,
  'City': Guarapuava,
  'State': Paraná,
  '2018Estimate': 180334 166195},
 {'2018rank': 164,
  'City': Ibirité,
  'State': Minas Gerais,
  '2018Estimate': 179015 152647},
 {'2018rank': 165,
  'City': Araguaína,
  'State': Tocantins,
  '2018Estimate': 177517 149313},
 {'2018rank': 166,
  'City': São José de Ribamar,
  'State': Maranhão,
  '2018Estimate': 176321 160775},
 {'2018rank': 167,
  'City': Mesquita,
  'State': Rio de Janeiro,
  '2018Estimate': 175620 159685},
 {'2018rank': 168,
  'City': Jaraguá do Sul,
  'State': Santa Catarina,
  '2018Estimate': 174158 142209},
 {'2018rank': 169,
  'City': Francisco Morato,
  'State': São Paulo,
  '2018Estimate': 174008 148935},
 {'2018rank': 170,
  'City': Itapecerica da Serra,
  'State': São Paulo,
  '2018Estimate': 173672 143448},
 {'2018rank': 171,
  'City': Itu,
  'State': São Paulo,
  '2018Estimate': 172268 150101},
 {'2018rank': 172,
  'City': Linhares,
  'State': Espírito Santo,
  '2018Estimate': 170364 138679},
 {'2018rank': 173,
  'City': Palhoça,
  'State': Santa Catarina,
  '2018Estimate': 168259 135720},
 {'2018rank': 174,
  'City': Timon,
  'State': Maranhão,
  '2018Estimate': 167973 147214},
 {'2018rank': 175,
  'City': Bragança Paulista,
  'State': São Paulo,
  '2018Estimate': 166753 139726},
 {'2018rank': 176,
  'City': Pindamonhangaba,
  'State': São Paulo,
  '2018Estimate': 166475 146117},
 {'2018rank': 177,
  'City': Poços de Caldas,
  'State': Minas Gerais,
  '2018Estimate': 166111 149667},
 {'2018rank': 178,
  'City': Ilhéus,
  'State': Bahia,
  '2018Estimate': 164844 176917},
 {'2018rank': 179,
  'City': Valparaíso de Goiás,
  'State': Goiás,
  '2018Estimate': 164723 122771},
 {'2018rank': 180,
  'City': Caxias,
  'State': Maranhão,
  '2018Estimate': 164224 154211},
 {'2018rank': 181,
  'City': Nilópolis,
  'State': Rio de Janeiro,
  '2018Estimate': 162269 154232},
 {'2018rank': 182,
  'City': Itapetininga,
  'State': São Paulo,
  '2018Estimate': 162231 144395},
 {'2018rank': 183,
  'City': São Caetano do Sul,
  'State': São Paulo,
  '2018Estimate': 160275 146547},
 {'2018rank': 184,
  'City': Teixeira de Freitas,
  'State': Bahia,
  '2018Estimate': 158445 133261},
 {'2018rank': 185,
  'City': Maricá,
  'State': Rio de Janeiro,
  '2018Estimate': 157789 116216},
 {'2018rank': 186,
  'City': Lages,
  'State': Santa Catarina,
  '2018Estimate': 157743 154122},
 {'2018rank': 187,
  'City': Camaragibe,
  'State': Pernambuco,
  '2018Estimate': 156736 138239},
 {'2018rank': 188,
  'City': Abaetetuba,
  'State': Pará,
  '2018Estimate': 156292 139749},
 {'2018rank': 189,
  'City': Jequié,
  'State': Bahia,
  '2018Estimate': 155800 151820},
 {'2018rank': 190,
  'City': Barreiras,
  'State': Bahia,
  '2018Estimate': 153831 130620},
 {'2018rank': 191,
  'City': Paranaguá,
  'State': Paraná,
  '2018Estimate': 153666 136911},
 {'2018rank': 192,
  'City': Parnaíba,
  'State': Piauí,
  '2018Estimate': 152653 145293},
 {'2018rank': 193,
  'City': Franco da Rocha,
  'State': São Paulo,
  '2018Estimate': 152433 126092},
 {'2018rank': 194,
  'City': Patos de Minas,
  'State': Minas Gerais,
  '2018Estimate': 150833 132332},
 {'2018rank': 195,
  'City': Alagoinhas,
  'State': Bahia,
  '2018Estimate': 150832 136473},
 {'2018rank': 196,
  'City': Mogi Guaçu,
  'State': São Paulo,
  '2018Estimate': 150713 137208},
 {'2018rank': 197,
  'City': Queimados,
  'State': Rio de Janeiro,
  '2018Estimate': 149265 131163},
 {'2018rank': 198,
  'City': Pouso Alegre,
  'State': Minas Gerais,
  '2018Estimate': 148862 126836},
 {'2018rank': 199,
  'City': Jaú,
  'State': São Paulo,
  '2018Estimate': 148581 131050},
 {'2018rank': 200,
  'City': Porto Seguro,
  'State': Bahia,
  '2018Estimate': 146625 122525},
 {'2018rank': 201,
  'City': Rio das Ostras,
  'State': Rio de Janeiro,
  '2018Estimate': 145989 101508},
 {'2018rank': 202,
  'City': Botucatu,
  'State': São Paulo,
  '2018Estimate': 144820 122659},
 {'2018rank': 203,
  'City': Araucária,
  'State': Paraná,
  '2018Estimate': 141410 116683},
 {'2018rank': 204,
  'City': Atibaia,
  'State': São Paulo,
  '2018Estimate': 141398 120886},
 {'2018rank': 205,
  'City': Sapucaia do Sul,
  'State': Rio Grande do Sul,
  '2018Estimate': 140311 129615},
 {'2018rank': 206,
  'City': Teófilo Otoni,
  'State': Minas Gerais,
  '2018Estimate': 140235 133161},
 {'2018rank': 207,
  'City': Sinop,
  'State': Mato Grosso,
  '2018Estimate': 139935 111643},
 {'2018rank': 208,
  'City': Garanhuns,
  'State': Pernambuco,
  '2018Estimate': 138983 126472},
 {'2018rank': 209,
  'City': Balneário Camboriú,
  'State': Santa Catarina,
  '2018Estimate': 138732 106220},
 {'2018rank': 210,
  'City': Toledo,
  'State': Paraná,
  '2018Estimate': 138572 119002},
 {'2018rank': 211,
  'City': Vitória de Santo Antão,
  'State': Pernambuco,
  '2018Estimate': 137915 123930},
 {'2018rank': 212,
  'City': Santana de Parnaíba,
  'State': São Paulo,
  '2018Estimate': 136517 102074},
 {'2018rank': 213,
  'City': Barbacena,
  'State': Minas Gerais,
  '2018Estimate': 136392 121571},
 {'2018rank': 214,
  'City': Cametá,
  'State': Pará,
  '2018Estimate': 136390 120897},
 {'2018rank': 215,
  'City': Santa Rita,
  'State': Paraíba,
  '2018Estimate': 135807 119546},
 {'2018rank': 216,
  'City': Sabará,
  'State': Minas Gerais,
  '2018Estimate': 135421 121085},
 {'2018rank': 217,
  'City': Varginha,
  'State': Minas Gerais,
  '2018Estimate': 134477 120975},
 {'2018rank': 218,
  'City': Apucarana,
  'State': Paraná,
  '2018Estimate': 133726 119159},
 {'2018rank': 219,
  'City': Araras,
  'State': São Paulo,
  '2018Estimate': 132934 117972},
 {'2018rank': 220,
  'City': Simões Filho,
  'State': Bahia,
  '2018Estimate': 132906 112099},
 {'2018rank': 221,
  'City': Brusque,
  'State': Santa Catarina,
  '2018Estimate': 131703 103944},
 {'2018rank': 222,
  'City': Crato,
  'State': Ceará,
  '2018Estimate': 131372 119949},
 {'2018rank': 223,
  'City': Pinhais,
  'State': Paraná,
  '2018Estimate': 130789 112852},
 {'2018rank': 224,
  'City': Araruama,
  'State': Rio de Janeiro,
  '2018Estimate': 130439 110057},
 {'2018rank': 225,
  'City': Resende,
  'State': Rio de Janeiro,
  '2018Estimate': 130334 117243},
 {'2018rank': 226,
  'City': Campo Largo,
  'State': Paraná,
  '2018Estimate': 130091 107711},
 {'2018rank': 227,
  'City': Cubatão,
  'State': São Paulo,
  '2018Estimate': 129760 116010},
 {'2018rank': 228,
  'City': Santa Cruz do Sul,
  'State': Rio Grande do Sul,
  '2018Estimate': 129427 117214},
 {'2018rank': 229,
  'City': Marituba,
  'State': Pará,
  '2018Estimate': 129321 108223},
 {'2018rank': 230,
  'City': Cachoeirinha,
  'State': Rio Grande do Sul,
  '2018Estimate': 129307 116499},
 {'2018rank': 231,
  'City': São Mateus,
  'State': Espírito Santo,
  '2018Estimate': 128542 106974},
 {'2018rank': 232,
  'City': Itapipoca,
  'State': Ceará,
  '2018Estimate': 128135 114693},
 {'2018rank': 233,
  'City': Ji-Paraná,
  'State': Rondônia,
  '2018Estimate': 127907 115593},
 {'2018rank': 234,
  'City': Conselheiro Lafaiete,
  'State': Minas Gerais,
  '2018Estimate': 127539 115578},
 {'2018rank': 235,
  'City': Valinhos,
  'State': São Paulo,
  '2018Estimate': 127123 100905},
 {'2018rank': 236,
  'City': Maranguape,
  'State': Ceará,
  '2018Estimate': 127098 94641},
 {'2018rank': 237,
  'City': Uruguaiana,
  'State': Rio Grande do Sul,
  '2018Estimate': 127079 125171},
 {'2018rank': 238,
  'City': Bragança,
  'State': Pará,
  '2018Estimate': 126436 112285},
 {'2018rank': 239,
  'City': Itaguaí,
  'State': Rio de Janeiro,
  '2018Estimate': 125913 100362},
 {'2018rank': 240,
  'City': Vespasiano,
  'State': Minas Gerais,
  '2018Estimate': 125376 95279},
 {'2018rank': 241,
  'City': Trindade,
  'State': Goiás,
  '2018Estimate': 125328 98159},
 {'2018rank': 242,
  'City': São Félix do Xingu,
  'State': Pará,
  '2018Estimate': 124763 90908},
 {'2018rank': 243,
  'City': Sertãozinho,
  'State': São Paulo,
  '2018Estimate': 124453 110081},
 {'2018rank': 244,
  'City': Jandira,
  'State': São Paulo,
  '2018Estimate': 123481 103784},
 {'2018rank': 245,
  'City': Guarapari,
  'State': Espírito Santo,
  '2018Estimate': 122982 102009},
 {'2018rank': 246,
  'City': Ribeirão Pires,
  'State': São Paulo,
  '2018Estimate': 122607 102741},
 {'2018rank': 247,
  'City': Codó,
  'State': Maranhão,
  '2018Estimate': 122597 115076},
 {'2018rank': 248,
  'City': Birigui,
  'State': São Paulo,
  '2018Estimate': 122359 108479},
 {'2018rank': 249,
  'City': Barcarena,
  'State': Pará,
  '2018Estimate': 122294 94641},
 {'2018rank': 250,
  'City': Colatina,
  'State': Espírito Santo,
  '2018Estimate': 121580 109789},
 {'2018rank': 251,
  'City': Barretos,
  'State': São Paulo,
  '2018Estimate': 121344 111884},
 {'2018rank': 252,
  'City': Votorantim,
  'State': São Paulo,
  '2018Estimate': 121331 105514},
 {'2018rank': 253,
  'City': Catanduva,
  'State': São Paulo,
  '2018Estimate': 121210 112143},
 {'2018rank': 254,
  'City': Arapongas,
  'State': Paraná,
  '2018Estimate': 121198 104010},
 {'2018rank': 255,
  'City': Guaratinguetá,
  'State': São Paulo,
  '2018Estimate': 121073 111322},
 {'2018rank': 256,
  'City': Bagé,
  'State': Rio Grande do Sul,
  '2018Estimate': 120943 116078},
 {'2018rank': 257,
  'City': Paço do Lumiar,
  'State': Maranhão,
  '2018Estimate': 120621 104567},
 {'2018rank': 258,
  'City': Várzea Paulista,
  'State': São Paulo,
  '2018Estimate': 120572 101230},
 {'2018rank': 259,
  'City': Tatuí,
  'State': São Paulo,
  '2018Estimate': 120533 106572},
 {'2018rank': 260,
  'City': Caraguatatuba,
  'State': São Paulo,
  '2018Estimate': 119625 99540},
 {'2018rank': 261,
  'City': Santana,
  'State': Amapá,
  '2018Estimate': 119610 99029},
 {'2018rank': 262,
  'City': Formosa,
  'State': Goiás,
  '2018Estimate': 119506 97903},
 {'2018rank': 263,
  'City': Três Lagoas,
  'State': Mato Grosso do Sul,
  '2018Estimate': 119465 98311},
 {'2018rank': 264,
  'City': Itabira,
  'State': Minas Gerais,
  '2018Estimate': 119186 105694},
 {'2018rank': 265,
  'City': Itatiba,
  'State': São Paulo,
  '2018Estimate': 119090 99705},
 {'2018rank': 266,
  'City': Bento Gonçalves,
  'State': Rio Grande do Sul,
  '2018Estimate': 119049 104470},
 {'2018rank': 267,
  'City': Salto,
  'State': São Paulo,
  '2018Estimate': 117561 103795},
 {'2018rank': 268,
  'City': Almirante Tamandaré,
  'State': Paraná,
  '2018Estimate': 117168 99514},
 {'2018rank': 269,
  'City': Paulo Afonso,
  'State': Bahia,
  '2018Estimate': 117014 107520},
 {'2018rank': 270,
  'City': Araguari,
  'State': Minas Gerais,
  '2018Estimate': 116691 107810},
 {'2018rank': 271,
  'City': Poá,
  'State': São Paulo,
  '2018Estimate': 116530 104574},
 {'2018rank': 272,
  'City': Igarassu,
  'State': Pernambuco,
  '2018Estimate': 115640 97032},
 {'2018rank': 273,
  'City': Ubá,
  'State': Minas Gerais,
  '2018Estimate': 114265 97726},
 {'2018rank': 274,
  'City': Passos,
  'State': Minas Gerais,
  '2018Estimate': 113998 104691},
 {'2018rank': 275,
  'City': Novo Gama,
  'State': Goiás,
  '2018Estimate': 113679 92788},
 {'2018rank': 276,
  'City': Altamira,
  'State': Pará,
  '2018Estimate': 113195 94624},
 {'2018rank': 277,
  'City': Parintins,
  'State': Amazonas,
  '2018Estimate': 113168 96223},
 {'2018rank': 278,
  'City': Ourinhos,
  'State': São Paulo,
  '2018Estimate': 112711 102302},
 {'2018rank': 279,
  'City': São Lourenço da Mata,
  'State': Pernambuco,
  '2018Estimate': 112362 101762},
 {'2018rank': 280,
  'City': Eunápolis,
  'State': Bahia,
  '2018Estimate': 112318 98078},
 {'2018rank': 281,
  'City': Senador Canedo,
  'State': Goiás,
  '2018Estimate': 112224 82712},
 {'2018rank': 282,
  'City': Tucuruí,
  'State': Pará,
  '2018Estimate': 112148 96343},
 {'2018rank': 283,
  'City': Paragominas,
  'State': Pará,
  '2018Estimate': 111764 97459},
 {'2018rank': 284,
  'City': Açailândia,
  'State': Maranhão,
  '2018Estimate': 111757 101022},
 {'2018rank': 285,
  'City': Piraquara,
  'State': Paraná,
  '2018Estimate': 111052 91783},
 {'2018rank': 286,
  'City': Corumbá,
  'State': Mato Grosso do Sul,
  '2018Estimate': 110806 102209},
 {'2018rank': 287,
  'City': Umuarama,
  'State': Paraná,
  '2018Estimate': 110590 100025},
 {'2018rank': 288,
  'City': Coronel Fabriciano,
  'State': Minas Gerais,
  '2018Estimate': 109405 103008},
 {'2018rank': 289,
  'City': Muriaé,
  'State': Minas Gerais,
  '2018Estimate': 108113 99949},
 {'2018rank': 290,
  'City': Patos,
  'State': Paraíba,
  '2018Estimate': 106984 100675},
 {'2018rank': 291,
  'City': Paulínia,
  'State': São Paulo,
  '2018Estimate': 106776 80874},
 {'2018rank': 292,
  'City': Catalão,
  'State': Goiás,
  '2018Estimate': 106618 84964},
 {'2018rank': 293,
  'City': Ariquemes,
  'State': Rondônia,
  '2018Estimate': 106168 88330},
 {'2018rank': 294,
  'City': Santa Cruz do Capibaribe,
  'State': Pernambuco,
  '2018Estimate': 105936 82649},
 {'2018rank': 295,
  'City': Cambé,
  'State': Paraná,
  '2018Estimate': 105704 96427},
 {'2018rank': 296,
  'City': Araxá,
  'State': Minas Gerais,
  '2018Estimate': 105083 93071},
 {'2018rank': 297,
  'City': Erechim,
  'State': Rio Grande do Sul,
  '2018Estimate': 105059 96031},
 {'2018rank': 298,
  'City': Tubarão,
  'State': Santa Catarina,
  '2018Estimate': 104937 96284},
 {'2018rank': 299,
  'City': Bacabal,
  'State': Maranhão,
  '2018Estimate': 104633 98445},
 {'2018rank': 300,
  'City': Ituiutaba,
  'State': Minas Gerais,
  '2018Estimate': 104067 96097},
 {'2018rank': 301,
  'City': Japeri,
  'State': Rio de Janeiro,
  '2018Estimate': 103960 91933},
 {'2018rank': 302,
  'City': Assis,
  'State': São Paulo,
  '2018Estimate': 103666 94659},
 {'2018rank': 303,
  'City': Tailândia,
  'State': Pará,
  '2018Estimate': 103664 79282},
 {'2018rank': 304,
  'City': Itumbiara,
  'State': Goiás,
  '2018Estimate': 103652 91892},
 {'2018rank': 305,
  'City': Lagarto,
  'State': Sergipe,
  '2018Estimate': 103576 94071},
 {'2018rank': 306,
  'City': Iguatu,
  'State': Ceará,
  '2018Estimate': 103255 95011},
 {'2018rank': 307,
  'City': São Pedro da Aldeia,
  'State': Rio de Janeiro,
  '2018Estimate': 102846 86506},
 {'2018rank': 308,
  'City': Lavras,
  'State': Minas Gerais,
  '2018Estimate': 102728 89380},
 {'2018rank': 309,
  'City': Itaperuna,
  'State': Rio de Janeiro,
  '2018Estimate': 102626 89762},
 {'2018rank': 310,
  'City': Leme,
  'State': São Paulo,
  '2018Estimate': 102412 91476},
 {'2018rank': 311,
  'City': Breves,
  'State': Pará,
  '2018Estimate': 101891 92283},
 {'2018rank': 312,
  'City': Tangará da Serra,
  'State': Mato Grosso,
  '2018Estimate': 101764 81918},
 {'2018rank': 313,
  'City': São Gonçalo do Amarante,
  'State': Rio Grande do Norte,
  '2018Estimate': 101102 86151},
 {'2018rank': 314,
  'City': Itaituba,
  'State': Pará,
  '2018Estimate': 101097 95210},
 {'2018rank': 315,
  'City': Santo Antônio de Jesus,
  'State': Bahia,
  '2018Estimate': 100605 86982},
 {'2018rank': 316,
  'City': Itanhaém,
  'State': São Paulo,
  '2018Estimate': 100496 85952},
 {'2018rank': 317,
  'City': Caieiras,
  'State': São Paulo,
  '2018Estimate': 100129 84165}]
import requests
from lxml import html as etree


response = requests.get("https://en.wikipedia.org/wiki/List_of_largest_cities_in_Brazil")
html = etree.fromstring(response.text)
tables = html.xpath("//table[contains(@class,'wikitable')]")

ths = tables[0].xpath('.//tr[1]/th')
keys = []

for th in ths:
    keys.append(" ".join(th.xpath('.//text()')).strip())

rows = tables[0].xpath('.//tbody/tr')