Part 1 Exploring The Data
(Basic Spreadsheet Format)
This section of the tutorial is for data downloaded through the basic spreadsheet format. To understand the advanced spreadsheet format click here.
SurveyMonkey data does not flow into SPSS easily. In order to understand why, we need to first look at how SurveyMonkey exports the data into Excel. In this section of the tutorial, we will take a close look at exported data, then we will manipulate that data in the following lesson.
Before we begin let's open the survey.
Action: Open Survey
Now read through the survey and examine the different types of questions. There are a total of 15. Look at question number seven. It has has six Likert scales below the main question. In reality this one question is actually six individual questions resulting in six variables. We will learn more about this when we explore the data in SPSS and Excel.
Action: Open file One and save it to the tutorial folder on your hard drive. Then close the file.
Section 1 Understanding SurveyMonkey Data in SPSS
In this section we will take a look at how SurveyMonkey data imports into SPSS and Excel.
Before we begin the process of importing data into SPSS we must go through a number of steps. Most of these steps will be in Excel. There is data that needs to be cleaned. To understand what I mean, let's first take a look at the data in SPSS without any editing. This is data downloaded directly from SurveyMonkey into Excel and then opened in SPSS. No changes have been made.
Action: Open SPSS
Action: Import the Excel file File>Open>Data> Then find the file you just downloaded. If you do not see your file you may need to change the file type (See Below).
Now take a look at the SPSS file. Expand the name column. It is not pretty. Let's take a closer look at several variables. The first 9 variable names work well. Number 10, however, does not. It is one very long name! Now look at variable 12 - which (not coincidently) is named, v12. This is definitely not a helpful or intuitive name. Scroll down to row 39. There is question seven again followed by 5 unnamed variables. These are the six Likert scale questions we saw previously.
Now take a look at the labels column. Some variables have labels and some do not. One last column to look at is the values column. In this case there is nothing useful at all. Needless to say, our data is not ready for SPSS.
Action: Close file one
Section 2 Understanding data organization using value labels
So we will leave SPSS for the time being and jump into Excel. Let's start by opening an excel file that has the actual value labels and take a look at how SurveyMonkey organizes the data. The file will be used to take a closer look at the data as we go through the tutorial.
Action: Open Excel file Two
Columns A through I are put there by SurveyMonkey and contain information such as ResponderID, CollectorID, StartDate etc. These columns are not part of our survey and right now we will ignore them. Instead let's examine column J which contains the data for the question "I have a pet dinosaur or have had a pet dinosaurs in the past." Below is the Excel spreadsheet and the corresponding survey question. The question is a yes/no type of question and the user can only select one answer. (Hint: questions that allow only one answer use round radio buttons.) The entire question is in the first row of column J. This is followed by the word response. After that each row contains either a yes or a no.
The next set of columns is not as simple. Columns K through P contain data for the following question "Below is a list of the most common domesticated dinosaurs. Please select any that you have had as a pet. These include dinosaurs that you have as pets now and those you have had in the past."
This question is displayed in the top row of column K. Take a look at your own version of the file and notice that, while this question looks as if it is stretched across columns K through P, it is not. Expand column K. The question remains in that column only and the other columns have empty cells in the top row.
This question allows the user to give more then one answer.(Hint: a question that allows more then one answer contains square check boxes.) SurveyMonkey exports the data by placing each choice in its own column. If the user checks the box the answer is placed in that user's row. If the user has left the box unchecked, the cell is left empty. This may seem pretty clear now but when we get to the actual file we will import into SPSS it is not as easy to understand.
Now let's examine columns Q through U and the corresponding question. "Below is a list of the most common domestic dinos. Please rate their scariness." This type of question is a Likert scale and every cell contains an answer. Once again radio buttons means that the user can only choose on answer per dinosaur. As above, we see that questions that use radio buttons have answers in each cell of the spreadsheet.
In your opened file, examine columns Q through U . The question, once again, looks as if it runs across all the columns.
Now open column Q. Once again the question is in only one column while the choices stretch across the second row in individual columns. The following rows give the users' answer for each type of dinosaur.
At this point there is no need to go through the rest of the questions since all the questions will be variations of these three. We cannot, however, import this file into SPSS because we need to import the value labels as numbers instead of words. Let's do that now.
Action: Close all opened files
Section 3 - Understanding data organization using values (numbers)
For the next part of this exercise we will use a new Excel file.
Action: Open file three
Take a look at the following question. Below is a list of the most common domestic dinos. Please rate their scariness.
Instead of using words as we saw above. SurveyMonkey has exported the data as numbers. This is what we want for analyzing our data in SPSS. This question is pretty straight forward. It is a Likert scale which goes from 1 (not scary at all) to 5 (Terrifying!) As you can see in our Excel spreadsheet below. The rows below each dino have the numbers 1 through 5, instead of the text. This one is straightforward.
This next example is what stumps most people. Take a look at columns K through P. The rows under each column contain either a blank cell or a specific number. K has either blank cells or the number 1, J has blank cells or the number 2, and so on.
So what is happening here? Remember when we examined the data using text values? Well, look again.
Each dino (variable) has a value which uses a specific number instead of specific text. This is problem because instead of yes and no values, you only have yes and missing values. This is one of the things we will have to clean up in the next lesson. It is this specific problem that confuses many people..
Section 4 -Understanding variable names
When SPSS imports Excel data the variable names are taken from the first row in the spreadsheet. This is a problem. Take a look in the at the top row and notice that some of the cells have text while others are blank. This may be hard to see because the questions seem to stretch out across the top cells. This is what we saw in the previous examples. Try expanding column Q and notice that R S T and U have blank cells in the top row.
At the same time, row 2 has text as well but SPSS will not read that text as variable labels because it is on the second row. In the next part of the tutorial, we will create a new row and use that row to make our variable labels.
Go to part 2 Cleaning SurveyMonkey (Basic Spreadsheet) |
