Editing Text Variables

Loading...

En provenance du cours de Johns Hopkins University

Getting and Cleaning Data

5014 notes

Johns Hopkins University

Getting and Cleaning Data

5014 notes

Cours 3 sur 10 dans Specialization Data Science

Before you can work with data you have to get some. This course will cover the basic ways that data can be obtained. The course will cover obtaining data from the web, from APIs, from databases and from colleagues in various formats. It will also cover the basics of data cleaning and how to make data “tidy”. Tidy data dramatically speed downstream data analysis tasks. The course will also cover the components of a complete data set including raw data, processing instructions, codebooks, and processed data. The course will cover the basics needed for collecting, cleaning, and sharing data.

À partir de la leçon

Week 4

Welcome to Week 4 of Getting and Cleaning Data! This week we finish up with lectures on text and date manipulation in R. In this final week we will also focus on peer grading of Course Projects.

Editing Text Variables10:45

Regular Expressions I5:16

Regular Expressions II8:00

Working with Dates6:02

Data Resources3:32

Rencontrer les enseignants

Jeff Leek, PhD
Associate Professor, Biostatistics
Bloomberg School of Public Health
Roger D. Peng, PhD
Associate Professor, Biostatistics
Bloomberg School of Public Health
Brian Caffo, PhD
Professor, Biostatistics
Bloomberg School of Public Health

A very common data cleaning step is to have text variables that are in sort of

a nasty format or have extra spaces or

periods or underscores that you need to remove.

And so this lecture's gonna be about how do you manipulate those texts

programatically in order to get nice, decent variable names but

also texts in the actual data set itself.

So again, we're gonna go back to this Baltimore fixed camera data because I

can't stop thinking about all the speeding tickets that I get.

So again, the first thing that we're gonna do is download the data set from the web.

So we're gonna check and see if this data directory exists.

And if it doesn't, we're gonna create it.

Then we're gonna download the data from this URL and

we're gonna read it into R with read.csv.

And so first thing that we can do is look at the names of this camera data.

And so this is the column names.

And you can see, for example, one of them is this variable crossStreet,

and it has a capitalized S in it.

This seems like not a big deal, but every time that you're gonna type that into R,

you might see you have this extra capital letter and you might miss it.

And so, R might give you an error.

So one thing that you might wanna do is make all of the letters lowercase.

And so one way to do that with R, is to just use tolower.

So you can make the names,

all be only lowercase letters by using the tolower command.

There's also a toupper command, if you need that,

in order to make the letters all uppercase.

Another thing that you might wanna do is separate out variables

that have values that are separated by periods.

So if we go back to this page you can see for

example that this location variable here, it's separated with a dot.

And that often happens when you have variable names loaded into R from files.

So what we can use is something called a string split command.

So it's S-T-R-S P-L-I-T.

And so then we can pass at the names of our data set and

we can tell it to split on periods.

So here I have to use this escape character because the period is

a reserved character.

And so what I get if I look at this data set, that comes out, split names,

which is just a list, you can look at say the fifth element and

that's the element corresponding to the intersection variable.

And you can see that nothing happened there.

It's just one variable because there were no periods in that variable name.

But if you look at the sixth element of that list,

you see that the location variable has now been split into two components,

the location in the one and the dot has been removed.

So we have been able to split that variable out.

So one thing that you might want to be able to do then is go through and sub set

out, and just take the part of variable name that doesn't have the dot in it.

I want to give you a very quick aside on lists because

the functions that we're gonna talk about in a minute depend on that.

And we want to make sure we remember how they work.

So here I'm creating a variable,

my list, and I'm doing that by asking it letters, numbers, and a matrix.

And so if I look at the list I can see that it has these three different

components to it, including some name components.

So I can select out the first element in the list and it returns the letters.

I can actually subset by the named variables.

Or I can actually subset out just the vector by

just taking the first element of that list.

So one thing that I might want to be able to do is actually remove all the periods,

and then only get the first part of the variable name.

So you can see if I took the sixth element of this list.

And remember, it had two parts to it.

It had the location and then it also had the one that came after the dot.

So if I just take the first element of this list element,

then I get just the location value.

And so what I can do is I can write a function called firstElement.

And firstElement, will just take the first value that comes out of that list.

And so, I can use sapply to split the name.

Apply to the split name which is a list.

I can apply the first element function which we'll go through and

then to each element of that list, it will take the first element out.

In this case, it'll take out just the variable names.

And, when you look at location,

you'll see that it has removed the period from that location.

So that's one way that you can remove, say, periods from names in data frames.

I'm gonna go back to this peer review experiment

data to show you some other ways that you can manipulate,

sort of text variables in order to get the answers that you need.

So again I'm gonna download the data from the Internet and

I'm gonna store it into two data frames.

One is the reviews data frame and one is the solutions data frame.

Where reviews is the set of problems that have been reviewed by peers in this

experiment and

solutions is the set of the SAT questions that have been submitted by people.

So I can see the data set again.

Looks like this.

And so a couple of things that I might want to be able to do is substitute out,

say characters.

So in this case I can see that a couple of the variable names are solution ID and

reviewer ID, and they have these underscores in them.

And so I might wanna remove the underscores from those variables,

also from this variable time left over here.

And so one thing that I can do, is I can use the sub command.

I can say every time that you find an underscore,

replace it with nothing in the names of reviews and what you end up with is

variable names where all of the underscores have been removed.

I can also use a g sub to replace multiple instances of a particular character.

So for example, suppose I have a variable name that has multiple underscores in it.

If I use sub, it will just replace the first underscore.

So, if I say Substitute underscore with nothing in testName.

It'll find the first underscore, replace it, but

leave the next few underscores alone.

If I use gsub, on the other hand, it would replace all of the underscores, so

you just get one variable like this.

You can use that to replace underscores and variable names, so that you have nice,

clean variable names.

Or you can also have this happen in actual,

the text values that you have in your data set.

The next thing that I'm gonna cover is searching for

specific values in variable names, or in variables, themselves.

So here, I'm gonna go back to the camera data, the fixed camera data.

And I'm gonna look at the intersection variable.

So one thing that I might want to be able to do is find all of the intersections

that include the Alameda as one of the roads.

So what I'm gonna do is I'm gonna use the grep function.

The grep will take as input a search string that you wanna look for,

the Alameda, and it will look through this variable and

find all of the instances in that vector where the Alameda appears.

So it appears in the 4th, 5th and 36th element of the intersection variable.

The other thing that I can use is the grepl command with an l

at the end of the grep.

And so, what that will do is it will look for this version Alameda.

It will look for it in this intersection variable.

And it will return a vector that's true whenever Alameda appears and

false whenever Alameda doesn't appear.

And so in this case you can see that in that three of the times the Alameda

appears, and so

if I make a table of the true and false values, it's true three of the times.

So the other thing that you can do is you can sort of subset.

So, for example,

suppose I wanna find all the cases where Alameda appears in the intersection.

And then I want to say,

if Alameda doesn't appear, then I want to subset to only that subset of the data.

I can do that using this grepl command.

So you can use that to subset your data based on certain searches

that you wanna be able to find.

A little bit more about grep you can also use value=TRUE as one of the parameters

sent to grep and what will do is instead of telling you which of the elements it

that Alameda appears in, it will actually return the values where Alameda appears.

So you can see these are the three intersections where the Alameda appears in

the camera data.

The other thing that you can do is you can look for values that don't appear.

So say, for example, JeffStreet does not appear in the intersection data, and so

it will return integer (0) when that value does not appear.

If you look at the link of that it actually turns out to be zero.

So, that's a nice way to check.

So, suppose you want to check and see if a particular value appears in a vector.

You can grep for that value, and then look at the length of that vector,

and if it's equal to zero, then you can say that that value doesn't appear.

A couple of other useful string functions appear both in the string package and

in the base package.

So the NCHAR will tell you

the number of characters that appear in a particular string.

So there are twelve characters in my name here.

The other thing that you can do is you can take out part of the string.

So in this case I wanna take this string,

and I wanna find the first through the seventh letters.

In that case, that's just my first name.

Another thing that you can do is you can paste two strings together.

So, suppose I have these two strings, and I apply the paste function to them.

I get one string that's separated by a space.

You can actually set the separation with the sep argument, S-E-P.

A very useful version when you're doing data analysis is often you just wanna

paste things together with no space in between.

And the way that you do that is there's actually a separate function called

paste0, where it pastes things together without any space in between.

In the stringer package there's a very nice function called string trim, and

what it'll do is it'll trim off any excess space that appears at the end of a string.

So you get a nice string without excess space at the beginning or the end.

So a couple of important points about text and data sets.

Whenever possible you'd like to have all lower case because missing caps can lead

to very difficult to find bugs in data analysis.

You'd like them to be descriptive, so

in other words, you'd like it to say diagnosis as opposed to Dx.

You'd like the variable names not to be duplicated and not to have underscores or

dots or white spaces, which we show you how to remove.

Variables with character values should usually be made into

factor variables as we've talked about, creating factor variables.

Although this depends a little bit on the application.

They should also be descriptive, so for example, if you have a true and

false variable, they should be labeled as true and false instead of zero and one or

one and zero.

And male and female should be labeled as such, as opposed to M verses F,

and zero verses one.

It just makes it easier to see what's going on in the data set.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play