How Excel Helps You Land a Data Engineer Job Faster

# pip install pandas openpyxl
import pandas as pd

df = pd.DataFrame({
    "customer_id": [1, 2, 2, None],
    "city": ["Noida", "Delhi", "Delhi", "noida "],
})

# Profile the FULL data in code...
summary = pd.DataFrame({
    "dtype": df.dtypes.astype(str),
    "nulls": df.isna().sum(),
    "distinct": df.nunique(),
})
print(summary)

# ...and send a small SAMPLE to Excel for human review
sample = df.sample(n=min(len(df), 1000), random_state=42)
sample.to_excel("sample_for_review.xlsx", index=False)
summary.to_excel("profile_summary.xlsx")

$ python3 profile_to_excel.py
             dtype  nulls  distinct
customer_id  float64     1         2
city         object      0         3

# (newer pandas versions may show 'str' instead of 'object')

# Opening the sample in Excel and filtering 'city'
# immediately shows "noida " with a trailing space
# and lower case - a cleaning rule to add in code:
#   df["city"] = df["city"].str.strip().str.title()

# reconcile.py - compare source and warehouse totals, write an Excel file
import pandas as pd

source = pd.DataFrame({
    "order_date": ["2026-03-01", "2026-03-02", "2026-03-03"],
    "source_rows": [120, 98, 143],
    "source_amount": [54000, 41200, 66100],
})
warehouse = pd.DataFrame({
    "order_date": ["2026-03-01", "2026-03-02", "2026-03-03"],
    "wh_rows": [120, 97, 143],
    "wh_amount": [54000, 40750, 66100],
})

recon = source.merge(warehouse, on="order_date", how="outer")
recon["row_diff"] = recon["source_rows"] - recon["wh_rows"]
recon["amount_diff"] = recon["source_amount"] - recon["wh_amount"]
recon["status"] = recon.apply(
    lambda r: "OK" if r.row_diff == 0 and r.amount_diff == 0 else "CHECK", axis=1
)

with pd.ExcelWriter("reconciliation.xlsx", engine="openpyxl") as writer:
    recon.to_excel(writer, sheet_name="Daily recon", index=False)
    recon[recon["status"] == "CHECK"].to_excel(writer, sheet_name="Exceptions", index=False)

print(recon[["order_date", "row_diff", "amount_diff", "status"]])

$ python3 reconcile.py
   order_date  row_diff  amount_diff status
0  2026-03-01         0            0     OK
1  2026-03-02         1          450  CHECK
2  2026-03-03         0            0     OK

# reconciliation.xlsx now has two sheets:
#   Daily recon - every date, for sign-off
#   Exceptions  - only dates that need investigation

# Next step in code (not Excel): find the missing
# row for 2026-03-02 with a LEFT JOIN on order_id.

// Power Query (M) - prototype cleaning steps in Excel
// Data > Get Data > From Text/CSV, then Advanced Editor
let
    Source   = Csv.Document(File.Contents("C:\data\orders.csv"),
                 [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed    = Table.TransformColumnTypes(Promoted, {
                 {"order_id", Int64.Type},
                 {"amount", type number},
                 {"order_date", type date}}),
    NoBlanks = Table.SelectRows(Typed, each [amount] <> null),
    Deduped  = Table.Distinct(NoBlanks, {"order_id"})
in
    Deduped

// Each step name becomes a rule to re-implement in the
// real pipeline: types, drop blank amounts, dedupe on id.

# load_mapping.py - validate a business-maintained Excel mapping
import pandas as pd

REQUIRED = ["source_code", "region"]

def load_mapping(path):
    df = pd.read_excel(path, sheet_name="mapping", dtype=str)
    df.columns = df.columns.str.strip().str.lower()

    missing = [c for c in REQUIRED if c not in df.columns]
    if missing:
        raise ValueError(f"Missing columns: {missing}")

    df = df[REQUIRED].apply(lambda col: col.str.strip())
    problems = []
    if df["source_code"].duplicated().any():
        dupes = df.loc[df["source_code"].duplicated(), "source_code"].tolist()
        problems.append(f"Duplicate codes: {dupes}")
    if df["region"].isna().any():
        problems.append(f"{df['region'].isna().sum()} rows with blank region")
    if problems:
        raise ValueError("; ".join(problems))
    return df

if __name__ == "__main__":
    try:
        mapping = load_mapping("region_mapping.xlsx")
        print(f"Mapping OK: {len(mapping)} rows")
    except ValueError as e:
        print("Rejected mapping file:", e)

# Rejected mapping file: Duplicate codes: ['DEL']; 1 rows with blank region

RECONCILIATION WORKBOOK LAYOUT

Sheet 'Summary'
  A: Check name     B: Source   C: Target   D: Diff   E: Status
  Row count         =Source!B1  =Target!B1  =B2-C2    =IF(D2=0,"OK","CHECK")
  Total amount      ...         ...         ...       ...

Sheet 'Daily recon'   (exported from reconcile.py)
Sheet 'Exceptions'    (only CHECK rows)
Sheet 'About'
  Source system: ________   Target table: ________
  Extract time:  ________   Query / script: ______
  Prepared by:   ________   Reviewed by:   ______

INCOMING SPREADSHEET VALIDATION CHECKLIST

[ ] Expected sheet name present
[ ] Required columns present (case/space-insensitive)
[ ] Values read as text where codes have leading zeros
[ ] Leading/trailing spaces stripped
[ ] No duplicate keys
[ ] No blank required values
[ ] Dates parsed correctly (check DD/MM vs MM/DD)
[ ] No merged cells or hidden header rows
[ ] Row count within expected range
[ ] File rejected with a clear message if any check fails

HANDOFF NOTE (send with the workbook)

Subject: Orders migration - reconciliation for 1-3 March

Hi team,

Attached is the reconciliation between the source system
and the new warehouse table for 1-3 March.

- 2 of 3 days match exactly on row count and amount.
- 2 March differs by 1 row; see the 'Exceptions' sheet.
  We are tracing it and will share the cause.
- Figures come from the automated check that runs after
  every load; this workbook is a snapshot of it.

Please reply with any questions or with sign-off.