标签：#dataanalysis

By Michael Nocito , data analyst · Published August 8, 2026 By the end of this page you can load a CSV into pandas, find out in twenty seconds what type every column became, stop the identifier columns losing their leading zeros, get dates read the way they were written, and turn a money column that arrived as text into numbers. It is about twenty-five minutes, and every output below was produced by running the code. Here is what to do today, the moment after you first load a file. Run df.dtypes . Not df.head() , which shows you what the values look like, but dtypes , which shows you what they are. A column of identifiers that says int64 has already lost its leading zeros, and a money column that says object or str is text that will refuse to add up. The short version: read_csv reads characters and guesses a type per column. The guess is usually right, it is silent when it is wrong, and four arguments replace guessing with instruction. The same characters becoming two different values is the idea, so it gets the picture. The original carries a diagram here. In words: On the left, a strip of five small square boxes holds one character each, reading zero, eight, zero, five, three, as the characters appear in the file. Two arrows branch out from that strip. The upper arrow leads to a strip of five boxes in which the first box is empty, crossed through and outlined in amber, while the remaining four hold eight, zero, five and three; the leading character has been discarded. The lower arrow leads to a strip of five boxes holding zero, eight, zero, five and three, identical to the original, outlined in blue. Both destinations came from the same source strip, and only one of them still contains everything the file did. Every output on this page is real. Run on pandas 3.0.2 against a small CSV built to contain the four problems every real export has: an identifier with leading zeros, ambiguous dates, a text marker for missing values, and money with a thousands separator. If

By Michael Nocito , data analyst · Published August 8, 2026 By the end of this page you can turn transactions into a monthly series, add period-on-period change and a cumulative total, get a share-of-total column, smooth a noisy line, and run all of it separately for every group. It is about twenty-five minutes, and every number below came out of running the code. Here is what to do today, on the series you already have. Count its rows against the number of periods in your date range. If your data covers January to May and the series has four rows, a period produced nothing, it never became a row, and every change figure after the gap is comparing the wrong pair. The short version: pct_change() divides each value by the one in the row above; cumsum() adds everything up to and including the current row. Both trust the rows you gave them to be the periods you meant. What happens when the previous period is zero is the idea, so it gets the picture. The original carries a diagram here. In words: Three bar positions stand on a baseline, labelled Mar, Apr and May. The March position holds a tall bar and the May position holds a slightly shorter tall bar. The April position holds no bar at all; there is only a short flat mark sitting on the baseline where a bar would start, drawn in amber to show a value of zero. An arc runs from the top of the March bar down to the April mark, and the figure minus one hundred percent is printed on it, which is a perfectly ordinary answer. A second arc runs from the April mark up to the top of the May bar, and the symbol printed on that one is not a percentage at all but the sideways figure eight that means infinity. The picture shows that a fall to nothing has an answer and a rise from nothing does not. Every number on this page is real. The sixteen-row orders table used across this whole set of guides, run in pandas 3.0.2. It runs from 5 January to 25 May 2026 and contains no April orders at all, which is not staged for this page; it is

By Michael Nocito , data analyst · Published August 8, 2026 By the end of this page you can attach columns from one DataFrame to another on a shared key, choose the right how for the question, see at a glance which rows failed to match, and catch the failure that quietly inflates every total in the frame. It is about twenty-five minutes, and every output below was produced by running the code. Here is what to do today, on every merge you write. Print the row count immediately before and immediately after it. A left merge must not change the row count, and if it did, the right-hand table has the key more than once and your totals have just gone up. The short version: merge pairs rows from two frames wherever their keys match, and the number of rows that come out depends on how many times each key appears on each side. One key twice on the right is the idea, so it gets the picture. The original carries a diagram here. In words: On the left a single row is drawn as a wide box, holding the key Desk and the value 880. To its right stands a small lookup table with two rows, and both of those rows carry the same key, Desk. Two lines run from the single left-hand row, one to each of the two matching lookup rows, so the one row is paired twice. On the far right the result is drawn as two separate output rows, and both of them contain Desk and 880; the value 880 is ringed in amber in each of them to show that it is the same original figure appearing twice. One row went in and two came out, without anything being added to the left-hand table. Every output on this page is real. Sixteen orders totalling 9,890 and a three-row product table, the same tables used across this whole set of guides, merged in pandas 3.0.2 with the results copied back. If you know SQL joins , this is the same operation with different words, and the two failure modes are identical. 1. merge in one line Two frames, one shared column, one call. orders.merge(products, on="product", how="left") order_id prod

An AI assistant will write you a query in ten seconds, the query will run, and the number that comes back will look completely reasonable. This page gives you the five checks that tell you whether that number is right. They take about two minutes, they need no tools beyond the database you already have, and they catch the four mistakes AI-written SQL actually makes. The order matters. The checks are arranged cheapest first, so the first one costs a single row count and the last one costs a short conversation. Most wrong queries fall to the first two. The short version. A query that runs has only passed a grammar check. The number is right when the rows, the filters and the denominator match the question you asked. The database only takes a query as far as the first gate. Why a query that runs can still be wrong Before the list: what do you think the database actually checks when it accepts a query? Grammar. That is the whole list. Spell a table name wrong and you get an error. Sum the wrong column, join in a way that doubles rows, or filter after grouping when the question needed it before, and you get a clean result set with a wrong number in it. Every mistake on this page is valid SQL. AI assistants add one specific difficulty: their queries are fluent. The aliases are tidy, the formatting is clean, and the shape looks like something a careful person wrote. Fluency reads as correctness, and it is not the same thing. Treat an AI query the way you would treat a first draft from a new colleague: with respect, and with the row counts open. The table the examples run on Everything below runs on one small shop dataset, so every number can be checked by hand. Thirteen orders in July, five customers, and a refunds table where two orders were refunded in two parts. Eleven of the thirteen orders are completed; one is refunded, one is pending. There is also a staff_accounts table listing internal accounts, and it contains one NULL row, because real lookup tables usually do.

By the end of this page you can pick the right chart in about five seconds, by asking one question: what comparison must the reader make? The four possible answers each map to one chart, and you will also know the two miscasts that cause most bad charts, the axis rules that keep bars honest, and the escape hatch for when one chart holds too much. It is about twenty minutes. Here is what to actually do with it today. Open the last chart you made. Say out loud what the reader is supposed to compare in it. If the chart type does not match that comparison in the table below, remake it. It is usually a two-minute fix. The short version: comparison across categories takes a bar. Change over time takes a line. Relationship between two measures takes a scatter. Part of a whole takes a bar too, once you pass a few slices. One picture carries the fork, so it comes first. The original carries a diagram here. In words: A decision fork. On the left, a single rounded node contains the question: compare what? Four lines branch from it to four small chart pictures on the right, stacked vertically. The first branch, labelled categories, leads to a miniature bar chart with four vertical bars of different heights. The second branch, labelled time, leads to a miniature line chart with a single rising line over an axis. The third branch, labelled relationship, leads to a miniature scatter plot of dots drifting upward to the right. The fourth branch, labelled parts, leads to a miniature horizontal stacked bar divided into segments, drawn next to a small crossed-out pie, meaning that for part-of-whole comparisons a bar is preferred over a pie. The picture says that the single question of what the reader must compare selects one of four chart types. Every number on this page is computed. The example tables are shown in full, and every total, percentage, and correlation was verified by running the arithmetic in Python before it went on the page. 1. The one question, and the decision table B

By the end of this page you can read a budget vs actual table without being fooled by it, and build one in Excel that does not fool anyone else. You will know the variance formula, why analysts write F and U instead of trusting plus and minus, the two ways percent variance lies, and how to say the whole table in one sentence. It is about twenty minutes. Here is what to actually do today. Open the last variance table you were sent and find its biggest percentage. Then find its biggest dollar amount. If they are different rows, and they usually are, you now know which row deserved the attention, and it is probably not the one that got it. The short version: variance is actual minus budget. On a revenue line, positive is good. On a cost line, positive is bad. So analysts label every line F for favorable or U for unfavorable, rank by dollars, and flag by percent. The sign flip is the trap people fall into first, so it gets the picture. The original carries a diagram here. In words: Two panels, each showing a pair of vertical bars rising from a shared baseline. In the left panel, labeled revenue, a shorter bar marked budget stands next to a taller bar marked actual. The extra height of the actual bar above the budget level is shaded in the accent color and marked with the letter F and a check mark, because collecting more revenue than budgeted is favorable. In the right panel, labeled cost, the bars have the same shapes: a shorter budget bar next to a taller actual bar. But here the extra height above budget is shaded in the warning color and marked with the letter U and a cross, because spending more than budgeted is unfavorable. A dashed horizontal line runs across each panel at the budget height. The two panels are geometrically identical, and only the meaning of the line decides whether the overshoot is good or bad. That is why the sign of a variance cannot be read without knowing the line type. Every number on this page is verified. The worked example is a small dep

By the end of this page you can say, out loud and in your own words, what every core operations number does. What the unit of work is. Throughput, and why a count on its own answers nothing. Cycle time, and the rule that ties it to how much work is sitting open. Backlog. Utilization, and why aiming for 100 percent makes everything slower. Error rate, rework and first pass yield. Service levels, and why the average hides the customers you are failing. That list is most of what an operations analyst job, a technical screen, and a first real dataset will ask of you. Here is what to actually do with it. Go through once end to end without stopping, just for the shape. Then come back to the retrieval sheet near the bottom, cover the right-hand column, and try to say each answer before you read it. That second pass is where the learning happens, and there is measured evidence for it further down. The short version: operations analytics is the study of how work moves through a process. Every number in it is either how much, how fast, how much is stuck, or how much was wrong. One idea decides more of your operations work than any other, so it gets the picture. Work arrives, waits, gets done, and leaves. How much is in progress and how long each item takes are two different spans over that same picture, and they are locked to each other. The original carries a diagram here. In words: A left-to-right process diagram. On the far left an arrow labelled "arriving" points into a row of three small stacked boxes labelled "waiting", representing a queue. An arrow leads from the queue into a single larger rounded box labelled "working", representing the person or machine doing the job. A final arrow leads out of that box to the right and is labelled "done". Above the queue and the working box, a bracket in a strong accent colour spans both and is labelled "in progress", showing that work in progress includes everything waiting as well as everything actively being worked on. Below, a

When this workbook is finished, you can change one number and watch the whole thing follow. Move a cut-off from 65 to 70 and every row re-bands, every fill recolors, every count updates, and the legend still matches the map. Nobody can color a cell by hand, because no cell has a color of its own. That is the whole trick, and it takes about twenty minutes to build. The example here is a security risk index across twenty sites. The same shape works for vendor scoring, lead scoring, incident triage, or any list where a number has to turn into a label and a color. The fault, and where it actually comes from You have met this file. A scored list, colored by hand, that nobody quite trusts any more. Look closely and the same faults turn up every time: Two rows score 61.4. One is amber, one is yellow. The same band is drawn in two shades, because two people picked from the palette on two different days. A row sits below the cut-off and is colored red anyway, because somebody knew that site was a problem. A score lands exactly on 65, which appears in two bands, so the answer depends on who typed it. One row has no band at all. It quietly drops out of every count. These look like five separate mistakes. They are one mistake, five times. The rule lives in the formatting instead of in a column. A color is not a value you can test. You cannot write a formula that asks "is this row the right shade of amber," so nothing checks it, and it drifts. The test: can you sort by band? If the band is only a color, you cannot sort it, count it, or filter it, and neither can anybody else. That is the tell. The chain: score, then band, then color Everything below is one idea applied three times. Each thing is derived from the thing before it, and only the first one is typed. Layer Where it lives Who decides it Sub-scores Four columns, one per category Your source data. Typed once. Composite score A formula, from the sub-scores and the weights The weights row Band A formula, from the score The

By the end of this page you can look at a folder of eight finished sheets and say which two or three belong on the dashboard, which one goes in the upper-left corner, and which of Tableau's three sizing options to pick. You'll also have a one-sentence test that decides every one of those calls. It's about fifteen minutes. Here's the move to make today. Open your busiest dashboard and write the single question it answers, in one sentence, for one named person. Then remove every view that isn't part of answering it. Most people delete half, and the half that survives lands harder than the whole thing did. The short version: Tableau's own guidance is two or three views on a dashboard. Crowding is what happens when one dashboard is asked to serve several audiences at once. Where the surviving views sit is the second decision, and it has a known answer, so that gets the picture. The original carries a diagram here. In words: A single dashboard rectangle divided into three panes. One large pane occupies the whole upper-left area and spans most of the width. Two smaller panes sit below it, side by side. A curved arrow enters at the top-left corner of the large pane, travels right across it, then drops down and moves left to right across the two smaller panes, showing the order a reader takes them in. A small numeral one sits on the large pane, two and three on the smaller panes. The drawing shows that the first thing a reader meets is whatever occupies the upper left, so the most important view belongs there and the supporting views belong underneath. 1. Why two or three, and where that number comes from Before the explanation: you have eight finished sheets and one dashboard. How many of them would you put on it? Two or three. That's not a taste call, it's Tableau's published guidance: "In general, it's a good idea to limit the number of views you include in your dashboard to two or three." The reason is about attention rather than about screen space. A dashboard is read,

This guide gives you a test that takes ten seconds and tells you whether the thing you just built is a report or an analysis. Then it gives you four moves that turn one into the other. Every move has a worked SQL example and real numbers. The whole method is here. What you actually do: take the number you just produced, and ask what someone would do differently because of it. If the honest answer is nothing, you have a report. Then you run the four moves below, in order, until the answer is a specific action a specific person can take on Monday. The short version. Data analysis is looking at records of things that already happened and finding a pattern that changes what someone does next. If nothing changes, it was not analysis. It was a report. The same starting number, two endings. The test: what would someone do differently? Before you read the answer, look at the last thing you built and try it yourself. Who was going to act on it, and what were they going to do? Take any number you have produced and finish this sentence out loud: "Because of this, someone should do a specific thing ." Both blanks have to fill in with something real. A named person or team, and an action they control. Here is a real one. "Churn was 4.1% in Q3." Who acts, and how? Nobody can act on that. It is a true, correctly calculated, carefully formatted number, and it changes nothing. That is a report, and reports are useful. A dashboard that tells you the servers are up is doing its job. It is just not analysis. Now the same underlying data, worked further. "Monthly-plan accounts that never opened the import tool churn at 9.2%. Ones that did churn at 1.8%. The email introducing that tool goes out on day 14, and most cancellations happen on day 11." Who acts? The lifecycle marketing owner. What do they do? Move the email to day 3. That is analysis, and the only difference is that it ended somewhere a person can stand. The word "analysis" is doing a lot of quiet work in job descriptions, so

By the end of this guide you will have a published Tableau dashboard and a three-point story, built on a real dataset. It lives on a public URL you can put in an application. You build four small sheets. Each one makes exactly one point. You arrange them on a single screen, then walk a reader through them in three steps that end with a recommendation. Every step says what to click and what you should see afterwards. Four small sheets, rather than a wall of charts, because a dashboard has to argue for something. A screen holding everything you could build leaves the reader to work out what matters. Most readers will not do that work. Dashboard vs Story, in one line. A dashboard puts several charts on one screen so someone can explore. A story is a sequence of views with captions, clicked through in order, so someone is walked to a conclusion. Build both: the dashboard is what a hiring manager glances at, the story is what proves you can think. The original carries a diagram here. In words: Four separate worksheets stack on the left: a big single number, a set of vertical bars, a set of horizontal bars, and a scatter of circles. An arrow points right to one dashboard panel that holds all four of them arranged on a single screen: the number across the top, the two bar charts side by side in the middle, the scatter along the bottom. A second arrow points right to three story cards numbered one, two and three, each showing one of those views with a caption line above it. The worked example. Every instruction below is written against a real, free dataset: the Telco Customer Churn file on Kaggle, 7,043 customers, one row each. A finished analysis of it, including the Python script that shapes the data, is public at telco-churn-analysis . Swap in your own dataset and the steps do not change, only the field names do. Step 1: Shape the data before you open Tableau Tableau is a display layer. Deriving something inside it takes longer than deriving it upstream in SQL, Python or

This guide gives you a repeatable loop for the days when nothing works, and four true stories showing it used on real problems. Here is what a working day actually contains. A website's firewall blocks you for no reason. A table that visibly exists tells your script it does not. A query runs for thirty minutes with no end in sight. A fix you know is correct changes nothing at all. None of that means you are doing it wrong. That is the job. What separates people who ship analyses from people who stop is technical tenacity : staying methodical when the tools fight back. It is not a personality trait you either have or lack. It is a small procedure, and you can learn it in the next ten minutes. The diagnosis loop (tenacity is a method, not a mood) Think back to the last time a tool beat you for an hour. What was the first thing you did when it failed, and what did you do second? Most people can name the first move and not the second, and the second is where the method lives. Gritting your teeth and re-running the same thing harder is not tenacity; it's frustration with extra steps. What experienced people actually run is a loop: Step Move 1. Read the actual message Not "it's broken" — the words. Error messages name the symptom precisely, even when the cause is elsewhere. 2. Form ONE hypothesis "The table isn't in the file the script reads." Specific enough to be wrong. 3. Run the cheapest test of it Prefer checks that take seconds — list the tables, count the rows, print one value. 4. Verify from a second vantage point Don't ask the tool that's confusing you whether it's confused. Check the file from outside, the data from a different program, the value with a different query. 5. Change ONE thing, re-run Change three things and you'll never know which one mattered — or which one broke something new. 6. Timebox, then change strategy If the current approach has eaten 30 minutes with no progress, stopping is a decision, not a defeat. There's usually a second road. Four tr

This guide walks through five steps for working out which records are the same real thing, and merging them without wrecking your data. It runs on real chart data, and it includes the two times the rules came out wrong. Here is the problem in one example. Count the distinct artists in Billboard's public chart history and the number is wrong. "Elvis Presley" and "Elvis Presley With The Jordanaires" are the same man, and so are five other credit strings. One real-world entity , seven database strings . Every dataset with human-entered names has this. Customers who signed up twice. "IBM" against "I.B.M." against "International Business Machines". The same supplier in two systems, spelled two ways. The work of fixing it is called entity resolution . Matching across two datasets is record linkage . Removing duplicates inside one is deduplication . They are the same skill pointed at different situations, and it is one of the most common tasks an analyst actually gets handed. The vocabulary map Term Meaning Entity The real-world thing: one artist, one customer, one company Entity resolution Figuring out which records refer to the same entity Record linkage The same problem across two datasets. "Is row 5 in file A the same person as row 90 in file B?" Formalized by Fellegi & Sunter (1969) Deduplication The same problem inside one dataset Normalization / standardization Transforming values toward a canonical form (lowercasing, trimming, cutting suffixes) so equal things become equal strings Match key The cleaned column(s) you actually join on Match rate The share of records that found their counterpart. This is the number that keeps the whole exercise honest Clerical review Human eyes on the records the rules could not decide. This is a formal stage of the classic framework, not an admission of failure Step 1: measure the fragmentation before fixing anything The worked example is Billboard Hot 100 history, 1958 to present. The goal is one clean row per artist. Before writing

Here is a question I could not answer from the headlines: which European countries are actually losing people the fastest, in absolute terms or per capita? Those are two different questions, and they give two different answers. So I pulled the open data and ran the numbers. The headline figure Across the 19 European countries in the 2024 dataset, 17 recorded a net loss of native-born residents . Only two were net positive. So the "brain drain" story is not a handful of outliers, it is the default state of the continent. But the interesting part is who tops the ranking, because it depends entirely on how you measure. Load the data yourself The dataset is public on GitHub (CC BY 4.0). Every number below is reproducible with a few lines of pandas. No download, no API key, it reads the raw CSV straight from the repo: import pandas as pd url = ( " https://raw.githubusercontent.com/DatapulseResearch/ " " brain-drain-eu/main/data/net_migration_native_born_2024.csv " ) df = pd . read_csv ( url ) print ( df . shape ) # (19, 3) print ( df . columns . tolist ()) # ['country', 'net_migration', 'per_1000_residents'] # How many countries lost native-born residents? losers = ( df [ " net_migration " ] < 0 ). sum () print ( f " { losers } of { len ( df ) } countries had a net loss " ) # 17 of 19 net_migration is the raw count for 2024 (negative means a net loss of native-born residents). per_1000_residents is the same flow normalized by population size. The absolute ranking: Germany runs away with it Sort by the raw count and one country dominates: worst_absolute = df . sort_values ( " net_migration " ). head ( 5 ) print ( worst_absolute [[ " country " , " net_migration " ]]) country net _ migration 0 Germany - 91067 ... Germany loses -91,067 native-born residents, far more than anyone else in absolute terms. If you stop reading here, the story writes itself: "Germany, Europe's biggest brain drain." Plenty of coverage did exactly that. The counterintuitive finding: the ranking inve

Let me confess something a little creepy. I have a habit of peeking at other people's dev posts. Not stealing the writing — relax. I run a tiny read-only job that fetches the public pages on dev.to, Zenn, and Qiita and counts only the boring parts: titles, post times, like counts. Who published what, at what hour, and how far it traveled. Then it tallies the lot. The reason is petty: my own posts weren't landing. The content is already in my hands — so I wanted to know how much the rest, the when and how you publish , actually moves the needle. By the numbers, not by gut. So I counted across three platforms. And the conditions that make a post fly turned out to be roughly mirror images between Japan (Zenn / Qiita) and the English-speaking world (dev.to). Here's the story. First, my most important disclaimer This post is full of numbers, so let me put up a guardrail before any of them. This is correlation, not causation . A result like "weekend posts don't do well" could mean the weekend itself is bad — or it could mean people who post on weekends are just dashing something off on the side. The data can't separate those. Please read it that way. Also, I only keep aggregate numbers I computed myself . I don't store or reuse anyone's article body (read-only GET, count the features, throw the page away). I peek, but only at the overall shape . Nobody gets singled out here. With that out of the way — four findings I enjoyed. 1. The best hour to publish is just your readers' time zone This one came out cleanest. On Qiita , posts published in the morning win (+32pt in the GOOD group). Midday is +14pt. Evening is -32pt, late night -14pt. Zenn likes midday too (+27pt). Late night is -15pt. dev.to is the exact opposite. Late night Japan time scores +7pt — Japanese evening is actually weak. The trick is obvious once you see it. dev.to's readers are English-speaking, mostly US. Late night in Japan is the US working day. Zenn and Qiita readers are in Japan, so the Japanese morni

Introduction Excel is a powerful spreadsheet program developed by Microsoft that is used to calculate, organize and analyze data. It provides a way of turning raw data into meaningful insights through handling large datasets more efficiently from tracking sales and expenses to analyzing trends. Various Excel applications. Decision making: One of the major ways Excel is used in real-world data analysis is to support decision making. Companies collect large volumes of raw data everyday ranging from customer information, sales records to log records. This data is organized and cleaned by Excel into tables, charts and reports making it easier to derive insights and identify trends that help in decision making. Financial reporting: Excel is also widely used for financial reporting and budgeting. Businesses use it to record income and expenses, calculate profit margins and create financial predictions. By analyzing financial data, organizations are able to monitor their performance over time and plan better for future growth. Marketing performance: In addition to that, Excel can be used in market analysis. Marketing teams utilize Excel to track campaign and social media performance, customer engagement and product popularity. Insights derived from this data helps companies improve their marketing strategies and better understand consumer behavior. This past week I was introduced to several data cleaning features and formulas used in Excel to make analysis less nerve-wracking. For example, in stead of editing data cell by cell in the case of duplicate values, you can use the Find and Replace filter. Also, conditional formatting makes it easier to highlight specific cell ranges and erase duplicate values. Functions and formulas make it easy to obtain statistical and mathematical data. Learning Excel helps you look at data differently. Instead of data being just a bunch of texts, numbers or logs, data becomes something you can use to gain insights, make decisions, reveal pat