<jupyter_start><jupyter_text>Table of Contents1&nbsp;&nbsp;Partial updates<jupyter_code>import os
import requests
import pprint
import pandas as pd
import pandas_gbq
import numpy as np
import re
import itertools

from time import sleep
from google.oauth2 import service_account
from bs4 import BeautifulSoup
from datetime import datetime
from requests_html import HTML , HTMLSession
HOME = "https://www.hemnet.se"
url=HOME + "/sitemap"
def find_el_by_cl(r_html, el='li', cl=('sitemap__list-item',)):
        els=r_html.find(el)
        return [el for el in els if el.attrs.get('class')==cl]
        

def get_lists(url):
    r=requests.get(url)
    r_html=HTML(html=r.text)
    els=find_el_by_cl(r_html,  el='li', cl=('sitemap__list-item',) )
    els=[el.find('li')[0].find('a')[0].attrs.get('href') for el in els]
    return  [el for el in els if el.startswith('/sitemap/plats/')]
lans=get_lists(url)
#find_el_by_cl(r_html, el='h1', cl='sitemap__heading')
city=r_html.find('h1')[0].text.split(' ')[2]

lans_urls = [HOME+el for el in lans]
def get_urls(urls):
    return [get_lists(url) for url in urls]
komms_raw=get_urls(lans_urls)
komms=list(itertools.chain(*komms_raw))
komms_urls=[HOME+el for el in komms]
komms_urls
# communs=[HOME+'/salda/'+url.split('/')[-1] for url in urls_list]
# communs
pd.DataFrame({'communs':communs}).to_pickle('communs')
def get_districts(komms_url):
    sleep(1)
    r_html=HTML(html=requests.get(komms_url).text)
    city=r_html.find('h1')[0].text.split(' ')[2]
    els =[re.sub("[\(\[].*?[\)\]]", "", el.text.replace('+', '-')) for el in find_el_by_cl(r_html, el='li', cl=('sitemap__list-item',))]
    districts=np.unique(list(itertools.chain(*[[x.strip() for x in d.split('-') if x !=''] for d in els])))
    districts = pd.DataFrame({'district':districts})
    districts['city']=city
    return districts

districts_raw=[get_districts(url) for url in komms_urls]

dim_districts=pd.concat(districts_raw)
dim_districts=pd.concat([dim_districts, 
 pd.DataFrame({'district':['Tycho Hedéns väg', 'Dag Hammarskjölds Väg'], 'city': ['Uppsala', 'Uppsala']})])
pandas_gbq.to_gbq(dim_districts, destination_table='crime_statistics.dim_districts_hemnet', 
                  project_id= os.environ['GCP_PROJECID'], if_exists='replace')
q_url=f"""
https://nominatim.openstreetmap.org/search/405 Säter Sweden?format=json&limit=1
"""
requests.get(q_url).text
import time
tic=time.time()
df = pandas_gbq.read_gbq("""SELECT * FROM `my-user-project-287504.crime_statistics.cities_refined` 
                    """, project_id=os.environ['GCP_PROJECID'])
toc=time.time()
print(toc-tic)
pandas_gbq.to_gbq(dim_districts, destination_table='crime_statistics.dim_districts_hemnet', 
                  project_id= os.environ['GCP_PROJECID'], if_exists='replace')
### Partial updates

#ToDo: 
# city_refined
# 1. pull from bq
# extract location details again
# get osm coordinates
# upload to bq


# city_refined_en
# 1. pull from bq
# extract location details again
# get osm coordinates
# upload to bq<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15856861457649377, 'fraction_numerical': 0.008535784635587657, 'mean_word_length': 5.321576763485477, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '85500', 'license': 'MIT', 'n_tokens_mistral': 1200, 'n_tokens_neox': 1171, 'n_words': 221, 'path': 'hemnet_scrap.ipynb', 'repo_name': 'alcoholfreebear/crime-stats-polisen-api', 'size': '3046'}