top of page

Database Manipulation with Pandas

Pandas is a powerful Python library used for data manipulation and analysis. It provides data structures and functions needed to efficiently manipulate structured data, such as CSV files, Excel spreadsheets, SQL tables, and more. In this explanation, I'll cover some of the fundamental operations for database manipulation with Pandas.

Installing Pandas

Before getting started, you need to ensure that you have Pandas installed. If you don't have it yet, you can install it using pip:

pip install pandas

Importing Pandas

Once installed, you can import Pandas into your Python script or Jupyter Notebook using:

import pandas as pd

Reading Data

To work with data, you first need to read it into a Pandas DataFrame. A DataFrame is a two-dimensional tabular data structure with labeled axes (rows and columns).

From CSV file:

df = pd.read_csv('data.csv')

From Excel file:

df = pd.read_excel('data.xlsx')

From SQL database (using SQLAlchemy):

from sqlalchemy import create_engine
engine = create_engine('sqlite:///mydatabase.db')
df = pd.read_sql_query('SELECT * FROM mytable', engine)

Basic DataFrame Operations

Once you have the data in a DataFrame, you can perform various operations on it.

Viewing DataFrame:

# To see the first few rows of the DataFrame

# To see the last few rows of the DataFrame

# To get basic statistics of the DataFrame

Accessing Columns:

# To access a single column

# To access multiple columns
df[['column_name1', 'column_name2']]

Filtering Data:

# To filter rows based on a condition
df[df['column_name'] > 10]

# To filter rows based on multiple conditions
df[(df['column_name1'] > 10) & (df['column_name2'] == 'value')]

Sorting Data:

# To sort the DataFrame by a specific column
df.sort_values(by='column_name', ascending=False)

Adding and Modifying Columns:

# To add a new column
df['new_column'] = some_values

# To modify an existing column
df['column_name'] = df['column_name'] + 1

Aggregating Data:

# To calculate the mean of a column
mean_value = df['column_name'].mean()

# To group data by a column and perform aggregation
grouped_data = df.groupby('group_column')['value_column'].sum()

Removing Rows or Columns:

# To drop a single column
df.drop('column_name', axis=1, inplace=True)

# To drop specific rows based on condition
df = df[df['column_name'] != 'value']

# To drop rows by index
df.drop([0, 1, 2], inplace=True)

Saving Data:

# To save the DataFrame to a CSV file
df.to_csv('new_data.csv', index=False)

# To save the DataFrame to an Excel file
df.to_excel('new_data.xlsx', index=False)

# To save the DataFrame to a SQL database (using SQLAlchemy)
df.to_sql('new_table', engine, index=False)

These are just some of the basic operations you can perform with Pandas for database manipulation. Pandas provides a wide range of functionalities for data cleaning, transformation, merging, and more. It's a versatile tool for data analysis and manipulation in Python.

Related Posts

See All


bottom of page