Excel Analysis with Pandas – Quick Statistics Without Formulas

pip install pandas openpyxl

import pandas as pd

# Read Excel file
df = pd.read_excel('sales_data.xlsx', sheet_name='Revenue')

# First look at the data
print(df.head())
print(df.info())

# Quick overview
print(df.describe())

# Individual statistics
print(f"Average: ${df['Revenue'].mean():.2f}")
print(f"Median: ${df['Revenue'].median():.2f}")
print(f"Minimum: ${df['Revenue'].min():.2f}")
print(f"Maximum: ${df['Revenue'].max():.2f}")
print(f"Total: ${df['Revenue'].sum():.2f}")

# Revenue by product
revenue_by_product = df.groupby('Product')['Revenue'].agg([
    ('Count', 'count'),
    ('Total', 'sum'),
    ('Average', 'mean')
])

print(revenue_by_product)

# Revenue over $10,000
high_revenue = df[df['Revenue'] > 10000]

# Multiple conditions
premium_sales = df[
    (df['Revenue'] > 10000) &
    (df['Product'] == 'Laptop')
]

# Filter by date
df['Date'] = pd.to_datetime(df['Date'])
q1_2026 = df[(df['Date'] >= '2026-01-01') &
             (df['Date'] <= '2026-03-31')]

print(f"Q1 Revenue: ${q1_2026['Revenue'].sum():.2f}")

# Set date as index
df['Date'] = pd.to_datetime(df['Date'])
df.set_index('Date', inplace=True)

# Monthly sums
monthly = df.resample('M')['Revenue'].agg([
    'sum', 'mean', 'count'
])

print(monthly)

# Top 10 revenue
top10 = df.nlargest(10, 'Revenue')[['Product', 'Customer', 'Revenue']]

# Bottom 5
flop5 = df.nsmallest(5, 'Revenue')[['Product', 'Revenue']]

print("Top 10 Deals:")
print(top10)

# Single sheet
revenue_by_product.to_excel('analysis.xlsx', sheet_name='Product Stats')

# Multiple sheets
with pd.ExcelWriter('analysis.xlsx') as writer:
    revenue_by_product.to_excel(writer, sheet_name='Products')
    monthly.to_excel(writer, sheet_name='Monthly')
    top10.to_excel(writer, sheet_name='Top10', index=False)

print("Analysis saved: analysis.xlsx")

import pandas as pd
from datetime import datetime

# Read data
df = pd.read_excel('sales_data.xlsx')
df['Date'] = pd.to_datetime(df['Date'])

# Define period
today = datetime.now()
last_month = today - pd.DateOffset(months=1)
df_month = df[df['Date'] >= last_month]

# Calculate statistics
report = {
    'Total Revenue': df_month['Revenue'].sum(),
    'Average': df_month['Revenue'].mean(),
    'Number of Sales': len(df_month),
    'Best Product': df_month.groupby('Product')['Revenue'].sum().idxmax(),
    'Top Customer': df_month.groupby('Customer')['Revenue'].sum().idxmax()
}

# As DataFrame for Excel
report_df = pd.DataFrame([report])
report_df.to_excel('monthly_report.xlsx', index=False)

print("Monthly report created!")
for key, value in report.items():
    print(f"{key}: {value}")

# Load only required columns
df = pd.read_excel('large_file.xlsx', usecols=['Date', 'Revenue'])

# Or: Read only sample
df = pd.read_excel('large_file.xlsx', nrows=10000)

# Check for NaN values
print(df.isnull().sum())

# Remove or replace NaN
df_clean = df.dropna()  # Remove rows with NaN
df['Revenue'].fillna(0, inplace=True)  # Replace NaN with 0

# Parse date with format
df['Date'] = pd.to_datetime(df['Date'], format='%m/%d/%Y')

# Handle different date formats
df['Date'] = pd.to_datetime(df['Date'], format='%d.%m.%Y')