PYTHR/unit-6-pandas/09-pandas-join/09-pandas-join.md

Pandas Joining

---

Learning Objectives

After this lesson, you will be able to:

• Concatenate objects with .append() and .concat().
• Combine objects with .join() and .merge().
• Combine timeseries objects with .merge_ordered().
• Traditionally, this functionality is performed in a relational database, such as SQL.
• With Pandas, you'll be able to perform the same operations in Python! The backend is numpy, a powerful linear algebra library which helps keep things speedy.

---

To the Notebook!

We actually will commence this lesson directly in the Jupyter Notebook, pandas-join.ipynb, to walk through the what, why, and how all at once.

Here we have slides reviewing the key concepts.

Teaching Tip:

• This is an intro to the notebook - use it as an overview to cover the concepts. Budget time accordingly.

---

What is Joining?

• Joining is the process of taking a single dataframe and combining it with another dataframe.
• Traditionally, this would be done with SQL.
  • SQL is database designed and optimized to distribute data across many tables.

Teaching Tip:

• Feel free to talk about databases and tables here, maybe even normal forms if you're comfortable

---

Why Join?

• Joining is important because:
  • It allows us to reduce the size of a database.
  • It allows us to increase the speed at which data is queried and returned.
  • It allows us to reduce the redundancy of the data stored in the database.
• Joining is fundamental to proper data architecture, and we'll get to do it in Pandas!

Teaching Tip:

• We could have made this into a speaker note, but it's helpful to get it out there so everybody's on the same page

---

Why Use Pandas for Joining Then?

• Pandas is based upon numpy, a linear algebra library.
• Using it for joins makes sense - the algorithms are optimized and fast.
• This allows allows us to use 'python only' - avoiding integrations to SQL.
• This makes data analysis faster as we don't need to switch tools.
• Longer term, code may be delegated to more specific tools (SQL, Spark, etc.).

Teaching Tips:

• There's no silver bullet for tooling; each use case is different.
• Remind students that pandas run on a machine and thus is limited by the machine.
• SQL Server is designed (for enterprise cases) to be distributed, have backup plans, etc.
• Be mindful to not give the students the idea that 'pandas can do everything'.
• Emphasize that this is good for prototyping and smaller, temporary jobs and analysis.

---

What Does a SQL Join Look Like?

• A SQL join looks like the above.
• We can specify:
  • The tables (dataframes) to be joined to each other.
  • How the columns (keys) are related to each other in the join.
  • We can use this logic (referred to as relational algebra) to:
    • Filter out information.
    • Make one-to-many or even many-to-many joins.
• We'll be using Pandas, so our syntax will look different than above.

Teaching Tips:

• We won't be going overboard with join algebra.
• Encourage students to study this on their own.
• Use SqLite3 as a reference; this is free for python and uses largely the same syntax as SQL Server (ANSI SQL)

---

What Does a Pandas Join Look Like?

pd.merge(df1, df2, how='left', left_index=True, right_index=True, suffixes=('_df1', '_df2'))

index	letter_df1	number_df1	letter_df2	number_df2
0	a	1	e	5.0
1	b	2	f	6.0
2	c	3	NaN	NaN
3	d	4	NaN	NaN

Teaching Tips:

• We'll be covering this exact example during the exercise.
• Note that we don't have the source tables included here.
  • This is intentional to reduce overall clutter and chatter.
  • Instead, focus on the syntax to give students an idea for what a join looks like.

---

Notes on Differences

• SQL uses JOIN. Pandas has two semi-equivalent functions:
  • pd.join - used for joining dataframes on their indices only
  • pd.merge - used for joining dataframes on any column you want
• Since pd.merge is more powerful and generalizes better, we'll focus on pd.merge
• SQL uses UNION. Pandas, again, has two semi-equivalent functions:
  • pd.append - stacks dataframes on top of each other
  • pd.concat - stacks dataframes on top of or next to each other
• Since pd.concat is more powerful and generalizes better, we'll focus on pd.concat

Teaching Tips:

• Note that joining operations creates dataframes that mesh into each other (use your hands and fingers to show this operation if you can).
• Note that concat and append create dataframes that stack on/next to each other (close your fingers and show this operation if you can).
• You'll probably get questions about the differences between the operations:
  • Note that mesh into is somewhat vague, since you can have 1:m, m:1, 1:1, and m:m join types.
  • Pandas pd.merge can handle these as well, and we touch on that briefly in the exercise.

---

Additional Resources

• Pandas documentation
• DataSchool 30-video series (by a former GA instructor!)