This course will introduce the learner to the basics of the python programming environment, including fundamental python programming techniques such as lambdas, reading and manipulating csv files, and the numpy library. The course will introduce data manipulation and cleaning techniques using the popular python pandas data science library and introduce the abstraction of the Series and DataFrame as the central data structures for data analysis, along with tutorials on how to use functions such as groupby, merge, and pivot tables effectively. By the end of this course, students will be able to take tabular data, clean it, manipulate it, and run basic inferential statistical analyses. This course should be taken before any of the other Applied Data Science with Python courses: Applied Plotting, Charting & Data Representation in Python, Applied Machine Learning in Python, Applied Text Mining in Python, Applied Social Network Analysis in Python.
Merging Dataframes
Loading...
En provenance du cours de University of Michigan
Introduction to Data Science in Python
6962 notes
À partir de la leçon
Week 3
In this week you'll deepen your understanding of the python pandas library by learning how to merge DataFrames, generate summary tables, group data into logical pieces, and manipulate dates. We'll also refresh your understanding of scales of data, and discuss issues with creating metrics for analysis. The week ends with a more significant programming assignment.
Rencontrer les enseignants
Christopher Brooks
Last week, we were introduced to the pandas data manipulation and
analysis library.
We saw that there is really two-core data structures which are very similar,
the one-dimensional series object and the two-dimensional DataFrame object.
Querying these two data structures is done in a few different ways,
such as using the iloc or loc attributes for row-based querying, or
using the square brackets on the object itself for column-based querying.
Most importantly, we saw that one can query the DataFrame and
Series objects through Boolean masking.
And Boolean masking is a powerful filtering method which
allows us to use broadcasting to determine what data should be kept in our analysis.
In this week's lecture,
we're going to go into more detail on how to manipulate DataFrame, in particular.
We're going to explore how to reduce and process data using GroupBy and Apply,
and how to join data sets from multiple files together into one.
We'll also talk about some features that pandas has which are useful for
both traditional statistical analyses and machine learning.
Let's start with how to merge data sets.
We've already seen how we add new data to an existing DataFrame.
We simply use the square bracket operator with the new column name,
and the data is added as long as an index is shared.
If there is no shared index and a scalar value is passed in,
which remember a scalar value is just a single value like an integer or a string.
The new value column is added with the scalar value as the default value.
What if we wanted to assign a different value for every row?
Well, it gets trickier.
If we could hardcode the values into a list, then pandas will unpack them and
assign them to the rows.
But if the list we have isn't long enough, then we can't do this,
since Pandas doesn't know where the missing values should go.
Here's an example.
We used the DataFrame of store purchases from one of our previous lectures,
where the index is a list of stores and the columns store purchase data.
If we want to add some new column called Date to the DataFrame, that's fine.
We just use the square bracket operator directly on the DataFrame,
as long as the column is as long as the rest of the records.
If we want to add some new field,
may be a delivery flag, that's easy too since it's a scalar value.
The problem comes in when we have only a few items to add.
In order for this to work, we have to supply pandas the list
which is long enough for the DataFrame, so that each row could be populated.
This means that we have to input none values ourselves.
If each of our rows has a unique index,
then we could just assign the new column identifier to the series.
For instance, if we reset the index in this example so the DataFrame is labeled
from 1 through 2, then we create a new series with these labels, we can apply it.
And the results we get are as we expected.
The nice aspect of this approach is that we could just ignore the items that we
don't know about, and pandas will put missing values in for us.
So this is a really nice way to do it.
More commonly, we want to join two larger DataFrames together, and
this is a bit more complex.
Before we jump into the code, we need to address a little relational theory, and
to get some language conventions down.
This is a Venn Diagram.
A Venn Diagram is traditionally used to show set membership.
For example, the circle on the left is the population of students at a university.
The circle on the right is the population of staff at a university.
And the overlapping region in the middle are all of those students who
are also staff.
Maybe these students run tutorials for a course, or grade assignments,
or engage in running research experiments.
We could think of these two populations as indices in separate DataFrames,
maybe with the label of Person Name.
When we want to join the DataFrames together, we have some choices to make.
First what if we want a list of all the people regardless of
whether they're staff or student, and all of the information we can get on them?
In database terminology, this is called a full outer join.
And in set theory, it's called a union.
In the Venn diagram, it represents everyone in any circle.
It's quite possible though that we only want those people who we have maximum
information for, those people who are both staff and students.
In database terminology, this is called an inner join.
Or in set theory, the intersection.
And this is represented in the Venn diagram as the overlapping parts
of each circle.
Okay, so let's see an example of how we would do this in pandas,
where we would use the merge function.
First we create two DataFrames, staff and students.
There's some overlap in these DataFrames, in that James and
Sally are both students and staff, but Mike and Kelly are not.
Importantly, both DataFrames are indexed along the value we want to merge them on,
which is called Name.
If we want the union of these,
we would call merge passing in the DataFrame on the left and the DataFrame on
the right, and telling merge that we want it to use an outer join.
We tell merge that we want to use the left and right indices as the joining columns.
We see in the resulting DataFrame that everyone is listed.
And since Mike does not have a role, and
John does not have a school, those cells are listed as missing values.
If we wanted to get the intersection, that is,
just those students who are also staff, we could set the how attribute to inner.
And we set the resulting DataFrame has only James and Sally in it.
Now there are two other common use cases when merging DataFrames.
Both are examples of what we would call set addition.
The first is when we would want to get a list of all staff regardless of whether
they were students or not.
But if they were students, we would want to get their student details as well.
To do this we would use a left join.
You could probably guess what comes next.
We want a list of all of the students and their roles if they were also staff.
To do this we would do a right join.
The merge method has a couple of other interesting parameters.
First, you don't need to use indices to join on, you can use columns as well.
Here's an example.
So what happens when we have conflicts between the DataFrames?
Let's take a look by creating new staff and
student DataFrames that have a location information added to them.
In the staff DataFrame,
this is an office location where we can find the staff person.
And we can see the Director of HR is on State Street,
while the two students are on Washington Avenue.
But for the student DataFrame,
the location information is actually their home address.
The merge function preserves this information, but appends an _x or
_y to help differentiate between which index went with which column of data.
The _x is always the left DataFrame information, and
the _ y is always the right DataFrame information.
And you could control the names of _x and
_y with additional parameters if you want to.
Now you try it.
Here's a DataFrame of products and invoices.
The product has an identifier and a sticker price.
The invoice lists the people, products, identifiers, and quantity.
Assuming that we want to generate totals, how do we join these two DataFrames
together so that we have one which lists all of the information we need?
Before we leave merging of DataFrames, let's talk about multi-indexing and
multiple columns.
It's quite possible that the first name for students and staff might overlap, but
the last name might not.
In this case, we use a list of the multiple columns that should be used to
join keys on the left_on and right_on parameters.
As you see here, James Wilde and James Hammond don't match on both keys.
So the inner join doesn't include these individuals in the output, and
only Sally Brooks is retained.
That's it for merging of DataFrames.
pandas has a lot more options in the area, but I think the merge function is perhaps
the most easiest to understand and the most flexible.
In the next section, we're going to talk about how to write idiomatic pandas and
delve a bit deeper into more advanced functions for manipulating DataFrames.
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.
