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.
Merging Data
Loading...
來自 Johns Hopkins University 的課程
Getting and Cleaning Data
5025 個評分
從本節課中
Week 3
Welcome to Week 3 of Getting and Cleaning Data! This week the lectures will focus on organizing, merging and managing the data you have collected using the lectures from Weeks 1 and 2.
與講師見面
Jeff Leek, PhDAssociate Professor, Biostatistics
Bloomberg School of Public Health
Roger D. Peng, PhDAssociate Professor, Biostatistics
Bloomberg School of Public Health
Brian Caffo, PhDProfessor, Biostatistics
Bloomberg School of Public Health
Sometimes you'll load in more than one dataset into R
and you'll want to be able to merge the datasets together.
And usually what you'll want to do is match those datasets based on an ID.
This is very similar to the idea of having a
linked set of tables and a database like My SQL.
So, what we're going to be talking about
here is data from this peer review experiment.
And so, this is an experiment that I was actually an author on.
And so, we collected data on people
who submitted answers to questions like SAT questions.
And then people who reviewed those answers and
tried to decide whether they were right or not.
And this was a model system for how
the peer review system works in scientific studies.
So, what we can do is download the peer review data.
The peer review data is actually available from my Dropbox account.
And so, we can get the reviews and the solutions from this dataset.
And so, we download these two files.
And so, there are two data frames, one corresponding to all
the SAT problems that were solved by people in the study.
And one by all the people, of course, bunch
of all the times that those problems were reviewed.
So we read them both into two data frames.
We can look at the reviews data frame, which has these variable names.
So you can see, one of the variable names for this data frame is
solution id, and that corresponds to the id that appears in the solutions dataset.
You can see the solutions dataset also has a problem id and a subject id.
Those will be matched to other datasets that we're
not going to be talking about in this particular example.
So, the first thing that we might want to use
is, the merge command, to merge the datasets together.
So, again, if we look at the names of these two variables.
We can use the solution id and the reviews variable or dataset
and the id variable in the solutions dataset to merge these datasets together.
So, the important parameters to merge are x and y, so those are two data frames.
And then, you can use either by, or by.x or by.y
to tell a merge which of the columns it should merge by.
By default it merges by all of the columns that have the common name.
So, for example, in this dataset, it would try to
merge by id, start, stop, time left, and so forth.
Because those variables also appear in solutions because
id is here, start, stop, and time left.
Even though those variables might not necessarily mean the same thing.
So, what we're going to do, instead, is tell
merge which of the variables it should merge on.
So, it merges the reviews and the solutions for the x data frame in
this case is the reviews data frame, we're going to merge on the solution id.
And in the solutions data frame, we're just going to merge based on the id.
Here I'm going to tell it, all equals true.
Which means if, if there's a value that appears
in one but not in the other, it should include
another row but with na values for the missing
values that are, don't appear in the other data frame.
And so, we can look at the top of this did new merge dataset and
you can see now that the solution id has now taken the place of the id.
And it's ordered by that variable and then this id variable
is the id variable that's left over in the reviews datasets.
So, this is the reviews main id that's appears here in this dataset.
So you can see that this has merged
the two datasets together, based on their common id.
So that then you can analyze it as one dataset.
The default again is to merge based on all column names common column names.
So, if you do an intersection of the names of the solutions data
frame and the names of the reviews data frame, you get these four variables.
And so, if you try to merge without telling it what to merge on the
basis of, it will try to merge on the basis of all those four variables.
So, what ends up happening is, the id
variable will match up sometimes between the two datasets.
But the start and the stop times won't necessarily match up.
So, what it ends up doing is it just
creates a data frame that's larger that applies multiple rows.
One for each row of reviews and each row of solutions.
You can also use join in the plyr package to join datasets together.
It's a bit faster than merge but, is a bit less full featured.
In other words, in other words, it only can merge on
the basis of common row er, common names between the two datasets.
And so, you couldn't merge the two datasets we talked about just a
minute ago because it, we need to merge by solution id and id.
And join will only merge on the basis of id even though it's faster.
So, here's an example.
I created two made up data frames with a common
id identifier, but I've sampled them, so they're in different orders.
And so, what I can do is, I can join the two datasets together.
It will join them by id, and then I can use
the arrange command to arrange them in increasing order by id.
And so you see I've arranged the two datasets together in that way.
You can play around with these commands to see how you could join datasets together.
The nice thing about the plyer package, and the reason why I bring it up is
that if you have multiple data frames it's
relatively challenging to do it with the merge command.
But if they have a common id, it's very
straightforward to do it with the join all command.
So, what I do is I create my three data frames, in this case, and
I put them all on a list, so one big data list of data frames.
So it's a data frame list with three data frames in it.
And then I just use the command join all, which merges
all of the different datasets on the basis of the common variables.
In this case, id.
So, that's a nice way to merge together multiple datasets very quickly.
The r da, data merging page is very good,
as is the plyr page for merging datasets together.
Before you start doing this intensively, it's worth paying
attention to, whether you're performing different kinds of joins.
Whether you're doing a sort of left joins, or right joins.
And the best way to learn more about that is to go read about joins in databases.
For example, here, this is the information about joining for SQL database joins.
