HARVARD_FIRST_NAME_URL = 'https://dataverse.harvard.edu/api/access/datafile/3078263?format=original&gbrecs=true'

HARVARD_FIRST_NAME_COLUMNS = [
    'firstname',
    'obs',
    'pcthispanic',
    'pctwhite',
    'pctblack',
    'pctapi',
    'pctaian',
    'pct2prace',
    'NaN',
]

TARGET_FIRST_NAME_COLUMNS = [
    'firstname',
    'pctwhite',
    'pctblack',
    'pctapi',
    'pctaian',
    'pct2prace',
    'pcthispanic',
]

TARGET_COLUMN_RENAMES = {
    'firstname': 'name',
    'pcthispanic': 'hispanic',
    'pctwhite': 'white',
    'pctblack': 'black',
    'pctapi': 'api',
    'pctaian': 'native',
    'pct2prace': 'multiple',
}

"""
Explanation: Background (links good as of 2021-01-22)
This fetches data from the following Excel file:
https://dataverse.harvard.edu/api/access/datafile/3078263?format=original&gbrecs=true
This excel file contains race data based on the first names. The script creates dataframes from the worksheet in this excel file. It then cleans them the data.
Information about the contents of these files may be found here:
https://dataverse.harvard.edu/dataset.xhtml?persistentId=doi:10.7910/DVN/TYJKEZ#
It then the data for use in Surgeo calculations.
Constants
End of explanation
"""


def url_to_df(url):
    '''Takes the URL of a the Harvard first name excel file and converts to DF
    '''
    # Download zipfile from census URL
    with urllib.request.urlopen(url) as response:
        # Write file into BytesIO object
        excel_data = io.BytesIO(response.read())
        # Open excel data as openpyxl workbook
        ef = openpyxl.load_workbook(excel_data)
        # Get the correct worksheet
        ws_data = ef.get_sheet_by_name('Data')
        # Extract the worsheet data
        raw_data = ws_data.values
        # Get the column names
        cols = next(raw_data)
        # Get the actual data
        values = list(raw_data)
        # Create dataframe
        df = pd.DataFrame(values, columns=cols)
        return df

df_harvard = url_to_df(HARVARD_FIRST_NAME_URL)

df_harvard.tail()

"""
Explanation: Download First Name Data
End of explanation
"""


def clean_df(df):
    '''Change column names, set index, and convert percentages'''
    # Change names
    df.columns = HARVARD_FIRST_NAME_COLUMNS
    # Filter columns
    df = df[TARGET_FIRST_NAME_COLUMNS]
    # Rename columns
    df = df.rename(columns=TARGET_COLUMN_RENAMES)
    # Set index to name
    df = df.set_index('name')
    # Sort index
    df = df.sort_index()
    # Convert percentages to 0 to 1 numbers
    df = df / 100
    return df

df_harvard = clean_df(df_harvard)

df_harvard.tail()

# Round to 4 digits
df_harvard = df_harvard.round(4)

df_harvard.tail()

"""
Explanation: Clean Data
End of explanation
"""


current_directory = pathlib.Path().cwd()
project_directory = current_directory.parents[0]
data_directory    = project_directory / 'surgeo' / 'data'
path_harvard      = data_directory / 'prob_race_given_first_name_harvard.csv'
df_harvard.to_csv(path_harvard)

"""
Explanation: Write data to module as CSV
End of explanation
"""


column_totals = df_harvard.sum(axis=0).divide(100.0)
ratio_by_column = df_harvard.divide(column_totals, axis='columns').copy()

ratio_by_column.tail()

# Prob first name given race
rbc_path = data_directory / 'prob_first_name_given_race_harvard.csv'
ratio_by_column.to_csv(rbc_path)

"""
Explanation: Create the reverse mapping data
End of explanation
"""


