Data Analyst

PowerShell ships with Windows; bash ships with macOS and Linux. You already have everything you need.

1. 1

   Pick a real chore

   Not a toy problem — something that genuinely annoys you. Sorting a Downloads folder full of four hundred files. Renaming photos to a consistent format. Finding every file over 500MB. It has to be real, or the payoff at the end will not land.

2. 2

   Do it by hand once, and time it

   Actually time it. Write the number down. This is the figure you are going to beat, and it is also how you will one day justify automation work to a manager who wants to know why it is worth your afternoon.

3. 3

   Make a copy to work on

   Duplicate the folder. Everything from here runs against the copy. This is not caution for beginners — it is what people do professionally, because the cost of being wrong is asymmetric.

   Windows

   Copy-Item -Path .\Downloads -Destination .\Downloads-copy -Recurse

   macOS

   cp -R ~/Downloads ~/Downloads-copy

   Linux

   cp -R ~/Downloads ~/Downloads-copy

4. 4

   Ask what it WOULD do, before it does anything

   Run the operation in preview mode first. Nothing changes; it just tells you what it intends. Read that output properly. This habit is the single biggest difference between someone safe to give automation to and someone who is not.

   Windows

   Get-ChildItem .\Downloads-copy -File | Group-Object Extension | Format-Table Name, Count

   macOS

   find ~/Downloads-copy -type f | sed "s/.*\.//" | sort | uniq -c | sort -rn

   Linux

   find ~/Downloads-copy -type f | sed "s/.*\.//" | sort | uniq -c | sort -rn

5. 5

   Now let it run

   Sort every file into a folder named after its extension. Five lines or so. It will not work first time — read the error, change one thing, run it again. That loop, repeated, is the actual job.

   Windows

   Get-ChildItem .\Downloads-copy -File | ForEach-Object { $d = ".\Downloads-copy\" + $_.Extension.TrimStart("."); New-Item -ItemType Directory -Force -Path $d | Out-Null; Move-Item $_.FullName $d }

   macOS

   cd ~/Downloads-copy && for f in *.*; do d="${f##*.}"; mkdir -p "$d"; mv "$f" "$d"/; done

   Linux

   cd ~/Downloads-copy && for f in *.*; do d="${f##*.}"; mkdir -p "$d"; mv "$f" "$d"/; done

6. 6

   Compare it to your number

   You wrote down how long it took by hand. It now takes under a second, and it will take under a second every time from now on. Sit with that for a moment — that gap, multiplied across an organisation, is the entire economic argument for automation as a career.

A spreadsheet is enough — Excel, Numbers, or free LibreOffice. There is an optional SQL path at the end if you want it. No account needed.

1. 1

   Get something real and unhelpful

   Download a CSV from a public data portal — data.gov.uk, data.gov, or your national statistics office. Pick a subject you actually care about: crime near you, rainfall, energy prices, hospital waiting times. Deliberately do not pick a tutorial dataset. Real data is messy, and the mess is the job.

2. 2

   Write your question down before you look

   One sentence, on paper, before you open the file. "Has burglary in my area gone up or down over five years?" Doing this first is what stops you finding whatever the data happens to show and calling it a finding afterwards.

3. 3

   Meet the mess

   Open it. Find the problems: blank cells, dates in three formats, a total row hiding at the bottom that will corrupt every average you take, categories renamed halfway through the period. Write a list of every defect you find before you fix any of them.

4. 4

   Clean it, and keep a record of what you changed

   Fix the defects — but write down each decision as you go. "Dropped 214 rows with no date." "Treated blank as zero." Those decisions change the answer, and being unable to say what you did to the data is the difference between analysis and guessing.

5. 5

   Answer the question

   Pivot table, chart, or a formula. Get to a number. Then do the part almost everyone skips: try to disprove it. Is the trend just one anomalous year? Did the collection method change? Would a different but equally reasonable cleaning decision have flipped the result?

   Any

   Optional SQL path — sqlite3 ships with macOS and Linux:
   
   sqlite3 data.db
   .mode csv
   .import yourfile.csv t
   SELECT year, COUNT(*) FROM t GROUP BY year ORDER BY year;

6. 6

   Say it in three sentences

   What you found, how confident you are, and what it does not tell you. Show it to someone who has not seen the data. If they ask a question you cannot answer, that is not a failure — that is you discovering the limit of your own finding, which is the most valuable output of the whole exercise.