# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# +
from lxml import etree
import pandas as pd
from collections import Counter
import os
import glob
import re
import matplotlib.pyplot as plt
import numpy as np
from collections import Counter
from numpy import array
import numpy as np


wdir = "/home/jose/Dropbox/biblia/tb/"
file = "TEIBible" # "*.xml"
outdir = "/home/jose/Dropbox/biblia/tb/resulting data/"


# -

entities = pd.ExcelFile(wdir + "entities.xls",  index_col=0).parse('Sheet1').fillna("")

entities.head(3)

secure_places = entities.loc[ (entities["type"]== "place" ) & (entities["latitude"]!="" ) & (entities["longitude"]!="") & (entities["geo_cert"].isin(["high","medium"]))]
secure_places.shape

secure_places.head(3)

books = pd.ExcelFile(wdir + "documentation/books.xlsx",  index_col=0).parse('Sheet1').fillna("")

books.head(3)

parser = etree.XMLParser(encoding='utf-8')

documento_xml = etree.parse(wdir+file+".xml", parser)


documento_root = documento_xml.getroot()

namespaces_concretos = {'tei':'http://www.tei-c.org/ns/1.0','xi':'http://www.w3.org/2001/XInclude'}


# +
books["latitude"] = ""
books["longitude"] = ""

for book in documento_root.xpath('//tei:TEI', namespaces=namespaces_concretos, with_tail=True):
    title = book.xpath('.//tei:title[2]/tei:idno[@type="string"]/text()', namespaces=namespaces_concretos, with_tail=True)[0]
    rss = book.xpath('.//tei:rs[not(@cert)]/@key|.//tei:rs[@cert="medium"]/@key', namespaces=namespaces_concretos, with_tail=True)
    set_entities = list(set([entity for reference in rss for entity in reference.split(" ")]))
    print(title)
    print(len(rss))
    print(len(set_entities))

    longitude = secure_places.loc[secure_places["id"].isin(set_entities)]["longitude"].mean()
    latitude = secure_places.loc[secure_places["id"].isin(set_entities)]["latitude"].mean()
    
    books.loc[books["codebook"]==title, ["longitude"]] = longitude
    
    books.loc[books["codebook"]==title, ["latitude"]] = latitude
    print(longitude, latitude)
    
    
# -

books

books.to_excel(wdir+"documentation/books_2.xlsx", encoding="utf-8")




StarcoderdataJupyterScriptsDedupFiltered