<jupyter_start><jupyter_text>Distractor analysis and scoring multiple-choice items___Using exercises spreadsheets from Designing and Analyzing Language Tests by Oxford.The purpose of this notebook is to compute find th percentage of the test takers selecting each response to a multiple-choice test, to score multiplee-choice reponse data, and to calculate the point-biserial coefficients for each option. General Setup___<jupyter_code># import libraries
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
# styling for plots
plt.style.use('seaborn-whitegrid')
plt.rcParams['figure.figsize'] = (14,5)<jupyter_output><empty_output><jupyter_text>Load the data___<jupyter_code>test = pd.read_excel('Data/scoring_and_distractor_analysis.xlsx', skiprows=lambda x: x in [1, 1])
test.head()
keys = pd.read_excel('Data/scoring_and_distractor_analysis.xlsx', nrows=1)
keys
# check the dataset info
test.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 30 entries, 0 to 29
Data columns (total 21 columns):
 #   Column   Non-Null Count  Dtype 
---  ------   --------------  ----- 
 0   Student  30 non-null     object
 1   Q01      30 non-null     object
 2   Q02      30 non-null     object
 3   Q03      30 non-null     object
 4   Q04      30 non-null     object
 5   Q05      30 non-null     object
 6   Q06      30 non-null     object
 7   Q07      30 non-null     object
 8   Q08      30 non-null     object
 9   Q09      30 non-null     object
 10  Q10      30 non-null     object
 11  Q11      30 non-null     object
 12  Q12      30 non-null     object
 13  Q13      30 non-null     object
 14  Q14      30 non-null     object
 15  Q15      30 non-null     object
 16  Q16      30 non-null     object
 17  Q17      30 non-null     object
 18  Q18      30 non-null     object
 19  Q19      30 non-null     object
 20  Q20      30 non-null     object
dtypes: object(21)
memory usage: 5.0+ KB<jupyter_text>Correctness___<jupyter_code># create a list of correct answers
correct_answers = []

for _, row in test.iterrows():
    correct_answers.append([np.where(row == row2, 1, 0) for _,row2 in keys.iterrows()][0])
# create a dataframe from the list and drop the first column
correctness = pd.DataFrame(correct_answers, columns=keys.columns.tolist())
correctness.drop('Student', axis=1, inplace=True)
correctness.head()
# calculate total correct answers and add it to the dataframe
correctness['Total'] = correctness.sum(axis=1)
correctness.head()
# calculate pandas stats and converting it to a dataframe
stats = pd.DataFrame(correctness['Total'].describe())
stats
# renaming the std to std(sample) and add std for population
stats.loc['std(sample)'] = stats.loc['std']
stats.loc['std(pop)'] = correctness['Total'].std(ddof=0)

# renaming the min and max 
stats.loc['high score'] = stats.loc['max']
stats.loc['low score'] = stats.loc['min']
stats.loc['n'] = stats.loc['count']

# adding other stats
stats.loc['mode'] = [correctness['Total'].mode().tolist()]
stats.loc['var(sample)'] = correctness['Total'].var()
stats.loc['var(pop)'] = correctness['Total'].var(ddof=0)
stats.loc['range'] = stats.loc['high score'] - stats.loc['low score'] + 1
stats.loc['Q'] = (stats.loc['75%'] - stats.loc['25%']) / 2
stats.loc['skewness'] = correctness['Total'].skew()
stats.loc['kurtosis'] =  correctness['Total'].kurt()

# removing not needed lines
stats.drop(['std', 'min', 'max', 'count'], axis=0, inplace=True)
stats
# round all stats to two decimal points and changing the order
stats = np.round(stats, 3)
stats = stats.reindex(index = ['mean','mode','25%', '50%', '75%', 'high score', 'low score', 
                       'range', 'std(pop)', 'std(sample)', 'var(pop)', 'var(sample)', 'Q', 'n',
                      'skewness', 'kurtosis'])
stats.index.name = 'Statistics'
stats<jupyter_output><empty_output><jupyter_text>Responces___<jupyter_code># create a list of reponces and 
responces_list = [[] for _ in range(len(np.unique(keys.values))-1)]

for _, col in test.iteritems():
    responces_list[0].append(sum(np.where(col == 'A', 1, 0)))
    responces_list[1].append(sum(np.where(col == 'B', 1, 0)))
    responces_list[2].append(sum(np.where(col == 'C', 1, 0)))
    responces_list[3].append(sum(np.where(col == 'D', 1, 0)))
    
# convert into a dataframe
responces = pd.DataFrame(responces_list, columns=keys.columns, index=['A', 'B', 'C', 'D'])
responces.drop('Student', axis=1, inplace=True)
# calculate total
responces.loc['Total'] = responces.sum(axis=0)
responces.index.name = 'Responce'
responces
# calculate percentage responce
responces_perc = np.around(responces[:-1] / responces.loc['Total'] * 100)
responces_perc.index = ['% A', '% B', '% C', '% D']
responces_perc.index.name = 'Responce %'
responces_perc
responces_perc.T.style.apply(lambda x: ["background: yellow" if v < 10 else "" for v in x], axis = 1)
# Create a list of flagged questions based on reponse percentage
flagged = responces_perc.apply(lambda x: [v if v < 10 else "" for v in x])
flagged = flagged.replace('', np.NaN)
flagged.dropna(axis=1, how='all', inplace=True)
flagged.index.name = 'Flagged'
flagged<jupyter_output><empty_output><jupyter_text>Point Biserial___Items for revision need to be looked at manually.<jupyter_code>r_pb = pd.DataFrame(columns=keys.columns[1:])

# create a dataframe for a responces
for i in np.unique(keys.values)[:-1]:
    option = pd.DataFrame([np.where(row == i, 1, 0) for _, row in test.iterrows()], columns=keys.columns)
    option.drop('Student', axis=1, inplace=True)
    r_pb.loc[i] = np.round(option.corrwith(correctness['Total'], method='pearson'), 3).T
    
r_pb.index.name = 'Option Point-Biserials'
r_pb.index = ['A pb(r)', 'B pb(r)', 'C pb(r)','D pb(r)']
r_pb.head()<jupyter_output><empty_output><jupyter_text>Plotting___<jupyter_code># histograms and frequency polygon
fig, [ax0, ax1] = plt.subplots(1,2)
fig.suptitle('Distribution of Scores', y=1.02, weight='bold', fontsize=13)

# total scores
ax0.hist(correctness['Total'], bins=40)
ax0.set(title='Histogram of Total Scores',
      xlabel='Scores',
      ylabel='Frequency')
ax0.axvline(stats.loc['mean'][0], linestyle='--', c='red', label='Mean')
ax0.axvline(stats.loc['mode'][0][0], linestyle='--', c='purple', label='Mode')
ax0.axvline(stats.loc['mode'][0][1], linestyle='--', c='purple')
ax0.axvline(stats.loc['mode'][0][2], linestyle='--', c='purple')
ax0.axvline(stats.loc['50%'][0], linestyle='--', c='orange', label='Median')

# total scores
ax1.plot(correctness['Total'],marker='.', linestyle='solid', markersize=20, markerfacecolor='lightyellow')
ax1.set(title='Frequency Polygon for Total Scores',
      xlabel='Scores',
      ylabel='Frequency')

# display legend
ax0.legend(frameon=True, fancybox=True, shadow=True)

# save the plot
plt.savefig('Data/correct_scores_multichoice.png', bbox_inches='tight');
# sort scored in descending order
sorted_scores = correctness.sort_values('Total', ascending=False, kind='stable')
sorted_scores['Total'] = correctness['Total']
sorted_scores.head()<jupyter_output><empty_output><jupyter_text>Save the results to an excel file___<jupyter_code># write and save all dataframes to the excel file 
writer = pd.ExcelWriter('Data/distractor_analysis.xlsx', engine='xlsxwriter')
sorted_scores.to_excel(writer, index = False)
responces.to_excel(writer, startrow=len(sorted_scores) + 2, index=True)
responces_perc.to_excel(writer, startrow=len(sorted_scores)+ len(responces) + 5, index=True)
flagged.to_excel(writer, startrow=len(sorted_scores)+ len(responces) + len(responces_perc) + 7, index=True)
stats.to_excel(writer, startcol=len(sorted_scores.columns)+1, index=True)

# insert the image into the worksheet
workbook = writer.book
worksheet = writer.sheets['Sheet1']
worksheet.insert_image('A54', 'Data/correct_scores_multichoice.png')

# styling
column_settings = [{'header': column} for column in sorted_scores.columns]
(max_row, max_col) = sorted_scores.shape
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
writer.save()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15301429459291485, 'fraction_numerical': 0.028340584213797388, 'mean_word_length': 4.755364806866953, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 29, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '612195', 'license': 'MIT', 'n_tokens_mistral': 2946, 'n_tokens_neox': 2785, 'n_words': 684, 'path': 'Data analytics/Language tests/Distractor analysis and scoring multiple-choice items.ipynb', 'repo_name': 'olgarozhdestvina/Data-Science-and-Machine-Learning', 'size': '8045'}