<jupyter_start><jupyter_text>Data CleaningFor some of this work we need to clean or aggragate the data. People entered a lot of details - which is amazing - but we don't want to look at per-person responses as much as in aggregate. This page describes what we did to clean up the survey responses.This notebook loads in the raw data, and writes out a new file that can be loaded "at will" with "clean" data by all the other notebooks.<jupyter_code>import pandas as pd
import numpy as np
from typing import List
import re

raw_survey = pd.read_csv('/workspaces/snowmass-attendance/survey.csv')<jupyter_output><empty_output><jupyter_text>TimeConvert the time the person filled out the survey into something that is a time object.<jupyter_code>time_col = pd.to_datetime(raw_survey["Timestamp"])
survey = pd.DataFrame(time_col).rename(columns={"Timestamp": "time"})
survey["time"] = pd.to_datetime(raw_survey["Timestamp"])<jupyter_output><empty_output><jupyter_text>Employed ByHere is a list of what people answered for how they were employed. People entered a lot of different things - below is how we mapped everyone. Some of the choices were... guesses. Hopefully they do not affect things too much.<jupyter_code>c_employed_by_name = "I'm employed by"
','.join(list((str(i) for i in raw_survey[c_employed_by_name].unique())))
employted_by_t = {
 'US National Lab': 'US National Lab',
 'US University': 'US University',
 'Italian National Institute' : "non-EU",
 'International university ' : "non-EU",
 'TRIUMF': "Canada",
 'Perfer not to answer': "Unknown",
 'International University': "EU",
 'Non-US institution': "EU",
 'European university': "EU",
 'EU Lab': "EU",
 'Spanish Research Council (CSIC)': "EU",
 'INFN (Italy)': "EU",
 'European Lab': "EU",
 'research institute in Eastern Europe': "EU",
 'Italian University': "EU",
 'European lab': "EU",
 'CERN': "EU",
 'German University': "EU",
 'Other country': "non-EU",
 'University in Canada': "Canada",
 'Research laboratory in Germany.': "EU",
 'UK University': "EU",
 'European National lab': "EU",
 'retired': "Unknown",
 'INFN': "EU",
 'Istituto Nazionale di Fisica Nucleare (INFN), Italy': "EU",
 'Lund University': "EU",
 'University': "EU",
 'overseas university': "EU",
 'retired faculty': "Unknown",
 'employeed by national lab, grad student at a US university': "US National Lab",
 'non US': "EU",
 'Canadian research institute': "Canada",
 'Università degli Studi di Padova': "EU",
 'Canada University': "Canada",
 'Research institute': "EU",
 'Canadian university (and, btw, "perfer" is not a word ...)': "Canada",
 'Foreign Lab': "EU",
 'Small Liberal Arts  College': "US University",
 'Ukrainian university (Main Astronomical Observatory of NAS Ukraine)': "non-EU",
 'foreign lab': "EU",
 'non-US lab': "EU",
 'Foreign University': "EU",
 'a European national insitution': "EU",
 'Korean University ': "non-EU",
 'EU university': "EU",
 'EU Research center': "EU",
 'European institution': "EU",
 'Non-US university (University of ICeland)': "non-EU",
 'University outside US': "EU",
 'non-US University': "EU",
 'The University of Torino': "EU",
 'Non US University (Colombia)': "non-EU",
 'UK university': "EU",
 'INFN, Italy': "EU",
 'Industry': "Industry",
 'University of Granada (Spain)': "EU",
 'University of Medellin': "EU",
 'CSIC (Spain)': "EU",
 'Indian University': "non-EU",
 'National Lab outside US': "EU",
 'Brazilian University': "non-EU",
 'German research institute': "EU",
 'European Institution': "EU",
 'US College': "US University",
 'UT Austin': "US University",
 'NASA': "US National Lab",
 'Joint Italian and US university': "EU",
 'University in Europe ': "EU",
 'European University': "EU",
 'Department of Atomic Energy, India': "non-EU",
 'Retired from US University': "US University",
 'Canadian university': "Canada",
 'Swiss University': "EU",
 'Small business': "Industry",
 'kIAS': "non-EU",
 'Sultan Qaboos University (Oman)': "non-EU",
 'non-US university': "EU",
 'Uk institution': "EU",
 'Cairo university, Egypt': "non-EU",
 'Canadian University ': "Canada",
 'US DOE-funded laboratory (not formally a US National Lab)': "US National Lab",
 'US Liberal-Arts College (if "university" was meant to encompass this, feel free to log my response as such, but liberal-arts-college faculty probably would not use that terminology)': "US University",
 'Japanese University': "non-EU",
 'International university': "International",
 'Business': "Industry",
 'EU National Research Institute': "EU",
 'Saha Institute of Nuclear Physics , kolkata, India': "non-EU",
 'Research institution in Canada': "Canada",
 'former employee of US Nat Lab': "US National Lab",
 'CNRS': "EU",
 'German National Lab': "EU",
 'Institute of High Energy Physics, Chinese Academy of Sciences': "non-EU",
 'Italian Institution': "EU",
 'INFN - Italy': "EU",
 'Foreign university': "EU",
 'FEI University at SBC São Paulo, Brazil': "non-EU",
 'Swiss and French pensioner would be on CERN mission': "EU",
 'South Korea University': "non-EU",
 'actually, I am retired': "US University",
 'Non-US University': "EU",
 'Dutch university': "EU",
 'French Lab, previously working in the US': "EU",
 'Retired': "US University",
 'EU University': "EU",
 'EU institution': "EU",
 'Overseas university': "EU",
 'A foreign university full time and a US University as a part time ': "US University",
 'emeritus': "US University",
 'both lab and university': "US National Lab",
 "US National Lab (ret'd)": "US National Lab",
 'Employed abroad in a National Lab': "EU",
 'Canadian University': "Canada",
 'I am a self-funded PhD researcher so not employed.': "Industry",
 'University&Laboratory': "US University"
}
survey["employed_by"] = raw_survey[c_employed_by_name].transform(lambda i: employted_by_t[i] if i == i else 'Unknown')
','.join(survey.employed_by.unique())<jupyter_output><empty_output><jupyter_text>RankNormalize the rank of the person that replied<jupyter_code>c_rank = "I am a"
','.join(list((str(i) for i in raw_survey[c_rank].unique())))
rank_t = {
 'Post-doc': "Post-doc",
 'Professor or Lab Scientist (tenured)': 'Professor or Lab Scientist (tenured)',
 'Graduate Student': 'Graduate Student',
 'Professor or Lab Scientist (untenured)': 'Professor or Lab Scientist (untenured)',
 'Research Scientist': 'Research Scientist',
 'Perfer not to answer': 'Unknown',
 'Lab fellow': 'Professor or Lab Scientist (untenured)',
 'Engineer, Staff, or other non-physicist role': 'Engineer, Staff, or other non-physicist role',
 'PHD student': 'Graduate Student',
 'Lab management': 'Professor or Lab Scientist (tenured)',
 'retired engineer/physicist': 'Professor or Lab Scientist (tenured)',
 'retired professor ': 'Professor or Lab Scientist (tenured)',
 'Lab Director - Japan': 'Professor or Lab Scientist (tenured)',
 'Retired lab scientist': 'Professor or Lab Scientist (tenured)',
 'Division Director': 'Professor or Lab Scientist (tenured)',
 'retired professor': 'Professor or Lab Scientist (tenured)',
 'Retired': 'Professor or Lab Scientist (tenured)',
 'Honorary Professor': 'Professor or Lab Scientist (tenured)',
 'Emeritus': 'Professor or Lab Scientist (tenured)',
 'Scientist Emeritus (Fermilab)': 'Professor or Lab Scientist (tenured)',
 'Guest Scientist, retired. ': 'Professor or Lab Scientist (tenured)'
}
survey["job_rank"] = raw_survey[c_rank].transform(lambda i: rank_t[i] if i == i else 'Unknown')
','.join(survey.job_rank.unique())<jupyter_output><empty_output><jupyter_text>AttendingWould you attend the workshop. While we designed the survey with no options, someone slipped a `Yes` in there, which we need to map back to the other values.<jupyter_code>c_attend = "Setting aside the COVID, funding, etc issues for the moment, would you attend the Snowmass Summer Workshop in person?"
raw_survey[c_attend].unique()
attend_t = {
    'Yes': 'Would attend', 
    'Would attend': 'Would attend', 
    'Might attend': 'Might attend',
    'Would not attend': 'Would not attend'
}
survey["attend"] = raw_survey[c_attend].transform(lambda i: attend_t[i])<jupyter_output><empty_output><jupyter_text>Attendance FeedbackThe text feedback has to be done as a giant set of text strings, so no cleaning here.<jupyter_code>c_attendance_feedback = "If you'd like to say a few words to expand on the answer, please do!"
survey["attend_feedback"] = raw_survey[c_attendance_feedback]
c_worried_covid = "Please indicate how worried you are about each issue that might prevent you from attending: [COVID]"
c_worried_funding = "Please indicate how worried you are about each issue that might prevent you from attending: [Funding]"
c_worried_length = "Please indicate how worried you are about each issue that might prevent you from attending: [Length of the Workshop]"
c_worried_accessibility = "Please indicate how worried you are about each issue that might prevent you from attending: [Accessibility/Accommodation]"
c_worried_responsibilities = "Please indicate how worried you are about each issue that might prevent you from attending: [Competing Responsibilities]"
raw_survey[c_worried_covid].unique()
worried_t = {
    'Very worried': 1,
    'Likely will prevent me from attending': 1,
    'Might prevent me from attending': 2,
    'Could attend, but might limit participation': 3,
    'A little worried': 3,
    'Not a concern': 4,
    'No Worried': 4,
    'Unknown': 5,
}
survey['worried_covid'] = raw_survey[c_worried_covid].transform(lambda i: worried_t[i])
survey['worried_funding'] = raw_survey[c_worried_funding].transform(lambda i: worried_t[i])
survey['worried_length'] = raw_survey[c_worried_length].transform(lambda i: worried_t[i])
survey['worried_accessibility'] = raw_survey[c_worried_accessibility].transform(lambda i: worried_t[i])
survey['worried_responsibilities'] = raw_survey[c_worried_responsibilities].transform(lambda i: worried_t[i] if isinstance(i, str) else 5)

c_other_issues = "If there are other issues that concern you that are not listed above, please indicate here!"
survey['attend_other_concerns'] = raw_survey[c_other_issues]<jupyter_output><empty_output><jupyter_text>Travel<jupyter_code>c_travel = "If I attend Snowmass, I will be traveling"
survey['travel'] = raw_survey[c_travel].transform(lambda v: v if isinstance(v, str) else "Unknown")<jupyter_output><empty_output><jupyter_text>Management<jupyter_code>c_management="Are you part of the Snowmass Management Team (frontier group or sub-group convener, etc.)?"
raw_survey[c_management].unique()
survey['management'] = raw_survey[c_management].transform(lambda v: v if isinstance(v, str) else "Unknown")<jupyter_output><empty_output><jupyter_text>DinnerNo need to convert this - there were no optional values!<jupyter_code>c_dinner = "If you attended the conference, would you also attend the dinner?"
raw_survey[c_dinner].unique()
survey["dinner"] = raw_survey[c_dinner]<jupyter_output><empty_output><jupyter_text>FrontierThis is a bit difficult b.c each item is comma separated list, which is not very amenable to manipulation. We will convert to using the abreviations and also to converting it to separate columns.<jupyter_code>c_frontier = "I primarily am interested in sessions associated with:"
frontier_mapping = {
    'Neutrino Frontier': 'NF',
    'Energy Frontier': 'EF',
    'Rare Processes and Precision Measurements': 'RF',
    'Cosmic Frontier': 'CF',
    'Theory Frontier': 'TF',
    'Accelerator Frontier': 'AF',
    'Instrumentation Frontier': 'IF',
    'Computational Frontier': "CompF",
    'Underground Facilities': 'UF',
    'Community Engagement': 'CommF'
}

survey["frontier_all"] = raw_survey[c_frontier].transform(lambda v: np.array([frontier_mapping[f.strip()] for f in v.split(',')]) if isinstance(v, str) else [])
for f in frontier_mapping.values():
    survey[f'frontier_{f}'] = survey["frontier_all"].apply(lambda v: f in v)<jupyter_output><empty_output><jupyter_text>COVID and Attending<jupyter_code>c_covid = "The points below are labeled on the case/day plot above. Please indicate the largest USA new case-load/day rate for which you'd be willing to attend Snowmass in person."
covid_t = {
    'Point A': 'A',
    'Point B': 'B',
    'Point C': 'C',
    'Point D': 'D',
    'Point E': 'E',
    'Poing E': 'E',
    'Not com': 'At No Level',
    'My inst': 'Travel Restrictions'
}
survey['covid_comfort'] = raw_survey[c_covid].transform(lambda v: covid_t[v[0:7]])
c_covid_comments = "There is no way to get everyone's full perspective with a multiple choice question like that. Please feel free to add a sentence or so if you wish!"
survey['covid_comments'] = raw_survey[c_covid_comments]<jupyter_output><empty_output><jupyter_text>AccomodationIn the end the below cleaning couldn't be used - the survey response format makes it impossible to tell between comma's in the answer, and separate answers. Something to know for the next time we run a survey! The code is left here because it does provide some good example code for future efforts.<jupyter_code>c_hearing = "Is there any type of hearing-related accommodation that would increase the accessibility of the Snowmass Summer Study for you? (Please note that captioning can be useful for both Deaf and hard of hearing people and for people who do not have these identities but who find it helpful to be able to see as well as hear speakers' words.)"
survey['acc_hearing'] = raw_survey[c_hearing]

survey['acc_hearing_ar'] = raw_survey[c_hearing].transform(lambda v: np.array([f.strip() for f in v.split(',')]) if isinstance(v, str) else [])
m = re.compile(r', (?=(?:"[^"]*?(?: [^"]*)*))|, (?=[^",]+(?:,|$))')
def splitter(v: str) -> List[str]:
    return [f.strip('"') for f in m.split(v)]    
c_other_acc = "What other accommodations would increase the accessibility of the Snowmass Summer Study for you?"
survey["acc_other"] = raw_survey[c_other_acc]

survey['acc_other_ar'] = raw_survey[c_other_acc].transform(lambda v: np.array(splitter(v)) if isinstance(v, str) else [])

survey["acc_other"]
survey['acc_other_ar'].loc[4]
c_acc_comments = "Do you have any additional details you'd like to share or any other comments on how Snowmass Summer Study accessibility could be improved for you?"
survey['acc_comments'] = raw_survey[c_acc_comments]<jupyter_output><empty_output><jupyter_text>Writing out the fileAs a WARNING: Never check this in!<jupyter_code>survey.to_parquet('/workspaces/snowmass-attendance/clean_survey.parquet')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13251508217183275, 'fraction_numerical': 0.0008321198252548367, 'mean_word_length': 5.854562737642586, 'pattern_counts': {'":': 2, '<': 53, '<?xml version=': 0, '>': 54, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '85055', 'license': 'MIT', 'n_tokens_mistral': 4744, 'n_tokens_neox': 4408, 'n_words': 1600, 'path': 'attendance/cleaning.ipynb', 'repo_name': 'gordonwatts/snowmass-attendance', 'size': '14421'}