psych week 3 dq 1

Mishawn6

PSYC_6800_WK3_CleaningDatasetsJobAidExcel.pdf

Home >Psychology homework help >psych week 3 dq 1

PSYC 6800: Applied Psychology Research Methods

Cleaning Datasets Job Aid

Introduction

There are two primary ways to obtain a dataset. The first way is where you, the

researcher, collect your own data, and then enter them onto a spreadsheet like Excel.

The second way is to use what is called existing data or secondary data, which are data

that have been collected and entered into a spreadsheet by somebody else.

The more reliable the person or organization that entered data, the more trustworthy

those data are. For example, if you use data from the Census Bureau, you can be very

confident that the data are trustworthy.

However regardless of whether you enter the data yourself or use secondary data that

somebody else entered, you should always check the data for errors and determine

how to address those errors in order to have a “clean” dataset. Without cleaning the

dataset, the results would be inaccurate and most likely incorrect.

Understanding Your Data

An important step before you check for errors is to really understand your data. Whether

you collect your own data or you get secondary data, it is essential that you understand

it.

PSYC 6800: Applied Psychology Research Methods

1. Download and open the Week 3 “Cleaning and Management Example Dataset”

in Excel.

2. Download and open the Week 3 “Cleaning and Management Example Dataset

Codebook”

3. Examine the codebook and variables to gain an understanding of the typical

range of scores for each variable and how those scores should look.

This is a raw data set. There are 13 variables. The first one is ID, numbered 1 to 191.

However, if you look carefully, there are 190 participants in this sample; ID number 133

was deleted from the sample. If you were generating the codebook for this sample, you

would want to include an explanation of why ID #133 was deleted: perhaps the

participant chose to withdraw from the study before the study was completed.

The next variable, “age” represents the age in years for each participant in the sample.

It is a continuous (i.e., scale) variable. In this case, the participants were students in

college.

Gender is dichotomous in this sample. Based on this dataset, 0 represents male and 1

represents female.In fact, when creating your own research dataset, it is important to

generate a codebook that includes the variables you have collected and what each

value (if a nominal or ordinal variable) represents. If you have a continuous (i.e., scale)

variable, include the acceptable range within your codebook.

PSYC 6800: Applied Psychology Research Methods

The next variable listed is “scholan”. According to the codebook, its data show whether

participants took a foreign language in high school; 0 = no, 1 = yes. This is another

dichotomous variable.

The “studyhab” variable ranges from 0 to 1. The variable indicates the degree to which

the participants have good study habits. The data came from an instrument called the

Study Habits Inventory. An example of an item would be, “I wait until the night before

studying for a test.” Participants responded with a “yes” or a “no”. The scores were

coded such that responses that reflected good study habits received a score of 1; poor

study habits received a score of 0. The score for the “studyhab” variable is based on the

average score across all the items.

GPA: in the US, that normally ranges from 0 to 4.0. Anything outside that range would

be an error.

The variable “expect’ was the expectation of the participants for their final course

average. For the course in the study, it ranged between 0 and 100.

The next variables, “esteem” and “schcomp”, are both self-perception variables. The

first measures academic self-esteem; the second measures perceived skill competence.

Both of these variables were collected using what is called a Likert-type scale, which

was the person's last name who came up with this scale. So in this case, a Likert-type

scale ranged from 1 to 5 with 1 meaning strongly disagree, 2 disagree, 3 neutral, 4

PSYC 6800: Applied Psychology Research Methods

agree, and 5 strongly agree. Several items were included to measure each variable

construct. The scores for each individual item were then averaged to generate the score

for the variable. So anything outside a range of 1–5 will be an error for both of these

variables.

The next four variables also use a Likert-type scale, similar in kind to that used to

calculate “esteem” and “schcomp”. The variable “cooperat” represents how cooperative

the students like to learn; “compete” represents the extent to which they like to compete

with other students in the class; “alone” measures whether they like to work alone and

study alone.

The final variable, “anxiety”, is from a foreign language anxiety scale. The score for

each of these variables ranges from one to five;anything outside that range will

represent an error.

For data cleaning purposes, you could summarize the variables as follows:

Variable Defined Parameter

age 18 years or older

gender 0 = male, 1= female

schoolan 0 = no, 1 = yes

studyhab 0.0 to 1.0 range

PSYC 6800: Applied Psychology Research Methods

gpa 0.0 to 4.0 point scale

expect 0 to 100

esteem, schcomp, cooperat,

compete, alone,anxiety

1 to 5 scale

Using Excel In Cleaning A Dataset

There are several methods to cleaning a dataset in Excel and you can locate many

examples of them through online sources. This week you will examine two forms of

inaccurate data (missing data and data errors), learn how to locate them, and decide

how to address them.

Sort data. Sorting data using Excel can help locate missing scores and extreme scores

that require investigating as a potential error.

1. Highlight the entire dataset. This can be done by clicking on the triangle at the

top left corner of the worksheet, or by clicking and dragging over the entire

dataset (including variable names).

PSYC 6800: Applied Psychology Research Methods

2. Prepare to use the Sort function. Under the Data tab ribbon, locate the Sort &

Filter section. Click on the Sort button. The Sort wizard will open.

3. Select the variable you wish to examine. In this case, you will start with “age”. In

the Column area, click on the “sort by” pull down box and select “age”.

PSYC 6800: Applied Psychology Research Methods

4. Check that the Sort On box indicates “Cell Values” and the Order area sorts by

“Smallest to Largest”. Be sure the “My data has headers” box is checked. Click OK.

Examine the variable column. You should see your entire dataset re-ordered so that

each participant’s data are kept intact, but the participants’ data are sorted in ascending

order within the “age” column. Examine the column at the beginning of the worksheet

and the end of the worksheet for a) missing data, b) extreme or unusual scores. In this

case, there is an age score of 3 for the participant whose ID is 33.

PSYC 6800: Applied Psychology Research Methods

At the bottom of the worksheet, you will notice participant ID 129 has an age of 71, and

ID 191 has an age score of <blank>. That meant that for whatever reason, that person

did not have a number entered for that variable.

Note. Missing data can be identified by the “#NULL!” designation, a period instead of

valid data (e.g., “ . “), a blank cell, or other similar designations.

In this dataset, the participant whose ID is 129 is missing her age data.

PSYC 6800: Applied Psychology Research Methods

Address missing data and errors. Ideally, researchers want to be protectionists; they

want to have a complete dataset. An occasional missing cell in the data might not affect

the results, but as the number of missing data increase, results will become less and

less reliable. When you are in control of collecting the data, it is important that you try to

avoid missing data.

If you find missing data, try to find out what that value should have been. If the data

were not collected by you, or if they were created by you but you could not track down

the original responses by ID, then it is unlikely you can find the actual scores. You have

to leave it as missing and perhaps note in your discussion that it is a limitation to the

findings.

Extreme or unusual scores could indicate an incorrect number-- a number that does not

seem to fit in the realm of possibility for the type of numbers for that variable. This is

when being familiar with the dataset and how it was collected can benefit you. You

noted in the “age” column a minimum score of 3 and a maximum score of 71. If you

were familiar with the university population that was sampled, you would know there

were students in the university population in their 50s and 60s and even 70s. Because

of this knowledge of the setting, you can assume that an age of 71 is accurate.

PSYC 6800: Applied Psychology Research Methods

However, because of the population that was sampled, it is highly unrealistic to accept

that an age of 3 years for an undergraduate participant is accurate. Perhaps the person

entering the information meant to code 30 or 33 or 23. Errors should be removed from

the dataset. In this situation, an age of 3 years could reduce the calculated mean (i. e.

average) for age, which would not be representative of your sample. If you could locate

the raw data that was used for this case, you could replace it with the correct

information.

Replace errors. If the correct information cannot be located, the next step is to remove

the erroneous data and replace it with—not a 0, because a 0 would be perceived as a

valid number, creating even more inaccurate results. It will actually make your

calculated mean smaller than when the 3 was in place.

Instead of a 0 or any other number, you will replace the erroneous data to denote a

missing value. Click on the cell and use the Delete button or Backspace button on your

keyboard to erase the number. Do NOT use the space bar to enter a blank space; Excel

will treat that as a non-numeric value and will give you an error message:

PSYC 6800: Applied Psychology Research Methods

Let’s try another one. You should follow along in Excel to see what you find.

1. Clean up “studyhab” variable: Select the entire dataset; Sort by studyhab, Sort

On Cell Values, with Order Smallest to Largest.

PSYC 6800: Applied Psychology Research Methods

2. Click OK. Examine the extreme scores at the beginning of the worksheet…

… and the end of the worksheet.

3. Did you find any missing scores? Did you spot the extreme score right away?

What was the ID for the erroneous score? See below to find out.

PSYC 6800: Applied Psychology Research Methods

4. Examining the codebook and the variable summary can help you determine

when an extreme score is unreasonable and should be considered erroneous.

5. Because you cannot determine what the actual score was, you must change

erroneous scores to missing (i.e., delete the erroneous score so the cell is left

blank).

6. Keep working on the remaining variables in the Example Dataset and see what

you find. Clean the erroneous scores by deleting the value, which denotes the

value as missing.

7. When you prepare to clean a dataset prior to conducting analyses, you might

notice there can be several dozen, if not hundreds, of variables. If you wish to be

efficient, clean only those data you will include in your analyses and save the

dataset using a filename that identifies it as a partially cleaned dataset.