In [4]:
import pandas as pd # Beautiful tool for data wrangling! e.g. '!pip install pandas' from a Notebook
# See https://mariadb.com/blog/how-connect-python-programs-mariadb e.g. '!pip install mysql' from Notebook
import MySQLdb
import re
from collections import Counter
import os
from collections import OrderedDict
import pickle
pd.set_option("display.max_rows",35) # Useful when having large Pandas DataFrames like we do here
In [8]:
conn = MySQLdb.connect(user='mos', passwd='', db='monuments_db', charset='utf8', use_unicode=True)
cursor = conn.cursor()
cursor.execute("SET NAMES utf8")
Out[8]:
In [9]:
# Load full table into memory
sql = "SELECT * FROM monuments_all"
df = pd.io.sql.read_sql(conn.escape_string(sql), conn)
In [10]:
In [11]:
dz = df[df["country"] == "dz"]
list(dz.iloc[1])
Out[11]:
In [19]:
def create_mapping_tables_from_monuments_all(df, code):
"""Takes a Pandas DataFrame object together with a country code and writes wikitables
to files named after the table they are produced from.
"""
country = df[df["country"] == code]
country_fields = list(country.keys())
country_values = list(country.iloc[0]) # get first row in table as example data from
h1 = "= Non-standardized fields from country " + code + " in monuments_all =\n"
table_header = '{| class="wikitable" style="width: 675px;\n'
table_name = '|+ '+code + "\n"
# create table columns
table_columns = "! heritage field\n! example value\n! Wikidata property\n! Conversion\n! Comment\n|-\n"
table_rows = []
for (field, value) in zip(country_fields, country_values):
row="| "+ str(field) + "\n| " + str(value) + "\n|\n| \n| \n|-\n"
table_rows.append(row)
table_rows_str = "".join(table_rows)
# Fill in examples values from the first record in the table
table_footer = "\n|}"
wikitable = table_header + table_name + table_columns + table_rows_str[:-1] + table_footer
if os.path.isdir("./mappingtables"):
out = open("./mappingtables/" + code + ".mappingtable","w")
out.write(wikitable)
out.flush()
print("Directory ./mappingtables exists. Wrote file {}".format(out.name))
out.close()
else:
os.mkdir("./mappingtables")
with open("./mappingtables/" + code + ".mappingtable","w") as out:
out.write(wikitable)
print("./mappingtables doesn't exist. Created it and wrote file {}".format(out.name))
In [20]:
country_codes = pickle.load(open("./country_codes.pickle", "rb"))
for code in country_codes:
create_mapping_tables_from_monuments_all(df, code)
In [ ]:
languages = ['az', 'cs', 'da', 'ru', 'nl', 'it', 'fr', 'ca', 'hu', 'sv', 'lb', 'den', 'no',\
'es', 'sr', 'uk', 'de', 'ar', 'be-tarask', 'he', 'en', 'hy', 'gl', 'pl', 'et', 'th', 'ro', 'sk', 'pt']
In [ ]: