Python Pandas Examples for Reading, Filtering, Sorting, Merging, De‑duplicating, Pivoting, Analyzing, Plotting, Formatting, and Conditional Formatting of Excel Data

Excel Data Reading and Display

import pandas as pd
def read_excel(file_path):
    df = pd.read_excel(file_path)
    print(df)
file_path = '/path/to/your/excel_file.xlsx'
read_excel(file_path)

Excel Data Filtering

import pandas as pd
def filter_excel(file_path, column, value):
    df = pd.read_excel(file_path)
    filtered_df = df[df[column] == value]
    print(filtered_df)
file_path = '/path/to/your/excel_file.xlsx'
column = 'Name'
value = 'John Doe'
filter_excel(file_path, column, value)

Excel Data Sorting

import pandas as pd
def sort_excel(file_path, by_column, ascending=True):
    df = pd.read_excel(file_path)
    sorted_df = df.sort_values(by=by_column, ascending=ascending)
    print(sorted_df)
file_path = '/path/to/your/excel_file.xlsx'
by_column = 'Age'
sort_excel(file_path, by_column, ascending=False)

Excel Data Merging

import pandas as pd
def merge_excel(file_paths, output_file):
    dfs = [pd.read_excel(file) for file in file_paths]
    merged_df = pd.concat(dfs, ignore_index=True)
    merged_df.to_excel(output_file, index=False)
file_paths = ['/path/to/first_file.xlsx', '/path/to/second_file.xlsx']
output_file = '/path/to/merged_file.xlsx'
merge_excel(file_paths, output_file)

Excel Data De‑duplication

import pandas as pd
def remove_duplicates(file_path, output_file):
    df = pd.read_excel(file_path)
    unique_df = df.drop_duplicates()
    unique_df.to_excel(output_file, index=False)
file_path = '/path/to/your/excel_file.xlsx'
output_file = '/path/to/no_duplicates.xlsx'
remove_duplicates(file_path, output_file)

Excel Pivot Table Creation

import pandas as pd
def create_pivot_table(file_path, output_file):
    df = pd.read_excel(file_path)
    pivot_table = pd.pivot_table(df, values='Sales', index=['Category'], columns=['Year'], aggfunc=sum)
    pivot_table.to_excel(output_file)
file_path = '/path/to/your/excel_file.xlsx'
output_file = '/path/to/pivot_table.xlsx'
create_pivot_table(file_path, output_file)

Excel Data Statistical Analysis

import pandas as pd
def analyze_data(file_path):
    df = pd.read_excel(file_path)
    stats = df.describe()
    print(stats)
file_path = '/path/to/your/excel_file.xlsx'
analyze_data(file_path)

Excel Chart Generation

import pandas as pd
import matplotlib.pyplot as pd
def plot_data(file_path):
    df = pd.read_excel(file_path)
    df.plot(x='Year', y='Sales', kind='bar')
    plt.show()
file_path = '/path/to/your/excel_file.xlsx'
plot_data(file_path)

Excel Data Formatting and Saving

import pandas as pd
def format_and_save(file_path, output_file):
    df = pd.read_excel(file_path)
    df.style.format({'Price': '${:.2f}', 'Quantity': '{:.0f}'}).to_excel(output_file, engine='openpyxl')
file_path = '/path/to/your/excel_file.xlsx'
output_file = '/path/to/formatted_file.xlsx'
format_and_save(file_path, output_file)

Excel Conditional Formatting

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.utils.dataframe import dataframe_to_rows

def apply_conditional_formatting(file_path, output_file):
    df = pd.read_excel(file_path)
    wb = load_workbook(file_path)
    ws = wb.active
    red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
    for r in dataframe_to_rows(df, index=False, header=True):
        ws.append(r)
    for row in ws.iter_rows(min_row=2, max_col=df.shape[1], max_row=df.shape[0]+1):
        if row[1].value > 100:
            row[1].fill = red_fill
    wb.save(output_file)
file_path = '/path/to/your/excel_file.xlsx'
output_file = '/path/to/conditional_formatted_file.xlsx'
apply_conditional_formatting(file_path, output_file)