<jupyter_start><jupyter_text>Supporting workings - Navigating census dataAs support for the main workbook, I first need an effective way of selecting which categories to select in the first instance from the extremely large datasets. From the raw metadata file for the 2016 datapack, there was 15536 different data points for each statistical area across all 59 tables. Some of these were totals of other lines, some were the total population, some had combinations of classifications available elsewhere in the overall population, etc. so a mechanism of refinement is clearly necessary.Ideally I would like to be able to import data based on some simple aggregations where possible (e.g. by sex), or otherwise taking the "highest" level data possible given the input constraints. E.g. I would like to say "Import data from tables G09, G52 and G53, but split by sex where possible", if "sex" is not available in one of the tables, it will still return the data from the table, but from the most aggregated column(s) only. Additionally, if "Sex" is only available as a sub-category of another split (e.g. the measure is "Sex by Age") I would like to be able to perform my own aggregation, to abstract away the unneccessary other details imbedded in the data (e.g. sum all the "Age" categories together, grouping by sex).<jupyter_code># Import statements
# Declare Imports
import numpy as np
import pandas as pd
import os
import re
import operator
import csv

# Set a variable for current notebook's path for various loading/saving mechanisms
nb_path = os.getcwd()
# Generalise this importation method to allow easy imports based on folder name (for SA level) 
# and a list of datapack files you want to amalgamate into a single dataframe
def load_census_csv(table_list, statistical_area_code):
    statistical_area_code = statistical_area_code.upper()
    for index, table in enumerate(table_list):
    
        if index==0:
            df = pd.read_csv('{}\Data\{}\AUST\\2016Census_{}_AUS_{}.csv'.format(nb_path,
                                                                                statistical_area_code,
                                                                                table,
                                                                                statistical_area_code
                                                                               ),
                                       engine='python')
        else:
            temp_df = pd.read_csv('{}\Data\{}\AUST\\2016Census_{}_AUS_{}.csv'.format(nb_path,
                                                                                statistical_area_code,
                                                                                table,
                                                                                statistical_area_code
                                                                               ),
                                       engine='python')
            merge_col = df.columns[0]
            df = pd.merge(df, temp_df, on=merge_col)
    
    return df<jupyter_output><empty_output><jupyter_text>Begin importing and exploring data<jupyter_code># Import metadata sheets
df_meta_tables = pd.read_excel('{}\Data\Metadata\Metadata_2016_GCP_DataPack.xlsx'.format(nb_path),
                               sheet_name = 'Table number, name, population',
                               skiprows=9)
df_meta_measures = pd.read_excel('{}\Data\Metadata\Metadata_2016_GCP_DataPack.xlsx'.format(nb_path),
                               sheet_name = 'Cell descriptors information',
                               skiprows=10)
df_meta_tables.tail()
df_meta_measures['Table number'] = df_meta_measures['DataPack file'].str[:3]
df_meta_measures = pd.merge(df_meta_measures, df_meta_tables, on='Table number')
df_meta_measures.shape
df_meta_measures.tail(20)<jupyter_output><empty_output><jupyter_text>Quick notes:I noticed that very conveniently a lot of the capitalisation in the "Long" field works as a delimiter between different population characteristics. Working through some exceptions below, split the Long fields based on capitalisation (and a couple of other hueristics) to get down to a much shorter list of 911 characteristics. I turned this information into a csv file which I then did some tedious review on in order to come up with a much shorter list of 61 categories and created a new "Meta" file which I import after this section.<jupyter_code>def lower_all_except_first_char(str_item):
    str_replace = '{}{}'.format(str_item[:1],str_item[1:].lower())
    df_meta_measures['Long'] = df_meta_measures['Long'].str.replace(str_item, str_replace)
replace_capitalisation_list = ['Not_stated',
                               'None',
                               'No_children',
                               'Aboriginal_and_or_Torres_Strait_Islander',
                               'Both_Aboriginal_and_Torres_Strait_Islander',
                               'Torres_Strait_Islander',
                               'Non_Indigenous',
                               'New_Zealand',
                               'South_Africa',
                               'Sri_Lanka',
                               'Bosnia_and_Herzegovina',
                               'China_excludes_SARs_and_Taiwan',
                               'Hong_Kong_SAR_of_China',
                               'Korea_Republic_of_South',
                               'Northern_Ireland',
                               'Papua_New_Guinea',
                               'South_Eastern_Europe',
                               'Total_Responses',
                               'Census_Night',
                               'Elsewhere_in_Australia',
                               'Birthplace_Australia',
                               'Birthplace_Elsewhere',
                               'Other_Language',
                               'Age_of_Persons',
                               'Count_of_Persons',
                               'Average_number_of_Persons_per_bedroom',
                               'Visitor_from_Different_SA2',
                               'Visitor_from_Same_Statistical_Area_Level_2_SA2',
                               'Western_Australia',
                               'Northern_Territory',
                               'Australian_Capital_Territory',
                               'South_Australia',
                               'New_South_Wales',
                               'Other_Territories',
                               'China_excl_SARs_and_Taiwan',
                               'United_Kingdom_Channel_Islands_and_Isle_of_Man',
                               'The_Former_Yugoslav_Republic_of_Macedonia',
                               'United_States_of_America',
                               'Year_of_arrival_Before',
                               'Speaks_English',
                               'speaks_English',
                               'Proficiency_in_English',
                               'Proficiency_in_english',
                               '_Before_2000',
                               'Total_Year_of_arrival_not_stated',
                               'Australian_Indigenous_Languages',
                               'Chinese_Languages_Cantonese',
                               'Chinese_Languages_Mandarin',
                               'Chinese_Languages_Other',
                               'Chinese_languages_Other',
                               'Chinese_Languages_Total',
                               'Indo_Aryan_Languages_Bengali',
                               'Indo_Aryan_Languages_Hindi',
                               'Indo_Aryan_Languages_Punjabi',
                               'Indo_Aryan_Languages_Sinhalese',
                               'Indo_Aryan_Languages_Urdu',
                               'Indo_Aryan_Languages_Other',
                               'Indo_Aryan_Languages_Total',
                               'Persian_excluding_Dari',
                               'Southeast_Asian_Austronesian_Languages_Filipino',
                               'Southeast_Asian_Austronesian_Languages_Indonesian',
                               'Southeast_Asian_Austronesian_Languages_Tagalog',
                               'Southeast_Asian_Austronesian_Languages_Other',
                               'Southeast_Asian_Austronesian_Languages_Total',
                               'Christianity_Anglican',
                               'Christianity_Assyrian_Apostolic',
                               'Christianity_Baptist',
                               'Christianity_Brethren',
                               'Christianity_Catholic',
                               'Christianity_Churches_of_Christ',
                               'Christianity_Eastern_Orthodox',
                               'Christianity_Jehovahs_Witnesses',
                               'Christianity_Latter_day_Saints',
                               'Christianity_Lutheran',
                               'Christianity_Oriental_Orthodox',
                               'Christianity_Other_Protestant',
                               'Christianity_Pentecostal',
                               'Christianity_Presbyterian_and_Reformed',
                               'Christianity_Salvation_Army',
                               'Christianity_Seventh_day_Adventist',
                               'Christianity_Uniting_Church',
                               'Christianity_Christianity_nfd',
                               'Christianity_Other_Christian',
                               'Christianity_Total',
                               'Other_Religions_Australian_Aboriginal_Traditional_Religions',
                               'Other_Religions_Sikhism',
                               'Other_Religions_Other',
                               'Other_Religions_Total',
                               'Secular_Beliefs_and_Other_Spiritual_Beliefs_and_No_Religious_Affiliation_No_Religion_So_Described',
                               'Secular_Beliefs_and_Other_Spiritual_Beliefs_and_No_Religious_Affiliation_Secular_Beliefs',
                               'Secular_Beliefs_and_Other_Spiritual_Beliefs_and_No_Religious_Affiliation_Other_Spiritual_Beliefs',
                               'Secular_Beliefs_and_Other_Spiritual_Beliefs_and_No_Religious_Affiliation_Total',
                               'Infants_Primary',
                               'Other_Non_Government',
                               'Technical_or_Further_Educational_institution',
                               'Full_Part_time',
                               'University_or_other_Tertiary_Institution',
                               'Males_Negative_Nil_income', #maybe look at this whole section
                               'Females_Negative_Nil_income', #maybe look at this whole section
                               'Persons_Negative_Nil_income', #maybe look at this whole section
                               'Males_Personal_income_not_stated',#maybe look at this whole section
                               'Feales_Personal_income_not_stated',#maybe look at this whole section
                               'Persons_Personal_income_not_stated',#maybe look at this whole section
                               'Cared_for_Own',
                               'Cared_for_Other',
                               'Visitor_from_within_Australia',
                               'born_in_Australia',
                               'Real_Estate_Agent',
                               'with_Children',
                               'with__No_children',
                               'Flat_or_Apartment',
                               'Graduate_Diploma_and_Graduate_Certificate_Level_Graduate_Diploma_Level', 
                               'Advanced_Diploma_and_Diploma_Level_Advanced_Diploma_and_Associate_Degree_Level',
                               'Advanced_Diploma_and_Diploma_Advanced_Diploma_and_Associate_Degree_Level',
                               'Certificate_Level_Certificate_III_and_IV_Level',
                               'Certificate_Level_Certificate_I_and_II_Level',
                               'Certificate_Level_Certificate_Level_nfd',
                               'Graduate_Diploma_and_Graduate_Certificate_Level',
                               'Advanced_Diploma_and_Diploma_Level_Diploma_Level',
                               'Occupation_Inadequately_described',
                               'Postgraduate_Degree_Level',
                               'Master_Degree_Level',
                               'Doctoral_Degree_Level',
                               'Certificate_I_and_II_Level',
                               'Certificate_III_and_IV_Level',
                               'Advanced_Diploma_and_Diploma_Level',
                               'Graduate_Certificate_Level',
                               'Bachelor_Degree_Level',
                               'Certificate_Level',
                               'Different_SA2',
                               'Same_Statistical_Area_Level_2',
                               'Lone_Parent',
                               'Worked_Full_Time',
                               'Worked_Part_Time',
                               'Away_From_Work',
                               'Age_Of_Dependent_Children',
                               '4_Years',
                               '9_Years',
                               '2_Years',
                               '7_Years',
                               '0_Years',
                               'Hours_Worked',
                               'Labour_Force_Status_Not_Stated',
                               'Not_In_The_Labour_Force',
                               'Labour_Force',
                               'Looking_For_Full_Time_Work',
                               'Looking_For_Part_Time_Work',
                               'Natural_and_Physical_Sciences',
                               'Information_Technology',
                               'Engineering_and_Related_Technologies',
                               'Architecture_and_Building',
                               'Agriculture_Environmental_and_Related_Studies',
                               'Management_and_Commerce',
                               'Society_and_Culture',
                               'Creative_Arts',
                               'Food_Hospitality_and_Personal_Services',
                               'Mixed_Field_Programmes',
                               'Male_Parent',
                               'Looking_For',
                               'Hours_Worked_Not_Stated',
                               'Inadequately_described_Not_stated',
                               'Number_of_hours_worked_None',
                               'Number_of_hours_worked_Not_stated',
                               'Dependent_children_In_Couple_Families',
                               'Negative_Nil',
                               'Never_Married',
                               '_Census_night',
                               'Speaks_English',
                               'Speaks_english',
                               'Speaks_other'
                              ]

# note: there were still some areas I missed and had to clean up in the tedious manual work on the csv file
replace_capitalisation_list.sort(key = len, reverse=True)
for correction in replace_capitalisation_list:
    lower_all_except_first_char(correction)
df_meta_measures.tail(20)
# replace any words following the word "and" with a lowercase version
replace_ands = set()
for cats in df_meta_measures.Long.unique():
    try:
        word = re.search('(?<=_and_)\w+', cats).group(0).split('_')[0]
        replace_ands.add('_and_{}'.format(word))
    except:
        pass

for repl in replace_ands:
    df_meta_measures['Long'] = df_meta_measures['Long'].str.replace(repl,repl.lower())
    
# replace any words following the word "Occupation" with a lowercase version
replace_occs = set()
for cats in df_meta_measures.Long.unique():
    try:
        word = re.search('(?<=ccupation_)\w+', cats).group(0).split('_')[0]
        replace_occs.add('ccupation_{}'.format(word))
    except:
        pass

for repl in replace_occs:
    df_meta_measures['Long'] = df_meta_measures['Long'].str.replace(repl,repl.lower())
measure_cats = []
for category in df_meta_measures.Long.tolist():
    measure_cats.append(re.findall('[A-Z][^A-Z]*', category))
#export list to csv for framework to build reference table
with open("out.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerows(measure_cats)<jupyter_output><empty_output><jupyter_text>Begin working with new metaclasses fileThis file has a kind of "OneHotEncoding" to assist infer which classes particular measures fall into, e.g. do they have detail for "Age", "Sex", "Place of Birth", etc.<jupyter_code>df_meta = pd.read_csv('{}\Data\Metadata\Metadata_2016_refined.csv'.format(nb_path))
df_meta.head()
#Thoughts on algorithms to select line items
# For table in table_list
    # slice meta based on table
    # for category in filter_cats 
        # slice based on category >0
    # select rows with lowest value in "Number of Classes Excl Total" field
test_tbl = 'G57'
test_cats = ['Sex','Age'] # testing for rows that have a "Total" row first
meta_df_select = df_meta[df_meta['Profile table'].str[:3] == test_tbl].copy()
for cat in test_cats:
    meta_df_select = meta_df_select[meta_df_select[cat]>0]
min_fields = meta_df_select['Number of Classes Excl Total'].min()
meta_df_select = meta_df_select[meta_df_select['Number of Classes Excl Total'] == min_fields]
print(meta_df_select.shape)
meta_df_select
# Select the table file(s) to import
import_table_list = meta_df_select['DataPack file'].unique()
# Import the data table itself
df_data = load_census_csv(import_table_list, 'SA3')
# Select only columns included in the meta-sliced table above
df_data.set_index(df_data.columns[0], inplace=True)
refined_columns = meta_df_select.Short.tolist()
df_data = df_data[refined_columns]
df_data.shape
# aggregate data by:
# transposing the dataframe
df_data_t = df_data.T.reset_index()
df_data_t.rename(columns={ df_data_t.columns[0]: 'Short' }, inplace = True)
# merging with the refined meta_df to give table name, "Measures" and "Categories" fields
meta_merge_ref = meta_df_select[['Short','Table name','Measures','Categories']]
df_data_t = df_data_t.merge(meta_merge_ref, on='Short')
df_data_t.head()
#get positions in measures field for selected categories
position_list = []
for i, j in enumerate(df_data_t.Categories.iloc[0].split("|")):
    if j in test_cats:
        position_list.append(i)
print(position_list)

table_name = df_data_t['Table name'].iloc[0].replace(" ","_") + "|"
# from the "Categories" field, you should be able to split an individual entry by the "|" character
# to give the index of the measure you are interested in grouping by
# create a new column based on splitting the "Measure" field and selecting the value of this index/
    # or textjoining indices where there are multiple categories selected
    # also remove any instance of the word "total" and any multiple underscores
# Merge above with the table name to form "[Table_Name]|[groupby_value]" to have a good naming convention
# eg "Method_of_Travel_to_Work_by_Sex|Three_methods_Females"

#p = table_name + '_'.join([df_data_t.Measures.iloc[0].split("|")[i] for i in position_list])

def custom_name(table_namer, string_item, measure_positions): 
    return table_namer + '_'.join([string_item.split("|")[i] for i in measure_positions])

df_data_t['Test_name'] = df_data_t.apply(lambda x: custom_name(x['Table name'], x['Measures'], position_list), axis=1)
df_data_t.head()
# then groupby this new column 
# then transpose again and either create the base data_df for future merges or merge with the already existing data_df
df_data_t = df_data_t.drop(['Short','Table name','Measures','Categories'], axis=1)
df_data_t = df_data_t.groupby(['Test_name']).sum()
df_data_t.head()
df_data = df_data_t.T
df_data.head()
# Consolidate into a single function
def refine_measure_name(table_namer, string_item, category_item, category_list):
    '''Simple function for generating measure names based on custom metadata information on ABS measures'''
    position_list = []
    for i, j in enumerate(category_item.split("|")):
        if j in category_list:
            position_list.append(i)
    return table_namer + '|' + '_'.join([string_item.split("|")[i] for i in position_list])

def load_table_refined(table_ref, category_list, statistical_area_code='SA3'):
    '''
    Function for loading ABS census data tables, and refining/aggregating by a set of defined categories
    (e.g. age, sex, occupation, English proficiency, etc.) where available.
    
    INPUTS
    table_ref: STRING - the ABS Census Datapack table to draw information from (G01-G59)
    category_list: LIST of STRING objects - Cetegorical informatio to slice/aggregate information from (e.g. Age)
    statistical_area_code: STRING - the ABS statistical area level of detail required (SA1-SA3)
    '''
    df_meta = pd.read_csv('{}\Data\Metadata\Metadata_2016_refined.csv'.format(os.getcwd()))
    
    # slice meta based on table
    meta_df_select = df_meta[df_meta['Profile table'].str[:3] == table_ref].copy()
    
    # for category in filter_cats, slice based on category >0
    for cat in category_list:
        # First, check if there *are* any instances of the given category
        if meta_df_select[cat].sum() > 0:
            # If so, apply the filter
            meta_df_select = meta_df_select[meta_df_select[cat]>0]
        else:
            pass # If not, don't apply (otherwise you will end up with no selections)
        
    # select rows with lowest value in "Number of Classes Excl Total" field
    min_fields = meta_df_select['Number of Classes Excl Total'].min()
    meta_df_select = meta_df_select[meta_df_select['Number of Classes Excl Total'] == min_fields]
    
    # Select the table file(s) to import
    import_table_list = meta_df_select['DataPack file'].unique()
    
    # Import the SA data tables
    df_data = load_census_csv(import_table_list, statistical_area_code.upper())
    
    # Select only columns included in the meta-sliced table above
    df_data.set_index(df_data.columns[0], inplace=True)
    refined_columns = meta_df_select.Short.tolist()
    df_data = df_data[refined_columns]
    
    # aggregate data by:
    # transposing the dataframe
    df_data_t = df_data.T.reset_index()
    df_data_t.rename(columns={ df_data_t.columns[0]: 'Short' }, inplace = True)
    # merging with the refined meta_df to give table name, "Measures" and "Categories" fields
    meta_merge_ref = meta_df_select[['Short','Table name','Measures','Categories']]
    df_data_t = df_data_t.merge(meta_merge_ref, on='Short')
    
    # from the "Categories" field, you should be able to split an individual entry by the "|" character
    # to give the index of the measure you are interested in grouping by
    # create a new column based on splitting the "Measure" field and selecting the value of this index/indices
    # Merge above with the table name to form "[Table_Name]|[groupby_value]" to have a good naming convention
    # eg "Method_of_Travel_to_Work_by_Sex|Three_methods_Females"
    df_data_t['Test_name'] = df_data_t.apply(lambda x: refine_measure_name(x['Table name'], 
                                                                           x['Measures'], 
                                                                           x['Categories'], 
                                                                           category_list), axis=1)
    
    # then groupby this new column 
    # then transpose again and either create the base data_df for future merges or merge with the already existing data_df
    df_data_t = df_data_t.drop(['Short','Table name','Measures','Categories'], axis=1)
    df_data_t = df_data_t.groupby(['Test_name']).sum()
    
    return df_data_t.T
# Test aggregation on import on category detail without "Total" fields
df_no_total = load_table_refined('G01', ['Education'])
df_no_total.head()
# Test import on table with no category detail matching
df_no_match = load_table_refined('G01', ['Occupation'])
df_no_match.head()
# Stitch it together to import multiple tables and multiple categories
def load_tables_specify_cats(table_list, category_list, statistical_area_code='SA3'):
    for index, table in enumerate(table_list):
        if index==0:
            df = load_table_refined(table, category_list, statistical_area_code)
            df.reset_index(inplace=True)
        else:
            temp_df = load_table_refined(table, category_list, statistical_area_code)
            temp_df.reset_index(inplace=True)
            merge_col = df.columns[0]
            df = pd.merge(df, temp_df, on=merge_col)
    return df
df_y = load_tables_specify_cats(['G01','G57','G58'],['Age','Occupation','Number of Commuting Methods'])
df_y.head()<jupyter_output><empty_output><jupyter_text>Remaining ChallengesIdeally I would like to also have a mechanism to bring in sub-sets where available, and if not available still bring in the super-set (e.g. bring in "Religious Sect" detail where possible [Christianity] and for those religions without "Sect" breakdowns, still bring them in). Currently if you choose both "Religious Sect" and "Religion" as a category, you will get a double up of the "Total" lines of the "Religious Sect" subset, which could confuse some algorithms using the data, and would need to be manually picked out and removed.It would also be nice to have a simple mechanism for showing which categories are available within which tables. Investigate why dropping duplicates isn't working<jupyter_code># Import statements
# Declare Imports
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from sklearn.model_selection import train_test_split, GridSearchCV
from sklearn.metrics import make_scorer, r2_score, mean_squared_error
from sklearn.preprocessing import MinMaxScaler
from sklearn.impute import SimpleImputer
from sklearn.pipeline import Pipeline
from sklearn.ensemble import RandomForestRegressor, AdaBoostRegressor
import os
import pickle
from textwrap import wrap
import operator
import au_census_analysis_functions as cnss_func

# Set a variable for current notebook's path for various loading/saving mechanisms
nb_path = os.getcwd()
import importlib
importlib.reload(cnss_func)
load_features = ['Age','Income','Occupation',
                 'Family Composition','Internet status',
                 'Building occupation status','Number of Bedrooms',
                 'Place of Birth','Country of Birth of Parents','Year of Arrival']
input_vectors = cnss_func.load_tables_specify_cats(['G53','G58'], load_features, statistical_area_code='SA2')
input_vectors.head()
input_vectors.shape
input_vectors.T.drop_duplicates().T.shape
input_vectors.loc[101021007,:].T<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07219487104122271, 'fraction_numerical': 0.004849731795135572, 'mean_word_length': 1.9627734842712528, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 36, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '476296', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 7434, 'n_tokens_neox': 7033, 'n_words': 1752, 'path': 'Census data navigation workings.ipynb', 'repo_name': 'blkemp/Census-ASGS-Level-and-Model-Sensitivity', 'size': '27218'}