def hypothesis_testing(request):

    search_line = "def hypothesis_testing(request)"

    pattern = "# finish_hypo"

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

    project_key = "hypothesis"

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

    # Path to import files

    file_path = os.path.join(static_dir, 'datasets/university_towns.txt')

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

    file_path3 = os.path.join(static_dir, 'datasets/gdplev.xls')

    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)

        # To retrieve data from datasets using pandas and convert it into a DataFrame, using the pd.read_fwf(), pd.read_excel(), and pd.read_csv() functions depending on the data source format.

        df_towns = pd.read_fwf(file_path, header=None).rename(columns={0: 'State'})

        df_gdp = pd.read_excel(file_path3, skiprows=7).rename(columns={'Unnamed: 4': 'Year_Quartile', 'Unnamed: 6': 'GDP'})

        df_housing = pd.read_csv(file_path2)

        df_gdp_end = df_gdp.copy()

        df_gdp_bottom = df_gdp.copy()

        df_gdp = df_gdp[211:]

        df_towns_hypothesis = df_towns.copy()

        df_university_towns = pd.DataFrame(columns=['State', 'RegionName'])

        pattern = r'\[edit\]'

        pattern_edit = '[edit]'

        df_row = []

        for line in df_towns['State']:

            if pattern_edit in line:

                town = None

                ed = re.search(pattern, line)

                state = line[:ed.start()].strip()

            else:

                if line == 'The Colleges of Worcester Consortium:':

                    town = 'The Colleges of Worcester Consortium:'

                elif line == 'The Five College Region of Western Massachusetts:':

                    town = 'The Five College Region of Western Massachusetts:'

                elif line == 'Faribault, South Central College':

                    town = 'Faribault, South Central College'

                elif line == 'North Mankato, South Central College':

                    town = 'North Mankato, South Central College'

                else:

                    nam_end = re.search(r'[\(:]', line)

                    if nam_end:

                        town = line[:nam_end.start()].strip()

            if town is not None and state is not None:

                df_row.append({'State': state, 'RegionName': town})

        df_university_towns = pd.concat([df_university_towns, pd.DataFrame(df_row)], ignore_index=True)

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

        # Provides the year and quarter of when the recession started. It is defined as two consecutive quarters of Gross Domestic Product (GDP) decline, concluding with two-quarters GDP growth.

        year_quartile_start = []

        for line in range(4, len(df_gdp)):

            if (df_gdp.iloc[line - 4, 6] > df_gdp.iloc[line - 3, 6]) and (

                    df_gdp.iloc[line - 3, 6] > df_gdp.iloc[line - 2, 6]):

                year_quartile_start.append(df_gdp.iloc[line - 3, 4])

        answer.append(year_quartile_start[0])

        # Provides the year and quarter of when the recession end time.

        start_index = df_gdp_end[df_gdp_end['Year_Quartile'] == year_quartile_start[0]].index.to_list()

        df_gdp_end = df_gdp_end[start_index[0]:]

        year_quartile_end = []

        for line in range(2, len(df_gdp_end)):

            if (df_gdp_end.iloc[line - 4, 6] < df_gdp_end.iloc[line - 3, 6]) and (

                    df_gdp_end.iloc[line - 3, 6] < df_gdp_end.iloc[line - 2, 6]):

                year_quartile_end.append(df_gdp_end.iloc[line - 2, 4])

        answer.append(year_quartile_end[0])

        # The recession bottom represents the quarter within a recession period that records the lowest GDP.

        end_index = df_gdp_end[df_gdp_end['Year_Quartile'] == year_quartile_end[0]].index.to_list()

        df_gdp_bottom = df_gdp_bottom[start_index[0]:end_index[0]]

        df_gdp_bottom.reset_index(drop=True, inplace=True)

        bottom_idx = df_gdp_bottom['GDP'].idxmin()

        bottom = df_gdp_bottom.iloc[bottom_idx, 4]

        answer.append(bottom)

        # This process involves converting housing data into quarterly intervals and presenting it as a DataFrame containing mean values. The resulting DataFrame will possess a multi-index structure, combining the "State" and "RegionName" as index levels.

        # The resulting DataFrame will have columns ranging from 2000q1 to 2016q3, providing a comprehensive overview of housing data trends over this period.

        df_housing['State'] = df_housing['State'].map(states)

        df_housing.drop(['RegionID', 'CountyName', 'Metro', 'SizeRank'], axis=1, inplace=True)

        df_housing = df_housing.set_index(['State', 'RegionName'])

        col_init = df_housing.columns.get_loc('2000-01')

        df_housing = df_housing.iloc[:, col_init:]

        quartile_id = ['q1', 'q2', 'q3', 'q4']

        df_quarters_name = []

        for y in range(2000, 2017):

            for q in quartile_id:

                df_quarters_name.append(str(y) + q)

        df_quarters_name.pop()

        periodo = df_housing.columns

        df_quarter_mean = []

        for p in range(0, len(periodo), 3):

            df_sub = df_housing.iloc[:, p:p + 3]

            quarter_mean = df_sub.mean(axis=1).round(2)

            df_quarter_mean.append(quarter_mean)

        df_mean = df_quarter_mean[0]

        for i in range(1, len(df_quarter_mean)):

            df_mean = pd.concat([df_mean, df_quarter_mean[i]], axis=1)

        df_mean.columns = df_quarters_name

        quarters_to_html = th_scope(df_mean.head(30).to_html(classes='table table-bordered table-striped to_html', index=True))

        df_mean['recession_diff'] = df_mean[year_quartile_start[0]] - df_mean[bottom]

        df_mean['with_university'] = False

        df_housing_towns_with = pd.merge(df_mean, df_university_towns, how='inner', on=['State', 'RegionName'])

        df_housing_towns_with['with_university'] = True

        df_ttest_with = df_housing_towns_with['recession_diff'].dropna()

        df_mean.drop('with_university', axis='columns', inplace=True)

        df_housing_towns_with.drop('with_university', axis='columns', inplace=True)

        df_housing_towns_non = pd.concat([df_mean, df_housing_towns_with]).drop_duplicates(keep=False)

        df_ttest_non = df_housing_towns_non['recession_diff'].dropna()

        st, p = ttest_ind(df_ttest_with, df_ttest_non, nan_policy='omit')

        if st < 0.01:

            st = True

        if df_ttest_with.mean() < df_ttest_non.mean():

            best = "university towns"

        else:

            best = "non-university town"

        answer.append(st)

        answer.append(p)

        answer.append(best)

        answer.append(round(p,3))

        context = {

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

           'answer': answer, 'merge_university_town': data_to_html, 'quarters_to_html': quarters_to_html,

        }

        return render(request, 'data-science/hypothesis-testing/index.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 %}