Detailed steps for Python to analyze and process excel file data

pip install pandas openpyxl xlrd

import pandas as pd
 
# Read the entire worksheetdf = pd.read_excel('sales_data.xlsx')
 
# Show the first 5 rows of dataprint(())
 
# Read a specific worksheet (if there are multiple worksheets)# df = pd.read_excel('sales_data.xlsx', sheet_name='Sheet1')
 
# Read a specific column# df = pd.read_excel('sales_data.xlsx', usecols=['date', 'product', 'Sales'])

from openpyxl import load_workbook
 
# Load the workbookwb = load_workbook('sales_data.xlsx')
 
# Get the active worksheet or specify the worksheetsheet =   # or wb['Sheet1'] 
# Read datadata = []
for row in sheet.iter_rows(values_only=True):
    (row)
 
# Convert to DataFrame (optional)import pandas as pd
df = (data[1:], columns=data[0])  # Assume that the first line is the title

# View basic data informationprint(())
 
# View statistics summaryprint(())
 
# View column namesprint(())

# Filter data for specific datesjan_data = df[df['date'] == '2023-01-01']
 
# Filter products with sales volume greater than 5high_sales = df[df['Sales'] > 5]
 
# Filter multiple criteriafiltered_data = df[(df['date'] >= '2023-01-01') & (df['product'] == 'Product A')]

# Total sales and total sales by product groupingproduct_stats = ('product').agg({
    'Sales': 'sum',
    'Sales': 'sum'
}).reset_index()
 
print(product_stats)
 
# Calculate the sum of daily salesdaily_sales = ('date')['Sales'].sum().reset_index()

# Sort by sales in descending ordersorted_data = df.sort_values('Sales', ascending=False)
 
# Sort by date and salessorted_data = df.sort_values(['date', 'Sales'], ascending=[True, False])

import  as plt
 
# Set Chinese fonts (avoid Chinese display problems)['-serif'] = ['SimHei']
['axes.unicode_minus'] = False
 
# Draw a bar chart - Total sales of each productproduct_stats.plot(kind='bar', x='product', y='Sales', title='Total sales of each product')
('Sales')
()
 
# Draw a line chart - Daily sales trendsdaily_sales.plot(kind='line', x='date', y='Sales', title='Daily Sales Trend')
('date')
('Sales')
(rotation=45)
plt.tight_layout()
()

pip install seaborn

import seaborn as sns
 
# Set style(style="whitegrid")
 
# Draw a box line chart - sales distribution of each product(figsize=(10, 6))
(x='product', y='Sales', data=df)
('Sales distribution of each product')
()
 
# Drawing heat maps - Correlation analysiscorr_matrix = df[['Sales', 'unit price', 'Sales']].corr()
(corr_matrix, annot=True, cmap='coolwarm')
('Variable correlation heat map')
()

# Check for missing valuesprint(().sum())
 
# Fill in missing valuesdf_filled = ({'Sales': 0, 'unit price': df['unit price'].mean()})
 
# Delete rows containing missing valuesdf_dropped = ()

# Convert date formatdf['date'] = pd.to_datetime(df['date'])
 
# Convert numerical typesdf['Sales'] = pd.to_numeric(df['Sales'], errors='coerce')
df['unit price'] = pd.to_numeric(df['unit price'], errors='coerce')
df['Sales'] = pd.to_numeric(df['Sales'], errors='coerce')

from  import StandardScaler
 
# Select the column that needs to be standardizedfeatures = df[['Sales', 'unit price', 'Sales']]
 
# Standardized processingscaler = StandardScaler()
scaled_features = scaler.fit_transform(features)
 
# Convert back to DataFramescaled_df = (scaled_features, columns=)

# Make sure the date is of datetime typedf['date'] = pd.to_datetime(df['date'])
 
# Set date to indexdf.set_index('date', inplace=True)
 
# Aggregate sales by weekweekly_sales = ('W')['Sales'].sum()
 
# Moving Averagedf['7-day moving average sales'] = df['Sales'].rolling(window=7).mean()

# Calculate the correlation matrixcorr_matrix = df[['Sales', 'unit price', 'Sales']].corr()
 
# Visualize Relevanceimport seaborn as sns
import  as plt
 
(figsize=(8, 6))
(corr_matrix, annot=True, cmap='coolwarm', center=0)
('Variable correlation heat map')
()

# Grouping aggregation using groupbygrouped = (['product', 'date']).agg({
    'Sales': 'sum',
    'Sales': 'sum'
}).reset_index()
 
# Create a perspective tablepivot_table = df.pivot_table(
    values='Sales',
    index='date',
    columns='product',
    aggfunc='sum',
    fill_value=0
)
 
print(pivot_table)

import pandas as pd
import  as plt
import seaborn as sns
from datetime import datetime
 
# 1. Read datadf = pd.read_excel('sales_data.xlsx')
 
# 2. Data Cleaningdf['date'] = pd.to_datetime(df['date'])
df['Sales'] = pd.to_numeric(df['Sales'], errors='coerce').fillna(0)
df['unit price'] = pd.to_numeric(df['unit price'], errors='coerce').fillna(df['unit price'].mean())
df['Sales'] = pd.to_numeric(df['Sales'], errors='coerce').fillna(0)
 
# 3. Basic statisticsprint("Basic Statistics:")
print(())
 
# 4. Group statistics by productproduct_stats = ('product').agg({
    'Sales': 'sum',
    'Sales': 'sum',
    'unit price': 'mean'
}).sort_values('Sales', ascending=False)
 
print("\nSales of each product:")
print(product_stats)
 
# 5. Time Series Analysisdf.set_index('date', inplace=True)
daily_sales = ('D')['Sales'].sum()
 
# 6. Visualization(figsize=(15, 10))
 
# Daily sales trends(2, 2, 1)
daily_sales.plot(title='Daily Sales Trend')
('Sales')
 
# Sales comparison of each product(2, 2, 2)
product_stats['Sales'].plot(kind='bar', title='Total sales of each product')
('Sales')
 
# Sales volume and unit price relationship(2, 2, 3)
(data=df, x='unit price', y='Sales', hue='product')
('Sales volume and unit price relationship')
('unit price')
('Sales')
 
# Product sales share(2, 2, 4)
product_stats['Sales'].plot(kind='pie', autopct='%1.1f%%', startangle=90)
('Product sales share')
('')  # Remove the default ylabel 
plt.tight_layout()
()

df = pd.read_excel('large_file.xlsx', usecols=['date', 'product', 'Sales'])

chunk_size = 10000
chunks = pd.read_excel('very_large_file.xlsx', chunksize=chunk_size)
 
for chunk in chunks:
    process(chunk)  # Process each block of data

import  as dd
 
# Use Dask to process large datasetsddf = dd.read_excel('large_file.xlsx')
result = ('product').Sales.sum().compute()

['-serif'] = ['SimHei']  # Set Chinese fonts['axes.unicode_minus'] = False    # Solve the problem of displaying minus signs

# Try multiple date format parsingdf['date'] = pd.to_datetime(df['date'], errors='coerce', format='%Y-%m-%d')
df['date'] = pd.to_datetime(df['date'], errors='coerce', format='%d/%m/%Y')
df['date'].fillna(pd.to_datetime('1900-01-01'), inplace=True)  # Processing unresolved dates