In [1]:

    

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 random

pd.set_option("display.max_rows",35) # Useful when having large Pandas DataFrames like we do here


    

In [4]:

    

def create_monuments_all_mappings_and_examples_page(df):
    """Takes a Pandas DataFrame object and writes wikitables to files named after the table they are
    produced from to {the current Directory}/wikitables/
    """
    country_codes = dict(df.country.value_counts()).keys()
    
    def create_examples_table(code):
        """Takes a string country code from the monuments_all table in monuments_db and returns a string 
        wikipage with an examples table containing 10 random values for each field for the specified country."""
        h1 = "= Non-standardized fields from country " + code + " in monuments_all =\n"
        column_tables = [] 
        country = df[df["country"] == code]
        lang = country.lang.sample(n=1).to_string().split("    ")[1] # A very complicated way of getting one value!
        columns = country.columns
        page_sections = []
        
        for column in columns:
            # the listed fields we avoid are assumed to be standardized
            if column not in ["country","lang","project","changed","lat","lon","lat_int","lon_int"]:
                h2 = "== 10 random samples from field " + column + " ==\n"
                values = country[column]
                try:
                    sample = values.sample(n=10)
                    examples = sample.values
                except ValueError as e:
                    print("country: {} cannot be sampled.\n Error: {}\n Total objects is {}. Skipping country.".format(code, e, len(values)))
                    break
    
                table_header = '{| class="wikitable" style="width: 675px;\n'
                table_name = '|+ '+code + "-" + column + " 10 random samples\n"
            
                # create table columns
                table_columns = "! scope='col' style='width: 225px;' |" + str(column) + "\n" + \
                "! scope='col' style='width: 225px; height: 20px;'|" + "Conversion \n" + \
                "! scope='col' style='width: 225px;' | Comment\n|-\n"
                
                table_rows = []
            
                for example in examples:
                    row="| style='height: 20px;'| " + str(example) + "\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|}"
            else:
                continue
            
            column_tables.append(h2)    
            column_table = table_header + table_name + table_columns + table_rows_str[:-1] + table_footer + "\n"
            column_tables.append(column_table)
            
        
        examples_page = h1 + "\n".join(column_tables)
        return examples_page
        
        
        
        ##### if __name__ == "main:"
        for code in country_codes:
            examples_table = create_examples_table(code)
            mapping_table = create_mapping_table(code)
            merged_tables = merge_mapping_and_example_table(mapping_table, example_table)
            save_merged_tables(merged_tables)
    
        if os.path.isdir("./langfiles_all"):
            #print(wikipage)
            out = open("./langfiles_all/" + lang + ".examples","w")
            out.write(wikipage)
            out.flush()
            print("Directory ./langfiles_all exists. Wrote file {}".format(out.name))
            out.close()
            
        else:
            os.mkdir("./langfiles_all")
            with open("./langfiles_all/" + lang + ".examples","w") as out:
                out.write(wikipage)
                print("./langfiles_all doesn't exist")
        
        if os.path.isdir("./countryfiles_all"):
            #print(wikipage)
            out = open("./countryfiles_all/" + code + ".examples","w")
            out.write(wikipage)
            out.flush()
            print("Directory ./countryfiles_all exists. Wrote file {}".format(out.name))
            out.close()
            
        else:
            os.mkdir("./countryfiles_all")
            with open("./countryfiles_all/" + code + ".examples","w") as out:
                out.write(wikipage)
                print("./countryfiles_all doesn't exist")


    

In [91]:

    

conn = MySQLdb.connect(user='mos', passwd='', db='monuments_db', charset='utf8', use_unicode=True)
cursor = conn.cursor()
cursor.execute("SET NAMES utf8")

# Load full table into memory
sql = "SELECT * FROM monuments_all"
df = pd.io.sql.read_sql(conn.escape_string(sql), conn)

create_monuments_all_value_example_tables(df)


    

    




Directory ./langfiles exists. Wrote file ./langfiles/nl.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/be-wal.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/bo.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/us.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/gb-sct.examples
Directory ./langfiles exists. Wrote file ./langfiles/nl.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/nl-prov.examples
Directory ./langfiles exists. Wrote file ./langfiles/de.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/de-he.examples
Directory ./langfiles exists. Wrote file ./langfiles/fr.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/tn.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/pa.examples
Directory ./langfiles exists. Wrote file ./langfiles/de.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ch.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/aq.examples
Directory ./langfiles exists. Wrote file ./langfiles/da.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/dk-bygning.examples
Directory ./langfiles exists. Wrote file ./langfiles/fr.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/be-vlg.examples
Directory ./langfiles exists. Wrote file ./langfiles/az.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/az.examples
Directory ./langfiles exists. Wrote file ./langfiles/fr.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ca.examples
Directory ./langfiles exists. Wrote file ./langfiles/sv.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/se-arbetsl.examples
Directory ./langfiles exists. Wrote file ./langfiles/ca.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/it-88.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/hk.examples
Directory ./langfiles exists. Wrote file ./langfiles/cs.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/cz.examples
Directory ./langfiles exists. Wrote file ./langfiles/no.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/no.examples
Directory ./langfiles exists. Wrote file ./langfiles/de.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/at.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ve.examples
Directory ./langfiles exists. Wrote file ./langfiles/fr.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/fr-object.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/in.examples
Directory ./langfiles exists. Wrote file ./langfiles/nl.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/be-bru.examples
Directory ./langfiles exists. Wrote file ./langfiles/sv.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/se-fornmin.examples
Directory ./langfiles exists. Wrote file ./langfiles/da.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/dk-fortids.examples
Directory ./langfiles exists. Wrote file ./langfiles/lb.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/lu.examples
Directory ./langfiles exists. Wrote file ./langfiles/nl.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/nl-gem.examples
Directory ./langfiles exists. Wrote file ./langfiles/ca.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ad.examples
Directory ./langfiles exists. Wrote file ./langfiles/sv.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/se-bbr.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/gb-eng.examples
Directory ./langfiles exists. Wrote file ./langfiles/de.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/de-nrw-bm.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/gh.examples
country: cm cannot be sampled.
 Error: Cannot take a larger sample than population when 'replace=False'
 Total objects is 1. Skipping country.
Directory ./langfiles exists. Wrote file ./langfiles/fr.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/cm.examples
Directory ./langfiles exists. Wrote file ./langfiles/it.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/it.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/cl.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/us-ca.examples
Directory ./langfiles exists. Wrote file ./langfiles/ro.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ro.examples
Directory ./langfiles exists. Wrote file ./langfiles/pt.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/pt.examples
Directory ./langfiles exists. Wrote file ./langfiles/hy.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/am.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/nl-aw.examples
Directory ./langfiles exists. Wrote file ./langfiles/he.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/il.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/pk.examples
Directory ./langfiles exists. Wrote file ./langfiles/ar.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/jo.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ch-old.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/cn.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/gb-wls.examples
Directory ./langfiles exists. Wrote file ./langfiles/uk.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ua.examples
Directory ./langfiles exists. Wrote file ./langfiles/ru.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ru.examples
Directory ./langfiles exists. Wrote file ./langfiles/nl.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/nl.examples
Directory ./langfiles exists. Wrote file ./langfiles/th.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/th.examples
Directory ./langfiles exists. Wrote file ./langfiles/ca.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/fr.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/uy.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/za.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/sv.examples
Directory ./langfiles exists. Wrote file ./langfiles/de.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/it-bz.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ph.examples
Directory ./langfiles exists. Wrote file ./langfiles/ca.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/es.examples
Directory ./langfiles exists. Wrote file ./langfiles/de.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/de-by.examples
Directory ./langfiles exists. Wrote file ./langfiles/ar.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/dz.examples
Directory ./langfiles exists. Wrote file ./langfiles/sv.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/se-ship.examples
Directory ./langfiles exists. Wrote file ./langfiles/den.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ke.examples
Directory ./langfiles exists. Wrote file ./langfiles/de.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/mt.examples
Directory ./langfiles exists. Wrote file ./langfiles/et.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ee.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/co.examples
Directory ./langfiles exists. Wrote file ./langfiles/sr.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/rs.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ar.examples
Directory ./langfiles exists. Wrote file ./langfiles/pl.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/pl.examples
Directory ./langfiles exists. Wrote file ./langfiles/be-tarask.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/by.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/ie.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/np.examples
Directory ./langfiles exists. Wrote file ./langfiles/hu.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/hu.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/jp-nhs.examples
Directory ./langfiles exists. Wrote file ./langfiles/es.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/mx.examples
Directory ./langfiles exists. Wrote file ./langfiles/de.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/de-nrw-k.examples
Directory ./langfiles exists. Wrote file ./langfiles/sk.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/sk.examples
Directory ./langfiles exists. Wrote file ./langfiles/en.examples
Directory ./countryfiles exists. Wrote file ./countryfiles/gb-nir.examples

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']