Combining Surveys from Qualtrics with Python

Setup by installing Python library.

library(reticulate)
use_python("C:/Users/mperki17/AppData/Local/Programs/Python/Python312/python.exe")

knitr::opts_chunk$set(echo = TRUE, error=TRUE)

Install Python libraries.

import sys
!pip install qualtricsAPI
!pip install pandas
import qualtricsAPI
print(qualtricsAPI.__file__)

Generate a list of surveys under your Qualtrics API and their IDs and write into a .csv file to later loop. Have your Qualtrics API Token on a txt file securely stashed.

import csv
import requests

# Read API key from a .txt file
with open("qualtrics.txt", "r") as f:
    api_key = f.read().strip()

# Construct the API URL
base_url = "https://uwyo.az1.qualtrics.com/API/v3/surveys"
headers = {
    "x-api-token": api_key,
    "Content-Type": "application/json"
}

try:
    # Fetch list of surveys
    response = requests.get(base_url, headers=headers)
    response.raise_for_status()  # Raise an error for non-2xx status codes
    
    # Extract survey IDs and names from the response
    surveys = response.json()["result"]["elements"]
    
    # Write survey IDs to CSV file
    with open("survey_ids.csv", "w", newline="") as csvfile:
        fieldnames = ["SurveyID", "SurveyName"]
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
        writer.writeheader()

        for survey in surveys:
            writer.writerow({"SurveyID": survey["id"], "SurveyName": survey["name"]})

    print("Survey IDs written to survey_ids.csv successfully.")
except requests.exceptions.RequestException as e:
    print("Error fetching survey IDs:", str(e))

Pull the data from one course. Use the csv file you wrote and the txt file. Here we pull from the fith survey on the list.

# Import necessary libraries
import os
import csv
import pandas as pd
import QualtricsAPI
from QualtricsAPI.Survey import Responses
from QualtricsAPI.Setup import Credentials

try:
    print("Attempting to read API key from qualtrics.txt...")
    
    # Read API key from a .txt file
    with open("qualtrics.txt", "r") as f:
        api_key = f.read().strip()

    print("API key read successfully.")
    print("Initializing Credentials...")

    # Initialize an instance of Credentials
    c = Credentials()

    print("Calling qualtrics_api_credentials method...")
    
    # Call the qualtrics_api_credentials() method
    c.qualtrics_api_credentials(token=api_key, data_center='uwyo.az1')

    print("Credentials initialized successfully.")
    print("Creating instance of Responses...")

    # Create an instance of Responses within the try block
    r = Responses() 

    print("Instance of Responses created successfully.")
    print("Fetching survey responses...")

   # Read survey ID from the CSV file
   # Read survey ID from the 5th row of the CSV file
with open("survey_data.csv", "r") as csv_file:
    csv_reader = csv.reader(csv_file)
    for _ in range(4):  # Skip the first four rows
        next(csv_reader)
    survey_id = next(csv_reader)[0]  # Assuming the survey ID is in the first column of the fifth row

print("Survey ID read successfully:", survey_id)

   
   
   
    # Fetch survey responses using the 'r' instance
    survey_responses = r.get_survey_responses(survey_id)

    print("Survey responses fetched successfully.")

    if not survey_responses.empty:
        # Specify the output file path
        output_file = "survey_data.csv"

        try:
            # Write survey responses DataFrame to a CSV file
            survey_responses.to_csv(output_file, index=False)
            print("CSV file saved:", output_file)
        except Exception as e:
            print("Failed to write CSV file:", str(e))
    else:
        print("No survey data available for survey ID:", survey_id)
except Exception as e:
    print("Error:", str(e))

Pull all surveys under the API key, pivot them, then row-bind them and write them into one csv file.

import os
import pandas as pd
import QualtricsAPI
from QualtricsAPI.Survey import Responses
from QualtricsAPI.Setup import Credentials

# Read API key from a .txt file
with open("qualtrics.txt", "r") as f:
    api_key = f.read().strip()

# Read survey IDs from a .csv file
survey_ids_df = pd.read_csv("survey_ids.csv")

# Extract survey IDs and names from columns A and B respectively
survey_ids = survey_ids_df.iloc[:, 0].tolist()
survey_names = survey_ids_df.iloc[:, 1].tolist()

# Specify the output directory
output_directory = "C:/Users/mperki17/OneDrive - University of Wyoming/Assistant Dean/STE/R Survey Work/output"

# Ensure that the output directory exists
if not os.path.exists(output_directory):
    os.makedirs(output_directory)
    print("Output directory created:", output_directory)
else:
    print("Output directory already exists:", output_directory)

# Initialize an empty list to store pivoted DataFrames
pivoted_dataframes = []

# Initialize an instance of Credentials
c = Credentials()

# Call the qualtrics_api_credentials() method
c.qualtrics_api_credentials(token=api_key, data_center='uwyo.az1')

# Initialize an instance of Responses
r = Responses()

# Iterate over each survey ID and name
for survey_id, survey_name in zip(survey_ids, survey_names):
    try:
        # Fetch survey responses using the existing instance of Responses
        survey_responses = r.get_survey_responses(survey_id)  # Fetch responses for the survey_id

        if not survey_responses.empty:
            # Add a new column with the survey name
            survey_responses['SurveyName'] = survey_name

            # Pivot the DataFrame longer
            pivoted_df = survey_responses.melt(id_vars=['ResponseId', 'SurveyName'], 
                                                var_name='Question', 
                                                value_name='Response')

            # Append pivoted DataFrame to the list
            pivoted_dataframes.append(pivoted_df)

            print("Survey data processed for:", survey_id)
        else:
            print("No survey data available for:", survey_id)
    except Exception as e:
        print("Error processing survey data for:", survey_id, "-", str(e))

# Concatenate all pivoted DataFrames into one DataFrame if DataFrames were successfully generated
if pivoted_dataframes:
    combined_dataframe = pd.concat(pivoted_dataframes, ignore_index=True)

    # Specify the output file path
    output_file = os.path.join(output_directory, "combined_survey_data.csv")

    try:
        # Write combined DataFrame to a CSV file
        combined_dataframe.to_csv(output_file, index=False)
        print("CSV file saved:", output_file)
    except Exception as e:
        print("Failed to write CSV file:", str(e))
else:
    print("No survey data available to create combined DataFrame.")

knitr::opts_chunk$set(echo = TRUE, error=TRUE)

Move to R to tidy the data and then pipe it to an rpivot table

library(dplyr)
library(rpivotTable)

# Read the CSV file
surveymaster <- read.csv("C:/Users/mperki17/OneDrive - University of Wyoming/Assistant Dean/STE/R Survey Work/output/combined_survey_data.csv")

# Remove rows with ResponseId equal to '{"ImportId":"_recordId"}' or 'Response ID'
surveymaster <- subset(surveymaster, !(ResponseId %in% c('{"ImportId":"_recordId"}', 'Response ID')))


rpivotTable(surveymaster)