MSLQ_excel_template to create excel after OCR on MSLQ.pdf

mslq_excel_template.py

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
import os

# Define the MSLQ questions and their corresponding scales
mslq_questions = [
    # Format: [question_number, question_text, scale, is_reversed]
    [
        1,
        "In a class like this, I prefer course material that really challenges me so I can learn new things.",
        "Intrinsic Goal Orientation",
        False,
    ],
    [
        2,
        "If I study in appropriate ways, then I will be able to learn the material in this course.",
        "Control of Learning Beliefs",
        False,
    ],
    [3, "When I take a test I think about how poorly I am doing compared with other students.", "Test Anxiety", False],
    [4, "I think I will be able to use what I learn in this course in other courses.", "Task Value", False],
    [
        5,
        "I believe I will receive an excellent grade in this class.",
        "Self-Efficacy for Learning & Performance",
        False,
    ],
    [
        6,
        "I'm certain I can understand the most difficult material presented in the readings for this course.",
        "Self-Efficacy for Learning & Performance",
        False,
    ],
    [
        7,
        "Getting a good grade in this class is the most satisfying thing for me right now.",
        "Extrinsic Goal Orientation",
        False,
    ],
    [8, "When I take a test I think about items on other parts of the test I can't answer.", "Test Anxiety", False],
    [9, "It is my own fault if I don't learn the material in this course.", "Control of Learning Beliefs", False],
    [10, "It is important for me to learn the course material in this class.", "Task Value", False],
    [
        11,
        "The most important thing for me right now is improving my overall grade point average, so my main concern in this class is getting a good grade.",
        "Extrinsic Goal Orientation",
        False,
    ],
    [
        12,
        "I'm confident I can learn the basic concepts taught in this course.",
        "Self-Efficacy for Learning & Performance",
        False,
    ],
    [
        13,
        "If I can, I want to get better grades in this class than most of the other students.",
        "Extrinsic Goal Orientation",
        False,
    ],
    [14, "When I take tests I think of the consequences of failing.", "Test Anxiety", False],
    [
        15,
        "I'm confident I can understand the most complex material presented by the instructor in this course.",
        "Self-Efficacy for Learning & Performance",
        False,
    ],
    [
        16,
        "In a class like this, I prefer course material that arouses my curiosity, even if it is difficult to learn.",
        "Intrinsic Goal Orientation",
        False,
    ],
    [17, "I am very interested in the content area of this course.", "Task Value", False],
    [18, "If I try hard enough, then I will understand the course material.", "Control of Learning Beliefs", False],
    [19, "I have an uneasy, upset feeling when I take an exam.", "Test Anxiety", False],
    [
        20,
        "I'm confident I can do an excellent job on the assignments and tests in this course.",
        "Self-Efficacy for Learning & Performance",
        False,
    ],
    [21, "I expect to do well in this class.", "Self-Efficacy for Learning & Performance", False],
    [
        22,
        "The most satisfying thing for me in this course is trying to understand the content as thoroughly as possible.",
        "Intrinsic Goal Orientation",
        False,
    ],
    [23, "I think the course material in this class is useful for me to learn.", "Task Value", False],
    [
        24,
        "When I have the opportunity in this class, I choose course assignments that I can learn from even if they don't guarantee a good grade.",
        "Intrinsic Goal Orientation",
        False,
    ],
    [
        25,
        "If I don't understand the course material, it is because I didn't try hard enough.",
        "Control of Learning Beliefs",
        False,
    ],
    [26, "I like the subject matter of this course.", "Task Value", False],
    [27, "Understanding the subject matter of this course is very important to me.", "Task Value", False],
    [28, "I feel my heart beating fast when I take an exam.", "Test Anxiety", False],
    [
        29,
        "I'm certain I can master the skills being taught in this class.",
        "Self-Efficacy for Learning & Performance",
        False,
    ],
    [
        30,
        "I want to do well in this class because it is important to show my ability to my family, friends, employer, or others.",
        "Extrinsic Goal Orientation",
        False,
    ],
    [
        31,
        "Considering the difficulty of this course, the teacher, and my skills, I think I will do well in this class.",
        "Self-Efficacy for Learning & Performance",
        False,
    ],
    [
        32,
        "When I study the readings for this course, I outline the material to help me organize my thoughts.",
        "Elaboration",
        False,
    ],
    [
        33,
        "During class time I often miss important points because I'm thinking of other things.",
        "Metacognitive Self-Regulation",
        True,
    ],
    [
        34,
        "When studying for this course, I often try to explain the material to a classmate or friend.",
        "Elaboration",
        False,
    ],
    [35, "I usually study in a place where I can concentrate on my course work.", "Time & Study Environment", False],
    [36, "When reading for this course, I make up questions to help focus my reading.", "Elaboration", False],
    [
        37,
        "I often feel so lazy or bored when I study for this class that I quit before I finish what I planned to do.",
        "Effort Regulation",
        True,
    ],
    [
        38,
        "I often find myself questioning things I hear or read in this course to decide if I find them convincing.",
        "Critical Thinking",
        False,
    ],
    [39, "When I study for this class, I practice saying the material to myself over and over.", "Rehearsal", False],
    [
        40,
        "Even if I have trouble learning the material in this class, I try to do the work on my own, without help from anyone.",
        "Help Seeking",
        True,
    ],
    [
        41,
        "When I become confused about something I'm reading for this class, I go back and try to figure it out.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [
        42,
        "When I study for this course, I go through the readings and my class notes and try to find the most important ideas.",
        "Organization",
        False,
    ],
    [43, "I make good use of my study time for this course.", "Time & Study Environment", False],
    [
        44,
        "If course readings are difficult to understand, I change the way I read the material.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [
        45,
        "I try to work with other students from this class to complete the course assignments.",
        "Peer Learning",
        False,
    ],
    [
        46,
        "When studying for this course, I read my class notes and the course readings over and over again.",
        "Rehearsal",
        False,
    ],
    [
        47,
        "When a theory, interpretation, or conclusion is presented in class or in the readings, I try to decide if there is good supporting evidence.",
        "Critical Thinking",
        False,
    ],
    [48, "I work hard to do well in this class even if I don't like what we are doing.", "Effort Regulation", False],
    [49, "I make simple charts, diagrams, or tables to help me organize course material.", "Organization", False],
    [
        50,
        "When studying for this course, I often set aside time to discuss course material with a group of students from the class.",
        "Peer Learning",
        False,
    ],
    [
        51,
        "I treat the course material as a starting point and try to develop my own ideas about it.",
        "Critical Thinking",
        False,
    ],
    [52, "I find it hard to stick to a study schedule.", "Time & Study Environment", True],
    [
        53,
        "When I study for this class, I pull together information from different sources, such as lectures, readings, and discussions.",
        "Elaboration",
        False,
    ],
    [
        54,
        "Before I study new course material thoroughly, I often skim it to see how it is organized.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [
        55,
        "I ask myself questions to make sure I understand the material I have been studying in this class.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [
        56,
        "I try to change the way I study in order to fit the course requirements and the instructor's teaching style.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [
        57,
        "I often find that I have been reading for this class but don't know what it was all about.",
        "Metacognitive Self-Regulation",
        True,
    ],
    [58, "I ask the instructor to clarify concepts I don't understand well.", "Help Seeking", False],
    [59, "I memorize key words to remind me of important concepts in this class.", "Rehearsal", False],
    [60, "When course work is difficult, I either give up or only study the easy parts.", "Effort Regulation", True],
    [
        61,
        "I try to think through a topic and decide what I am supposed to learn from it rather than just reading it over when studying for this course.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [62, "I try to relate ideas in this subject to those in other courses whenever possible.", "Elaboration", False],
    [
        63,
        "When I study for this course, I go over my class notes and make an outline of important concepts.",
        "Organization",
        False,
    ],
    [64, "When reading for this class, I try to relate the material to what I already know.", "Elaboration", False],
    [65, "I have a regular place set aside for studying.", "Time & Study Environment", False],
    [
        66,
        "I try to play around with ideas of my own related to what I am learning in this course.",
        "Critical Thinking",
        False,
    ],
    [
        67,
        "When I study for this course, I write brief summaries of the main ideas from the readings and my class notes.",
        "Organization",
        False,
    ],
    [
        68,
        "When I can't understand the material in this course, I ask another student in this class for help.",
        "Help Seeking",
        False,
    ],
    [
        69,
        "I try to understand the material in this class by making connections between the readings and the concepts from the lectures.",
        "Elaboration",
        False,
    ],
    [
        70,
        "I make sure that I keep up with the weekly readings and assignments for this course.",
        "Time & Study Environment",
        False,
    ],
    [
        71,
        "Whenever I read or hear an assertion or conclusion in this class, I think about possible alternatives.",
        "Critical Thinking",
        False,
    ],
    [72, "I make lists of important items for this course and memorize the lists.", "Rehearsal", False],
    [73, "I attend this class regularly.", "Time & Study Environment", False],
    [
        74,
        "Even when course materials are dull and uninteresting, I manage to keep working until I finish.",
        "Effort Regulation",
        False,
    ],
    [75, "I try to identify students in this class whom I can ask for help if necessary.", "Help Seeking", False],
    [
        76,
        "When studying for this course I try to determine which concepts I don't understand well.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [
        77,
        "I often find that I don't spend very much time on this course because of other activities.",
        "Time & Study Environment",
        True,
    ],
    [
        78,
        "When I study for this class, I set goals for myself in order to direct my activities in each study period.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [
        79,
        "If I get confused taking notes in class, I make sure I sort it out afterwards.",
        "Metacognitive Self-Regulation",
        False,
    ],
    [80, "I rarely find time to review my notes or readings before an exam.", "Time & Study Environment", True],
    [
        81,
        "I try to apply ideas from course readings in other class activities such as lecture and discussion.",
        "Elaboration",
        False,
    ],
]

# Define the scales with their constituent questions
scales = {
    "Intrinsic Goal Orientation": [1, 16, 22, 24],
    "Extrinsic Goal Orientation": [7, 11, 13, 30],
    "Task Value": [4, 10, 17, 23, 26, 27],
    "Control of Learning Beliefs": [2, 9, 18, 25],
    "Self-Efficacy for Learning & Performance": [5, 6, 12, 15, 20, 21, 29, 31],
    "Test Anxiety": [3, 8, 14, 19, 28],
    "Rehearsal": [39, 46, 59, 72],
    "Elaboration": [32, 34, 36, 53, 62, 64, 69, 81],
    "Organization": [42, 49, 63, 67],
    "Critical Thinking": [38, 47, 51, 66, 71],
    "Metacognitive Self-Regulation": [33, 41, 44, 54, 55, 56, 57, 61, 76, 78, 79],
    "Time & Study Environment": [35, 43, 52, 65, 70, 73, 77, 80],
    "Effort Regulation": [37, 48, 60, 74],
    "Peer Learning": [45, 50],
    "Help Seeking": [40, 58, 68, 75],
}

# Group the selected scales as mentioned in the manual
selected_scales = [
    "Task Value",
    "Self-Efficacy for Learning & Performance",
    "Test Anxiety",
    "Rehearsal",
    "Elaboration",
    "Organization",
    "Metacognitive Self-Regulation",
    "Time & Study Environment",
    "Effort Regulation",
]


def create_mslq_workbook(num_respondents=5, filename="MSLQ_Questionnaire.xlsx"):
    """
    Create an Excel workbook for the MSLQ questionnaire with specified number of respondents.
    Scale and Reversed information are now hidden in a separate tab to avoid priming respondents.
    """
    # Create a new workbook
    wb = Workbook()

    # Create the Questions worksheet (main page for respondents)
    ws_questions = wb.active
    ws_questions.title = "MSLQ Questions"

    # Create the Results worksheet
    ws_results = wb.create_sheet("MSLQ Results")

    # Create a new Metadata sheet (hidden from respondents) for scales and reversed information
    ws_metadata = wb.create_sheet("Metadata")

    # Format the Questions worksheet (now without Scale and Reversed columns)
    # Add headers for the questions sheet
    headers = ["Question #", "Question Text"] + [f"Respondent {i+1}" for i in range(num_respondents)]
    for col_idx, header in enumerate(headers, 1):
        cell = ws_questions.cell(row=1, column=col_idx, value=header)
        cell.font = Font(bold=True)
        cell.fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

    # Add question data (without scale and reversed info)
    for row_idx, question in enumerate(mslq_questions, 2):
        question_num, question_text, scale, is_reversed = question

        # Add question number
        cell = ws_questions.cell(row=row_idx, column=1, value=question_num)
        cell.alignment = Alignment(horizontal="center", vertical="center")

        # Add question text
        cell = ws_questions.cell(row=row_idx, column=2, value=question_text)
        cell.alignment = Alignment(wrap_text=True, vertical="center")

        # Add respondent input cells
        for col_idx in range(3, 3 + num_respondents):
            # Empty cells for user input
            ws_questions.cell(row=row_idx, column=col_idx)

    # Add instructions at the top
    ws_questions.insert_rows(1, 3)
    instruction_cell = ws_questions.cell(
        row=1, column=1, value="MSLQ Questionnaire - Motivated Strategies for Learning Questionnaire"
    )
    instruction_cell.font = Font(bold=True, size=14)
    ws_questions.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(headers))

    instruction_cell = ws_questions.cell(
        row=2,
        column=1,
        value="Instructions: Please rate yourself on a scale from 1 to 7, where 1 is 'not at all true of me' and 7 is 'very true of me'.",
    )
    instruction_cell.font = Font(italic=True)
    ws_questions.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(headers))

    instruction_cell = ws_questions.cell(row=3, column=1, value="Enter values 1-7 in the respondent columns.")
    instruction_cell.font = Font(italic=True)
    ws_questions.merge_cells(start_row=3, start_column=1, end_row=3, end_column=len(headers))

    # Adjust column widths in questions sheet
    ws_questions.column_dimensions[get_column_letter(1)].width = 12  # Question #
    ws_questions.column_dimensions[get_column_letter(2)].width = 80  # Question Text (increased width)

    for i in range(3, 3 + num_respondents):  # Respondent columns now start at 3 instead of 5
        ws_questions.column_dimensions[get_column_letter(i)].width = 15  # Respondent columns

    # Format the Metadata worksheet (for scales and reversed info)
    metadata_headers = ["Question #", "Question Text", "Scale", "Reversed?"]
    for col_idx, header in enumerate(metadata_headers, 1):
        cell = ws_metadata.cell(row=1, column=col_idx, value=header)
        cell.font = Font(bold=True)
        cell.fill = PatternFill(start_color="FFD966", end_color="FFD966", fill_type="solid")
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

    # Add metadata for each question
    for row_idx, question in enumerate(mslq_questions, 2):
        question_num, question_text, scale, is_reversed = question

        # Add question number
        cell = ws_metadata.cell(row=row_idx, column=1, value=question_num)
        cell.alignment = Alignment(horizontal="center", vertical="center")

        # Add question text
        cell = ws_metadata.cell(row=row_idx, column=2, value=question_text)
        cell.alignment = Alignment(wrap_text=True, vertical="center")

        # Add scale
        cell = ws_metadata.cell(row=row_idx, column=3, value=scale)
        cell.alignment = Alignment(horizontal="center", vertical="center")

        # Add reversed status
        cell = ws_metadata.cell(row=row_idx, column=4, value="Yes" if is_reversed else "No")
        cell.alignment = Alignment(horizontal="center", vertical="center")

    # Adjust metadata column widths
    ws_metadata.column_dimensions[get_column_letter(1)].width = 12  # Question #
    ws_metadata.column_dimensions[get_column_letter(2)].width = 60  # Question Text
    ws_metadata.column_dimensions[get_column_letter(3)].width = 30  # Scale
    ws_metadata.column_dimensions[get_column_letter(4)].width = 12  # Reversed?

    # Format the Results worksheet
    # Add headers for the results sheet
    result_headers = ["Scale"] + [f"Respondent {i+1}" for i in range(num_respondents)]
    for col_idx, header in enumerate(result_headers, 1):
        cell = ws_results.cell(row=1, column=col_idx, value=header)
        cell.font = Font(bold=True)
        cell.fill = PatternFill(start_color="C6E0B4", end_color="C6E0B4", fill_type="solid")
        cell.alignment = Alignment(horizontal="center", vertical="center")

    # Add scales and formulas to calculate the average for each scale
    for row_idx, scale in enumerate(selected_scales, 2):
        # Add scale name
        cell = ws_results.cell(row=row_idx, column=1, value=scale)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(vertical="center")

        # Add formulas to calculate scale scores
        scale_questions = scales[scale]
        for col_idx in range(2, 2 + num_respondents):
            respondent_col = get_column_letter(
                col_idx + 1
            )  # +1 because respondent columns now start at column 3 in the Questions sheet

            # Create a list of formulas for each question in this scale
            formula_parts = []
            for q_num in scale_questions:
                q_row = (
                    next((idx for idx, q in enumerate(mslq_questions, 2) if q[0] == q_num), None) + 3
                )  # +3 due to the inserted instruction rows
                is_reversed = next((q[3] for q in mslq_questions if q[0] == q_num), False)

                if is_reversed:
                    # For reversed items: 8 - original value
                    formula_parts.append(
                        f"IF(ISNUMBER('MSLQ Questions'!{respondent_col}{q_row}), 8-'MSLQ Questions'!{respondent_col}{q_row}, \"\")"
                    )
                else:
                    # For normal items: use original value
                    formula_parts.append(
                        f"IF(ISNUMBER('MSLQ Questions'!{respondent_col}{q_row}), 'MSLQ Questions'!{respondent_col}{q_row}, \"\")"
                    )

            # Combine the parts with an AVERAGE function, but only if all values are filled in
            formula = f"=IF(COUNTA({','.join(formula_parts)})={len(formula_parts)}, AVERAGE({','.join(formula_parts)}), \"Not Complete\")"

            # Set the formula in the cell
            ws_results.cell(row=row_idx, column=col_idx, value=formula)

    # Add description for the scales
    ws_results.append([])
    description_row = ws_results.max_row + 1
    cell = ws_results.cell(row=description_row, column=1, value="Scale Descriptions:")
    cell.font = Font(bold=True)

    scale_descriptions = {
        "Task Value": "The student's evaluation of how interesting, how important, and how useful the task is.",
        "Self-Efficacy for Learning & Performance": "Expectancy for success and self-efficacy. Includes judgments about ability to accomplish tasks and confidence in skills to perform tasks.",
        "Test Anxiety": "Concerns both worry and emotionality components of anxiety. Refers to negative thoughts that disrupt performance and affective and physiological aspects of anxiety.",
        "Rehearsal": "Reciting or naming items from a list to be learned. For basic tasks and activation of information in working memory rather than acquisition of new information.",
        "Elaboration": "Strategies such as paraphrasing, summarizing, creating analogies, and note-taking. Helps students store information into long-term memory by building connections.",
        "Organization": "Strategies such as clustering, outlining, and selecting main ideas. Helps the learner select appropriate information and construct connections.",
        "Metacognitive Self-Regulation": "Planning, monitoring, and regulating. Includes goal setting, questioning, and self-testing.",
        "Time & Study Environment": "Includes scheduling, planning, and managing one's study time and setting where the student does their classwork.",
        "Effort Regulation": "Self-regulation, commitment to completing study goals even when there are difficulties or distractions.",
    }

    for scale, description in scale_descriptions.items():
        if scale in selected_scales:
            ws_results.append([scale, description])
            ws_results.merge_cells(
                start_row=ws_results.max_row, start_column=2, end_row=ws_results.max_row, end_column=1 + num_respondents
            )

    # Adjust column widths in results sheet
    ws_results.column_dimensions[get_column_letter(1)].width = 30  # Scale Names
    for i in range(2, 2 + num_respondents):
        ws_results.column_dimensions[get_column_letter(i)].width = 15  # Result columns

    # Add question mapping section to Metadata sheet
    ws_metadata.append([])
    ws_metadata.append(["Scale Mappings:"])
    cell = ws_metadata.cell(row=ws_metadata.max_row, column=1)
    cell.font = Font(bold=True)

    for scale, items in scales.items():
        ws_metadata.append([scale, ", ".join(map(str, items))])

    # Add reversed items section to Metadata sheet
    ws_metadata.append([])
    ws_metadata.append(["Reversed Items:"])
    cell = ws_metadata.cell(row=ws_metadata.max_row, column=1)
    cell.font = Font(bold=True)

    reversed_items = [q[0] for q in mslq_questions if q[3]]
    ws_metadata.append([", ".join(map(str, reversed_items))])

    # Save the workbook
    wb.save(filename)
    print(f"Excel file '{filename}' created successfully!")
    return filename


if __name__ == "__main__":
    try:
        num_respondents = 5
        filename = "MSLQ_Questionnaire_nobias.xlsx"

        output_file = create_mslq_workbook(num_respondents, filename)
        print(f"\nThe MSLQ questionnaire has been generated successfully!")
        print(f"File saved as: {os.path.abspath(output_file)}")
        print("\nInstructions:")
        print("1. Open the Excel file")
        print("2. Navigate to the 'MSLQ Questions' sheet")
        print("3. Have respondents enter values from 1-7 in their respective columns")
        print("4. The 'MSLQ Results' sheet will automatically calculate scale scores")
        print("5. Scale scores will show 'Not Complete' until all questions for that scale are answered")
        print("6. The 'Metadata' sheet contains scale and reversed item information (hidden from respondents)")

    except Exception as e:
        print(f"An error occurred: {e}")
        print("Using default values instead.")
        create_mslq_workbook()