<jupyter_start><jupyter_text>JP Data CleaningGoing to explore the locational columns and make a couple of functions to tidy them up<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
X_raw = pd.read_csv('../data/train_data.csv', dtype={'id': object})
y_raw = pd.read_csv('../data/train_targets.csv', dtype={'id': object})

# Join the two tables using their 'id' column
df = y_raw.set_index('id').join(X_raw.set_index('id'))<jupyter_output><empty_output><jupyter_text>UNASSIGNED -  'num_private', 'basin', 'population' IGNORE - 'id', 'recorded_by', 'wpt_name' subgroups of 'region' - 'subvillage', 'region_code', 'district_code', 'ward'<jupyter_code>df.ward.value_counts()<jupyter_output><empty_output><jupyter_text>**LGA column seems to have some hidden info in it**it seems to be a more specific district list, with some entries being marked 'urban' or 'rural'i'm going to try to separate these entries out using the following encoding<jupyter_code>def encode_lga(df):
    '''
    encodes the 'lga' column into the values
    'rural', 'urban', and 'other'
    
    returns DataFrame with column 'lga_coded'
    '''
    
    lga_e = []
    
    for entry in df.lga:
        key = entry.split()[-1]
        if key == 'Rural':
            lga_e.append('rural')
        elif key == 'Urban':
            lga_e.append('urban')
        else:
            lga_e.append('other')
    
    df['lga_coded'] = lga_e
    return df.drop('lga', axis=1)<jupyter_output><empty_output><jupyter_text>Now let's see how many of each value we get<jupyter_code>encode_lga(df).lga_coded.value_counts()<jupyter_output><empty_output><jupyter_text>Maybe this is not too good, although it does highlight a lot of water points as ruralNext i'm going to make a function to bin the different regions<jupyter_code>def encode_region(df, num_of_bins=5):
    '''
    Takes in Tanzania Water Point Data, groups by region,
    sorts regions by proportion of non-functional wells,
    and bins them in equally-sized bins according to
    num_of_bins parameter
    
    
    returns: DataFrame with 'region_bins' column added
             and with 'region' and 'region_code' columns dropped
    '''
    
    #group DataFrame by region and count each type of waterpoint
    reg = df.groupby('region')['status_group'].value_counts().unstack()

    #calculate proportion of non-functional waterpoints in each region
    reg['total'] = reg.sum(axis=1)
    reg['non'] = reg['non functional'] / reg['total']

    #sort by that proportion
    reg = reg.sort_values('non')

    #sort regions into specified number of equally wide bins
    bin_labels = list(range(num_of_bins))
    reg['region_bins'] = pd.cut(reg.non, bins=num_of_bins,
                          labels=bin_labels)
    codes = reg.region_bins
        
    #return bin numbers attached to dataframe
    return df.join(codes, on='region').drop(['region','region_code'], axis=1)
encode_region(df, 6)['region_bins'].value_counts()<jupyter_output><empty_output><jupyter_text>That's nice<jupyter_code>pop_no_0 = df[df.population != 0]
pop_0 = df[df.population == 0]
pd.concat([pop_0.status_group.value_counts(),pop_no_0.status_group.value_counts()], axis=1)
pop_no_0 = pop_no_0[['status_group', 'population']]
pop_0 = pop_0[['status_group', 'population']]
plt.scatter(pop_no_0.status_group, pop_no_0.population)
pop_no_0.population.hist(bins=100)
pop_0.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1205942324497524, 'fraction_numerical': 0.006990969997087096, 'mean_word_length': 4.250764525993884, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '342474', 'license': 'MIT', 'n_tokens_mistral': 1195, 'n_tokens_neox': 1142, 'n_words': 293, 'path': 'notebooks/03_jp_geographic_cleaning.ipynb', 'repo_name': 'sydroth/tanzaniawellclassification', 'size': '3433'}