DEV Community

shray kumar
shray kumar

Posted on

From Chaos to CSV: How I Cleaned and Structured 15 Years of Messy Number Data Using Python

#webdev #ai #database #datascience

Most tutorials teach you how to clean data that’s already almost clean.

Real life isn’t like that.

Real-life data is:

incomplete
inconsistent
duplicated
mixed across HTML, text, images, PDFs
poorly formatted
and sometimes completely wrong

This post is a breakdown of how I collected, cleaned, validated, and transformed 5,000+ rows of unstructured numeric data into a usable, publishable open dataset — and built a small informational platform around it.

If you want a practical example of real-world data engineering, this is it.

🟩 Step 1: Collecting Data from a Messy Web Environment

The biggest challenge wasn’t cleaning the data.
It was extracting it in the first place.

The data existed across:

old HTML pages
inconsistent table structures
images with embedded text
PDFs with broken formatting
pages that randomly changed formats every few months

🔧 My scraping stack:

import requests
from bs4 import BeautifulSoup
import pandas as pd
Enter fullscreen mode Exit fullscreen mode

For images, I had to bring in OCR:

import pytesseract
from PIL import Image
Enter fullscreen mode Exit fullscreen mode

OCR misread numbers constantly:

  • "89" → "B9"
  • "24" → "2A"
  • "11" → "I1"

This taught me early on that cleaning would be the hardest part of the project.

🟩 Step 2: Cleaning the Dataset

After merging 15 years of data, the next step was cleaning.

Common issues included:

inconsistent dates (01-01-20, 2020/1/1, 1 Jan 2020)
missing rows
incorrect numbers from OCR
duplicated entries
swapped open/close values
formatting problems during merges

🔧 Converting dates to a single format:

df['date'] = pd.to_datetime(df['date'], errors='coerce')
Enter fullscreen mode Exit fullscreen mode

🔧 Validating number ranges:

def valid(row):
    return (0 <= row['open'] <= 99) and (0 <= row['close'] <= 99)
df = df[df.apply(valid, axis=1)]
Enter fullscreen mode Exit fullscreen mode

🔧 Removing duplicates:

df = df.drop_duplicates(subset=['date'], keep='first')
Enter fullscreen mode Exit fullscreen mode

This took days, not hours — which is normal for real-world data projects.

🟩 Step 3: Creating a Repeatable Data Pipeline

I didn’t want to clean everything manually again every time new data arrived.

So I built a small pipeline:

✔ Daily scraper

✔ Data validator

✔ Transformation script

✔ Auto-export to CSV/Excel/JSON

✔ Upload to a public dataset folder

🔧 Cron-based updater (example):

0 */6 * * * python3 update_dataset.py
Enter fullscreen mode Exit fullscreen mode

Now the dataset updates on its own.

🟩 Step 4: Visualizing the Dataset (The Fun Part)

Once the data was clean, I built charts to surface patterns:

🔧 Open vs Close scatter:

import seaborn as sns
sns.scatterplot(x=df['open'], y=df['close'])
Enter fullscreen mode Exit fullscreen mode

🔧 Jodi frequency:

df['jodi'].value_counts().head(20).plot(kind='bar')
Enter fullscreen mode Exit fullscreen mode

Monthly heatmap:

df['month'] = df['date'].dt.month
heat = df.pivot_table(values='jodi', index='month', columns='year', aggfunc='count')
sns.heatmap(heat)
Enter fullscreen mode Exit fullscreen mode

This revealed interesting distribution clusters — not predictable outcomes, but statistically engaging patterns.

🟩 Step 5: Preparing the Dataset for Public Release

To make it useful for others:

✔ Clean CSV

✔ Excel version

✔ JSON API

✔ README with metadata

✔ Clear column definitions

✔ A statistical summary (PDF)

✔ Kaggle-ready version

Documentation alone took several hours but dramatically improved the dataset’s value.

🟩 Step 6: What I Built on Top of It

After organizing the data, I built a simple informational dashboard where users can:

  • view daily updates
  • browse historical charts
  • analyze number patterns
  • download datasets

The tech stack:

Python (ETL)
Pandas
Node.js (API)
JavaScript frontend
Cloudflare caching
Enter fullscreen mode Exit fullscreen mode

The result:
A fast, clean, informational-only number-analysis platform.

🟩 Key Lessons Learned

1. Real data is messy. Always.

Tutorial datasets will deceive you.

2. Cleaning is 80% of the work.

Writing code is the easy part.

3. Documentation matters.

If others can’t use your dataset, it dies.

4. Automate early.

Manual cleanup doesn’t scale.

5. Visualization creates meaning.

Charts reveal what tables hide.

🟩** Want to Explore the Dataset?**

You can check it here:

Dashboard:
https://www.realsattaking.com
(Informational data charts & archives)

Top comments (0)