Combining DataFrames

1. Stacking DataFrames with pd.concat()

The simplest way to combine DataFrames is to stack them on top of each other or side-by-side. This is called concatenation, and it's done using pd.concat(). Think of it as simply "gluing" DataFrames together along an axis.

• Vertical Stacking (axis=0): This is the most common use case. You stack one DataFrame on top of another. This is useful when you have data with the same columns but from different sources (e.g., sales data for January in one file, February in another).

  import pandas as pd
  
  df1 = pd.DataFrame({'A': ['A0', 'A1'], 'B': ['B0', 'B1']})
  df2 = pd.DataFrame({'A': ['A2', 'A3'], 'B': ['B2', 'B3']})
  
  # Stack df2 below df1
  result = pd.concat([df1, df2])
  print(result)
  #    A   B
  # 0  A0  B0
  # 1  A1  B1
  # 0  A2  B2
  # 1  A3  B3
  

  Notice the index is repeated. To create a new, continuous index, you can use ignore_index=True.

  result_reindexed = pd.concat([df1, df2], ignore_index=True)
  print(result_reindexed)
  #    A   B
  # 0  A0  B0
  # 1  A1  B1
  # 2  A2  B2
  # 3  A3  B3
  

• Horizontal Stacking (axis=1): This stacks DataFrames side-by-side. It's useful when you have DataFrames with the same index but different columns.

Now, let's watch a video that demonstrates pd.concat() and introduces how it handles non-matching columns.

As the video shows, concat is straightforward but powerful for simple stacking operations. However, for more complex combinations based on shared values, we need a more sophisticated tool.

2. Database-Style Joins with pd.merge()

This is the most flexible and powerful method for combining DataFrames. If you have experience with SQL, you'll find pd.merge() very familiar. It combines DataFrames based on values in common columns, known as keys.

The how parameter in pd.merge() lets you specify the type of join, which determines how to handle keys that don't appear in both DataFrames. There are four main types:

1. Inner Join (how='inner'): This is the default. It returns only the rows where the key exists in both DataFrames. It's the intersection of the keys.
2. Outer Join (how='outer'): Returns all rows from both DataFrames. If a row from one DataFrame doesn't have a matching key in the other, the columns from the other DataFrame will be filled with NaN. It's the union of the keys.
3. Left Join (how='left'): Returns all rows from the left DataFrame and the matched rows from the right DataFrame. If a key from the left DataFrame has no match in the right, the right-side columns will be NaN.
4. Right Join (how='right'): The opposite of a left join. Returns all rows from the right DataFrame and matched rows from the left.

The video below gives an excellent visual and coded explanation of these join types.

Key Parameters for pd.merge()

While the video covers the basics, pd.merge() has several important parameters that give you fine-grained control.

The most critical parameters are:

• on: Specifies the column name(s) to join on. It's best practice to always specify this to avoid ambiguity.
• left_on & right_on: Used when the key columns have different names in the left and right DataFrames.
• suffixes: A tuple of strings to append to overlapping column names (that are not keys) to distinguish their origin, e.g., ('_left', '_right').

employees = pd.DataFrame({
    'emp_id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'David'],
    'dept_id': [101, 102, 101, 103]
})

departments = pd.DataFrame({
    'id': [101, 102],
    'dept_name': ['Sales', 'Engineering']
})

3. Index-Based Joining with .join()

Finally, Pandas provides the .join() method. It is a convenience method that joins DataFrames on their indices. Under the hood, it's just a specialized call to pd.merge().

Key characteristics of .join():

• It's a DataFrame method (df1.join(df2)), not a top-level function.
• By default, it performs a left join on the indices.
• It can be less intuitive than merge if you're trying to join on columns, as it requires you to set the columns as the index first.

Here's a typical use case for .join():

left = pd.DataFrame({'A': ['A0', 'A1']},
                    index=['K0', 'K1'])

right = pd.DataFrame({'B': ['B0', 'B1']},
                     index=['K0', 'K2'])

# Performs a left join on the index
result = left.join(right)
print(result)
#      A    B
# K0  A0   B0
# K1  A1  NaN

Because 'K1' from the left index doesn't exist in the right, the value for column 'B' is NaN. Because 'K2' from the right index isn't in the left, that row is dropped entirely (due to the left join).

Conclusion

You've now learned the three essential methods for combining data in Pandas, completing your foundational toolkit for data manipulation.

Key Takeaways:

• pd.concat(): Best for simple "stacking" of DataFrames, either vertically (rows) or horizontally (columns).
• pd.merge(): The most versatile and powerful tool for combining data based on common values in columns, similar to database joins. Use the how parameter (inner, outer, left, right) to control the logic.
• .join(): A convenient method for performing index-on-index joins. It's essentially a shortcut for a specific type of pd.merge().

Preview of the Next Module:

Congratulations on completing the "Data Manipulation with Pandas" module! You are now proficient in loading, cleaning, filtering, grouping, and combining data.

What's next? Raw numbers and tables can only tell you so much. The next step in our journey is Data Visualization and Preparation. In the upcoming module, we'll start by learning how to create plots to visually explore your data and uncover patterns, trends, and outliers. Our first lesson will cover how to create basic plots (line, scatter, histogram) using Matplotlib.