<jupyter_start><jupyter_text>Analysis of the metadata metricsIncludes sthings like age, completeness, number and types of links.Data processing can be found in [Estimated Metadata Age](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/data_processing/Estimated%20Metadata%20age.ipynb), [Metadata Completion Rates](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/data_processing/Metadata%20Completion%20Rates%20by%20Age.ipynb), [Response Word Counts](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/data_processing/Response%20Word%20Counts.ipynb)<jupyter_code>import pandas as pd

df = pd.read_csv('outputs/metadata_metrics.csv', sep='|', parse_dates=[1])
df[:10]<jupyter_output><empty_output><jupyter_text>Bag of Words StatsWe can only aggregate by data for ISO and FGDC. There's no equivalent date in OGC.**Average number of tokens by protocol**| Protocol   |  Average Token Count | |------------|----------------------| | FGDC       | 1740                 | | ISO        | 1292                 | | OGC        | 470                  | | OpenSearch | 218                  |<jupyter_code>from IPython.display import Image
Image(filename='screenshots/iso_bow_year.png', width=600) 

# the image from libreoffice

# ISO tokens over time
from IPython.display import Image
Image(filename='screenshots/fgdc_bow_year.png', width=600) 

# FGDC tokens over time
from IPython.display import Image
Image(filename='screenshots/fgdc_iso_bow_year.png', width=600)<jupyter_output><empty_output><jupyter_text>I think but can't really back it up with anything (seriously, we'd have to track down a bunch of metadata editors from the last twenty years) that metadata dates in ISO and FGDC are not always conceptually the same. Or at the very least, it is possibly the date the metadata record was written as FGDC or ISO, the date from the source metadata record (so the older key-value style text files) or possibly even the date it was emitted from the platform. I do wonder if that not-quite syncing up 2005 on is partly due to automation (the metadata as representation form a store that is either FGDC or ISO but came from row X in the database).I didn't include the oddball years (ie the future!) although I will not that our future metadata seems to have a higher average token count. So good job, future selves!So 1992 ISO. That is fantastic. Two LTER records from the same group with a big chunk of provenance and big chunks of data quality info. https://catalog.data.gov/harvest/object/8422d2e3-3d5f-4dbc-bca2-cf8a79f07c07https://catalog.data.gov/harvest/object/b1e0689c-c08f-4e5b-9c76-11aa23b7d5961992: what happens when people actually write metadata.  A Table of Token Counts by HostCan't have dates or identities here - we don't identify every possible XML thing and not many of those things have a date that *might* be related to the document. I am not considering the header timestamps here - that is related to the delivery of the content, not the internal specifics of the content (ie, I generated this xml on Tuesday and that is what the header reflects but the content was authored in 2012).FYI: roughly 10K unique hosts identified with XML, only 1660 have more than 10 XML documents.<jupyter_code>%matplotlib inline
import pandas as pd
import json as js  # name conflict with sqla
import sqlalchemy as sqla
from sqlalchemy.orm import sessionmaker

pd.set_option("display.max_rows",250)

# grab the clean text from the rds
with open('../local/big_rds.conf', 'r') as f:
    conf = js.loads(f.read())

# our connection
engine = sqla.create_engine(conf.get('connection'))
# some summary stats for the token counts, complete bag of words
sql = """
select r.host, count(r.host) as records_per_host,
    avg(array_length(b.bag_of_words, 1))::int as avg_bow_size,
    min(array_length(b.bag_of_words, 1))::int as min_bow_size,
    max(array_length(b.bag_of_words, 1))::int as max_bow_size
from responses r join bags_of_words b on b.response_id = r.id
where r.host is not null and r.host != ''
group by r.host
having count(r.host) > 10
order by records_per_host desc;
"""

df = pd.read_sql(sql, engine)
df<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/metadata_metrics/select-bow_summary_stats.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-bow_summary_stats.csv)I am not sure what the above numbers mean. That's a lot of variability.<jupyter_code># who's serving what kinds of (identified) things
sql = """with i
as (
    select d.response_id, jsonb_array_elements(d.identity::jsonb) ident
    from identities d
    where d.identity is not null
), h as (
    select r.host, count(r.host) as total_for_host
    from responses r
    where r.host is not null and r.host != ''
    group by r.host
)
select r.host,
    count(i.ident->'protocol') as num_identified_by_protocol,
    i.ident->'protocol' as protocol,
    min(h.total_for_host) as total_for_host
from responses r join i on i.response_id = r.id
    join h on h.host = r.host
group by r.host, protocol
order by r.host asc, protocol desc;"""

df = pd.read_sql(sql, engine)
df<jupyter_output><empty_output><jupyter_text>The sql: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql/metadata_metrics/select-host_by_identified.sql)The csv: [link](https://github.com/b-cube/Response-Identification-Info/blob/master/notebooks/sql_output/select-host_by_identified.csv)Not sure what those mean either but at least we get some sense of results by host - did we get only one kind of thing from a place or multiple kinds of things?So let's drop everything that only has one response and no opensearch (I'm curious about some other things).<jupyter_code>sql = """with i
as (
    select d.response_id, jsonb_array_elements(d.identity::jsonb) ident
    from identities d
    where d.identity is not null
), h as (
    select r.host, count(r.host) as total_for_host
    from responses r
    where r.host is not null and r.host != ''
    group by r.host
)
select r.host,
    count(i.ident->'protocol') as num_identified_by_protocol,
    i.ident->'protocol' as protocol,
    min(h.total_for_host) as total_for_host
from responses r join i on i.response_id = r.id
    join h on h.host = r.host
where h.total_for_host > 1 and i.ident->>'protocol' != 'OpenSearch'
group by r.host, protocol
order by r.host asc, protocol desc;"""

df = pd.read_sql(sql, engine)
from IPython.display import display
with pd.option_context('display.max_rows', 500):
    display(df)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09700044769437398, 'fraction_numerical': 0.017161617669004627, 'mean_word_length': 4.378812199036918, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 29, 'https://': 9, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '463259', 'license': 'MIT', 'n_tokens_mistral': 2184, 'n_tokens_neox': 2056, 'n_words': 712, 'path': 'notebooks/Analysis - Metadata Metrics.ipynb', 'repo_name': 'b-cube/Response-Identification-Info', 'size': '6701'}