main.py

from os import path

import numpy as np
import pandas
import re

import pandas as pd

data_file = "GameDev Salaries.csv"
data_fullpath = path.join(path.realpath(__file__).replace('/main.py', ''), 'data', data_file)

df = pandas.read_csv(data_fullpath)
for (column_name, column_data) in df.items():
	if re.match(r'^Unnamed: [0-9]+$', column_name):
		df = df.drop(column_name, axis='columns')
del column_name, column_data, data_file, data_fullpath  # Cleaning up no longer used variables
to_drop = [
	"Any additional comments you would like to add?",
	"Any words of wisdom for those currently seeking positions?",
	"Job Category",
	"Benefits"
]
to_rename = {
	"Job Title": "Title",
	"Country of Residence": "Country",
	"State, Province, Territory, etc.": "State",
	"Gross Annual Salary": "Salary",
	"USD Gross Salary": "USD Salary",
	"Hourly Rate": "Hourly",
	"Do you identify as queer?": "Queer",
	"Do you identify as disabled?": "Disabled",
	"Are you satisfied with your current salary?": "Satisfied with salary",
	"Are you satisfied with your current benefits?": "Satisfied with benefits",
	"Are you a manager or team lead?": "Manager or Lead"
}

us_state_to_abbrev = {
	"(?i)Alabama": "AL",
	"(?i)Alaska": "AK",
	"(?i)Arizona": "AZ",
	"(?i)Arkansas": "AR",
	"(?i)California": "CA",
	"(?i)Colorado": "CO",
	"(?i)Connecticut": "CT",
	"(?i)Delaware": "DE",
	"(?i)Florida": "FL",
	"(?i)Georgia": "GA",
	"(?i)Hawaii": "HI",
	"(?i)Idaho": "ID",
	"(?i)Illinois": "IL",
	"(?i)Indiana": "IN",
	"(?i)Iowa": "IA",
	"(?i)Kansas": "KS",
	"(?i)Kentucky": "KY",
	"(?i)Louisiana": "LA",
	"(?i)Maine": "ME",
	"(?i)Maryland": "MD",
	"(?i)Massachusetts": "MA",
	"(?i)Michigan": "MI",
	"(?i)Minnesota": "MN",
	"(?i)Mississippi": "MS",
	"(?i)Missouri": "MO",
	"(?i)Montana": "MT",
	"(?i)Nebraska": "NE",
	"(?i)Nevada": "NV",
	"(?i)New Hampshire": "NH",
	"(?i)New Jersey": "NJ",
	"(?i)New Mexico": "NM",
	"(?i)New York": "NY",
	"(?i)North Carolina": "NC",
	"(?i)North Dakota": "ND",
	"(?i)Ohio": "OH",
	"(?i)Oklahoma": "OK",
	"(?i)Oregon": "OR",
	"(?i)Pennsylvania": "PA",
	"(?i)Rhode Island": "RI",
	"(?i)South Carolina": "SC",
	"(?i)South Dakota": "SD",
	"(?i)Tennessee": "TN",
	"(?i)Texas": "TX",
	"(?i)Utah": "UT",
	"(?i)Vermont": "VT",
	"(?i)Virginia": "VA",
	"(?i)Washington": "WA",
	"(?i)West Virginia": "WV",
	"(?i)Wisconsin": "WI",
	"(?i)Wyoming": "WY",
	"(?i)District of Columbia": "DC",
	"(?i)American Samoa": "AS",
	"(?i)Guam": "GU",
	"(?i)Northern Mariana Islands": "MP",
	"(?i)Puerto Rico": "PR",
	"(?i)United States Minor Outlying Islands": "UM",
	"(?i)U.S. Virgin Islands": "VI",
}
countries = {
	'AD': '(?i)Andorra',
	'AE': '(?i)United Arab Emirates',
	'AF': '(?i)Afghanistan',
	'AG': '(?i)Antigua & Barbuda',
	'AI': '(?i)Anguilla',
	'AL': '(?i)Albania',
	'AM': '(?i)Armenia',
	'AN': '(?i)Netherlands Antilles',
	'AO': '(?i)Angola',
	'AQ': '(?i)Antarctica',
	'AR': '(?i)Argentina',
	'AS': '(?i)American Samoa',
	'AT': '(?i)Austria',
	'AU': '(?i)Australia',
	'AW': '(?i)Aruba',
	'AZ': '(?i)Azerbaijan',
	'BA': '(?i)Bosnia and Herzegovina',
	'BB': '(?i)Barbados',
	'BD': '(?i)Bangladesh',
	'BE': '(?i)Belgium',
	'BF': '(?i)Burkina Faso',
	'BG': '(?i)Bulgaria',
	'BH': '(?i)Bahrain',
	'BI': '(?i)Burundi',
	'BJ': '(?i)Benin',
	'BM': '(?i)Bermuda',
	'BN': '(?i)Brunei Darussalam',
	'BO': '(?i)Bolivia',
	'BR': '(?i)Brazil|Brasil',
	'BS': '(?i)Bahama[s]?',
	'BT': '(?i)Bhutan',
	'BU': '(?i)Burma (no longer exists)',
	'BV': '(?i)Bouvet Island',
	'BW': '(?i)Botswana',
	'BY': '(?i)Belarus',
	'BZ': '(?i)Belize',
	'CA': '(?i)Canada',
	'CC': '(?i)Cocos (Keeling) Islands',
	'CF': '(?i)Central African Republic',
	'CG': '(?i)Congo',
	'CH': '(?i)Switzerland',
	'CI': '(?i)Côte D\'ivoire (Ivory Coast)',
	'CK': '(?i)Cook Iislands',
	'CL': '(?i)Chile',
	'CM': '(?i)Cameroon',
	'CN': '(?i)China',
	'CO': '(?i)Colombi[a]?',
	'CR': '(?i)Costa Rica',
	'CS': '(?i)Czechoslovakia (no longer exists)',
	'CU': '(?i)Cuba',
	'CV': '(?i)Cape Verde',
	'CX': '(?i)Christmas Island',
	'CY': '(?i)Cyprus',
	'CZ': '(?i)Czech Republic',
	'DD': '(?i)German Democratic Republic (no longer exists)',
	'DE': '(?i)Germany',
	'DJ': '(?i)Djibouti',
	'DK': '(?i)Denmark',
	'DM': '(?i)Dominica',
	'DO': '(?i)Dominican Republic',
	'DZ': '(?i)Algeria',
	'EC': '(?i)Ecuador',
	'EE': '(?i)Estonia',
	'EG': '(?i)Egypt',
	'EH': '(?i)Western Sahara',
	'ER': '(?i)Eritrea',
	'ES': '(?i)Spain',
	'ET': '(?i)Ethiopia',
	'FI': '(?i)Finland',
	'FJ': '(?i)Fiji',
	'FK': '(?i)Falkland Islands (Malvinas)',
	'FM': '(?i)Micronesia',
	'FO': '(?i)Faroe Islands',
	'FR': '(?i)France',
	'FX': '(?i)France, Metropolitan',
	'GA': '(?i)Gabon',
	'GB': '(?i)^United Kin[g]?do[nm][ ]?$|^Great Britain$|England|Scotland',
	'GD': '(?i)Grenada',
	'GE': '(?i)Georgia',
	'GF': '(?i)French Guiana',
	'GH': '(?i)Ghana',
	'GI': '(?i)Gibraltar',
	'GL': '(?i)Greenland',
	'GM': '(?i)Gambia',
	'GN': '(?i)Guinea',
	'GP': '(?i)Guadeloupe',
	'GQ': '(?i)Equatorial Guinea',
	'GR': '(?i)Greece',
	'GS': '(?i)South Georgia and the South Sandwich Islands',
	'GT': '(?i)Guatemala',
	'GU': '(?i)Guam',
	'GW': '(?i)Guinea-Bissau',
	'GY': '(?i)Guyana',
	'HK': '(?i)Hong Kong',
	'HM': '(?i)Heard & McDonald Islands',
	'HN': '(?i)Honduras',
	'HR': '(?i)Croatia',
	'HT': '(?i)Haiti',
	'HU': '(?i)Hungary',
	'ID': '(?i)Indonesia',
	'IE': '(?i)Ireland',
	'IL': '(?i)Israel',
	'IN': '(?i)India',
	'IO': '(?i)British Indian Ocean Territory',
	'IQ': '(?i)Iraq',
	'IR': '(?i)Islamic Republic of Iran',
	'IS': '(?i)Iceland',
	'IT': '(?i)Italy',
	'JM': '(?i)Jamaica',
	'JO': '(?i)Jordan',
	'JP': '(?i)Japan',
	'KE': '(?i)Kenya',
	'KG': '(?i)Kyrgyzstan',
	'KH': '(?i)Cambodia',
	'KI': '(?i)Kiribati',
	'KM': '(?i)Comoros',
	'KN': '(?i)St. Kitts and Nevis',
	'KP': '(?i)Korea, Democratic People\'s Republic of',
	'KR': '(?i)Korea, Republic of',
	'KW': '(?i)Kuwait',
	'KY': '(?i)Cayman Islands',
	'KZ': '(?i)Kazakhstan',
	'LA': '(?i)Lao People\'s Democratic Republic',
	'LB': '(?i)Lebanon',
	'LC': '(?i)Saint Lucia',
	'LI': '(?i)Liechtenstein',
	'LK': '(?i)Sri Lanka',
	'LR': '(?i)Liberia',
	'LS': '(?i)Lesotho',
	'LT': '(?i)Lithuania',
	'LU': '(?i)Luxembourg',
	'LV': '(?i)Latvia',
	'LY': '(?i)Libyan Arab Jamahiriya',
	'MA': '(?i)Morocco',
	'MC': '(?i)Monaco',
	'MD': '(?i)Moldova, Republic of',
	'MG': '(?i)Madagascar',
	'MH': '(?i)Marshall Islands',
	'ML': '(?i)Mali',
	'MN': '(?i)Mongolia',
	'MM': '(?i)Myanmar',
	'MO': '(?i)Macau',
	'MP': '(?i)Northern Mariana Islands',
	'MQ': '(?i)Martinique',
	'MR': '(?i)Mauritania',
	'MS': '(?i)Monserrat',
	'MT': '(?i)Malta',
	'MU': '(?i)Mauritius',
	'MV': '(?i)Maldives',
	'MW': '(?i)Malawi',
	'MX': '(?i)M[eé]xico',
	'MY': '(?i)Malaysia',
	'MZ': '(?i)Mozambique',
	'NA': '(?i)Namibia',
	'NC': '(?i)New Caledonia',
	'NE': '(?i)Niger',
	'NF': '(?i)Norfolk Island',
	'NG': '(?i)Nigeria',
	'NI': '(?i)Nicaragua',
	'NL': '(?i)The Netherlands[ ]?|^Netherlands$',
	'NO': '(?i)Norway',
	'NP': '(?i)Nepal',
	'NR': '(?i)Nauru',
	'NT': '(?i)Neutral Zone (no longer exists)',
	'NU': '(?i)Niue',
	'NZ': '(?i)New Zealand',
	'OM': '(?i)Oman',
	'PA': '(?i)Panama',
	'PE': '(?i)Peru',
	'PF': '(?i)French Polynesia',
	'PG': '(?i)Papua New Guinea',
	'PH': '(?i)Philippines',
	'PK': '(?i)Pakistan',
	'PL': '(?i)Poland|Polska',
	'PM': '(?i)St. Pierre & Miquelon',
	'PN': '(?i)Pitcairn',
	'PR': '(?i)Puerto Rico',
	'PT': '(?i)Portugal',
	'PW': '(?i)Palau',
	'PY': '(?i)Paraguay',
	'QA': '(?i)Qatar',
	'RE': '(?i)Réunion',
	'RO': '(?i)Romania',
	'RU': '(?i)Russian Federation|Russia',
	'RW': '(?i)Rwanda',
	'SA': '(?i)Saudi Arabia',
	'SB': '(?i)Solomon Islands',
	'SC': '(?i)Seychelles',
	'SD': '(?i)Sudan',
	'SE': '(?i)Sweden|Sverige',
	'SG': '(?i)Singapore',
	'SH': '(?i)St. Helena',
	'SI': '(?i)Slovenia',
	'SJ': '(?i)Svalbard & Jan Mayen Islands',
	'SK': '(?i)Slovakia',
	'SL': '(?i)Sierra Leone',
	'SM': '(?i)San Marino',
	'SN': '(?i)Senegal',
	'SO': '(?i)Somalia',
	'SR': '(?i)Suriname',
	'ST': '(?i)Sao Tome & Principe',
	'SU': '(?i)Union of Soviet Socialist Republics (no longer exists)',
	'SV': '(?i)El Salvador',
	'SY': '(?i)Syrian Arab Republic',
	'SZ': '(?i)Swaziland',
	'TC': '(?i)Turks & Caicos Islands',
	'TD': '(?i)Chad',
	'TF': '(?i)French Southern Territories',
	'TG': '(?i)Togo',
	'TH': '(?i)Thailand',
	'TJ': '(?i)Tajikistan',
	'TK': '(?i)Tokelau',
	'TM': '(?i)Turkmenistan',
	'TN': '(?i)Tunisia',
	'TO': '(?i)Tonga',
	'TP': '(?i)East Timor',
	'TR': '(?i)Turkey',
	'TT': '(?i)Trinidad & Tobago',
	'TV': '(?i)Tuvalu',
	'TW': '(?i)Taiwan, Province of China|Taiwan',
	'TZ': '(?i)Tanzania, United Republic of',
	'UA': '(?i)Ukraine',
	'UG': '(?i)Uganda',
	'UM': '(?i)United States Minor Outlying Islands',
	'US': '(?i)United State[s]? of America|USA|^[ ]?United State[sd]?[ ]?$',
	'UY': '(?i)Uruguay',
	'UZ': '(?i)Uzbekistan',
	'VA': '(?i)Vatican City State (Holy See)',
	'VC': '(?i)St. Vincent & the Grenadines',
	'VE': '(?i)Venezuela',
	'VG': '(?i)British Virgin Islands',
	'VI': '(?i)United States Virgin Islands',
	'VN': '(?i)Viet Nam',
	'VU': '(?i)Vanuatu',
	'WF': '(?i)Wallis & Futuna Islands',
	'WS': '(?i)Samoa',
	'YD': '(?i)Democratic Yemen (no longer exists)',
	'YE': '(?i)Yemen',
	'YT': '(?i)Mayotte',
	'YU': '(?i)Yugoslavia',
	'ZA': '(?i)South Africa',
	'ZM': '(?i)Zambia',
	'ZR': '(?i)Zaire',
	'ZW': '(?i)Zimbabwe',
	'ZZ': '(?i)Unknown or unspecified country|Remote',
	'MK': '(?i)North Macedonia',
	'RS': '(?i)Serbia',
	'ME': '(?i)Montenegro',
	'CW': '(?i)Curaçao',
	'SS': '(?i)South Sudan',
	'JE': '(?i)Jersey'
}
standardize_genders = {
	'(?i)^(Cis )?Female$|^(Cis )?Woman[ ]?$|^F$|^Woman/Female$|^Female( to most people)?( mostly)?': 'Female',
	'(?i)^(Cis )?Male[ ]?$|^M$|^Man$|he/him': 'Male',
	'(?i).*Trans.*': 'Transgender',
	'(?i).*Nb$|.*Non[- ]*?Binary.*|NonBinary': 'Non-Binary',
	'(?i).*Genderfluid.*': 'Genderfluid',
	'(?i).*Genderqueer.*': 'Genderqueer',
	'(?i)^[ ]?-[ ]?$|shouldn\'t matter|WIP': ''
}
yes_no = {
	'(?i)Yes': '1',
	'(?i)No': '0'
}
ethnicity = {
	"(?i)^white.*": "White",
	"(?i)^mix.*": ""
}
countries = dict((name, code) for code, name in countries.items())
clean_salary = r',|\$'  # List of characters to remove from the salary fields

df.drop(to_drop, axis='columns', inplace=True)  # Drop select columns
df['Timestamp'] = pd.to_datetime(df['Timestamp'])  # Convert to timestamp
df.rename(columns=to_rename, inplace=True)  # Rename columns
df['Salary'] = df['Salary'].str.replace(clean_salary, '', regex=True)  # Clean Salary
df['USD Salary'] = df['USD Salary'].str.replace(clean_salary, '', regex=True)  # Clean USD Salary
df['Ethnicity'] = df['Ethnicity'].str.split(' \(').str[0]

df.replace({'State': us_state_to_abbrev}, inplace=True, regex=True)
df.replace({'Country': countries}, inplace=True, regex=True)
df.replace({'Gender': standardize_genders}, inplace=True, regex=True)
df['Gender'] = df['Gender'].str.split(' \(').str[0]
for item in ['Satisfied with salary', 'Satisfied with benefits', 'Manager or Lead']:
	df.replace({item: yes_no}, inplace=True, regex=True)
for item in ['Queer', 'Disabled']:
	df[item] = df[item].str.replace(r'(?i)No', str(0), regex=True)
	df.loc[df[item] != '0', item] = '1'
df.to_csv('test.csv')  # Export to CSV
pd.options.display.max_columns = None  # Display all columns when printing head
print(df.head())