Data Cleaning with Pandas Library

def data_cleaning(request):

    search_line = "def data-cleaning(request):"

    pattern = "def find_min_max(row):"

    try:

        with open(__file__, 'r') as file:   # este módulo se documenta a sí mismo

            content = file.readlines()

            index = None

            for i, line in enumerate(content):

                if search_line in line and index is None:

                    index = i

                if line.startswith(pattern) and index is not None:

                    pos = i

        with open(os.path.join(static_dir, 'json/data-science/content.json'), 'r') as file:

            content_json = file.read()

        content_raw = json.loads(content_json)

        content_filter = {key: value for key, value in content_raw.items() if key.startswith("pandas/data-cleaning")}

        with open(os.path.join(parent_directory, 'portfolio/templates/data-science/pandas/data-cleaning.html'),

                  'r') as file:

            content_template = file.read()

    except FileNotFoundError:

        result = "The file could not be found."

    views = content[index - 1: pos]

    try:

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

        df = pd.read_csv(file_path, index_col=0, skiprows=1)

        df_sorted = df.sort_values(by='Combined total',

                                   ascending=False)  # Change 'ascending' to False retrieve the countries with more medals gotten.

        # The type of medals area is defined with a number (01: Gold, 02: Silver, 03:Bronze). The next line was used to rename the column name and retrieve data with a better description.

        for col in df_sorted.columns:

            if col[:2] == '01':

                df_sorted.rename(columns={col: 'Gold' + col[4:]}, inplace=True)

            if col[:2] == '02':

                df_sorted.rename(columns={col: 'Silver' + col[4:]}, inplace=True)

            if col[:2] == '03':

                df_sorted.rename(columns={col: 'Bronze' + col[4:]}, inplace=True)

            if col[:1] == '№':

                df_sorted.rename(columns={col: '#' + col[1:]}, inplace=True)

        # To extract the ID of country was applied the split() function in the column name.

        names_ids = df_sorted.index.str.split('(')  # split the index by '('

        df_sorted.index = names_ids.str[0]  # the [0] element is the country name (new index)

        df_sorted['ID'] = names_ids.str[1].str[

                          :3]  # the [1] element is the abbreviation or ID (take first 3 characters from that)

        df = pd.DataFrame(df_sorted)

        # Rename the column at position 0 (Column1) to 'New Name'

        # Assuming you have your DataFrame df already defined

        df_html = th_scope(df[1:].to_html(classes='table table-bordered table-striped', index=True))

        # The country that has won the most gold medals in summer games is ____. Using idxmax()

        answer = []

        answer.append(df['Gold'][1:].idxmax())

        # Calculate the absolute difference between 'Summer_Gold' and 'Summer_Gold.1'

        # Find the country with the maximum Gold difference

        max_difference_gold = abs(df['Gold'][1:] - df['Gold.1'][1:]).idxmax()

        # Filter the DataFrame to get the row for "Max Gold difference"

        gb_row = df.loc[max_difference_gold]

        # Get the 'Gold_Difference' value for "Max Gol difference"

        gold_difference_gb = gb_row['Gold'] - gb_row['Gold.1']

        # Add a condition to filter only Countries that gained at least one Gold medal for each season (Summer - Winter).

        condition = (df['Gold'] >= 1) & (df['Gold.1'] >= 1)

        new_df = df[condition]

        # Get biggest difference between summer-winter gold

        biggest_summer_winter = (abs(new_df['Gold'] - new_df['Gold.1']) / new_df['Gold.2']).idxmax()

        # This function generates a Series named 'Points.' This Series represents a weighted value system, where each gold medal (Gold.2) is assigned a weight of 3 points, silver medals (Silver.2) carry 2 points, and bronze medals (Bronze.2) contribute 1 point. The function should return the resulting column as a Series object, with the country names serving as the indices.

        df['Points'] = pd.Series(df['Gold.2'] * 3 + df['Silver.2'] * 2 + df['Bronze.2'])

        df_points = pd.DataFrame(df['Points'])

        points = th_scope(df_points[1:].to_html(classes='table table-bordered table-striped', index=True))

        answer.append(max_difference_gold)

        answer.append(gold_difference_gb)

        answer.append(biggest_summer_winter)

        context = {'data': df_html, 'views': views, 'content': content_filter, 'answer': answer, 'points': points,

                   'template': content_template}

        return render(request, 'data-science/pandas/data-cleaning.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)

# start_census

{% extends 'base.html' %}

{% load i18n %}
{% load static %}
{% load cached_pygments %}
{% block title %}{% trans "Data Cleaning with pandas" %} | Marco A. Parra F.{% endblock %}
{% block meta_description %}{% trans "Clean a messy dataset with pandas: types, missing values, duplicates and derived columns." %}{% 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 country that has won the most gold medals in summer games is {{ answer.0 }}. Using idxmax() function.<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>
                </p>
                <code class="python">df['Gold'][1:].idxmax()</code>
            </div>
            <hr>
            <div class="row g-0">
                <p>
                    The country with the most significant difference between their summer and winter gold medal counts is {{ answer.1 }} the difference was of {{ answer.2 }} . Using idxmax() & abs() functions.<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>
                </p>
                <code class="python">abs(df['Gold'][1:] - df['Gold.1'][1:])).idxmax()</code>
            </div>
            <hr>
            <div class="row g-0">
                <p>Determine the countries that have won at least one gold medal in both the summer and winter games, and then identify which of these countries has the greatest relative difference between their summer and winter gold medal counts, considering their total gold medal count.
                    {{ answer.3 }} was the countrie with the biggest difference.
                <br>
                    <math xmlns="http://www.w3.org/1998/Math/MathML" display="block">
                      <mfrac>
                        <mrow>
                          <mi>S</mi>
                          <mi>u</mi>
                          <mi>m</mi>
                          <mi>m</mi>
                          <mi>e</mi>
                          <mi>r</mi>
                          <mtext>&#xA0;</mtext>
                          <mi>G</mi>
                          <mi>o</mi>
                          <mi>l</mi>
                          <mi>d</mi>
                          <mo>&#x2212;<!-- − --></mo>
                          <mi>W</mi>
                          <mi>i</mi>
                          <mi>n</mi>
                          <mi>t</mi>
                          <mi>e</mi>
                          <mi>r</mi>
                          <mtext>&#xA0;</mtext>
                          <mi>G</mi>
                          <mi>o</mi>
                          <mi>l</mi>
                          <mi>d</mi>
                        </mrow>
                        <mrow>
                          <mi>T</mi>
                          <mi>o</mi>
                          <mi>t</mi>
                          <mi>a</mi>
                          <mi>l</mi>
                          <mtext>&#xA0;</mtext>
                          <mi>G</mi>
                          <mi>o</mi>
                          <mi>l</mi>
                          <mi>d</mi>
                        </mrow>
                      </mfrac>
                    </math>
                <code class="python">condition = (df['Gold'] >=1) & (df['Gold.1'] >=1) <br>
                    new_df = df[condition] <br>
                (abs(new_df['Gold'] - new_df['Gold.1'])/new_df['Gold.2']).idxmax()</code>
            </div>
            <hr>
            <div class="row g-0">
                <p>This function generates a Series named 'Points.' This Series represents a weighted value system, where each gold medal (Gold.2) is assigned a weight of 3 points, silver medals (Silver.2) carry 2 points, and bronze medals (Bronze.2) contribute 1 point. The function should return the resulting column as a Series object, with the country names serving as the indices.</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">
                            {{ points|safe }}
                        </div>
                    </div>
                <code class="python">df['Points'] = pd.Series(df['Gold.2'] * 3 + df['Silver.2'] * 2 + df['Bronze.2']) <br>
                                    df_points = pd.DataFrame(df['Points'])<br>
                                    points = df_points[1:].to_html(classes='table table-bordered table-striped', index=True)</code>
            </div>
        </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 %}

<script type="text/javascript" async
  src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.7/MathJax.js?config=TeX-MML-AM_CHTML">
</script>