Python - Advanced-Data Manipulation with Pandas

Advanced Data Manipulation with Pandas in Python

Pandas is one of the most powerful and widely-used Python libraries for data manipulation and analysis. While basic data operations such as loading data, filtering, and basic statistics are well-known, Pandas also provides a suite of advanced features that are essential for working with real-world datasets efficiently.

In this document, we will explore the more advanced capabilities of Pandas. These include:

• Advanced indexing and selection
• Hierarchical indexing (MultiIndex)
• Advanced reshaping using stack, unstack, melt, and pivot
• Efficient data merging and joining techniques
• Window functions and rolling computations
• Working with time series
• Advanced aggregation and grouping
• Performance optimization techniques

Advanced Indexing and Selection

Boolean Indexing with Multiple Conditions

You can combine multiple conditions using logical operators for more complex filtering.

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie', 'David'],
    'age': [24, 27, 22, 32],
    'salary': [50000, 60000, 45000, 80000]
})

result = df[(df['age'] > 25) & (df['salary'] > 55000)]
print(result)

Using query() for Complex Conditions

The `query()` method allows you to filter a DataFrame using a string expression.

filtered = df.query('age > 25 and salary > 55000')
print(filtered)

Hierarchical Indexing (MultiIndex)

Creating MultiIndex

Hierarchical indexing enables you to work with higher-dimensional data in a lower-dimensional form.

arrays = [
    ['Group1', 'Group1', 'Group2', 'Group2'],
    ['A', 'B', 'A', 'B']
]
index = pd.MultiIndex.from_arrays(arrays, names=('Group', 'Label'))
data = pd.Series([10, 20, 30, 40], index=index)
print(data)

Accessing Data with MultiIndex

print(data['Group1'])       # All entries in Group1
print(data.loc[('Group2', 'B')])  # Specific label

Reshaping and Pivoting

Using stack() and unstack()

df = pd.DataFrame({
    'group': ['A', 'A', 'B', 'B'],
    'label': ['x', 'y', 'x', 'y'],
    'value': [10, 20, 30, 40]
})

pivoted = df.pivot(index='group', columns='label', values='value')
print(pivoted)

stacked = pivoted.stack()
print(stacked)

unstacked = stacked.unstack()
print(unstacked)

Melt for Unpivoting

melted = pd.melt(pivoted.reset_index(), id_vars=['group'])
print(melted)

Joining and Merging DataFrames

Merge on Multiple Keys

df1 = pd.DataFrame({
    'key1': ['K0', 'K1', 'K2'],
    'key2': ['A', 'B', 'C'],
    'value1': [1, 2, 3]
})

df2 = pd.DataFrame({
    'key1': ['K0', 'K1', 'K2'],
    'key2': ['A', 'B', 'C'],
    'value2': [4, 5, 6]
})

merged = pd.merge(df1, df2, on=['key1', 'key2'])
print(merged)

Join with Different Join Types

left = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie']
})

right = pd.DataFrame({
    'id': [2, 3, 4],
    'score': [80, 90, 85]
})

outer_join = pd.merge(left, right, on='id', how='outer')
print(outer_join)

Rolling, Expanding, and Window Functions

Rolling Mean and Aggregations

data = pd.Series([1, 2, 3, 4, 5, 6])
rolling_mean = data.rolling(window=3).mean()
print(rolling_mean)

Expanding Functions

expanding_mean = data.expanding().mean()
print(expanding_mean)

Custom Aggregations with Rolling

custom_sum = data.rolling(window=2).apply(lambda x: x.sum())
print(custom_sum)

Time Series Manipulation

Generating Time Series

dates = pd.date_range('2023-01-01', periods=6, freq='D')
ts = pd.Series(range(6), index=dates)
print(ts)

Resampling and Frequency Conversion

resampled = ts.resample('2D').sum()
print(resampled)

Shifting and Lagging

shifted = ts.shift(1)
print(shifted)

Time Zone Handling

ts_utc = ts.tz_localize('UTC')
ts_est = ts_utc.tz_convert('US/Eastern')
print(ts_est)

Advanced Aggregation with GroupBy

GroupBy with Multiple Aggregations

df = pd.DataFrame({
    'dept': ['IT', 'IT', 'HR', 'HR'],
    'salary': [60000, 80000, 50000, 45000],
    'age': [25, 30, 35, 40]
})

agg = df.groupby('dept').agg({
    'salary': 'mean',
    'age': ['min', 'max']
})
print(agg)

Transform vs Apply

# Using transform to broadcast result
df['salary_mean'] = df.groupby('dept')['salary'].transform('mean')

# Using apply to modify groups
def normalize(x):
    return (x - x.mean()) / x.std()

df['salary_norm'] = df.groupby('dept')['salary'].apply(normalize)
print(df)

Data Cleaning and Transformation

Replacing and Mapping Values

df['dept_code'] = df['dept'].map({'IT': 1, 'HR': 2})
print(df)

Discretization and Binning

bins = [0, 30, 40, 100]
labels = ['Young', 'Middle-aged', 'Senior']
df['age_group'] = pd.cut(df['age'], bins=bins, labels=labels)
print(df)

String Operations

df['dept_lower'] = df['dept'].str.lower()
print(df)

Performance Optimization

Using Categoricals

df['dept_cat'] = df['dept'].astype('category')
print(df.dtypes)

Vectorized Operations

df['bonus'] = df['salary'] * 0.10  # Vectorized
print(df)

Using eval() and query()

df.eval('new_col = salary + age', inplace=True)
print(df)

filtered = df.query('salary > 60000 and age < 35')
print(filtered)

Pivot Tables and Cross Tabulations

Pivot Table with Aggregations

df = pd.DataFrame({
    'department': ['IT', 'IT', 'HR', 'HR'],
    'gender': ['M', 'F', 'M', 'F'],
    'salary': [60000, 80000, 50000, 45000]
})

pivot = pd.pivot_table(df, values='salary', index='department', columns='gender', aggfunc='mean')
print(pivot)

Cross Tabulation

cross = pd.crosstab(df['department'], df['gender'])
print(cross)

Chaining Methods Efficiently

Method Chaining with Pipe

def add_bonus(df):
    df['bonus'] = df['salary'] * 0.05
    return df

def categorize_salary(df):
    df['high_salary'] = df['salary'] > 60000
    return df

processed = df.pipe(add_bonus).pipe(categorize_salary)
print(processed)

Advanced data manipulation with Pandas allows analysts and data scientists to handle complex data structures, perform sophisticated aggregations, and optimize data pipelines efficiently. From hierarchical indexing and time series to pivot tables and performance tuning, Pandas provides a robust toolkit for real-world data analysis.

By mastering these advanced features, you can streamline data workflows, reduce errors, and increase the clarity and performance of your code. Whether you are preparing data for machine learning, reporting, or exploration, advanced Pandas techniques are indispensable tools in a Python developer’s arsenal.