Pandas - processing and analysis of data

Pandas: The Ultimate Guide to Data Analysis in Python

Pandas is a fundamental library for data analysis in Python that has become an industry standard for processing structured information. Created by Wes McKinney in 2008, it got its name from the term "Panel Data" in econometrics.

What is Pandas and Why is it So Important?

Pandas is a powerful open-source library built on NumPy, which provides high-level data structures and tools for fast and efficient data analysis. The library is especially effective when working with tabular data, time series, and any structured information.

Key Benefits of Pandas:

• Versatility: Pandas supports working with various data formats - from CSV and Excel to JSON, SQL, HTML, and many others.
• Performance: Thanks to optimized algorithms based on Cython and NumPy, the library provides high-speed processing of large amounts of data.
• Intuition: The syntax of the library is as close as possible to natural language, which makes the code understandable and easy to learn.
• Integration: Pandas integrates perfectly with other Python ecosystem libraries - NumPy, Matplotlib, Scikit-learn, SciPy.

Areas of Application of Pandas:

• Data Analysis and Visualization - creating reports, dashboards, exploratory analysis
• Machine Learning - data preprocessing, feature engineering, dataset preparation
• Financial Analytics - time series analysis, calculation of financial indicators
• Business Intelligence - report preparation, sales analysis, KPI
• Scientific Research - processing experimental data, statistical analysis
• Web Scraping - structuring and cleaning collected data

Installation and Import of Pandas

Installation via pip:

pip install pandas

Installation via conda:

conda install pandas

Importing the Library:

import pandas as pd
import numpy as np  # often used with pandas

The abbreviation pd has become a universal standard in the Python community and is used in all manuals and documentation.

Основные структуры данных: Series и DataFrame

Series — One-Dimensional Data with Indexes

Series is a one-dimensional array with labels (indexes) that can contain any data types: numbers, strings, boolean values, Python objects.

# Creating a Series from a list
s = pd.Series([10, 20, 30, 40], index=['a', 'b', 'c', 'd'])

# Creating a Series from a dictionary
data = {'Moscow': 12500000, 'Saint Petersburg': 5400000, 'Novosibirsk': 1600000}
population = pd.Series(data)

# Accessing elements
print(population['Moscow'])  # 12500000
print(population.iloc[0])    # first element

DataFrame — Two-Dimensional Tabular Data

DataFrame is a two-dimensional data structure with row and column labels, similar to a table in a relational database or an Excel spreadsheet.

# Creating a DataFrame from a dictionary
data = {
    'Name': ['Anna', 'Boris', 'Vika', 'Dmitry'],
    'Age': [25, 30, 22, 28],
    'City': ['Moscow', 'St. Petersburg', 'Kazan', 'Moscow'],
    'Salary': [80000, 95000, 60000, 75000]
}
df = pd.DataFrame(data)

# Creating a DataFrame with a custom index
df = pd.DataFrame(data, index=['emp1', 'emp2', 'emp3', 'emp4'])

Loading and Saving Data

Loading from Different Formats

# CSV files
df_csv = pd.read_csv('data.csv', encoding='utf-8', sep=',')

# Excel files
df_excel = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# JSON files
df_json = pd.read_json('data.json')

# SQL databases
import sqlite3
conn = sqlite3.connect('database.db')
df_sql = pd.read_sql('SELECT * FROM table_name', conn)

# HTML tables
df_html = pd.read_html('https://example.com/table')

# Parquet files (for big data)
df_parquet = pd.read_parquet('data.parquet')

Additional Loading Parameters

# CSV with parameters
df = pd.read_csv('data.csv', 
                 sep=';',           # separator
                 header=0,          # row with headers
                 index_col=0,       # column for index
                 parse_dates=True,  # automatic date conversion
                 na_values=['N/A', 'NULL'])  # values for NaN

Saving Data

# Saving to CSV
df.to_csv('output.csv', index=False, encoding='utf-8')

# Saving to Excel
df.to_excel('output.xlsx', sheet_name='Data', index=False)

# Saving to JSON
df.to_json('output.json', orient='records', force_ascii=False)

# Saving to SQL
df.to_sql('table_name', conn, if_exists='replace', index=False)

Indexing, Filtering, and Data Selection

Column Selection

# One column
names = df['Name']

# Multiple columns
subset = df[['Name', 'Age']]

# All columns except specified
df_without_salary = df.drop('Salary', axis=1)

Row Filtering

# Logical filtering
young_employees = df[df['Age'] < 30]

# Multiple conditions
moscow_young = df[(df['City'] == 'Moscow') & (df['Age'] < 30)]

# Using the query method
high_salary = df.query('Salary > 70000')

# Filtering by a list of values
moscow_spb = df[df['City'].isin(['Moscow', 'St. Petersburg'])]

Indexing loc and iloc

# loc - by labels
df.loc[0, 'Name']                    # specific cell
df.loc[0:2, ['Name', 'Age']]     # range of rows and columns
df.loc[df['Age'] > 25, 'Salary'] = 100000  # conditional assignment

# iloc - by positions
df.iloc[0, 1]                       # first row, second column
df.iloc[1:3, 0:2]                   # slice by positions
df.iloc[-1]                         # last row

Working with Missing Values

Detecting Missing Values

# Checking for missing values
df.isnull()                         # boolean mask
df.isnull().sum()                   # number of missing values per column
df.info()                           # general information about missing values

Handling Missing Values

# Removing rows with missing values
df.dropna()                         # delete all rows with NaN
df.dropna(subset=['Name'])           # delete rows with NaN in the 'Name' column
df.dropna(thresh=2)                 # keep rows with at least 2 non-empty values

# Filling in missing values
df.fillna(0)                        # fill with zeros
df.fillna(method='ffill')           # fill with the previous value
df.fillna(method='bfill')           # fill with the next value
df.fillna(df.mean())                # fill with the mean value

Grouping and Aggregating Data

Grouping with groupby

# Simple grouping
city_groups = df.groupby('City')

# Aggregation by groups
average_salary_by_city = df.groupby('City')['Salary'].mean()

# Multiple grouping
df.groupby(['City', 'Age'])['Salary'].sum()

# Multiple aggregations
df.groupby('City').agg({
    'Salary': ['mean', 'max', 'min'],
    'Age': 'mean'
})

Pivot Tables

# Creating a pivot table
pivot_table = df.pivot_table(
    values='Salary',
    index='City',
    columns='Age',
    aggfunc='mean'
)

# More complex pivot table
pivot_complex = df.pivot_table(
    values=['Salary', 'Age'],
    index='City',
    aggfunc={'Salary': 'mean', 'Age': 'count'}
)

Merging, Joining, and Concatenating Tables

Joining Methods

# Vertical concatenation (concat)
df_combined = pd.concat([df1, df2], ignore_index=True)

# Horizontal concatenation
df_wide = pd.concat([df1, df2], axis=1)

# Merging by keys (merge)
df_merged = pd.merge(df1, df2, on='ID', how='inner')

# Different types of joins
left_join = pd.merge(df1, df2, on='ID', how='left')
right_join = pd.merge(df1, df2, on='ID', how='right')
outer_join = pd.merge(df1, df2, on='ID', how='outer')

# Joining by indexes
df_joined = df1.join(df2, how='left')

Data Modification and Cleaning

Data Transformation

# Renaming columns
df.rename(columns={'Name': 'Full Name', 'Age': 'Age'}, inplace=True)

# Replacing values
df.replace('Moscow', 'Moscow', inplace=True)
df.replace({'City': {'St. Petersburg': 'Saint Petersburg'}}, inplace=True)

# Converting data types
df['Age'] = df['Age'].astype('int64')
df['Date'] = pd.to_datetime(df['Date'])

# Applying functions
df['Name_upper'] = df['Name'].str.upper()
df['Salary_thousands'] = df['Salary'] / 1000

Working with Strings

# String operations
df['Name'].str.len()                 # string length
df['Name'].str.contains('An')        # contains substring
df['Name'].str.extract('([A-Z])')    # extraction by regular expression
df['Name'].str.split(' ')            # string split

Working with Time Series

# Creating a time index
dates = pd.date_range('2023-01-01', periods=100, freq='D')
ts = pd.Series(np.random.randn(100), index=dates)

# Resampling (changing frequency)
monthly = ts.resample('M').mean()   # by months
weekly = ts.resample('W').sum()     # by weeks

# Time shifts
ts.shift(1)                         # shift by one period
ts.shift(-1)                        # shift back

# Moving windows
rolling_mean = ts.rolling(window=7).mean()  # moving average

Data Visualization with Pandas

import matplotlib.pyplot as plt

# Simple graphs
df['Age'].plot(kind='hist', bins=10)
df['Salary'].plot(kind='box')
df.plot(x='Age', y='Salary', kind='scatter')

# Grouping and visualization
df.groupby('City')['Salary'].mean().plot(kind='bar')

# Time series
ts.plot()
plt.show()

Comprehensive Table of Pandas Methods and Functions

Performance Optimization

Tips for Accelerating Pandas:

• Use vectorized operations instead of loops
• Apply the query() method for complex filters
• Use the categorical type for string data with repeating values
• Read data in chunks when working with large files
• Use inplace=True for data modification operations

# Optimized reading of large files
chunk_size = 10000
chunks = []
for chunk in pd.read_csv('large_file.csv', chunksize=chunk_size):
    chunks.append(chunk)
df = pd.concat(chunks, ignore_index=True)

# Using categorical data
df['category'] = df['category'].astype('category')

Frequently Asked Questions

What is Pandas?

Pandas is a Python library for analyzing and manipulating structured data. It provides powerful tools for working with tables, time series, and other forms of data.

What is the Difference Between Series and DataFrame?

Series is a one-dimensional data structure (like a column in a table), while DataFrame is a two-dimensional structure (a complete table with multiple columns).

How to Load an Excel File with Multiple Sheets?

# Loading all sheets
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)

# Loading a specific sheet
df = pd.read_excel('file.xlsx', sheet_name='Sheet2')

How to Combine Multiple CSV Files?

import glob

files = glob.glob('*.csv')
dfs = [pd.read_csv(file) for file in files]
combined = pd.concat(dfs, ignore_index=True)

How to Work with Large Files That Don't Fit into Memory?

# Reading in chunks
for chunk in pd.read_csv('large_file.csv', chunksize=10000):
    # Processing each chunk
    process_chunk(chunk)

How to Speed Up Pandas?

• Use vectorized operations
• Apply the correct data types
• Use query() for filtering
• Avoid loops
• Use inplace=True to save memory

Conclusion: Why Pandas is Indispensable in Data Work

Pandas has become an integral part of the Python ecosystem for working with data due to its versatility, performance, and ease of use. The library provides an intuitive interface for solving a wide variety of tasks - from simply loading a CSV file to complex time series analysis.

Key Reasons for Pandas Popularity:

• Rich functionality: more than 200 methods and functions for any data processing tasks
• High performance: optimized algorithms for working with large amounts of data
• Flexibility: support for many data formats and ways to process them
• Integration: excellent compatibility with other Python libraries
• Active community: continuous development and support

For data scientists, analysts, researchers, and developers, Pandas is a fundamental tool that significantly simplifies and speeds up working with data. Learning this library is an investment in professional development, which pays off by saving time and the ability to solve more complex tasks with less effort.