Data Cleaning with Pandas Library

def data_read_excel_file(request):

    search_line = "def data_read_excel_file(request)"

    pattern = "# finish_excel"

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

    project_key = "excel-reads/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)

        # Read datasets from static folder

        file_path = os.path.join(static_dir, 'datasets/Energy Indicators.xls')

        file_path2 = os.path.join(static_dir, 'datasets/world_bank.csv')

        file_path3 = os.path.join(static_dir, 'datasets/scimagojr-3.xlsx')

        # This project involves loading energy-related data from the files with xls, csv, xlsx extension

        # Read energy data from an Excel file, skipping 17 rows from the top

        # and 38 rows from the bottom to exclude header and footer information.

        energy = pd.read_excel(file_path, skiprows=17, skipfooter=38)

        # Drop the first and second columns of the 'energy' DataFrame to remove unnecessary data.

        energy.drop(energy.columns[[0, 1]], axis=1, inplace=True)

        # Rename the columns of the 'energy' DataFrame for clarity and consistency.

        energy.columns = ['Country', 'Energy Supply', 'Energy Supply per Capita', '% Renewable']

        # Replace '...' entries in the 'Energy Supply' column with NaN (Not a Number) values to represent missing or incomplete data.

        # Should be import numpy as np in the header of file

        energy.loc[energy['Energy Supply'] == '...'] = np.NaN

        # Drop rows with missing data (NaN values) from the 'energy' DataFrame.

        energy = energy.dropna()

        # Convert Energy to gigajoules

        energy['Energy Supply'] *= 1000000

        # Rename entries for Country Columns

        energy['Country'] = energy['Country'].replace('Iran (Islamic Republic of)', 'Iran')

        energy['Country'] = energy['Country'].str.replace(r'\(.*\)', '')

        energy['Country'] = energy['Country'].str.replace(r'\,\ ', ' ')

        energy['Country'] = energy['Country'].str.replace(r'\d+', '', regex=True)

        energy['Country'] = energy['Country'].replace('Republic of Korea', 'South Korea')

        # energy['Country'] = energy['Country'].replace('China2', 'China')

        # energy['Country'] = energy['Country'].replace('France6', 'France')

        energy['Country'] = energy['Country'].replace('United States of America', 'United States')

        energy['Country'] = energy['Country'].replace('United Kingdom of Great Britain and Northern Ireland',

                                                      'United Kingdom')

        energy['Country'] = energy['Country'].replace('China, Hong Kong Special Administrative Region3', 'Hong Kong')

        # Read Gross domestic product, that represent is a measure of the size and health of a country's economy over a period of time

        # Install in virtual enviroment pipenv install xlrd

        gdp = pd.read_csv(file_path2, skiprows=4)

        # Rename Country Name for consitency with energy DataFrame

        gdp['Country Name'] = gdp['Country Name'].replace(

            {'Korea, Rep.': 'South Korea', 'Iran, Islamic Rep.': 'Iran', 'Hong Kong SAR, China': 'Hong Kong'})

        # Rename the 'Country Name' column to 'Country' in the 'GDP' DataFrame for consistency with Country column in energy DataFrame.

        gdp = gdp.rename(columns={'Country Name': 'Country'})

        # Install in virtual enviroment pipenv install openpyxl

        power_energy = pd.read_excel(file_path3)

        # Select top 10 countries of Ranking

        power_energy_ranking = power_energy[0:10]

        # Merge the 'gdp' and 'power_energy_ranking' DataFrames using an inner join on the 'Country' column.

        gdp_merge_power_energy_ranking = pd.merge(gdp, power_energy_ranking, how='inner', left_on='Country',

                                                  right_on='Country')

        # Merge the 'gdp_merge_power_energy_ranking' and 'energy' DataFrames using an inner join on the 'Country' column.

        merges_data = pd.merge(gdp_merge_power_energy_ranking, energy, how='inner', left_on='Country',

                               right_on='Country')

        # Re-Define index how Contry columns

        merges_data = merges_data.set_index('Country')

        data = merges_data[

            ['Rank', 'Documents', 'Citable documents', 'Citations', 'Self-citations', 'Citations per document',

             'H index', 'Energy Supply', 'Energy Supply per Capita', '% Renewable', '2006', '2007', '2008', '2009',

             '2010', '2011', '2012', '2013', '2014', '2015']]

        data = data.sort_values('Rank')

        data_to_html = th_scope(data.to_html(classes='table table-bordered table-striped to_html', index=True))

        # Retrieve the Average energy between 2006 and 2015

        top15_avg_condition = data[['2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015']]

        top15_avg_rank = top15_avg_condition.mean(axis=1).sort_values(ascending=False)

        # In this analysis, we aim to calculate the extent of GDP fluctuation over a 10-year duration for the nation holding the 4th position in terms of its average GDP.

        data['avg_gdp'] = data[['2006', '2007', '2008', '2009', '2010', '2011', '2012', '2013', '2014', '2015']].mean(

            axis=1)

        data.sort_values("avg_gdp", ascending=False, inplace=True)

        abs_value = round(abs(data.iloc[3]['2015'] - data.iloc[3]['2006']), 1)

        country = data.index[3]

        answer.append(country)

        answer.append(abs_value)

        # Retrieve the country with the lowest percentage of renewable energy and reporting the name of the country along with its corresponding percentage.

        lower_index = data['% Renewable'].idxmin()

        lower_percentage = round(data['% Renewable'].min(), 1)

        lower_percentage = f"{lower_percentage}%"

        answer.append(lower_index)

        answer.append(lower_percentage)

        # Calculate a new column representing the ratio of Self-Citations to Total Citations. Determine the maximum value within this new column and identify the country with the lowest ratio.

        data['Ratio'] = data['Self-citations'] / data['Citations']

        lower_index_ratio = data['Ratio'].idxmin()

        lower_percentage_ratio = round(data['Ratio'].min(), 3)

        answer.append(lower_index_ratio)

        answer.append(lower_percentage_ratio)

        # Generate a new column to estimate the population by using the Energy Supply and Energy Supply per capita values. Determine the third most populous country based on this population estimate.

        # Convert columns to numeric and handle non-numeric values

        data['Energy Supply'] = pd.to_numeric(data['Energy Supply'], errors='coerce')

        data['Energy Supply per Capita'] = pd.to_numeric(data['Energy Supply per Capita'], errors='coerce')

        # Fill missing values with 0 or other appropriate values

        data['Energy Supply'].fillna(0, inplace=True)

        data['Energy Supply per Capita'].fillna(0, inplace=True)

        # Perform the division and round

        data['PopEstimate'] = (data['Energy Supply'] / data['Energy Supply per Capita']).round()

        data.sort_values('PopEstimate', ascending=False, inplace=True)

        estimate_country = data.iloc[2].name

        estimate_value = data['PopEstimate'].iloc[2]

        answer.append(estimate_country)

        answer.append(estimate_value)

        # Generation a new column that estimates the ratio of citable documents to the population (per person). Calculate the correlation between this ratio and the energy supply per capita using Pearson's correlation coefficient via the .corr() method.

        data['PopEstimate'] = data['Energy Supply'] / data['Energy Supply per Capita']

        data['PopCitableDocuments'] = data['Citable documents'] / data['PopEstimate']

        correlation = data['PopCitableDocuments'].astype(float).corr(data['Energy Supply per Capita'].astype(float))

        # A correlation coefficient of 0.79 suggests a strong positive linear relationship between the ratio of citable documents to the population (per person) and the energy supply per capita. In other words, as the energy supply per capita increases, there tends to be a corresponding increase in the ratio of citable documents to the population. This indicates that countries with higher energy supply per capita tend to have more citable documents per person, implying a potential connection between energy availability and scientific research output.

        answer.append(correlation)

        # Use the matplotlib built-in function plot() to visualize the relationship between Energy Supply per Capita and Citable Documents per Capita. Install the matplotlib, BytesIO, & base64  library in your virtual environment and include the necessary import statement in the file's header.

        data.plot(x='PopCitableDocuments', y='Energy Supply per Capita', kind='scatter')

        # Save the plot to a BytesIO object

        buffer = BytesIO()

        plt.savefig(buffer, format='png')

        buffer.seek(0)

        image_png = buffer.getvalue()

        buffer.close()

        # Encode the image to base64

        graphic = base64.b64encode(image_png).decode()

        mediana_value = data['% Renewable'].median()

        data['HighRenewable'] = data.apply(lambda x: 1 if x['% Renewable'] >= mediana_value else 0, axis=1)

        data.sort_values('Rank', ascending=True, inplace=True)

        answer.append(round(mediana_value, 2))

        matches_median = data[['Rank','% Renewable', 'HighRenewable']]

        matches_median_to_html = th_scope(matches_median.to_html(classes='table table-bordered table-striped to_html', index=True))

        # Utilize the provided dictionary, defined as "ContinentDict," to group countries by their respective continents. Subsequently, construct a DataFrame that presents key statistics for each continent, including the sample size (number of countries), the sum, mean, and standard deviation of the estimated population of the countries within that continent.

        data_by_continent = pd.DataFrame(columns=['size', 'sum', 'mean', 'std'])

        for idx, name in data.groupby(ContinentDict):

            data_by_continent.loc[idx] = [len(name), name['PopEstimate'].sum(), name['PopEstimate'].mean(),

                                          name['PopEstimate'].std()]

        # Divide the '% Renewable' values into five distinct bins. Afterward, categorize the 'Top15' dataset by both continent and the newly defined bins for '% Renewable.' Determine the count of countries in each of these combined groupings.

        # The desired outcome is a Series that incorporates a MultiIndex structure, first based on the continent, and then further sub-divided by the bins representing '% Renewable.' Exclude groups that have no countries within them.

        data['ByContinent'] = data.index.to_series().map(ContinentDict)

        data['SubGroups'] = pd.cut(data['% Renewable'], 5)

        data_by_continent_bins = data.groupby(['ByContinent', 'SubGroups']).size()

        data_by_continent_bins = data_by_continent_bins[data_by_continent_bins > 0]

        print(data_by_continent_bins)

        context = {

            'views': views_code, 'content': content_json_data, 'template': template_code,

            "data": data_to_html, 'avg_rank': top15_avg_rank, 'answer': answer, 'graphic': graphic,

            'matches_median': matches_median_to_html, 'data_by_continent_bins': data_by_continent_bins

        }

        return render(request, 'data-science/pandas/data-read-excel-files.html', context)

    except FileNotFoundError:

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

                   'reference': "Read & Process Excel files with Data Frame"}

        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)

    else:

        context = {'description': 'Something is wrong to retrieve data.',

                   'reference': "Something wrong with retrieving Data with Pandas"}

        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 %}