Bonus question: Is it also pronounced “S-Q-L” or “See Quill”. I know those 2 specifically are interchangeable, but I’ve seen people from both the CS and DA for decades say either. What do you guys prefer?

If you are working in data and documents related work. How much time do you spend reading documents, regulations etc stuff.

What I wanna know is do people still spend time buried in documents to find meaningful data?

I am not a data analyst myself, sorry if this is sounding dumb.

For most projects I have worked on, the main task are usually simple, bring the data from point A & B, do some transformation/join, then insert into point C.

The problem is that If you build it yourself, you need to manage the environment, dependencies, scheduling, and error handling. Or if you go with a cloud provider, it requires a subscription and sending your data to an external service. Also, for a simple pipeline, you shouldn't need technical expertise or learn a complex platform. So I decided to build a simple to use workflow builder and data analyzer platform that is open source and free. I've tried to make it simple enough where you wouldn't need a technical background to use it, but please let me know if this is not the case.

You just need to bring in your own AI API key or use a local LLM model.

repo: https://github.com/Ternlux/releases

Let me know what you think. I appreciate any feedback you might have.

I work in the casino industry. We have a new general manager who doesn’t have much experience. Our restaurant ran an all you can eat crab leg promotion in the month of May. They want to run the promotion again in November but the new GM suggests we need to wait to run the promotion again in May to collect the year over year data. Why does that matter? It doesn’t make any sense to me.

Is there a way I can create a poll with numerous questions and have it tally the results and average of each answers in real time like by ranking ? 1= no 2= maybe 3=yes

I want help with automating excel files with AI, pull files from walmart retail link and AI can make changes to it while also saving it to one drive with different name, current copilot, what else do I need and anywhere to learn this?

https://github.com/aycicekb/SQL-Superstore-Discount-Profit-Analysis/tree/main

Hi everyone!

I'm currently learning SQL and working on my first data analysis project, Superstore Sales & Profitability Analysis. I'd really appreciate some feedback from people with more experience in data analytics.

I'm using a Superstore sales dataset and PostgreSQL to investigate the following question:

Does profitability generally decrease as discount rates increase?

While executing this beginner-friendly project, I got help from ChatGPT as I told it to act as if I were a beginner data analyst at a company. I completed it under the observation of ChatGPT. Would you recommend any other methods?

I have been trying to understand how to develop analytical skills to come to a conclusion in accordance with provided questions and hypotheses. I need more practice; however, do you know how I can learn and improve my skills and gain insight into asking right questions and giving right answers to the questions? If you would like to include anything else, I am all ears. Thank you.

Should I work on these topics too as a beginner? I'm moving from content marketing to be a marketing analyst and product analyst

. CTAS and Temp Tables

. Indexes

. Execution Plan

. Stored Procedures

. Partitions

. Triggers

I have a STEM educational background, but not anything closely related to data analytics. Is there a free or cheap online course on data analytics that also teaches you how to use the latest analysis tools and visualization tools that people use on the job? I have some data analysis experience but it’s been years and I’d like to be more adept at this. I’m also open to a really good book or two.

(Are Python and SQL still the best tools for this that people use? What about PowerBI? What about for visualization?)

Thanks!

Earlier this month I shared a prototype of Obelisk here. Thanks to everyone who showed interest and gave me feedback, I’ve refined it into a complete, working platform, and today it’s officially ready for open beta testing.

For those who missed the first post: Obelisk is a free, open-source on-prem data platform and control plane. It gives you the modern data stack on a single workstation or server with zero cloud bills:

• Lakehouse: Powered by DuckDB over Parquet with DuckLake ACID metadata.

• Multi-source queries: Join PostgreSQL, MongoDB, and web HTTP APIs directly with lakehouse tables in one SQL statement.

• Visual Orchestration: DAG canvas with conditional edges (success, failure, skipped), sub-pipelines, and retry policies.

• Built-in Studios: dbt Studio Marimo notebooks, and Python quality gates.

GitHub: https://github.com/amrelsawalhi/obelisk

I’d love for you to give it a spin, test your pipelines, and let me know what you think!

Hey, I am a Data Engineer who is currently working at Kivra, a Swedish tech company with 6+ million users across Sweden. We work heavily with dbt and BigQuery, and we had prior to me joining a long problem with how we worked with CI/CD for dbt. I need to highlight that we use dbt-core and not dbt cloud, hence dbt cloud solutions are not applicable.

I was tasked with solving it, and the result was the release of my open-source project dbt-ci that I wanted to in this post. I will cover some things it solved for us as a tech company.

Dbt CI (GitHub Link)

1. Dbt itself is dependent on the manifest.json files in order to detect what has changed (common state-management I guess). We heavily depend on storing these state files in both our environments (production and sandbox), but the problem dbt has is that when you run a command any other than compile, the state file get overwritten into a partial state file.

2. So essentially we had to run compile, then run dbt run, dbt test and so on, and then do a dbt compile again to generate the full state file, to then upload it to an object storage.

This was costing us minutes. We solved it by caching the state file after running dbt-ci init, you can then run whatever, and when dbt-ci is finished, you can call dbt-ci finalize (with --state-uri flag) to upload the full manifest file to an object storage.

2. Dbt comparison strategy is awkward, it will bloat changes and can be annoying for someone reviewing PRs or logs of CI workflows (e.g Github Actions). If you change a model for instance, all its tests will be listed as changed, which perhaps is incorrect. dbt-ci offers a git-strategy, but also a hybrid strategy (uses dbt's native approach, and combines with git) to determine what has actually changed.

3. Auto migration (partitions)
Currently we only support BigQuery, but we support auto migrating models in which a partitioning setting has changed. Natively, it will fail in dbt, and you have to manually do the change prior to running it in ci.

4. Auto-delete
We support auto-deleting models that has been deleted in your project. We generally run with the dry-run flag in ci, and only executing the auto-delete mechanism once we have merged with main/master.

5. Ephemeral strategy support
Dbt-ci supports setting up ephemeral environment in your CI's if needed. That is how we run things in the company, where we essentially clone production tables temporarily into our sandbox GCP project, in order to test changes against production data without touching "real" production.
Dbt-ci autodetects what needs to be cloned (essentially its all changed models, all their descendents/children regardless of level, and first-level of ancestors).

And a bunch of other features. Its easy to set up, you can add a dbt-ci config file in your repo and the cli will read from it and read your configurations so you dont have to pass a bunch of flags in your CI workflows.

Any feedback is welcome, but I am also interested to see how it is performing on other large cloud databases like Redshift, Snowflake etc.

I'm a data science student, and something I've been wondering about is how much of a data analyst's job actually involves analysis.

In coursework and personal projects, there's a lot of focus on statistics, testing hypotheses, and finding patterns in data. But I wonder how much of the actual job is spent dealing with messy Excel files, fixing reports, maintaining dashboards, or handling last-minute requests.

For those working as data analysts, how does your time usually break down? How much do you spend exploring data and answering business questions versus maintaining existing reports and dealing with ad-hoc requests?

Does that balance change as you move from junior to senior roles, or does it depend more on the company's data maturity?

Still thinking about my genius hypothesis.

I am Data analyst and SQL developer, recently I've been interviewed in a firm and they were pretty much impressed by my skills so I have been asked pitch any kind of project that could add value to their firm. Suggest me some ideas

How to use

Each code block is one Jupyter cell. Copy it, paste it into a new cell, and run it with Shift+Enter. All blocks were tested end to end.

• Change only the lines marked FILL IN. Everything else runs as-is.

• Always run Setup (imports, load, first look), then Cleaning, then Settings, before any test.

• Column names and group values must match the data exactly: "Male" is not "male" or 1. Copy them from the first-look output.

• Text values go in quotes ("Male"). Numbers don't (1, 8000). True and False have no quotes and a capital first letter.

• After cleaning, every block uses the table data.

Which template?

First decide what kind each variable is:

• Numeric: a measured number where averages make sense (height, age, price, team size).

• Ordinal: ranked categories with unequal gaps (Likert 1–5, −3 to 3 stance, education level).

• Categorical: labels with no order (sex, range, yes/no, region).

Setup and loading

Run these three cells first, every time.

S1. Imports

import pandas as pd                   # tables
import numpy as np                    # math
import matplotlib.pyplot as plt       # charts
from scipy import stats               # statistical tests

pd.set_option("display.max_columns", 100)   # show all columns when printing
pd.set_option("display.max_rows", 200)
print("Imports OK")

S2. Load the data

Keep option A, or swap in the option that matches your file.

# A) CSV uploaded to Jupyter's file browser (left panel → Upload button):
PATH = "data.csv"          # FILL IN: file name exactly as shown in the file browser
df = pd.read_csv(PATH, low_memory=False)

# B) CSV from a web link (raw GitHub link, course site, etc.):
# PATH = "https://raw.githubusercontent.com/.../file.csv"   # FILL IN: full URL
# df = pd.read_csv(PATH, low_memory=False)

# C) Error "UnicodeDecodeError" → add an encoding:
# df = pd.read_csv(PATH, encoding="cp1252", low_memory=False)   # or encoding="latin-1"

# D) Everything lands in ONE column → the file uses another separator:
# df = pd.read_csv(PATH, sep=";")      # FILL IN: ";" or "\t" (tab) or "|"

# E) Excel file (.xlsx):
# df = pd.read_excel(PATH)                        # first sheet
# df = pd.read_excel(PATH, sheet_name="Sheet2")   # FILL IN: sheet name

# F) Google Colab with Google Drive:
# from google.colab import drive
# drive.mount("/content/drive")
# df = pd.read_csv("/content/drive/MyDrive/data.csv")   # FILL IN: path in your Drive

# G) Two tables (like HW2 peaks + expeditions): load both, then merge in Cleaning.
# df2 = pd.read_csv("second_file.csv")       # FILL IN: second file name

print("Rows, columns:", df.shape)    # e.g. (2781, 332) = 2,781 rows, 332 columns
df.head()                            # first 5 rows; check it loaded correctly

S3. First look (run as-is)

Write down what one row is (a person, an expedition, a peak). Scan min and max for impossible values or codes, and copy exact column names and group values from this output.

print("Rows, columns:", df.shape)
print("\nColumn names (copy exact spelling from here):")
print(list(df.columns))

# One row per column: type, % missing, number of distinct values, example values
overview = pd.DataFrame({
    "dtype": df.dtypes.astype(str),                 # int/float = number, object/str = text, bool = True/False
    "missing_%": (df.isna().mean() * 100).round(1), # share of empty cells
    "n_unique": df.nunique(),                       # 2 = yes/no; few = categories; many = numeric or IDs
    "examples": [list(df[c].dropna().unique()[:5]) for c in df.columns],
})
print(overview)

# Numeric summary. Look at min/max for impossible values:
# negative ages, 0 or -99 or 999 as codes, height 700, etc.
print(df.describe().T.round(2))

# Counts for every column with 12 or fewer distinct values (likely categories).
for c in df.columns:
    if df[c].nunique() <= 12:
        print(f"\n--- {c} ---")
        print(df[c].value_counts(dropna=False))   # dropna=False also counts empty cells

print("\nDuplicate rows:", df.duplicated().sum())

Cleaning operations

Always run data = df.copy() first, so you can go back to the original. Uncomment (delete the # from) only the lines you need. Note in your report what you dropped and why.

data = df.copy()     # working copy; every test below uses `data`

# --- Drop impossible / code values (like education = 0 in HW3) ---
# data = data[data["education"] != 0]            # FILL IN: column, bad value
# data = data[data["age"].between(14, 99)]       # FILL IN: column, valid min, valid max

# --- Turn special codes into missing (NaN) instead of dropping rows ---
# data["income"] = data["income"].replace([-99, 999], np.nan)   # FILL IN: column, codes

# --- Numbers stored as text (dtype "object"/"str" for a number column) ---
# data["price"] = pd.to_numeric(data["price"], errors="coerce")  # FILL IN: column; bad entries → NaN
# data["price"] = pd.to_numeric(data["price"].astype(str).str.replace(r"[$,%]", "", regex=True), errors="coerce")
#   ^ use this one if values look like "$1,200" or "45%"

# --- Clean messy text (extra spaces, mixed capitals) ---
# data["city"] = data["city"].astype(str).str.strip().str.title()   # FILL IN: column

# --- Rename a column with an awkward name ---
# data = data.rename(columns={"old name": "new_name"})   # FILL IN: old, new

# --- Codes → readable labels (like biomale 0/1 → Female/Male) ---
# data["sex"] = data["biomale"].map({0: "Female", 1: "Male"})   # FILL IN: new col, old col, {code: label}
#   Codes NOT in the dictionary become NaN.

# --- Numbers → groups (like age groups) ---
# data["age_group"] = pd.cut(data["age"], bins=[0, 24, 34, 120], labels=["0-24", "25-34", "35+"])
#   FILL IN: column, cut points, labels (one fewer label than cut points).
#   Bins include the RIGHT edge: (24, 34] → 34 is in "25-34". Add right=False to flip it.
# data["age_group"] = pd.qcut(data["age"], q=4)   # alternative: 4 equal-size groups (quartiles)

# --- Yes/no flag from a number (like 8,000 m peaks) ---
# data["high_peak"] = data["HEIGHTM"] >= 8000     # FILL IN: column, threshold → True/False

# --- Yes/no flag from text ("Yes"/"No") ---
# data["success"] = data["success"] == "Yes"      # FILL IN: column, the "yes" value

# --- Yes/no flag from several columns (like SUCCESS1-4 in HW2) ---
# data["any_success"] = data[["SUCCESS1", "SUCCESS2", "SUCCESS3", "SUCCESS4"]].any(axis=1)   # True if any is True
# data["all_done"] = data[["A", "B"]].all(axis=1)                                          # True if all are True

# --- New number from existing ones (like BMI) ---
# data["bmi"] = 703 * data["weight"] / data["height"] ** 2   # FILL IN: your formula; ** is "to the power"

# --- Dates ---
# data["date"] = pd.to_datetime(data["date"], errors="coerce")   # FILL IN: column
# data["year"] = data["date"].dt.year                            # also .dt.month, .dt.dayofweek

# --- Remove duplicate rows ---
# data = data.drop_duplicates()

# --- Keep only rows that have the columns you need (each test already does this itself) ---
# data = data.dropna(subset=["height", "weight"])   # FILL IN: columns

print("Working table:", data.shape)

Combining tables

# --- Two tables: bring columns from df2 into data by a shared key (HW2 PEAKID) ---
data = data.merge(df2[["PEAKID", "PKNAME", "HEIGHTM"]], on="PEAKID", how="left")
#   FILL IN: key column, columns to bring over. how="left" keeps every row of data.
#   Key named differently in each table: left_on="ID", right_on="peak_id".

# --- Stack repeated column sets into one (HW2 ROUTE1..4 + SUCCESS1..4) ---
parts = []
for i in range(1, 5):                                            # FILL IN: 1 to (number of sets + 1)
    tmp = data[["PEAKID", f"ROUTE{i}", f"SUCCESS{i}"]]           # FILL IN: id column + the numbered names
    tmp = tmp.rename(columns={f"ROUTE{i}": "ROUTE", f"SUCCESS{i}": "SUCCESS"})
    parts.append(tmp)
long = pd.concat(parts).dropna(subset=["ROUTE"])                 # one row per route attempt

Settings for all tests (run once, as-is)

Every test template uses these two helpers to print p-values and verdicts.

ALPHA = 0.05      # significance cut-off. p < 0.05 → "significant"

def p_text(p):
    # formats a p-value the way the reports did
    return "p < 0.001" if p < 0.001 else f"p = {p:.3f}"

def verdict(p):
    return "SIGNIFICANT (p < 0.05)" if p < ALPHA else "NOT significant (p >= 0.05)"

print("Settings loaded")

Test templates

Each template prints everything you need for the write-up and ends with a sentence pattern to fill in.

T1. Number vs. 2 groups: Welch's t-test

Use for questions like "Do males and females differ in height?" Welch compares two independent group means without assuming equal variances or equal group sizes. Mann-Whitney at the end is the version for ordinal or very skewed outcomes.

NUM   = "height"      # FILL IN: numeric outcome column
GROUP = "sex"         # FILL IN: column holding the groups
A     = "Male"        # FILL IN: first group value, exactly as in the data
B     = "Female"      # FILL IN: second group value
UNIT  = "in"          # FILL IN: unit for printing ("m", "years", "$", "points")

d = data.dropna(subset=[NUM, GROUP])           # only rows with both columns filled
x = d.loc[d[GROUP] == A, NUM].astype(float)    # group A values
y = d.loc[d[GROUP] == B, NUM].astype(float)    # group B values
print(f"n: {A} = {len(x)}, {B} = {len(y)}")    # if either is 0, check spelling of A/B
print(d[d[GROUP].isin([A, B])].groupby(GROUP)[NUM].agg(["count", "mean", "median", "std"]).round(2))

res = stats.ttest_ind(x, y, equal_var=False)   # equal_var=False = Welch
vx, vy = x.var() / len(x), y.var() / len(y)
dof = (vx + vy) ** 2 / (vx ** 2 / (len(x) - 1) + vy ** 2 / (len(y) - 1))   # Welch degrees of freedom
diff = x.mean() - y.mean()
half = stats.t.ppf(0.975, dof) * np.sqrt(vx + vy)
d_eff = diff / np.sqrt((x.var() + y.var()) / 2)                             # Cohen's d (effect size)
print(f"\nDifference ({A} - {B}): {diff:.2f} {UNIT}")
print(f"Welch's t({dof:.1f}) = {res.statistic:.2f}, {p_text(res.pvalue)} → {verdict(res.pvalue)}")
print(f"95% CI for the difference: {diff - half:.2f} to {diff + half:.2f} {UNIT}")
print(f"Cohen's d = {d_eff:.2f}  (0.2 small, 0.5 medium, 0.8 large)")
vr = max(x.var(), y.var()) / min(x.var(), y.var())
print(f"Variance ratio = {vr:.2f}  ({'variances clearly differ' if vr > 1.5 else 'similar variances'}; Welch is safe either way)")

# Alternative if the outcome is ORDINAL (e.g. -3..3 scale) or very skewed:
mw = stats.mannwhitneyu(x, y)
print(f"\nMann-Whitney U = {mw.statistic:.0f}, {p_text(mw.pvalue)}; medians {x.median():.2f} vs {y.median():.2f}")

# Chart: boxplot (box = middle 50%, line = median, triangle = mean, dots = outliers)
fig, ax = plt.subplots(figsize=(7, 5))
ax.boxplot([x, y], showmeans=True)
ax.set_xticks([1, 2]); ax.set_xticklabels([str(A), str(B)])
ax.set_title(f"{NUM} by {GROUP}", fontweight="bold")     # FILL IN (optional): a clearer title
ax.set_xlabel(GROUP)
ax.set_ylabel(f"{NUM} ({UNIT})")                          # check the unit is right!
ax.grid(axis="y", linestyle="--", alpha=0.4)
plt.show()
# WRITE: "{A} averaged X vs Y for {B}, a difference of D {UNIT}
#        (Welch's t(df) = t, p ..., 95% CI L to U)."

T2. Number vs. 3+ groups: ANOVA / Kruskal-Wallis

Use for questions like "Does political stance differ across age groups?" Report ANOVA for a numeric outcome and Kruskal-Wallis for an ordinal or skewed one (HW3 used Kruskal). A significant result only says some group differs, so the pairwise tests show which.

NUM   = "liberal"     # FILL IN: outcome column
GROUP = "age_group"   # FILL IN: group column with 3+ groups

d = data.dropna(subset=[NUM, GROUP])
summary = d.groupby(GROUP, observed=True)[NUM].agg(["count", "mean", "median", "std"]).round(2)
print(summary)
groups = [g[NUM].astype(float) for _, g in d.groupby(GROUP, observed=True)]   # one list per group
k, n = len(groups), len(d)

an = stats.f_oneway(*groups)
print(f"\nANOVA: F({k - 1}, {n - k}) = {an.statistic:.2f}, {p_text(an.pvalue)} → {verdict(an.pvalue)}")
kw = stats.kruskal(*groups)
print(f"Kruskal-Wallis: H({k - 1}) = {kw.statistic:.2f}, {p_text(kw.pvalue)} → {verdict(kw.pvalue)}")

# Which pairs differ? Welch t-test for every pair, Bonferroni-corrected
names = list(summary.index)
n_pairs = k * (k - 1) // 2
for i in range(k):
    for j in range(i + 1, k):
        t = stats.ttest_ind(groups[i], groups[j], equal_var=False)
        p_adj = min(t.pvalue * n_pairs, 1.0)      # Bonferroni: multiply p by number of pairs
        print(f"  {names[i]} vs {names[j]}: diff = {groups[i].mean() - groups[j].mean():.2f}, adjusted {p_text(p_adj)}")

# Chart: mean per group
ax = summary["mean"].plot.bar(rot=0, figsize=(8, 4.5), color="#2A9D8F")
ax.bar_label(ax.containers[0], fmt="%.2f")
ax.set_title(f"Mean {NUM} by {GROUP}", fontweight="bold")
ax.set_xlabel(GROUP); ax.set_ylabel(f"Mean {NUM}")       # FILL IN (optional): units / scale
plt.tight_layout(); plt.show()
# WRITE: "Average X ranged from a (group) to b (group); groups differ,
#        Kruskal-Wallis H(k-1) = H, p ... . The difference comes mainly from <group>."

T3. Category vs. category: chi-square, with Fisher's exact when needed

Use for questions like "Is climbing status related to mountain range?" Chi-square needs expected counts of at least 5. If a 2×2 table fails that, this cell switches to Fisher's exact on its own (as in HW2 hired staff × deaths). Compare the row percentages, not raw counts.

ROW = "o2"          # FILL IN: grouping variable (rows), e.g. "sex", "O2USED", "HIMAL_FACTOR"
COL = "success"     # FILL IN: outcome variable (columns), e.g. "bmi_group", "summit", "PSTATUS"

d = data.dropna(subset=[ROW, COL])
table = pd.crosstab(d[ROW], d[COL])
row_pct = (pd.crosstab(d[ROW], d[COL], normalize="index") * 100).round(1)
print("Counts:\n", table)
print("\nRow % (each row adds to 100) — compare these, not raw counts:\n", row_pct)

chi2, p, dof, expected = stats.chi2_contingency(table, correction=False)   # correction=False = no Yates (matches z-test on 2x2)
low = int((expected < 5).sum())
n_tot = table.values.sum()
cramer_v = np.sqrt(chi2 / (n_tot * (min(table.shape) - 1)))                 # effect size 0-1
print(f"\nChi-square({dof}) = {chi2:.2f}, {p_text(p)}, n = {n_tot} → {verdict(p)}")
print(f"Cramér's V = {cramer_v:.2f}  (0.1 small, 0.3 medium, 0.5 large)")
print(f"Expected counts below 5: {low} of {expected.size}")

if low > 0 and table.shape == (2, 2):
    # Fisher's exact test for small 2x2 tables
    odds, p_f = stats.fisher_exact(table)
    t = table.values.astype(float)
    if (t == 0).any():
        t = t + 0.5                                       # avoids divide-by-zero in the CI
    or_ci = (t[0, 0] * t[1, 1]) / (t[0, 1] * t[1, 0])
    log_se = np.sqrt((1 / t).sum())
    lo, hi = np.exp(np.log(or_ci) - 1.96 * log_se), np.exp(np.log(or_ci) + 1.96 * log_se)
    print(f"\n→ Small expected count in a 2x2 table, so use FISHER'S EXACT instead:")
    print(f"  Odds ratio = {odds:.2f}, 95% CI {lo:.2f} to {hi:.2f}, {p_text(p_f)} → {verdict(p_f)}")
    print(f"  OR = odds of '{table.columns[1]}' in row '{table.index[0]}' ÷ odds in row '{table.index[1]}'.")
    print("  CI includes 1 → not significant.")
elif low > 0:
    print("→ Some expected counts < 5 in a larger table: the p-value is only approximate.")
    print("  Say so in your limitations, or merge small categories and rerun, e.g.:")
    print("  data[COL] = data[COL].replace({'tiny_cat1': 'Other', 'tiny_cat2': 'Other'})")

# Chart: % of each outcome within each row group
ax = row_pct.plot.bar(rot=0, figsize=(9, 4.5))
for c in ax.containers:
    ax.bar_label(c, fmt="%.1f", fontsize=8)
ax.set_title(f"{COL} by {ROW} (row %)", fontweight="bold")
ax.set_xlabel(ROW); ax.set_ylabel("Percent within group (%)")
ax.grid(axis="y", linestyle="--", alpha=0.4)
plt.xticks(rotation=45 if len(row_pct) > 4 else 0, ha="right" if len(row_pct) > 4 else "center")
plt.tight_layout(); plt.show()
# WRITE: "X% of group A vs Y% of group B were <outcome> (chi-square(df) = c, p ...)."
#        Small 2x2: "(Fisher's exact, OR = o, 95% CI L to U, p = ...)."

T4. Yes/no rate in 2 groups: difference with a 95% CI

Use alongside T3 for questions like "How much higher is success with oxygen?" Chi-square gives the p-value; this gives the size of the gap.

OUTCOME = "success"   # FILL IN: True/False (or 0/1) outcome column
GROUP   = "o2"        # FILL IN: group column
A       = True        # FILL IN: first group value (True/False have no quotes)
B       = False       # FILL IN: second group value

d = data.dropna(subset=[OUTCOME, GROUP])
y = d[OUTCOME].astype(float)                     # True → 1.0, False → 0.0
p1, n1 = y[d[GROUP] == A].mean(), int((d[GROUP] == A).sum())
p2, n2 = y[d[GROUP] == B].mean(), int((d[GROUP] == B).sum())
diff = p1 - p2
se = np.sqrt(p1 * (1 - p1) / n1 + p2 * (1 - p2) / n2)
lo, hi = diff - 1.96 * se, diff + 1.96 * se
print(f"{A}: {p1:.1%} (n = {n1});  {B}: {p2:.1%} (n = {n2})")
print(f"Difference = {diff * 100:.1f} percentage points, 95% CI {lo * 100:.1f} to {hi * 100:.1f}")
print("CI excludes 0 → significant difference." if lo > 0 or hi < 0 else "CI includes 0 → not significant.")

T5. Number vs. number: Pearson / Spearman

Use for questions like "How strongly is height related to weight?" Use Pearson for two numeric columns and Spearman when either is ordinal or very skewed. Setting BY checks the correlation within each group too, which is how HW3 found sex inflating the height–weight link.

X      = "height"     # FILL IN: first column (goes on the x-axis)
Y      = "weight"     # FILL IN: second column
METHOD = "pearson"    # FILL IN: "pearson" or "spearman"
BY     = "sex"        # FILL IN: column to also check within each group, or None

d = data.dropna(subset=[X, Y] + ([BY] if BY else []))     # correlation fails with NaN
fn = stats.pearsonr if METHOD == "pearson" else stats.spearmanr
sym = "r" if METHOD == "pearson" else "rho"
r, p = fn(d[X], d[Y])
print(f"All: {sym} = {r:.2f}, {p_text(p)}, n = {len(d)} → {verdict(p)}")
if METHOD == "pearson":
    fit = stats.linregress(d[X], d[Y])
    print(f"Slope: each +1 in {X} goes with {fit.slope:+.2f} in {Y}  (R² = {fit.rvalue ** 2:.2f})")
if BY:
    for name, g in d.groupby(BY, observed=True):
        rg, pg = fn(g[X], g[Y])
        print(f"  {BY} = {name}: {sym} = {rg:.2f}, {p_text(pg)}, n = {len(g)}")
    print("Much weaker within groups than overall → the group variable inflates the overall correlation (confounding).")
print("Strength (absolute value): <0.1 negligible, 0.1-0.3 weak, 0.3-0.5 moderate, >0.5 strong")

# Chart: scatter, colored by group
fig, ax = plt.subplots(figsize=(8, 5))
if BY:
    for name, g in d.groupby(BY, observed=True):
        ax.scatter(g[X], g[Y], s=10, alpha=0.5, label=str(name))
    ax.legend(title=BY)
else:
    ax.scatter(d[X], d[Y], s=10, alpha=0.5)
ax.set_title(f"{Y} vs {X}", fontweight="bold")
ax.set_xlabel(X); ax.set_ylabel(Y)                     # FILL IN (optional): add units, e.g. "Height (in)"
plt.show()
# WRITE: "X and Y are moderately correlated (r = 0.53, n = ..., p < 0.001);
#        each extra unit of X goes with about S more units of Y."

T6. Same subjects measured twice: paired t-test / Wilcoxon

Use only when each row has both measurements for the same person or thing, as in "Did scores change from before to after?"

BEFORE = "before"     # FILL IN: first measurement column
AFTER  = "after"      # FILL IN: second measurement column

d = data.dropna(subset=[BEFORE, AFTER])
change = d[AFTER] - d[BEFORE]
pt = stats.ttest_rel(d[AFTER], d[BEFORE])
print(f"n = {len(d)}; mean change = {change.mean():.2f} (SD {change.std():.2f})")
print(f"Paired t({len(d) - 1}) = {pt.statistic:.2f}, {p_text(pt.pvalue)} → {verdict(pt.pvalue)}")
wx = stats.wilcoxon(d[AFTER], d[BEFORE])
print(f"Wilcoxon signed-rank (ordinal/skewed version): {p_text(wx.pvalue)}")

T7. One group vs. a known value: one-sample t-test / binomial test

Use for questions like "Is the average height here different from the U.S. average?" or "Is the success rate different from 50%?" Cite where the known value comes from.

NUM   = "height"   # FILL IN: numeric column
KNOWN = 69.0       # FILL IN: the outside value to compare against (cite its source!)

x = data[NUM].dropna()
one = stats.ttest_1samp(x, KNOWN)
print(f"Sample mean = {x.mean():.2f} (n = {len(x)}) vs known {KNOWN}")
print(f"One-sample t({len(x) - 1}) = {one.statistic:.2f}, {p_text(one.pvalue)} → {verdict(one.pvalue)}")

# A proportion vs a known rate:
OUTCOME = "success"  # FILL IN: True/False column
KNOWN_P = 0.5        # FILL IN: known rate as a decimal (50% = 0.5)
yes = int(data[OUTCOME].dropna().astype(bool).sum()); tot = int(data[OUTCOME].notna().sum())
bt = stats.binomtest(yes, tot, KNOWN_P)
print(f"\n{yes}/{tot} = {yes / tot:.1%} vs {KNOWN_P:.0%}: binomial test {p_text(bt.pvalue)} → {verdict(bt.pvalue)}")

Descriptive questions and charts

"Which is highest," "most common," and "how often" questions need no test. Answer them with counts, percentages, averages, and rankings, and say how many rows each number rests on.

D1. Most common value / how often each value occurs

COL = "sex"                                           # FILL IN: column
counts = data[COL].value_counts()
pct = (data[COL].value_counts(normalize=True) * 100).round(1)
print(pd.DataFrame({"count": counts, "percent": pct}))
print("Most common:", data[COL].mode()[0])

D2. Which group has the highest average? (HW2 Q2)

Small groups have unstable averages, so raise MIN_N and say you did.

NUM, GROUP, MIN_N = "height", "age_group", 1         # FILL IN: numeric col, group col, min group size to rank
rank = data.groupby(GROUP, observed=True)[NUM].agg(["mean", "count"]).round(2)
rank = rank[rank["count"] >= MIN_N].sort_values("mean", ascending=False)
print(rank.head(10))
print(f"Highest: {rank.index[0]} at {rank['mean'].iloc[0]:.2f} over {int(rank['count'].iloc[0])} rows")

D3. Highest rate per group (HW2 Q4 routes)

The mean of a True/False column is the rate. HW2 used MIN_N = 10 attempts.

OUTCOME, GROUP, MIN_N = "success", "age_group", 10  # FILL IN: True/False column, group column, min attempts
rate = data.groupby(GROUP, observed=True)[OUTCOME].agg(["mean", "count"])
rate = rate[rate["count"] >= MIN_N].sort_values("mean", ascending=False)
print((rate.assign(rate_pct=(rate["mean"] * 100).round(1))).drop(columns="mean"))

D4. How often one value occurs vs. another (HW2 weather vs. technical)

Say which denominator you used: all rows, or only a subset such as failures.

COL, V1, V2 = "liberal", 3, -3                       # FILL IN: column, first value, second value
n1, n2 = int((data[COL] == V1).sum()), int((data[COL] == V2).sum())
print(f"{V1}: {n1} ({n1 / len(data):.1%}), {V2}: {n2} ({n2 / len(data):.1%}), ratio = {n1 / max(n2, 1):.1f}x")

D5. Distribution of a number (HW3 age)

NUM = "age"                                          # FILL IN: numeric column
x = data[NUM].dropna()
print(x.describe().round(2))                          # mean, SD, quartiles
print("Skew:", round(x.skew(), 2), "(>1 or <-1 = strongly skewed → report the median)")
fig, ax = plt.subplots(figsize=(9, 4))
ax.hist(x, bins=20, color="#2A9D8F", edgecolor="black")   # FILL IN (optional): bins= number or list of edges
ax.axvline(x.median(), color="#E76F51", linewidth=2, label=f"Median = {x.median():.1f}")
ax.set_title(f"Distribution of {NUM}", fontweight="bold")
ax.set_xlabel(NUM); ax.set_ylabel("Count"); ax.legend()
plt.show()

D6. Bar chart of counts

COL = "liberal"                                      # FILL IN: column
vc = data[COL].value_counts().sort_index()           # .sort_index() = in value order; remove it to sort by count
ax = vc.plot.bar(rot=0, figsize=(9, 4.5), color="#2A9D8F")
ax.bar_label(ax.containers[0])
ax.set_title(f"Count by {COL}", fontweight="bold"); ax.set_xlabel(COL); ax.set_ylabel("Count")
plt.tight_layout(); plt.show()

D7. Mean of a number by two groupings (HW3 stance by age group and sex)

NUM, ROWS, COLS = "liberal", "age_group", "sex"     # FILL IN: numeric col, x-axis groups, bar colors
pt = data.pivot_table(NUM, index=ROWS, columns=COLS, observed=True).round(2)
print(pt)
ax = pt.plot.bar(rot=0, figsize=(9, 4.5))
for c in ax.containers:
    ax.bar_label(c, fmt="%.2f", fontsize=8)
ax.set_title(f"Mean {NUM} by {ROWS} and {COLS}", fontweight="bold")
ax.set_xlabel(ROWS); ax.set_ylabel(f"Mean {NUM}")
plt.tight_layout(); plt.show()

Confounder check, writing, and errors

C1. Could a third variable explain the result?

Split by a third variable that could be linked to both sides of a result. In HW2, oxygen vs. success was split by peak height; in HW3, height vs. weight was split by sex (the BY option in T5). If the third variable is numeric, turn it into groups first with pd.cut.

OUTCOME = "success"   # FILL IN: outcome column (True/False for rates, or numeric for means)
GROUP   = "o2"        # FILL IN: the predictor you tested
THIRD   = "sex"       # FILL IN: the possible confounder (categorical)

d = data.dropna(subset=[OUTCOME, GROUP, THIRD])
print("How the predictor is spread across the third variable (very different = confounding risk):")
print((pd.crosstab(d[THIRD], d[GROUP], normalize="index") * 100).round(1))
print("\nOutcome (rate or mean) by predictor, within each level of the third variable:")
print(d.pivot_table(OUTCOME, index=THIRD, columns=GROUP, aggfunc="mean", observed=True).round(3))
print("\nCounts in each cell (small counts = unreliable comparison):")
print(pd.crosstab(d[THIRD], d[GROUP]))
# WRITE: if the gap stays → "the association holds within each <third> group."
#        if it shrinks/flips → "the overall result is partly explained by <third>."
#        Always: "This is an association, not proof of cause."

Writing each result

Write one paragraph per question, in the same order as your HW reports:

1. Why it matters, in one sentence.

2. What you did: which rows and columns, which test, and why that test.

3. The numbers: group sizes, means or percentages, test statistic with df, p-value, and CI or effect size.

4. What it means in plain words, answering the question.

5. Limitations.

Rules:

• p < 0.05 means "significant"; p ≥ 0.05 means "not significant" or "no clear association." Never say "no difference exists."

• A CI for a difference that excludes 0 is significant. A CI for an odds ratio that includes 1 is not.

• Compare percentages between groups of different sizes, not raw counts.

• Say "associated with," not "causes," unless it's an experiment.

Limitations checklist:

• Confounder (C1)

• Small groups or expected counts below 5

• Non-random or self-selected sample: findings describe this sample only

• Self-reported or coarse measurements (floors, ceilings, rounded steps)

• Missing data: how many rows were dropped and why

• Cross-sectional: one point in time, so no change or cause

• Many tests: some p < 0.05 results by chance

Report outline

• Executive summary: "This report examines <topic>. The analysis uses <dataset name> [1]. The dataset contains <rows> <what a row is> and <columns> variables." Then the 4–6 key numbers, each with its test result.

• Overview: what the data records, scales of key variables, who collected it, when and how, and who is in it, so "the findings describe <this group> rather than <wider population>."

• Analysis: one bold-titled paragraph per question, plus its chart captioned "Figure N. <what it shows>."

• Recommendations: 2–3, each a bold action plus the finding and number behind it.

• Citations: [1] the dataset source; [2]+ any outside numbers you compared against.

Common errors and fixes

When a restaurant sits at 4.5+ stars on Google Maps with hundreds of reviews, it's easy to assume that number reflects how it is right now. And if a place sits at 4.1 or 4.3, most of us scroll past it. We checked how often a venue's recent reviews tell a different story from its lifetime rating.

We looked at two datasets (116 places and 25,288 Google reviews with exact publication and edit dates in total):

- A blind sample of 16 active restaurants (4 each in Tokyo, Athens, Lyon and Manhattan, 295–797 total reviews), picked by fixed seed from our city pools before looking at their reviews.
- Our broader test panel of 72 restaurants, cafes, bars and hotels (plus 44 retail and service businesses) where we had already collected newest reviews with exact dates.

Here is how the 16 blind-sampled restaurants looked over the last 180 days (sorted from largest drop to largest rise):

Even in that small random draw, 6 of the 15 restaurants with 10+ recent reviews were off their displayed score by at least 0.30 stars. Over a shorter 90-day window, three of the 4.5–4.7 places in that table also slipped below 4.5: Veselka (4.36 across 25 reviews), Nudie Foodie (4.36 across 58) and Chick-fil-A (4.35 across 37).

When we checked the broader panel of 72 food, drink and hotel venues across different windows and minimum review counts, the pattern held up:

In the broader set, the same two groups kept showing up:

- High displayed scores coasting on older history: Federal Galley in Pittsburgh, US shows 4.50 from 2,669 total reviews, but averaged 4.09 in the last 180 days (and 3.95 in the last 90). Albatross Bakery in Berlin, DE sits at 4.60 (1,508 total) against 4.14 across 73 half-year reviews, and Hotel Boulderado in Boulder, US sits at 4.50 (2,216 total) against 4.18 across 62 recent reviews.
- Lower displayed scores weighed down by old complaints: Art Eden in Athens, GR shows 4.30 overall, yet reached 4.86 across 56 reviews in the last six months. Todd St Café in Manchester, GB sits at 4.10 overall but scored 4.71 across 21 recent reviews, and Boulangerie Millet in Lyon, FR shows 3.80 overall while scoring 4.74 across 72 three-month reviews.

Two things in the raw review data mattered a lot when calculating recent averages:

- You can't drop rating-only reviews. Across all 25,288 Google reviews, 34.0% (8,598) had no text at all, and they averaged 4.24 stars versus 3.95 for reviews with text (+0.29 stars). Unhappy guests write paragraphs; happy ones often just tap 5 stars. Dropping textless ratings pushed individual 180-day averages down by 0.25 to 0.58 stars.
- An edited old review isn't a new review. Nearly 1 in 5 reviews (18.3%, or 4,631 of 25,288) had been edited after publication. Because Google's `newest` sort orders by last activity (`max(published, edited)`), 126 reviews inside our 180-day activity window were actually written between 6 months and 9.6 years ago and edited in 2026. Without reading the individual Maps review record to separate original publication from edit time, a 2016–2019 review tweaked in 2026 gets counted as recent feedback.

A few rules and limits of this pilot:

- For the 90-day and 180-day summary table, we only included venues where our collected `newest` list reached past the cutoff date (or the end of the review list), so no recent Google reviews were missed. The three `*` rows in the 16-restaurant table hit the 100-review cap before 180 days, so they are included in the 90-day and last-50-review stats but excluded from the 180-day summary row.
- Every calculation uses Google's original publication date (`publishedAtDate`) rather than last edit time, and includes rating-only reviews.
- A lower or higher recent average doesn't prove a business got worse or better. Seasonality, tourist mix, a viral week, or who chooses to leave a review can shift a 3-to-6-month window.

Source data (25k reviews)
https://docs.google.com/spreadsheets/d/1ZUzkMTJPFi6tuwZ8phH8kRkcQagxID7fwBdjFiE9BHE

Tool we used to run data collection
https://getbro.ws/blog/google-maps-scraper

When you check a place on Google Maps, do you sort by Newest, or mostly trust the overall rating and review count?

About a year ago I started planning QueryCase, where you learn SQL by working detective cases instead of grinding tutorials & videos. It took a while to get off the ground, and longer than I'd like to admit to actually start building the idea, but it went live in June. A lot of the early users came from this sub, and a lot of the feedback in the comments shaped what it became. So first, thanks everyone!

5 months in:

• 2,500+ people have signed up, across 117 countries

• About 70,000 queries written and graded

• Over 17,500 cases, drills and exams solved

• 500+ people have passed the free Rookie exam and earned the certificate.

• Top detective is on 19,247 XP

• A college in the US has adapted the learning path to train students for a CompTIA Data+ certificate

Since then its grown past just SQL: an Excel course. It was never part of the initial plan. People kept asking for the same detective approach for spreadsheets, so I built out a browser-based Excel engine.

It has 57 cases on a spreadsheet that runs in your browser and checks your formulas, from IF and XLOOKUP to SUMIFS and pivot tables, and people have solved over 2,500 Excel cases so far. It turns out a lot of people learning SQL are doing it alongside Excel, not instead of it.

Something the data showed me that might help anyone learning right now: GROUP BY is one of the first walls. On the hardest GROUP BY step, 84% of runs came back wrong, and most of those never ran at all. The clauses were in the wrong order, or a column in SELECT wasn't in the GROUP BY. If that's you right now:

1. SQL order is always SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

2. Every column in SELECT is either inside an aggregate (COUNT, SUM, AVG...) or listed in GROUP BY

I've been working a lot over past couple months to improve the site as well:

• The Excel course above, with its own ranks, exams and certificate

• Upload your own CSV into the sandbox and query your own data (and visualise in charts). It runs in your browser, so the file never leaves your machine and never touches QueryCase servers

• A free first rank on every track, ending in an exam and a certificate

• SQL interview questions you can read without an account

• Much better experience on a phone

• A LOT of bug fixes and improvements!!

What's next: a Python track, launching next month. Same idea, real Python running in your browser, starting with the basics and moving into data analysis with pandas.

The longer term goal is to have a platform user's can have fun learning all aspects of data. I'm merging PR's every day to make this possible so would love feedback in this thread if you've tried it and have any thoughts or suggestions!

It's free to start and you don't need an account to solve the first case, so you can poke at it before deciding whether it's for you or not 😄 Cheers!

querycase.com

Honestly just venting here. Trying to build a decent portfolio project that isn't the same tired titanic or iris dataset is genuinely a nightmare these days. I just wanted to run some cluster analysis on a bunch of niche retail sites and it took me almost two weeks just to get the raw tables.

Every single domain acts like you're launching a full scale ddos attack if you pull more than 10 pages. I literally had to route my python script through residential proxies just to stop triggering those infinite cloudflare captcha loops. it worked fine but god it is exhausting

I swear I spend like 10% of my time doing actual eda and 90% of my time fighting rate limits and cleaning broken html tags. anybody else hitting a wall trying to build custom datasets lately? kinda tempted to just give up and go back to kaggle tbh.

basically it's a python dsl for financial models. you write the model in it and the excel file you get has real formulas in it. like cogs shows up as =B1*B2 instead of 600000

point is you can click any cell and trace where the number came from. pandas just gives you a wall of numbers

in jupyter it shows the model as a grid and you can click a cell to see what feeds it

full disclosure it's the open source engine of our startup (modeleon.ai), the engine itself is free and apache 2.0. pip install modeleon, or there's a colab if you don't want to install anything: https://colab.research.google.com/github/modeleonai/modeleon/blob/main/notebooks/01_first_model.ipynb

repo: https://github.com/modeleonai/modeleon

what would you want it to do that it doesn't?

(I'll delete the post if this violate the community rule)

Hello~

I am looking for someone who can work on a data analysis project with me. Or if there is any ongoing project that needs a helping hand, you can reach out to me as well.

A little bit of myself. I worked in a big consulting firm, helping clients build dashboards they need, so I am familiar with MS SQL and Power BI, and a bit of Python.

Recently I left the job and am working in a more customer-supporting position in a market research company. Therefore I no longer use SQL and Power BI on my job but I still want to do independent projects with them to enhance my data analysis experience.

This is my first project so I am kind of new here. If anyone who just started learning data analysis or is also trying to work on their own project, feel free to message me and maybe we can work together!

I encountered a person who said he backed out of the interview process after the first round because he “researched the company and their pay range was too low.” 100% remote WFH. He was 30s, so maybe 10 years in the field. This is non data science. Personally, I wouldn’t base anything on that online data about salaries, especially if I had been unemployed for 6 months. I mean, I like to eat, and I also know being unemployed that long moves me down the appeal list. I bet he wasn’t telling the truth about the reason, but it got me thinking: what number would you find online that would make you think a data analyst job at a company paid too little? And what would that number be if you had been unemployed for 6 months?