Data Cleaning with Pandas Library

# start_census

def find_min_max(row):

    # Select the columns of interest

    columns_of_interest = ['POPESTIMATE2010', 'POPESTIMATE2011', 'POPESTIMATE2012', 'POPESTIMATE2013',

                           'POPESTIMATE2014', 'POPESTIMATE2015']

    # Find the minimum and maximum values for the selected columns in the row

    min_value = row[columns_of_interest].min()

    max_value = row[columns_of_interest].max()

    return pd.Series({'MIN_POP': min_value, 'MAX_POP': max_value, 'Population Change': max_value - min_value})

def data_cleaning_census(request):

    search_line = "# start_census"

    pattern = "# finish_census"

    content_json = "/static/json/data-science/content.json"

    project_key = "census/pandas/data-cleaning"

    template_path = "portfolio/templates/data-science/pandas/data-cleaning-census.html"

    answer = []  # Initialize list to store results

    try:

        # Call the get_views_code to retrieve code in the views file

        views_code = get_views_code(search_line, pattern, module_dir)

        content_json_data = get_content_json_data(content_json, project_key)

        template_code = get_template_code(template_path)

    except FileNotFoundError:

        result = "The file could not be found."

    try:

        # The census file never changes, so the aggregations and HTML tables are cached

        # for 15 minutes (same as the KNN demo): two heavy demos requested at once were

        # enough to make the dyno drop connections (H13/H18).

        cached = cache.get("census_demo:results")

        if cached is not None:

            context = {'views': views_code, 'content': content_json_data, 'template': template_code, **cached}

            return render(request, 'data-science/pandas/data-cleaning-census.html', context)

        file_path = os.path.join(static_dir, 'datasets/census.csv')

        df = pd.read_csv(file_path)

        # Identify the state that encompasses the greatest number of counties. The {{ result.0 }} is the State with more Counties in the United State they are {{ result.1 }}

        greatest_counties = df.groupby(['STNAME']).sum()['COUNTY'].idxmax()

        quantity_greatest_county = df.groupby(['STNAME']).sum()['COUNTY'].max()

        answer.append(greatest_counties)

        answer.append(quantity_greatest_county)

        # Find the top ten populous states in descendent order, using the column CENSUS2010POP. To make more complex was filtered with code 50 to group by Counties

        # The key for SUMLEV is as follows:

        # 040 = State and/or Statistical Equivalent

        # 050 = County and /or Statistical Equivalent

        condition_state = df['SUMLEV'] == 50  # Select only STATE, Drop rows that contain state data

        new_condition_state = df[condition_state]

        # Sort the county-level data by state ('STNAME') and population ('CENSUS2010POP') in descending order

        most_population_state = new_condition_state.sort_values(['STNAME', 'CENSUS2010POP'], ascending=[True, False])

        # Calculate the total population for each state and sort them in descending order

        population = most_population_state.groupby('STNAME').agg('sum').sort_values('CENSUS2010POP', ascending=False)

        highest_state = population.head(10).reset_index()

        # The next task is to identify the City Name with the largest absolute change in population between the years 2010 and 2015.

        # The code calculates the population change for each county and selects the top ten city with the highest change.

        df_pop = df[condition_state]

        # Apply the function to each row to find the minimum and maximum values

        min_max_values = df_pop.apply(find_min_max, axis=1)

        # Concatenate the resulting DataFrame with the original DataFrame

        df = pd.concat([df_pop, min_max_values], axis=1)

        pop_diff = df[['CTYNAME', 'Population Change']].sort_values('Population Change', ascending=False)

        pop_change = pop_diff.head(10).rename(columns={'CTYNAME': 'County Name'})

        pop_change_to_html = th_scope(pop_change.to_html(classes='table table-bordered table-striped text-center', index=False))

        # Query the dataset to retrieve parameters passed through GET methods in the URL for columns related to regions and the starting city name.

        # Validate if there has been an increase in population from 2014 to 2015 for the specified counties using conditional filtering and string operations. The code then extracts and returns these counties as a DataFrame."

        region_df = df.copy()

        if request.GET.get("region", None) is not None:

            region = request.GET.get("region")

            # filter region condition

            condition_region = (region_df['SUMLEV'] == 50) & (region_df['REGION'] == int(region))

            region_df = region_df[condition_region]

        else:

            # If 'region' parameter is not provided, retrieve all values

            condition_region = (region_df['SUMLEV'] == 50) & (region_df['REGION'].isin([1, 2, 3, 4]))

            region_df = region_df[condition_region]

        # Filter county name that start with some text.

        if request.GET.get("county", None) is not None:

            county = request.GET.get("county")

            condition_starts_name = region_df['CTYNAME'].str.startswith(county)

            start_city = region_df[condition_starts_name]

        else:

            # If 'region' parameter is not provided, retrieve Washington

            condition_starts_name = region_df['CTYNAME'].str.startswith('Washington')

            start_city = region_df[condition_starts_name]

        # Validate increase Population 2015/2016

        condition_comparation_population = start_city['POPESTIMATE2015'] > start_city['POPESTIMATE2014']

        fiend_counties = start_city[condition_comparation_population]

        found_counties = fiend_counties[

            ['REGION', 'STNAME', 'CTYNAME', 'POPESTIMATE2014', 'POPESTIMATE2015']].sort_values('REGION',

                                                                                               ascending=True).rename(

            columns={'STNAME': 'County Name'})

        found_counties_to_html = th_scope(found_counties.to_html(classes='table table-bordered table-striped to_html',

                                                        index=False))

        computed = {'answer': answer,

                    'most_population_state': th_scope(highest_state[['STNAME', 'CENSUS2010POP']].to_html(

                        classes='table table-bordered table-striped', index=True)),

                    'pop_change': pop_change_to_html, 'counties': found_counties_to_html}

        cache.set("census_demo:results", computed, 60 * 15)

        context = {'views': views_code, 'content': content_json_data, 'template': template_code, **computed}

        return render(request, 'data-science/pandas/data-cleaning-census.html', context)

    except FileNotFoundError:

        context = {'description': 'File not found. Please check the file path or create the file if it does not',

                   'reference': "Books Store connection from XML File"}

        return render(request, 'data-science/retrieve_error.html', context)

    except TemplateDoesNotExist:

        context = {'description': 'Template not found. Please check the file path or create the file if it does not',

                   'reference': "Books Store connection from XML File"}

        return render(request, 'data-science/retrieve_error.html', context)

{% extends 'base.html' %}

{% load i18n %}
{% load static %}
{% load cached_pygments %}
{% block title %}{% trans "Census Data Cleaning (pandas)" %} | Marco A. Parra F.{% endblock %}
{% block meta_description %}{% trans "Clean and aggregate census data with pandas, with the code and the resulting tables." %}{% endblock %}
{% block content %}
<br>
<br>

<section class="container-section mb-4">
    <div class="container-fluid">
        <div class="row g-0">
            <div class="portfolio-box-caption">
                <div class="card">
                {% for key, values in content.items %}
                {% for items in content.values %}
                {% for value in items %}
                    <h2 class="cart-title text-center">{{ value.title }}</h2>
                    <hr>
                    <div class="card-body mb-3">
                        <h5 class="card-subtitle mb-3">{{ value.description }}</h5>
                        <p class="card-text">{{ value.key_components }}</p>
                    </div>
                {% endfor %}
                {% endfor %}
                {% endfor %}
                </div>
            </div>
        </div>
        <hr>
        <div class="container border" style="background-color: aliceblue;">
            <div class="row g-0">
                <p>
                    The state has the highest number of counties is {{ answer.0 }} within its borders, which has {{ answer.1 }}. Using idxmax() function to get the Name of State and the max() to get the quantity.<br>
                    <q>For comprehensive information and detailed instructions, please refer to the official documentation. </q><br>
                    <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.idxmax.html" target="_blank">https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.idxmax.html</a><br>
                    <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.max.html" target="_blank">https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.max.html</a>
                </p>
                <pre>
                    <code class="python">
                        greatest_counties = df.groupby(['STNAME']).sum()['COUNTY'].idxmax()<br>
                        quantity_greatest_county = df.groupby(['STNAME']).sum()['COUNTY'].max()
                    </code>
                </pre>
            </div>
            <hr>
            <div class="row g-0">
                <p>Find the top ten populous states in descendent order, using the column CENSUS2010POP. To make more complex was filtered with code 50 to group by Counties. The groupby, sort_values, and aggregation functions are used to accomplish this task.</p>
                <br>
                <div class="row" style="width: 85%; max-height: 25rem; /* Set the desired height here */
        overflow-y: scroll; /* Enable vertical scrolling */">
                    <div class="table-responsive">
                        {{ most_population_state|safe }}
                    </div>
                </div>
                <pre>
                    <code class="python">
                        condition_state = df['SUMLEV'] == 50
                        new_condition_state = df[condition_state]<br>
                        most_population_state = new_condition_state.sort_values(['STNAME', 'CENSUS2010POP'], ascending=[True, False])<br>
                        population = most_population_state.groupby('STNAME').agg('sum').sort_values('CENSUS2010POP', ascending=False)<br>
                        highest_state = population.head(10).reset_index()<br>
                        context = { 'most_population_state': highest_state[['STNAME', 'CENSUS2010POP']].to_html(classes='table table-bordered table-striped', index=True)}
                    </code>
                </pre>
            </div>
            <hr>
            <div class="row g-0">
                <p>Identify the City with the largest absolute change in population between the years 2010 and 2015.
                    <br>The code calculates the population change for each city and selects the top ten cities with the highest change.</p>
                <br>
                    <div class="row" style="width: 85%; max-height: 25rem; /* Set the desired height here */
            overflow-y: scroll; /* Enable vertical scrolling */">
                        <div class="table-responsive">
                            {{ pop_change|safe }}
                        </div>
                    </div>
                <pre>
                    <code class="python">
                        def find_min_max(row):
                            columns_of_interest = ['POPESTIMATE2010', 'POPESTIMATE2011', 'POPESTIMATE2012', 'POPESTIMATE2013', 'POPESTIMATE2014', 'POPESTIMATE2015']<br>
                            min_value = row[columns_of_interest].min()<br>
                            max_value = row[columns_of_interest].max()<br>
                            return pd.Series({'MIN_POP': min_value, 'MAX_POP': max_value, 'DIF_POP': max_value-min_value})<br>
                        min_max_values = df_pop.apply(find_min_max, axis=1)<br>
                        df = pd.concat([df_pop, min_max_values], axis=1)
                    </code>
                </pre>
            </div>
            <hr>
            <div class="row g-0">
                <p>Query the dataset to retrieve parameters passed through GET methods in the URL for columns related to regions and the starting city name.
                    <br>Validate if there has been an increase in population from 2014 to 2015 for the specified counties using conditional filtering and string operations. The code then extracts and returns these counties as a DataFrame.</p>
                <br>
                <h2>{% trans "Search Form" %}</h2>
                <hr>
                <form method="get" action="{% url 'pandas-census' %}">
                    {% csrf_token %}
                    <div class="row">
                        <div class="col-12 col-sm-6">
                            <div class="form-group mb-3">
                                <label for="region">Region ID:</label>
                                <input type="text" class="form-control" id="region" name="region" placeholder="Enter a value [1, 2, 3,4]">
                            </div>
                        </div>
                        <div class="col-12 col-sm-6">
                            <div class="form-group mb-3">
                                <label for="county">County Name:</label>
                                <input type="text" class="form-control" id="county" name="county" placeholder="Enter a value">
                            </div>
                        </div>
                    </div>
                    <button type="submit" class="btn btn-primary">{% trans "Search" %}</button>
                </form>

                <div class="row" style="width: 85%; max-height: 25rem; /* Set the desired height here */
        overflow-y: scroll; /* Enable vertical scrolling */">
                    <div class="table-responsive">
                        {{ counties|safe }}
                    </div>
                </div>
                <pre>
                    <code class="python">
        if request.GET.get("region", None) is not None:
            region = request.GET.get("region")
            # filter region condition
            condition_region = (region_df['SUMLEV'] == 50) & (region_df['REGION'] == int(region))
            region_df = region_df[condition_region]
        else:
            # If 'region' parameter is not provided, retrieve all values
            condition_region = (region_df['SUMLEV'] == 50) & (region_df['REGION'].isin([1, 2, 3, 4]))
            region_df = region_df[condition_region]

        # Filter county name that start with some text.
        if request.GET.get("county", None) is not None:
            county = request.GET.get("county")
            condition_starts_name = region_df['CTYNAME'].str.startswith(county)
            start_city = region_df[condition_starts_name]
        else:
            # If 'region' parameter is not provided, retrieve Washington
            condition_starts_name = region_df['CTYNAME'].str.startswith('Washington')
            start_city = region_df[condition_starts_name]

        # Validate increase Population 2015/2016
        condition_comparation_population = start_city['POPESTIMATE2015'] > start_city['POPESTIMATE2014']
        fiend_counties = start_city[condition_comparation_population]
                    </code>
                </pre>
            </div>
            <hr>
        </div>
    </div>
</section>

<section class="container-section mb-4">
    <div class="container-fluid">
        <div class="row g-0">
            <div class="portfolio-box-caption">
                <h1>Data Cleaning with Pandas Library</h1>
                <hr>
                <div class="container">

                </div>
                <hr>
                <div>
                    <div class="row" style="width: 100%; max-height: 50rem; /* Set the desired height here */
            overflow-y: scroll; /* Enable vertical scrolling */">
                        <div class="table-responsive">
                            {{ data|safe }}
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

<section class="container-section mb-4">
    <div class="container-fluid">
        <div class="row g-0">
            <div class="portfolio-box-caption">
                <div class="row">
                    <div class="col-12 col-sm-10">
                        <h3 class="portfolio-box-headers" style="text-align:center">
                            {% trans "Show Code" %}</h3>
                    </div>
                    <div class="col">
                        <button type="button" class="btn btn-light" onclick="$('#section-w').toggle();">
                            Show/Hide
                        </button>
                    </div>
                </div>
            </div>
            <hr>
            <div class="container" style="display:none;" id="section-w">
                <div class="row mb-4">
                    <div class="col">
                        <h4>views.py Code</h4>
                        <div class="sourcecode">
                            {% for item in views %}
                            {{ item|pygmentize:"python3" }}
                            {% endfor %}
                        </div>
                    </div>
                    <div class="col">
                        <h4>Template</h4>
                        <div class="sourcecode">
                            {{ template|pygmentize:"html" }}
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

{% endblock %}