Qualtrics to WebCDI
How to reformat CDI data collected on Qualtrics so that it can be uploaded onto webCDI
Last updated
How to reformat CDI data collected on Qualtrics so that it can be uploaded onto webCDI
Last updated
All of the files you need will be located on the psych drive in: BergelsonLab > Subject_information > CDI_data_wrangling
The Set-Up
Open "CDI_June 15, 2018.csv" (this is the CDI data from Qualtrics).
Open "webcdi_temple_respones.csv."
Create a new csv file, name it "STUDYNAME_webcdi_format.csv" (for example if I am working on VNA data I would name the file "VNA_webcdi_format.csv"), and save it in the CDI_data_wrangling folder.
Copy and paste the 11 rows of the webcdi template spreadsheet into the top of your new csv file (you will use this as a guide while reformatting the data).
In the Qualtrics data spreadsheet (the June 15, 2018 csv), look for your study in the "Subject_id" column (the Subject_id column is sorted alphabetically to make this easy. So, if I am working with VNA data I will scroll down until I find all the subject id's that start with "V").
Copy and paste the rows with your subjects' data into your csv file that now has the template data in it. To make it easier for you later, paste this data farther down in the spreadsheet so that you can work with in the rows between the rows of template data and the rows of qualtrics data you are pasting in. (NOTE: some of the dates/times will show up as "########" if this happens, just double click on those cells).
STOP: Make sure to check if there are any duplicate subject numbers. If there are, cross check with the "Participantdata.xlsx" spreadsheet (also in the Subject_information folder) to figure out which row of data is the correct one (the best way to do this is usually looking at the date of participation and seeing which CDI was completed closer to that date and use that one).
Lastly, copy and paste the first row of the Qualtrics spreadsheet with the column headings above the rows of subject data that you just pasted in. At this point, your spreadsheet should look like this (with template data on top, a section of blank rows for reformatting, and then rows of your subjects' data):
Now we are actually going to move stuff around!
Now you can close the June 15, 2018. csv and the template file
In the csv you have created, the first thing you should do is copy and paste the "Subject_id" column from the Qualtrics data section to line up below the "name_of_child" column from the template data section (this should be column D). Then, delete the letter in front of each of the subject id's so that only the numbers are left.
Now, we will go back and fill in the three columns to the left.
The first column (column A) which is filled with CDI_Words_GESTURES_fillable_XXXX.pdf in the template version will be left blank.
The second column labeled "From" and filled with "anonymous user" will be the same for your reformatted data. To do this, type in "anonymous user" in the first row of your reformatted data, then hover over the bottom right hand corner of that cell until you see a black cross. When you see the cross, click and drag down to the last row of your reformatted data to fill all of the rows with "anonymous user."
In column C, labeled "Received Date," in the template data, copy and paste the RecordedDate column from the Qualtrics data section.
Next, for columns E, F, and G line up "gender," "birthdate," and date_today" correspond to "Q36," "Q36_2" and "EndDate" respectively. Copy and paste these columns in the correct places.
As you will see, the data in the two gender columns is entered slightly differently. To fix this, highlight the "Q36" column, go to the "Edit" menu, click "Replace..." and a window like the one pictured below should pop up.
In the "Find what:" box type "Female" and in the "Replace with:" box type "f" and then click "Replace All." Then do the same thing for male to replace "Male" with "m."
Now, the remaining columns should be in the right order! YAY!!!!
Highlight the Qualtrics data starting with the column titled "Q5_1" until the third to last column titled "Q32_15" and copy and paste this data in the columns to the right of the "EndDate" column in your reformatted section. At this point, the rightmost column of the template data should line up with the rightmost column of your reformatted data. If not, something is wrong :(
Once you have confirmed that you have the same number of columns in the template data as in your reformatted data you can delete the original data that you copied over from the Qualtrics spreadsheet.
Now its time for more replacing!!!
Highlight columns "Q6_1" through "Q6_28" using the replace tool that you used before, replace "Understands" with "Yes" and replace " " (blank cells, to do this you can just leave the "Find what:" box completely blank) with "Off."
Then, highlight columns "Q9_1" through "Q26_8" and replace "Understands and Says" with "understands and says", "Understands" with "understands", and " " (blank) with "Off."
Now, you can delete the rows of template data (BUT NOT THE COLUMN HEADINGS) and the column headings from the Qualtrics data. Your spreadsheet should now look like the image below. SAVE THE SPREADSHEET!!
Uploading the data to WebCDI
Go to the webcdi webpage (https://webcdi.stanford.edu/) and log into the bergelsonlab account (ask if you don't know the password).
In the dropdown menu, select the study you are uploading data for, if your study does not appear on the menu you can add it by clicking the "New Study" button on the right side of the screen.
Once on the correct study's page, click the down arrow on the right side of the leftmost gray button "Add Participants" and select "Import Spreadsheet Data."
Click on "Choose File" and navigate to the csv file you created (remember it should be on the psych drive in BergelsonLab > Subject_information > CDI_data_wrangling).
Click Submit (NOTE: if nothing happens, something is most likely formatted incorrectly in your csv file, so go back and make sure everything looks right).
Make sure that all of the subjects you just added now show up under data.
YOU'RE DONE!!
The data in the "EndDate" column also looks slightly different. To fix this, highlight the EndDate column and in the top toolbar, where it says "Number" select "Date" in the drop down menu. This should reformat the date with just MM/DD/YY to match the template.
