<jupyter_start><jupyter_text>Import modules<jupyter_code>import numpy as np  # Matrix  manipulation
import os  # Filesystem support
import pandas as pd  # Dataframe handling
import Levenshtein  # Edit distance calculations<jupyter_output><empty_output><jupyter_text>Set project folders<jupyter_code>PROJECT_FOLDER = os.path.dirname(os.path.dirname(os.getcwd()))
PROCESSED_DATA_FOLDER = os.path.join(PROJECT_FOLDER, 'data', 'processed')
FINAL_DATA_FOLDER = os.path.join(PROJECT_FOLDER, 'data', 'final')<jupyter_output><empty_output><jupyter_text>Pandas settings<jupyter_code>pd.set_option("display.precision", 3)
pd.set_option("display.expand_frame_repr", False)
pd.set_option("display.max_rows", 50)<jupyter_output><empty_output><jupyter_text>Load dataframe<jupyter_code>DATA = os.path.join(
    PROCESSED_DATA_FOLDER, 
    'experiment_2',
    'data_processed.feather'
)

df = pd.read_feather(DATA)
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 1799 entries, 0 to 1798
Columns: 8322 entries, Start Date to Id
dtypes: float64(7372), int64(18), object(932)
memory usage: 114.2+ MB<jupyter_text>Drop timing variables and rename column headers<jupyter_code>to_drop = [
    'First Click',
    'Last Click',
    'Click Count',
]

columns_to_drop = [x for x in df.columns for y in to_drop if y in x]
df = df.drop(columns_to_drop, axis=1)\
    .rename(lambda x: x.split(' - ')[0], axis=1)\
    .rename(lambda x: x.replace('_Page Submit', '_timer'), axis=1)<jupyter_output><empty_output><jupyter_text>Merge colums with consent answer for each group & drop columns<jupyter_code>consent_cols = [f'G{x}Q1' for x in range(1, 16, 1)]
df['consent'] = df[consent_cols].sum(axis=1)
df = df.drop(consent_cols, axis=1)<jupyter_output><empty_output><jupyter_text>Merge columns with submitted fragments for each group into one column & drop columns<jupyter_code>cols = {
    f'Q{x}_answer': [f'G{y}Q{z}' for y in range(1, 16, 1)]
    for x, z in zip(range(1, 111, 1), range(5, 442, 4))
}

for quest, columns in cols.items():
    df.loc[:, quest] = df.loc[:, columns].fillna('').sum(axis=1)
    df = df.drop(columns, axis=1)<jupyter_output><empty_output><jupyter_text>Replace empty strings with nan values<jupyter_code>df.loc[:, cols.keys()] = df.loc[:, cols.keys()].replace({'': np.nan})<jupyter_output><empty_output><jupyter_text>Display dataframe section with submitted fragments (first five questions only)<jupyter_code>df.loc[0:10, list(cols.keys())[:5]]<jupyter_output><empty_output><jupyter_text>Count number of submitted fragments (nb: the last submission is not counted))<jupyter_code>df['Counter'] = df['fragments'] - 1<jupyter_output><empty_output><jupyter_text>Count non-empty submissions<jupyter_code>def count_non_empty(row: pd.Series) -> int:
    """
    Count actual number of submitted fragments.
    
    This function counts only non-empty submissions. 
    It takes account of the fact that workers
    skip fragments.
    """
    raw_submits = row['Counter']
    answer_columns = [f'Q{x}_answer' for x in range(1, raw_submits + 1)]
    return row[answer_columns].count()

df['Counter_real'] = df.apply(count_non_empty, axis=1)<jupyter_output><empty_output><jupyter_text>Generate columns with counter for emty submissions and share of empty submissions<jupyter_code>df['Counter_empty'] = df['Counter'] - df['Counter_real']
df['Counter_quota'] = df['Counter_empty'] / df['Counter']<jupyter_output><empty_output><jupyter_text>Merge columns with intervention timer and drop columns<jupyter_code>intervent_cols = [
    'Q4_timer',
    'Q7_timer',
    'Q10_timer',
    'Q13_timer',
]

df['Intervention_timer'] = df[intervent_cols].sum(axis=1)
df = df.drop(intervent_cols, axis=1)<jupyter_output><empty_output><jupyter_text>Merge columns with submission timer for each group<jupyter_code>timer_cols = {
    f'Q{x}_timer': [f'G{y}Q{z}_timer' for y in range(1, 16, 1)]
    for x, z in zip(range(1, 111, 1), range(2, 439, 4))
}

for quest, columns in timer_cols.items():
    df.loc[:, quest] = df.loc[:, columns].sum(axis=1)
    df = df.drop(columns, axis=1)<jupyter_output><empty_output><jupyter_text>Replace null timer with nan<jupyter_code>df.loc[:, timer_cols.keys()] = df.loc[:, timer_cols.keys()].replace({0.0:np.nan})<jupyter_output><empty_output><jupyter_text>Typecast columns with timing info to datetime objects<jupyter_code>timseries = ['Start Date', 'End Date']
for series in timseries:
    df[series] = pd.to_datetime(df[series], infer_datetime_format=True)

df['duration_survey'] = (df['End Date'] - df['Start Date']).dt.seconds
df['duration_work'] = df['end_time'] - df['start_time']<jupyter_output><empty_output><jupyter_text>Identify subjects for which the timer did not work (i.e. 10 min. +/- 5%)<jupyter_code>low_limit = (df['duration_work'] < 600 * 0.95)
up_limit = (df['duration_work'] > 600 * 1.05)
broken_timer = (low_limit| up_limit)

broken_columns = ['treatment', 'duration_work', 'fragments']
df_broken = df.loc[broken_timer, broken_columns]\
    .sort_values(by='duration_work')\
    .rename(lambda x: x.capitalize().replace('_', ' '), axis=1)

df_broken<jupyter_output><empty_output><jupyter_text>Remove subjects for which the timer did not work<jupyter_code>df = df[~broken_timer]
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1768 entries, 0 to 1798
Columns: 264 entries, Start Date to duration_work
dtypes: datetime64[ns](2), float64(152), int64(21), object(89)
memory usage: 3.6+ MB<jupyter_text>Make column with treatment names<jupyter_code>treatment_dict = {
    1: 'Neutral',
    2: 'Charisma without goal',
    3: 'Goal',
    4: 'Full charisma'
}

df['Treatment_str'] = df['treatment'].replace(treatment_dict)<jupyter_output><empty_output><jupyter_text>Make columns with binary variable for main treatment dimensions<jupyter_code>df['Charisma'] = df['treatment'].isin([2, 4]).astype(int)
df['Goal'] = df['treatment'].isin([3, 4]).astype(int)<jupyter_output><empty_output><jupyter_text>Rename columns with background questions<jupyter_code>var_dict = {
    'Q17': 'Enjoyment',
    'Q18': 'Motivation',
    'Q19': 'Future_motivation',
    'Q20': 'Frick_knowledge',
    'Q21': 'Identify_mission',
    'Q22': 'Shedule_change',
    'Q23': 'Time_mturk_increase',
    'Q24': 'Hours_mturk',
    'Q25': 'Concentration',
    'Q26': 'Mobile_device',
    'Q27': 'Touch_typing',
    'Q28': 'Hours_keyboard',
    'Q29': 'Hours_keyboard_total',
    'Q30': 'Age',
    'Q31': 'Gender',
    'Q32': 'Education',
    'Q33': 'Comments',
    'Q1_browser': 'Browser',
    'Q1_operating system': 'Operating_system',
    'Q1_resolution': 'Resolution',
    'Q1_version': 'Version',
}

df = df\
    .rename(var_dict, axis=1)\
    .rename(lambda x: x.capitalize(), axis=1)<jupyter_output><empty_output><jupyter_text>Generate columns with indicator variable for different genders<jupyter_code>df[['Male', 'Female', 'Diverse']] = pd.get_dummies(df['Gender'])<jupyter_output><empty_output><jupyter_text>Drop uneccessary columns<jupyter_code>df = df.drop(df.columns[7:11], axis=1)<jupyter_output><empty_output><jupyter_text>Remove leading/trailing whitespace and linebreaks<jupyter_code>def remove_whitespace_and_breaks(x):
    if isinstance(x, str):
        return x.strip().replace('\n', ' ').replace('\r', '')
    else:
        return x

df = df.applymap(remove_whitespace_and_breaks)<jupyter_output><empty_output><jupyter_text>Make dataframe with empty columns for edit distance and error_share, then join with main dataframe<jupyter_code>edit_cols = [
    col for x in range(1, 111)
    for col in (f'Q{x}_solution', f'Q{x}_distance', f'Q{x}_editratio')
]

edit_frame = pd.DataFrame(index=df.index, columns=edit_cols)
df = df.merge(edit_frame, left_index=True, right_index=True)<jupyter_output><empty_output><jupyter_text>Load dataframe with correct solutions<jupyter_code>path = os.path.join(PROCESSED_DATA_FOLDER, 'experiment_2', 'fragments.xlsx')
df_solutions = pd.read_excel(path, index_col=0, engine='openpyxl')
df_solutions.head()<jupyter_output><empty_output><jupyter_text>Make columns with correct solutions<jupyter_code>for group in range(1, 16, 1):
    for index, row in df_solutions.iterrows():
        df.loc[df['Group'] == group, index] = row[f'Solutions_group_{group}']<jupyter_output><empty_output><jupyter_text>Calculate Levenshtein edit distance<jupyter_code>column_pairs = [(f'Q{x}_answer', f'Q{x}_solution') for x in range(1, 111)]

for index, row in df.iterrows():
    number_answers_submitted = row.at['Counter']
    columns_to_check = column_pairs[:number_answers_submitted]

    for number, (answer_col, solution_col) in enumerate(columns_to_check):
        answer = row[answer_col]
        solution = row[solution_col]

        if isinstance(answer, str):
            edit_distance = Levenshtein.distance(answer, solution)
            edit_ratio = edit_distance / max(len(answer), len(solution))

        else:
            edit_distance = np.NAN
            edit_ratio = np.NAN

        dist_column = f'Q{number + 1}_distance'
        ratio_column = f'Q{number + 1}_editratio'

        df.at[index, dist_column] = edit_distance
        df.at[index, ratio_column] = edit_ratio<jupyter_output><empty_output><jupyter_text>Manually inspecting answers and evaluations for submitted fragments<jupyter_code>x = 33
cols = [
    f'Q{x}_answer', 
    f'Q{x}_solution', 
    f'Q{x}_distance',
    f'Q{x}_editratio', 
    f'Q{x}_timer',
    'Counter'
]

df.loc[(df['Counter'] >= x), cols].head(15)<jupyter_output><empty_output><jupyter_text>Order dataframe columns<jupyter_code>cols = [
    'Q{0}_answer',
    'Q{0}_solution',
    'Q{0}_distance',
    'Q{0}_editratio',
    'Q{0}_timer',
]

ques_cols = [elem.format(x) for x in range(1, 111) for elem in cols]
main = list(df.columns.difference(ques_cols))
df = df.reindex(main + ques_cols, axis=1)<jupyter_output><empty_output><jupyter_text>Save dataframe<jupyter_code>folder = os.path.join(FINAL_DATA_FOLDER, 'experiment_2')
df.reset_index(drop=True).to_feather(os.path.join(folder, 'data_final.feather'))
df.to_csv(os.path.join(folder, 'data_final.csv'))<jupyter_output><empty_output><jupyter_text>Convert to html<jupyter_code>!jupyter nbconvert --output-dir='./docs' --to html 1_make_dataset.ipynb<jupyter_output>[NbConvertApp] Converting notebook 1_make_dataset.ipynb to html
[NbConvertApp] Writing 656391 bytes to docs/1_make_dataset.html
{'fraction_non_alphanumeric': 0.1459177764910249, 'fraction_numerical': 0.022486006562439684, 'mean_word_length': 5.959704499664205, 'pattern_counts': {'":': 0, '<': 129, '<?xml version=': 0, '>': 131, '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': '32', 'dir': 'jupyter-structured-clean-dedup', 'id': '567608', 'license': 'AFL-3.0', 'n_tokens_mistral': 4003, 'n_tokens_neox': 3787, 'n_words': 717, 'path': 'notebook/experiment_2/1_make_dataset.ipynb', 'repo_name': 'sebfest/motivation_and_incentives', 'size': '10362'}