<jupyter_start><jupyter_text>Exploring the Queensland naturalisation index from Queensland State ArchivesIn [this notebook](qld_add_series_info_to_naturalisations_index.ipynb) we loaded the Naturalisation Index from the Queensland government data portal and added a series identifier to each record.Now that we've associated each entry in the original dataset with a series, we can break the data down by series to better understand the content of the index.<jupyter_code>import pandas as pd
import altair as alt<jupyter_output><empty_output><jupyter_text>Load the dataset we created with series identifiers.<jupyter_code>df = pd.read_csv('qsa_naturalisations_index_with_series.csv')<jupyter_output><empty_output><jupyter_text>Show the number of entries per series.<jupyter_code>pd.set_option('display.max_colwidth', None)
series_counts = df.value_counts(['series_id', 'series_title']).to_frame().reset_index()
series_counts.columns = ['series_id', 'series_title', 'count']
series_counts
alt.Chart(series_counts).mark_bar().encode(
    x='series_id:N',
    y='count:Q',
    color='series_id:N',
    tooltip=['series_id', 'series_title', 'count']
)<jupyter_output><empty_output><jupyter_text>Visualise records over time<jupyter_code>year_counts = df.value_counts(['Year', 'series_id']).to_frame().reset_index()
year_counts.columns = ['year', 'series', 'count']
year_counts<jupyter_output><empty_output><jupyter_text>See that '18889' at the end? Looks like there's some dodgy dates. Let's sort by year to see what the limits should be.<jupyter_code>year_counts.sort_values('year')<jupyter_output><empty_output><jupyter_text>So, it looks like we should filter out records before 1851 and after 1905.Let's apply that filter and visualise the results for all series in a stacked bar chart.<jupyter_code>alt.Chart(year_counts.loc[(year_counts['year'] >= 1851) & (year_counts['year'] <= 1905)]).mark_bar(size=10).encode(
    x=alt.X('year:Q', axis=alt.Axis(format='c')),
    y=alt.Y('count:Q', stack=True),
    color='series:N',
    tooltip=['year', 'series', 'count']
).properties(width=700)<jupyter_output><empty_output><jupyter_text>To make the different date ranges clearer, we can facet the results by series.<jupyter_code>alt.Chart(year_counts.loc[(year_counts['year'] >= 1851) & (year_counts['year'] <= 1905)]).mark_bar(size=5).encode(
    x=alt.X('year:Q', axis=alt.Axis(format='c')),
    y='count:Q',
    color='series:N',
    facet=alt.Facet('series:N', columns=2),
    tooltip=['year', 'count']
).properties(width=350, height=150)<jupyter_output><empty_output><jupyter_text>We can see that series 5177 and 5745, which both record Oaths of Allegiance, have similar distributions, though there are significant differences. On way we might be able to create a more complete time series would be to combine the results of these two series, then remove duplicates based on name and year.First we filter the dataset to include just these series.<jupyter_code>df_oaths = df.copy().loc[df['series_id'].isin([5177, 5741])]
# How many records?
df_oaths.shape[0]<jupyter_output><empty_output><jupyter_text>Then we remove duplicates based on name and year.<jupyter_code>df_oaths.drop_duplicates(subset=['Last name', 'Given names', 'Year'], inplace=True)
# How many now?
df_oaths.shape[0]<jupyter_output><empty_output><jupyter_text>We can now visualise the deduplicated dataset.<jupyter_code>oath_year_counts = df_oaths.value_counts(['Year', 'series_id']).to_frame().reset_index()
oath_year_counts.columns = ['year', 'series', 'count']
alt.Chart(oath_year_counts.loc[(oath_year_counts['year'] >= 1851) & (oath_year_counts['year'] <= 1905)]).mark_bar(size=10).encode(
    x=alt.X('year:Q', axis=alt.Axis(format='c')),
    y='count:Q',
    tooltip=['year', 'count']
).properties(width=700)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13602941176470587, 'fraction_numerical': 0.019170168067226892, 'mean_word_length': 7.5212527964205815, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 44, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '575110', 'license': 'MIT', 'n_tokens_mistral': 1349, 'n_tokens_neox': 1263, 'n_words': 300, 'path': 'qld_explore_naturalisation_index.ipynb', 'repo_name': 'wragge/naturalization-data-stories', 'size': '3808'}