pandas

Pandas

### What is Pandas?

Pandas is an open-source Python library that provides high-performance, easy-to-use data structures and data analysis tools. It's built on top of NumPy, another popular Python library for numerical computing, and is designed to make working with structured data fast, easy, and expressive.

At the core of Pandas are two primary data structures: Series and DataFrame.

1. **Series**: A one-dimensional labeled array capable of holding any data type (e.g., integers, strings, floating-point numbers, Python objects). Think of it as a single column of data.

2. **DataFrame**: A two-dimensional labeled data structure with columns of potentially different types. It's like a spreadsheet or SQL table, where each column is a Series.

With these data structures, Pandas provides powerful tools for:

- Data manipulation: filtering, sorting, grouping, and aggregating data.

- Data cleaning: handling missing data, removing duplicates, and transforming data.

- Data analysis: statistical and time-series analysis, visualization, and much more.

### Why Use Pandas?

Pandas is widely adopted in the data science community for several reasons:

1. **Ease of Use**: Pandas provides intuitive and expressive syntax, making it easy to perform complex data manipulations with just a few lines of code.

2. **Flexibility**: It can handle a wide range of data formats, including CSV, Excel, SQL databases, JSON, HTML, and more.

3. **Performance**: Pandas is built on top of NumPy, which means it's optimized for speed and efficiency, especially when working with large datasets.

4. **Integration**: Pandas seamlessly integrates with other Python libraries like Matplotlib, Seaborn, and Scikit-learn, enabling end-to-end data analysis workflows.

### Getting Started with Pandas

To start using Pandas, you'll first need to install it. If you haven't already, you can install Pandas using pip, Python's package manager:

```

pip install pandas

```

Once installed, you can import Pandas into your Python scripts or Jupyter notebooks using the following convention:

```python

import pandas as pd

```

Basic operations with Pandas. 

```python

import pandas as pd

# Create a DataFrame from a dictionary

data = {

    'Name': ['Alice', 'Bob', 'Charlie', 'David'],

    'Age': [25, 30, 35, 40],

    'City': ['New York', 'Los Angeles', 'Chicago', 'Houston']

}

df = pd.DataFrame(data)

# Display the DataFrame

print(df)

```

This code creates a DataFrame from a dictionary and displays it. 

DataFrames are the fundamental data structure in Pandas, providing a two-dimensional, tabular data structure with labeled axes (rows and columns). Let's break down the structure of DataFrames:

### Rows and Columns:

- **Rows**: Each row in a DataFrame represents a separate observation or data point. These are often indexed by integers starting from 0 by default, but they can also be indexed with more meaningful labels.

- **Columns**: Each column in a DataFrame represents a different variable or feature. Columns have labels, which are often strings, allowing easy access to the data they contain.

### Index:

- **Index**: An index is a sequence of labels for rows or columns. By default, rows are indexed with integers starting from 0, but you can define a custom index based on meaningful labels. Indices allow for fast access, selection, and alignment of data.

### Data Types:

- **Data Types**: Each column in a DataFrame can have its own data type (e.g., integer, float, string, datetime, etc.). Pandas automatically infers the data types when you create a DataFrame, but you can also specify them explicitly.

### Attributes and Methods:

- **Attributes**: These are the properties of a DataFrame, such as shape (number of rows and columns), index (row labels), columns (column labels), and values (data stored in the DataFrame).

- **Methods**: These are functions that can be applied to a DataFrame to perform operations like data manipulation, filtering, sorting, grouping, aggregation, merging, and more. Methods in Pandas often return new DataFrames, allowing for method chaining.

### Missing Values:

- **Missing Values**: DataFrames can handle missing or NaN (Not a Number) values gracefully, allowing you to clean, fill, or drop them as needed.

### Operations on DataFrames:

- **Selection**: You can select subsets of data from a DataFrame using various methods, including integer indexing, label-based indexing, slicing, and boolean indexing.

- **Modification**: DataFrames support in-place modification of data, allowing you to update values, add or remove columns, and perform other modifications.

- **Aggregation**: You can perform aggregation operations like sum, mean, median, min, max, count, etc., on columns or rows of a DataFrame.

- **Joining and Merging**: DataFrames support SQL-like operations for joining and merging data from multiple sources based on common keys or indices.

 Queries

import pandas as pd

# Sample data for demonstration

data = {

    'Title': ['Book1', 'Book2', 'Book3', 'Book4'],

    'Publication_Year': [1945, 1955, 1960, 2000]

}

# Creating the DataFrame

books_df = pd.DataFrame(data)

# Filtering books published after 1950

recent_books = books_df[books_df['Publication_Year'] > 1950]

print("Books published after 1950:\n", recent_books)

Searching in Pandas DataFrames using various techniques:

### Example 1: Filtering by Condition

```python

# Filter books published after 1950

recent_books = books_df[books_df['Publication_Year'] > 1950]

print("Books published after 1950:\n", recent_books)

```

This example filters the DataFrame to include only the books published after the year 1950.

### Example 2: Using Multiple Conditions

```python

# Filter books published after 1950 and priced less than $10

cheap_recent_books = books_df[(books_df['Publication_Year'] > 1950) & (books_df['Price'] < 10)]

print("Cheap books published after 1950:\n", cheap_recent_books)

```

This example demonstrates how to filter the DataFrame based on multiple conditions using logical operators like `&` for 'and' and `|` for 'or'.

### Example 3: Searching by Partial String Match

```python

# Search for books containing 'Great' in the title

great_books = books_df[books_df['Title'].str.contains('Great')]

print("Books with 'Great' in the title:\n", great_books)

```

This example filters the DataFrame to include only the books whose titles contain the word 'Great', irrespective of case.

### Example 4: Filtering by List of Values

```python

# Filter books by specific authors

selected_authors = ['Jane Austen', 'F. Scott Fitzgerald']

selected_books = books_df[books_df['Author'].isin(selected_authors)]

print("Books by selected authors:\n", selected_books)

```

Here, we filter the DataFrame to include only the books authored by 'Jane Austen' or 'F. Scott Fitzgerald'.

### Example 5: Using Query Method

```python

# Using the query method to filter books with price less than $10

cheap_books_query = books_df.query('Price < 10')

print("Cheap books using query method:\n", cheap_books_query)

```

The `query()` method allows filtering DataFrame using a SQL-like syntax. Here, we select books with a price less than $10.

In Pandas, logical operators such as "and", "or", and "not" are used for combining multiple conditions when filtering DataFrames. These operators allow you to create more complex conditions to extract subsets of data that meet specific criteria. Let's explore how these operators work:

### Logical Operators in Pandas:

#### 1. AND Operator (`&`):

- The `&` operator is used for combining two or more conditions, and it requires both conditions to be true for the result to be true.

- It's similar to the logical "and" operator in Python.

- When using the `&` operator, make sure to wrap each condition in parentheses.

Example:

```python

# Filter books published after 1950 and priced less than $10

recent_and_cheap_books = books_df[(books_df['Publication_Year'] > 1950) & (books_df['Price'] < 10)]

```

#### 2. OR Operator (`|`):

- The `|` operator is used for combining two or more conditions, and it requires at least one of the conditions to be true for the result to be true.

- It's similar to the logical "or" operator in Python.

- When using the `|` operator, make sure to wrap each condition in parentheses.

Example:

```python

# Filter books published after 1950 or priced less than $10

recent_or_cheap_books = books_df[(books_df['Publication_Year'] > 1950) | (books_df['Price'] < 10)]

```

#### 3. NOT Operator (`~`):

- The `~` operator is used to negate a condition, meaning it selects the rows where the condition is not true.

- It's similar to the logical "not" operator in Python.

- When using the `~` operator, make sure to wrap the condition in parentheses.

Example:

```python

# Filter books NOT published after 1950

old_books = books_df[~(books_df['Publication_Year'] > 1950)]

```

### Combining Logical Operators:

You can combine these logical operators to create complex conditions. When doing so, it's important to use parentheses to ensure proper evaluation order and avoid ambiguity.

Example:

```python

# Filter books published after 1950 and priced less than $10 OR authored by 'Jane Austen'

filtered_books = books_df[((books_df['Publication_Year'] > 1950) & (books_df['Price'] < 10)) | (books_df['Author'] == 'Jane Austen')]

```

In this example, we combine the AND operator (`&`) with the OR operator (`|`) to filter books based on multiple conditions. 

Understanding and effectively using logical operators in Pandas allows you to create sophisticated filters for extracting specific subsets of data from your DataFrame based on various criteria.

Ordering in Pandas refers to arranging the rows of a DataFrame based on the values of one or more columns. Ordering allows you to sort the data in either ascending or descending order, making it easier to analyze and interpret the dataset. Pandas provides several methods for ordering DataFrames:

### 1. `sort_values()` Method:

The `sort_values()` method is used to sort the DataFrame by the values in one or more columns. You can specify the column(s) by which you want to sort and the order (ascending or descending).

```python

# Sort books by publication year in ascending order

books_sorted_by_year = books_df.sort_values(by='Publication_Year')

# Sort books by price in descending order

books_sorted_by_price = books_df.sort_values(by='Price', ascending=False)

```

### 2. `sort_index()` Method:

The `sort_index()` method is used to sort the DataFrame by its index, either row index (axis=0) or column index (axis=1).

```python

# Sort DataFrame by row index (ascending order)

sorted_by_index = books_df.sort_index(axis=0)

# Sort DataFrame by column index (ascending order)

sorted_by_columns = books_df.sort_index(axis=1)

```

### 3. `nlargest()` and `nsmallest()` Methods:

These methods are used to get the n largest or smallest values from a DataFrame based on one or more columns.

```python

# Get top 3 most expensive books

top_expensive_books = books_df.nlargest(3, 'Price')

# Get top 3 oldest books

oldest_books = books_df.nsmallest(3, 'Publication_Year')

```

### 4. `rank()` Method:

The `rank()` method assigns a rank to each row based on the values in one or more columns. You can specify the method used to break ties (e.g., 'average', 'min', 'max', 'first', 'dense').

```python

# Rank books by price

books_df['Price_Rank'] = books_df['Price'].rank(method='min')

```

Aggregate Queries

## Exploring Aggregate Queries in Pandas

Pandas, a powerful library for data manipulation and analysis in Python, offers a wide range of functionalities to handle complex data operations with ease. Among these functionalities, aggregate queries play a crucial role in summarizing and extracting meaningful insights from data. In this blog post, we'll dive deep into aggregate queries in Pandas, exploring their various applications and demonstrating their use with practical examples.

### Table of Contents

1. Introduction to Aggregate Queries

2. Setting Up the Environment

3. Basic Aggregation Functions

4. Grouping Data

5. Applying Multiple Aggregations

6. Aggregating with Custom Functions

7. Handling Missing Data in Aggregations

8. Performance Tips

9. Conclusion

### 1. Introduction to Aggregate Queries

Aggregate queries are operations that summarize data by performing calculations such as sum, mean, median, count, min, max, etc., on one or more columns. These operations are essential for gaining insights from data, especially when dealing with large datasets.

### 2. Setting Up the Environment

Before we begin, let's ensure we have Pandas installed. If not, you can install it using pip:

```sh

pip install pandas

```

Now, let's import Pandas and set up a sample DataFrame to work with:

```python

import pandas as pd

# Sample data

data = {

    'Category': ['A', 'B', 'A', 'B', 'A', 'B'],

    'Values': [10, 20, 15, 30, 10, 25],

    'Quantity': [1, 2, 3, 4, 2, 3]

}

df = pd.DataFrame(data)

print(df)

```

### 3. Basic Aggregation Functions

Pandas provides several built-in aggregation functions that can be directly applied to DataFrame columns. Here are some basic examples:

```python

# Sum of 'Values' column

total_values = df['Values'].sum()

print('Total Values:', total_values)

# Mean of 'Values' column

mean_values = df['Values'].mean()

print('Mean Values:', mean_values)

# Maximum value in 'Values' column

max_value = df['Values'].max()

print('Max Value:', max_value)

# Minimum value in 'Values' column

min_value = df['Values'].min()

print('Min Value:', min_value)

# Count of non-NA/null entries in 'Values' column

count_values = df['Values'].count()

print('Count of Values:', count_values)

```

### 4. Grouping Data

Grouping data is often necessary before performing aggregate operations, especially when dealing with categorical data. The `groupby` method in Pandas is used for this purpose. Here's how you can group data and apply aggregate functions:

```python

# Group by 'Category' and calculate the sum of 'Values'

grouped_sum = df.groupby('Category')['Values'].sum()

print('Grouped Sum:\n', grouped_sum)

# Group by 'Category' and calculate the mean of 'Values'

grouped_mean = df.groupby('Category')['Values'].mean()

print('Grouped Mean:\n', grouped_mean)

```

### 5. Applying Multiple Aggregations

Sometimes, you may need to apply multiple aggregation functions simultaneously. The `agg` method allows you to specify multiple functions to be applied to each group:

```python

# Group by 'Category' and apply multiple aggregations on 'Values'

multiple_aggregations = df.groupby('Category')['Values'].agg(['sum', 'mean', 'max', 'min'])

print('Multiple Aggregations:\n', multiple_aggregations)

```

### 6. Aggregating with Custom Functions

Pandas also allows you to apply custom aggregation functions. You can define your own function and pass it to the `agg` method:

```python

# Custom aggregation function: Range (max - min)

def range_func(x):

    return x.max() - x.min()

# Group by 'Category' and apply custom aggregation on 'Values'

custom_aggregation = df.groupby('Category')['Values'].agg(range_func)

print('Custom Aggregation (Range):\n', custom_aggregation)

```

### 7. Handling Missing Data in Aggregations

Handling missing data is crucial in any data analysis task. Pandas provides options to handle missing data during aggregations:

```python

# Adding missing values to the DataFrame

df_with_nan = df.copy()

df_with_nan.loc[2, 'Values'] = None

# Group by 'Category' and calculate the sum, skipping NaN values

grouped_sum_with_nan = df_with_nan.groupby('Category')['Values'].sum()

print('Grouped Sum with NaN:\n', grouped_sum_with_nan)

# Fill missing values before aggregation

filled_df = df_with_nan.fillna(0)

grouped_sum_filled = filled_df.groupby('Category')['Values'].sum()

print('Grouped Sum with Filled NaN:\n', grouped_sum_filled)

```

### 8. Performance Tips

When working with large datasets, performance becomes a key consideration. Here are some tips to optimize your aggregate queries in Pandas:

- **Use Vectorized Operations**: Pandas operations are optimized for vectorized operations. Avoid using loops for aggregations.

- **Reduce Memory Usage**: Downcast numerical data types where possible to reduce memory usage.

- **Leverage Built-in Functions**: Use Pandas built-in functions which are optimized for performance.

- **Chunking**: For very large datasets, consider processing data in chunks using the `chunksize` parameter in functions like `read_csv`.

Contact us for software training, education or development