Ứng dụng AI trong Phân tích Dữ liệu

df.info()
df.isna().mean().round(3)
df["region"].value_counts(dropna=False)
df.duplicated("order_id").sum()

SELECT COUNT(*)                 AS n_rows,
       COUNT(DISTINCT order_id) AS n_orders,
       SUM(CASE WHEN region IS NULL
                THEN 1 ELSE 0 END) AS missing_region
FROM raw_orders;

= Table.Profile(Source)

region_map = {"HN": "Hà Nội", "Ha Noi": "Hà Nội",
              "hcm": "TP.HCM", "TP HCM": "TP.HCM"}
df["region"] = df["region"].replace(region_map)
df["order_date"] = pd.to_datetime(
    df["order_date"], format="mixed", dayfirst=True)

SELECT o.order_id,
       COALESCE(m.clean_region, o.region) AS region
FROM raw_orders o
LEFT JOIN region_map m
       ON o.region = m.raw_region;

let
  Mapped = Table.ReplaceValue(Source, "HN", "Hà Nội",
             Replacer.ReplaceValue, {"region"}),
  Typed  = Table.TransformColumnTypes(Mapped,
             {{"order_date", type date}}, "vi-VN")
in Typed

before = len(df)
df = df.drop_duplicates(subset="order_id", keep="first")
log.append(("dedup", before - len(df)))

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (
           PARTITION BY order_id
           ORDER BY order_date) AS rn
  FROM orders_std
) t
WHERE rn = 1;

= Table.Distinct(Typed, {"order_id"})

df["flag_missing_region"] = df["region"].isna()
df["region"] = df["region"].fillna("Chưa rõ")
q1, q3 = df["revenue"].quantile([0.25, 0.75])
df["flag_outlier"] = df["revenue"] > q3 + 1.5 * (q3 - q1)

SELECT *,
       region IS NULL               AS flag_missing_region,
       COALESCE(region, 'Chưa rõ')  AS region_clean
FROM orders_dedup;

= Table.AddColumn(Dedup, "flag_missing_region",
    each [region] = null, type logical)

assert df["order_id"].is_unique
assert df["order_date"].notna().all()
assert (df["revenue"] > 0).all()
assert (raw.drop_duplicates("order_id")["revenue"].sum()
        == df["revenue"].sum())

SELECT COUNT(*) - COUNT(DISTINCT order_id) AS dup_left,
       SUM(CASE WHEN revenue <= 0
                THEN 1 ELSE 0 END)       AS bad_revenue
FROM orders_clean;   -- kỳ vọng: 0, 0

= Table.RowCount(Flagged)
    = List.Count(List.Distinct(Flagged[order_id]))

def clean_orders(raw: pd.DataFrame):
    log = []
    df = standardize(raw, log)
    df = dedup(df, log)
    df = flag_quality(df, log)
    validate(raw, df)
    return df, log

CREATE VIEW orders_clean AS
SELECT ...   -- gộp các bước trên
FROM raw_orders;

fxCleanOrders = (Source as table) as table =>
  let
    ...      // các bước chuẩn hoá, lọc trùng, gắn cờ
  in Flagged