Mastering Data Science and Applied Analytics
Data Manipulation Mastery
Beyond Flat Files
In the real world, data rarely arrives in a pristine CSV file. More often, it's scattered across web APIs, buried in nested JSON structures, or stored in relational databases. Pandas is equipped to handle this complexity, moving beyond simple file loading to become a powerful data integration tool.
Let's start with web APIs. Many services provide data through REST APIs, which usually return data in JSON format. You can use the requests library to fetch this data and then load it directly into a DataFrame.
import pandas as pd
import requests
# Fetch data from a public API
api_url = 'https://api.publicapis.org/entries'
response = requests.get(api_url)
data = response.json()
# The data is nested under the 'entries' key
df = pd.DataFrame(data['entries'])
print(df.head())
APIs often return , where values are themselves dictionaries or lists. This structure is efficient for transmission but tricky for analysis. Pandas' json_normalize function is designed to flatten these structures into a table-friendly format.
from pandas import json_normalize
# Example nested JSON data
nested_data = [
{'id': 1, 'name': 'Alice', 'info': {'age': 30, 'city': 'New York'}},
{'id': 2, 'name': 'Bob', 'info': {'age': 25, 'city': 'London'}}
]
# Flatten the data
normalized_df = json_normalize(nested_data)
print(normalized_df)
# Output will have columns: 'id', 'name', 'info.age', 'info.city'
Similarly, you can pull data directly from SQL databases. Using a library like SQLAlchemy to create a connection engine, you can execute a SQL query and load the results straight into a DataFrame with pd.read_sql.
Advanced Data Cleaning
Once data is loaded, the next challenge is cleaning it. You're likely familiar with drop_duplicates(), but real-world duplication is often more subtle. For example, you might need to identify duplicates based on a specific combination of columns, ignoring others like a timestamp or unique ID.
# Consider duplicates only based on 'user_id' and 'product_id'
df.drop_duplicates(subset=['user_id', 'product_id'], keep='first', inplace=True)
Text data is notoriously messy. User-entered fields can result in variations like "New Delhi," "delhi," and "New Dehli." Standardizing these requires more than simple string matching. This is where comes in. Libraries like thefuzz can calculate the similarity between strings, allowing you to identify and merge inconsistent entries.
Another common issue is incorrect data types. A column of numbers might be loaded as strings because of a stray character or a placeholder like "N/A". Using pd.to_numeric with the errors='coerce' argument is a robust way to handle this. It attempts to convert the column to a numeric type and replaces any values that fail the conversion with NaN (Not a Number), which you can then handle separately.
Reshaping Data
The shape of your data can dramatically affect how easily you can work with it. The ideal format is often called "tidy data," where each row is an observation, and each column is a variable. However, data is often delivered in a "wide" format that is easy for humans to read but difficult for machines to analyze.
The melt function in Pandas is the perfect tool for converting data from a wide to a long format. It "unpivots" a DataFrame, turning columns into rows.
The inverse operation is pivoting. The method transforms long-format data back into a wide format. It's incredibly versatile because it can also aggregate data in the process. You can specify which columns should become the new index, which should become the new columns, and which values should fill the cells, along with an aggregation function like mean, sum, or count.
# long_df is the result from the melt operation
# Pivot the long data back to wide, using 'mean' for aggregation
pivoted_df = long_df.pivot_table(
index='City',
columns='Month',
values='Temperature',
aggfunc='mean'
)
print(pivoted_df)
Unifying Data
Often, your analysis requires combining data from multiple sources. Pandas offers two primary ways to do this: concatenating and merging.
pd.concat() is used to stack DataFrames on top of each other (axis=0) or side-by-side (axis=1). This is useful when the datasets have the same structure and you simply want to append them.
pd.merge() is for more complex, database-style joins. It combines DataFrames based on the values in one or more common columns (the 'keys'). You can perform all standard join types: inner, outer, left, and right.
| Join Type | Description |
|---|---|
inner | Returns only the rows that have matching keys in both DataFrames. |
outer | Returns all rows from both DataFrames, filling in NaN where matches don't exist. |
left | Returns all rows from the left DataFrame and matched rows from the right. |
right | Returns all rows from the right DataFrame and matched rows from the left. |
Finally, for high-performance data aggregation, the groupby method is essential. While you may have used it for simple aggregations, its power lies in its flexibility. You can group by multiple columns, apply multiple aggregation functions at once, or even apply your own custom functions.
# Group by 'category' and calculate both the sum and mean of 'sales'
agg_df = df.groupby('category')['sales'].agg(['sum', 'mean'])
# Rename columns for clarity
agg_df.rename(columns={'sum': 'total_sales', 'mean': 'average_sale'}, inplace=True)
print(agg_df)
Mastering these techniques—importing complex data, performing advanced cleaning, reshaping, and combining—forms the backbone of effective data manipulation. It's the critical preparation that makes all subsequent analysis and modeling possible.
You have fetched data from a web API that returns a list of nested JSON objects. Which Pandas function is specifically designed to flatten this complex structure into a table-like DataFrame?
A DataFrame has a 'City' column with inconsistent entries like "New Delhi", "delhi", and "New Dehli". Which technique is most suitable for identifying and standardising these similar but not identical strings?