Advanced Data Manipulation Techniques

Dive into advanced data manipulation techniques in Pandas, including merging datasets, joining, and handling time series data for sophisticated data analysis.

By Kevin McAleer,    4 Minutes


Cover photo

Overview

This lesson focuses on Advanced Data Manipulation Techniques with Pandas. Building on the basics, we now venture into more sophisticated techniques like merging datasets, joining, and handling time series data, which are crucial for complex data analysis tasks.


Merging Datasets

Concatenation

Concatenate Pandas objects along a particular axis:

import pandas as pd

monday = pd.DataFrame({'robot': ['SMARS', 'Quad'], 'distance_m': [12, 8]})
tuesday = pd.DataFrame({'robot': ['SMARS', 'BurgerBot'], 'distance_m': [15, 20]})

# Stack the rows of both tables
all_days = pd.concat([monday, tuesday], ignore_index=True)

ignore_index=True gives the result a fresh index of 0, 1, 2, 3 instead of repeating 0, 1, 0, 1.


Merge

Merge two datasets that share a column, like a database join:

specs = pd.DataFrame({'robot': ['SMARS', 'Quad', 'BurgerBot'], 'wheels': [4, 0, 2]})

merged = pd.merge(all_days, specs, on='robot')

Each row of all_days gets the matching wheels value from specs. Use how='left' to keep rows that have no match.


Joining

join() matches a column in one table to the index of another:

specs_by_robot = specs.set_index('robot')

joined = all_days.join(specs_by_robot, on='robot')

This gives the same result as the merge above. merge() is usually easier when both tables have the key as a normal column.


Handling Time Series Data

Date and Time in Pandas

Pandas is robust in handling time series data:

log = pd.DataFrame({
    'date': ['2024-01-01', '2024-01-03', '2024-01-09', '2024-01-12'],
    'distance_m': [12, 15, 9, 20],
})

# Turn the text into real dates
log['date'] = pd.to_datetime(log['date'])

Time Series Functions

Utilize functions specific to time series:

# Total distance per week
weekly = log.resample('W', on='date').sum()
print(weekly)

resample() groups rows into time periods, here weeks ('W'). Use 'D' for days or 'MS' for months. If the dates are the index, you can leave out on=.


Top 10 tips for Data Scientists

Here are 10 essential tips for using pandas in data science:

  1. Use Vectorized Operations: Leverage pandas’ vectorized operations for efficient data manipulation, rather than iterating over DataFrame rows.

  2. Master Indexing and Selecting Data: Understand how to use loc[], iloc[], and conditional selection to effectively extract and filter data.

  3. Handling Missing Data: Familiarize yourself with methods like fillna(), dropna(), and interpolate() to handle missing data appropriately.

  4. Use groupby() for Aggregation: Grouping data and performing aggregate functions is key in data analysis; master groupby() for these tasks.

  5. Efficiently Merge and Concatenate Data: Learn to use merge(), join(), and concat() for combining multiple datasets effectively.

  6. Data Type Conversion: Understand how to change column data types using astype() for optimal memory usage and correct data representation.

  7. Use apply() for Custom Functions: When built-in functions don’t suffice, use apply() to apply a custom function to DataFrame columns or rows.

  8. Date and Time Handling: Get comfortable with pandas’ time series tools, especially if you’re dealing with time-stamped data.

  9. Pivot Tables and Crosstabs: Learn to reshape data and perform grouped summaries using pivot_table() and crosstab().

  10. Optimize Performance and Memory Usage: Use the category data type for columns with only a few different values, and try query() for readable filtering.

These tips can significantly enhance your efficiency and effectiveness in data manipulation and analysis using pandas.


Quick quiz 1 of 2

Which function stacks the rows of two Data Frames on top of each other?

Quick quiz 2 of 2

What does log.resample('W', on='date').sum() do?


Summary

In this lesson, we have covered advanced data manipulation techniques in Pandas, including merging, joining, and handling time series data. These techniques are invaluable when dealing with complex datasets and analyses.


< Previous Next >

You can use the arrows  ← → on your keyboard to navigate between lessons.

Course complete

Congratulations, you finished Data Manipulation with Pandas and Numpy!

That's all 12 lessons done. Let people know, it's a great way to inspire someone else to start.

Share card: I completed Data Manipulation with Pandas and Numpy on KevsRobots
Post Bluesky LinkedIn Facebook


What are you looking for?
Watch Videos Get Ideas Learn Something Read a Review Read the Blog Search
... Z Z z