उन्नत डेटा वर्कफ़्लो के लिए एक्सेल और पायथन को संयोजित करने के 5 शक्तिशाली तरीके

1. गंदे एक्सेल डेटा को साफ़ और मानकीकृत करें (बार-बार)

• राजस्व =इकाइयाँ * इकाई मूल्य
• नेटरेवेन्यू =राजस्व * (1 - डिस्काउंटपीसीटी)

import pandas as pd
file_path = "SalesData.xlsx"
df = pd.read_excel(file_path, sheet_name="Sales Data")
# Clean types
df["Units"] = pd.to_numeric(df["Units"], errors="coerce").fillna(0).astype(int)
df["UnitPrice"] = pd.to_numeric(df["UnitPrice"], errors="coerce").fillna(0.0)
df["DiscountPct"] = pd.to_numeric(df["DiscountPct"], errors="coerce").fillna(0.0)
df["Returned"] = (
 df["Returned"].astype(str).str.strip().str.lower()
 .map({"yes": True, "no": False})
 .fillna(False)
)
# Add calculated fields
df["Revenue"] = df["Units"] * df["UnitPrice"]
df["NetRevenue"] = df["Revenue"] * (1 - df["DiscountPct"])
# Write back into the SAME file as a NEW sheet
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
 df.to_excel(writer, sheet_name="CleanData", index=False)
print("Saved CleanData sheet inside:", file_path)

2. स्वचालित रूप से सारांश बनाएं (दोहराने योग्य रिपोर्ट)

import pandas as pd
file_path = "SalesData.xlsx"
clean_sheet = "CleanData"
out_sheet = "Summary"
df = pd.read_excel(file_path, sheet_name=clean_sheet)
summary = (
 df.groupby(["Region", "Category"], as_index=False)
 .agg(
 Orders=("OrderID", "count"),
 Units=("Units", "sum"),
 NetRevenue=("NetRevenue", "sum"),
 Returns=("Returned", "sum")
 )
)
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
 summary.to_excel(writer, sheet_name=out_sheet, index=False)
print(f"✅ Saved '{out_sheet}' sheet inside: {file_path}")

3. एक्सेल डेटा से चार्ट बनाएं (मैन्युअल फ़ॉर्मेटिंग के बिना)

import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
file_path = "SalesData.xlsx"
source_sheet = "CleanData"
output_sheet = "RegionChart" # data + chart in this one sheet
# Prepare chart data (NetRevenue by Region)
df = pd.read_excel(file_path, sheet_name=source_sheet)
chart_data = (
 df.groupby("Region", as_index=False)["NetRevenue"]
 .sum()
 .sort_values("NetRevenue", ascending=False)
)
# Write chart data into output_sheet (same workbook)
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
 chart_data.to_excel(writer, sheet_name=output_sheet, index=False)
# Add the native Excel chart on the same sheet
wb = load_workbook(file_path)
ws = wb[output_sheet]
chart = BarChart()
chart.title = "Net Revenue by Region"
chart.y_axis.title = "Net Revenue"
chart.x_axis.title = "Region"
values = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
labels = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
chart.add_data(values, titles_from_data=True)
chart.set_categories(labels)
ws.add_chart(chart, "D2") # place chart to the right of the data table
wb.save(file_path)
print(f"✅ Chart Created: {output_sheet}")

4. कई एक्सेल फ़ाइलों को एक मास्टर टेबल में मर्ज करें

import pandas as pd
from pathlib import Path
base_folder = Path(__file__).resolve().parent
folder = base_folder / "Weekly Reports"
files = sorted(folder.glob("*.xlsx"))
files = [f for f in files if not f.name.startswith("~$")] # ignore Excel lock files
print("Looking in:", folder)
print("Files found:", [f.name for f in files])
frames = []
for f in files:
 temp = pd.read_excel(f)
 temp["SourceFile"] = f.name
 frames.append(temp)
master = pd.concat(frames, ignore_index=True)
master.to_excel(base_folder / "master_report.xlsx", index=False)
print("Saved: master_report.xlsx")

5. भविष्यवाणी करें कि एक्सेल आसानी से कुछ नहीं कर सकता (मशीन लर्निंग उदाहरण)

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import OneHotEncoder
from sklearn.compose import ColumnTransformer
from sklearn.pipeline import Pipeline
from sklearn.linear_model import LogisticRegression
 
file_path = "SalesData.xlsx"
df = pd.read_excel(file_path, sheet_name="CleanData")
 
X = df[["Region", "SalesRep", "Category", "Units", "UnitPrice", "DiscountPct"]]
y = df["Returned"].astype(int)
 
cat_cols = ["Region", "SalesRep", "Category"]
num_cols = ["Units", "UnitPrice", "DiscountPct"]
 
preprocess = ColumnTransformer(
 transformers=[
 ("cat", OneHotEncoder(handle_unknown="ignore"), cat_cols),
 ("num", "passthrough", num_cols),
 ]
)
 
model = Pipeline(steps=[
 ("prep", preprocess),
 ("clf", LogisticRegression(max_iter=1000))
])
 
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)
model.fit(X_train, y_train)
 
# Predict probability of return for all rows
df["ReturnProb"] = model.predict_proba(X)[:, 1]
 
with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
 df.to_excel(writer, sheet_name="WithReturnRisk", index=False)

एक्सेल में पायथन (यदि आपके एक्सेल में उपलब्ध है)

• एक्सेल में, अपना डेटासेट दर्ज करें
• खाली सेल पर क्लिक करें
• सूत्र पर जाएं टैब>> पायथन डालें चुनें
• पायथन स्क्रिप्ट चिपकाएँ

import pandas as pd
# Read the Excel range A1:J21 (including headers)
df = xl("A1:J21", headers=True)
 
# Clean text columns
for col in ["Region", "SalesRep"]:
 df[col] = df[col].astype(str).str.strip().str.title()
 
# Fix data types
df["OrderDate"] = pd.to_datetime(df["OrderDate"], errors="coerce")
df["Units"] = pd.to_numeric(df["Units"], errors="coerce").fillna(0).astype(int)
df["UnitPrice"] = pd.to_numeric(df["UnitPrice"], errors="coerce").fillna(0.0)
 
# Add a calculated column
df["Revenue"] = df["Units"] * df["UnitPrice"]
 
df

• डेटा डालें पर क्लिक करें डेटाफ़्रेम से>> डेटाटाइप कार्ड दिखाएं चुनें तालिका का पूर्वावलोकन करने के लिए

• arrayPreview चुनें टेबल को Excel में लाने के लिए
• अब आपके पास मानकीकृत पाठ और एक नया राजस्व है स्तंभ

निष्कर्ष