Forem: Jampa Matos

Press Start to Continue

Jampa Matos — Sat, 07 Jun 2025 05:43:26 +0000

These blog-like communities are a bit funny to me.

People pop in here, all professorial-like, sharing insights and dropping little tips about stuff they've discovered. And that's totally cool. But sometimes it feels like people are doing it because they're supposed to, not necessarily because they're bursting with amazing things they just HAVE to share.

And look, I get it. You gotta show up to get noticed. Part of networking is having a voice and, as we say here in Brazil, being able to "sell your fish" (I’m sure there’s an equivalent expression in English, but honestly, I'm too lazy to look it up). And nothing says "I'm GOOD" more than teaching people some neat new trick to make that div box land EXACTLY where you want it -- because honestly, THOSE DIV BOXES ARE ALIVE AND ACTIVELY TESTING MY PATIENCE AND ONE DAY THEY'LL DRIVE ME CRAZY!

Right, that was a rant. Sorry.

Anyway, I'm pretty green to the whole tech thing. It's funny, cause I've been at it for a while now, as my Github account is almost 4 years old, but it's been a slow and steady process. I'm entirely self-taught, purely a hobbyist, and by hobbyist I really mean it: every single line of code I've ever written was purely and simply for FUN. But yeah, definitely still a newbie.

And the thing about teaching yourself is: when you're teaching something you don't know to people that also don't know the thing, it gets nasty.

Anyway, the point I was trying to make is , because of that, I do not have a lot to share. As you could probably guess by looking over my other posts.

But I've finally reached the point where I'm ready to make the leap from hobbyist to PROFESSIONAL coder. I want to turn something I love doing for fun into something that will make me scream, cry, and want to throw things -- but will also pay for my inevitable ulcer treatments.

I want to be a DEV.

And if that means writing stuff down and giving y’all a little piece of my mind to become a more active part of this community, well, then consider me officially on board.

Talking about myself

Since I cannot talk shop, and I cannot share helpful working anecdotes as insights, let me offer the next best thing:

I want to talk about me!

Or rather, let me tell you a little story about how I've always wanted to code but consistently convinced myself I was too dumb to do it. Honestly, I still feel that way most days, but this time around I'm choosing to push through.

We've all heard about impostor syndrome, and yeah, that's probably exactly what's going on here. The more you learn, the more you realize how little you actually know. Eventually, you start staring at increasingly abstract concepts until they make no sense at all, and then you realize it is all made up and nothing is real and now you're missing your Friday night where you could be desperately dancing in a club by just being desperate for ABSOLUTELY NO REASON since THIS (franticly pointing at the computer screen where the absolute mad words read className="px-4 py-2 text-center space-x-2") IS NOT A THING THAT EXISTS IN REAL LIFE!

And yes, honestly, I feel like I'm becoming dumber, not smarter, as I go along. I feel kind of terrible that large language models emerged right in the middle of my learning curve because now I'm convinced that I can't code without ChatGPT.

It's weird though. Whenever I read back code that I wrote with an LLM’s help, I genuinely understand it. I can explain it, spot errors, and when ChatGPT inevitably gets stuck on a loop suggesting the same useless patches again and again, I'm able to step back, reconsider, and ultimately find a solution on my own. Every single time.

But, when I'm staring at the flashing cursor in an empty file in my IDE, I feel dumb and dumber. I have no idea where to start. I feel ashamed that I'm even trying to look for a job in the industry since I don't know how to start a fudging PYTHON CLASS without some lookups.

And if that's a whole project, brother, I don't even know where to start.

But put me in front of that blank file, cursor flashing mockingly in my IDE, and suddenly I don't know anything. I have absolutely no clue where to start. I feel ashamed for even considering a job in the industry when I can't begin writing a simple PYTHON CLASS without checking references.

And if it’s an entire project, brother, I’m completely lost.

You know those kids who buy a Ramones T-shirt, hang around with real punk rock fans, and spend the whole time anxious that sooner or later they'll be exposed as an impostor?

Well, Visual Studio Code is my Ramones shirt.

But it's funny, because I didn’t always feel this way.

Beat on the brat

When I was really young, and I’m not exactly sure how young, but let's say around 9 or 10 maybe, my dad came home from work one day carrying a rectangular box.

Inside was this strange device: a black plastic brick with keys covered in numbers and letters, and a small coaxial cable you'd plug into your TV.

That, my young under-40 friends, was a TK85, a Brazilian clone of the legendary ZX81.

That was a computer.

My 8-year-old brain was utterly mesmerized.

Funny, because when you look at it now, especially compared to the triple-screen PC setup I’m typing these words on, complete with a graphics card that cost more than my last paycheck, it’s genuinely mind-blowing.

That computer didn't even have built-in storage. You had to buy an external MODULE to save your progress onto a CASSETTE TAPE, probably costing as much as the computer itself.

It didn’t have a dedicated screen; you had to plug it into your TV.

No operating system. No pre-installed software. No plug-and-play peripherals.

Just a keyboard and a dream.

Oh, and right, a Sinclair BASIC interpreter and an enormous BASIC book.

Now, I'd absolutely love to tell you how this brave 6-year-old soldiered through the entire BASIC manual, mastered it cover-to-cover, and by age 7 created an application that transformed his home into a high-tech haven straight out of the 24th-and-one-half century. But no. I couldn’t make heads or tails of any of that gibberish.

I mean, come on, it was a BASIC book for a 5 year old boy!

(Seriously, I have NO idea how old I actually was back then.)

But those books did have some words in a monospace font that stood out from the rest. And I quickly discovered that typing those particular words into this mysterious device made things happen.

You could perform little calculations. You could display your name on TV. You could draw a tiny square. Heck, you could even draw an entire Christmas tree made of little white squares. All of that just by typing WORDS!

That was uncanny!

And I truly wanted to explore it all. But then, a few months later, my dad came home with another rectangular box containing a Sega Genesis console, three game cartridges, and absolutely no instruction manual required.

So I put the computer aside. At least for a while.

For the Win!

Years later, we got our first PC, our personal computer. It was an Intel 486DX2, running at a blazing-fast 33MHz. It probably had around 4 or 8MB of RAM, and definitely less than 100MB of hard drive space. It had two floppy disk drives: a 5¼-inch drive capable of storing an astonishing 360KB of data, and a 3½-inch one that handled up to 1.44MB.

When you turned it on, THINGS WOULD HAPPEN on the screen. Lines of text scrolled by, informing you about memory allocations and boot procedures, all while a quiet ticking echoed steadily from inside the computer. And when all was finally said and done, you'd once again be greeted by a black screen and a blinking prompt awaiting your commands.

But this prompt was different. It stood beside some curious characters, specifically: C:\>.

And you couldn’t just type anything you wanted, because it would rudely inform you that it didn’t understand your so-called "command."

Oh, and this time around, there was no instructional manual, and, obviously, no internet. So figuring things out became a bit trickier.

Around that period, classes promising to teach you "how to use a computer" began popping up everywhere. And trust me, if using computers seems intuitive nowadays, back then it certainly wasn't. Naturally, I enrolled in one of those.

Those were fun times. You'd leave your computer class carrying a floppy disk with a fresh new copy (pirated, of course, but come on!) of Prince of Persia, Indy 500, or Where in the World Is Carmen Sandiego? And right about now, if you're around your 40s, you're definitely humming that catchy little tune in your head, wondering which country the suspect who changed all their money into marks was heading to next.

You learned how to navigate directories, create, delete, and search for files using nothing but the keyboard. You’d constantly interact with console-based programs, playing entire video games within these text-based environments.

That was MS-DOS, the grandpa of PowerShell, the grumpy old-timer who sparred endlessly with another grumpy old guy called Unix Bash.

Exploring these uncharted territories was amazing. Every day brought a new command to master; some of those I still use today when fiddling with terminals during development.

But then, one day, we learned a different new command:

win.

Typing those three little letters and pressing enter transported you to another dimension, as it was the Feywild of computing: Windows 3.11, a GRAPHICAL USER INTERFACE where you could actually control the computer with a MOUSE.

You could CLICK things. You could RIGHT-CLICK things. And they were DIFFERENT actions!

You could DRAG AND DROP things.

You could OPEN a window. And then you could CLOSE that same window.

You could draw masterpieces in PAINTBRUSH. You could play SOLITAIRE. You could play MINESWEEPER. Or not, because let's face it, that game made no sense at all.

But there it was—something both utterly weird and spectacular. Seriously, it hit me so hard that years later it directly inspired the layout of my own website today.

(I realize my site has more of a Windows 95 vibe than a 3.11, but the feeling is exactly the same.)

Having that kind of control over things was incredible. I felt like Dean Pelton when he discovered VR: I COULD DO THINGS THAT MADE JESUS WEEP, FOR THERE WERE NO MORE WORLDS TO CONQUER!

(If you didn’t get that very specific reference, I completely understand.)

The point is, you could create amazing things on that computer, and that deeply resonated with me.

I wanted to learn more.

Kick ass and chew bubble gum

I really enjoyed fiddling around with that old computer. So much so that eventually, we upgraded it to a shiny new Intel Pentium machine, complete with an impressive CD-ROM drive, multimedia capabilities, and a set of stereo speakers.

Around that time, exciting new things began to happen everywhere. Newsstands started selling magazines bundled with CDs brimming with games that you could take home and play right away.

Let me pause for a quick sidebar: these early magazines were really something special. The gaming industry was booming, and there were plenty of new games coming out all the time. But proper channels for selling computer games weren’t widespread yet, and newsstand magazines certainly weren't the ideal distribution method back then.

I come from a small town in the countryside of Minas Gerais, Brazil, where, in the early '90s, nobody would dream of opening a computer game store. Computers were still extremely expensive and uncommon, so it wasn't exactly a brilliant business idea.

Because of this, the magazines we bought typically didn't include complete games. Instead, they had DEMO versions, letting you play for about 15 minutes or just like the first stage before stopping abruptly and politely asking you to go buy the full version.

It's funny because you'd expect us to get annoyed, but honestly, we had a blast playing those brief segments. We'd finish one demo level, eagerly jump into the next, and then onto another, enjoying the small tastes of many different games.

Maybe we were just less demanding back then, because everything was so new and exciting.

To be fair, some magazines also provided SHAREWARE versions. For example, I remember playing Duke Nukem 3D's shareware version, which included the entire first act, an enormous amount of gameplay for free. Big shout-out to the folks at Apogee for making my childhood (and my friends') a whole lot more awesome.

(Death Rally was the first game, that I had as a shareware as well, that I got actually GOOD at. So much so that, when I got the change, I bought the whole thing. Probably the first full game I ever bought).

But I digress. The point is, I'd buy those magazines-with-CDs-packed-with-demos. Most games at that time ran on DOS, so you'd have to exit Windows (these computers booted directly into Windows 95 without stopping at DOS) and manually type a command from the CD-ROM drive.

Typing that command would launch a simple little program, presenting you with menus to pick genres and games, very much like the tiny CLI apps you build at the start of any coding bootcamp. You know the drill: prompt the user, store their input, run some conditional logic, and output what the user asked for.

Back then, I spent a lot of time in front of my computer. Half the time I played (demo) games; the other half, I spent exploring, clicking around, trying to understand how things worked.

I don't exactly remember why I first did it. Maybe I saw someone opening a .bat file in Windows' notorious Notepad and got curious, but I noticed the commands used to launch these CD menus were always in .bat files.

What I clearly remember is the day I opened one of those .bat files in Notepad and suddenly realized I could make sense of what I was seeing.

It was basically the exact text displayed on the screen when the menus popped up, mixed in with strange words like ECHO. But I recognized some of those other words as DOS commands I'd already learned. This was my starting point, a way to begin decoding the mystery.

If you don't know what a .bat file is, think of it as a script-like executable in DOS, similar to a .sh shell script in Bash. It lets you bundle multiple commands together and execute them all at once. You could even write simple scripts with inputs, variables, and conditionals.

Of course, I didn't understand all of that back then. But I did realize those were DOS commands, and it dawned on me that I could create my own interactive menus, launching games, returning to Windows, or anything else I wanted.

(This became even more intriguing when I learned you could call one .bat file from another, and that the first thing the computer executed on boot, before loading Windows, was a file called autoexec.bat.)

Now, I've said before that I'm self-taught. That's mostly true, though I have taken bootcamp courses and used resources like The Odin Project. But here... Boy, this moment right here, I truly learned entirely by myself. Just reading through someone else's code, figuring out what each command did, piece by piece.

It's a feeling I savor to this day. It might even have been my smartest moment.

... as time goes by

But along came elementary school, and then high school. Biology captured my interest, while math faded into the background. Truth is, I had no clue how one would pursue a career in tech. Computers were fun, sure, but programming felt utterly alien, inaccessible, and impossibly distant.

It's funny, because I always claimed to hate math. Yet no other subject could match the warm, fuzzy feeling I experienced whenever I finally sat down, tackled a problem, and actually solved it. That pure sense of accomplishment and victory was simply incredible.

But as far as coding went, I learned next to nothing. Sure, maybe I could write small Excel macros, but beyond that? It seemed unattainable.

I think I concluded that a career in programming wasn't realistic simply because I couldn't find a way to truly create a full-fledged program myself. Those .bat files were amusing, sure, but how could I ever hope to draw some muscular blond dude in a red tank-top and Johnny Bravo shades blasting ugly aliens in the streets using a batch script? It made zero sense.

And then I tried opening an .exe file in Notepad, and that made even less sense.

So no, writing .bat scripts wasn't real programming, and I believed there wasn't any feasible way to pursue programming further. How could I aspire to something that felt entirely foreign, especially since all the software I encountered in Brazil came from abroad?

Life moved on. I made choices, some good, many questionable. I drifted through creative roles, labor-intensive jobs, management positions, then college to study Biological Sciences. Eventually, I became a high school teacher. Later, I tried pursuing a Master's degree.

Then the pandemic hit.

Being somewhat older during the pandemic was particularly tough: it meant that (1) I was part of a risk group, and (2) my parents were older and vulnerable. I moved back home to support them, spending more time with these two lovely people who’d always supported me.

But being back home also meant I suddenly had a lot more free time on my hands.

One day, for some reason, I'd bought a Unity Game Development course fromZenva via the Humble Bundle. It had been sitting there untouched for over a year, maybe even longer.

So, on a whim, I decided to give it a go.

Once again, I was astonished, bedazzled, and otherwise stupefied. Everything came flooding back: the power to create things, to shape ideas into reality. Writing words that seemed meaningless but somehow conjured powerful, magical behaviors. It felt just like wizardry.

The game engine itself was fun, but the real magic happened within that Visual Studio Code file. There I was, typing commands reminiscent of those DOS days, hunting down bizarre bugs by scouring documentation pages that echoed the BASIC book from when I was 4 (seriously, I have no idea how old I actually was). Running a piece of code and seeing the exact output I expected gave me that familiar rush of solving math problems.

It all came rushing back.

I'm not a particularly spiritual guy. I don't really believe in fate, destiny, or even talent.

But sometimes, life tosses things at me that make me question my perspective.

Because, looking back, it all feels kind of connected.

All Play and No Work Makes Jampa A Poor Boy

Ever since then, I've been at it. Constantly. I dove into Python, then HTML/CSS/Javascript -- and nearly died of Flexbox-induced rage. Then Ruby and Rails. Learned what an API was, built an API. Built two APIs. Played around with some more C#, tried Lua and gaming with LöVE2D. Touched React, then Flask, Django, NodeJS, TypeScript, Golang, Wails, Tailwind... and then some more. And then something else. And yet another thing.

I built a landing page. Then a calculator. Then a CLI chess game. A PONG-like game. A Flappy Bird clone. My own website.

But then I'd start a project, hit a wall or lose interest, and dump it. Start another, get bored, dump it. Start yet another, realize it's been done before, dump that too. And so on, and so forth.

The thing about being self-taught is that you're your own motivator, but also your own saboteur. You have to constantly motivate the guy who failed yesterday, and that guy must somehow keep motivating the motivator too.

It's easy to get lost, easy to stretch yourself thin. It’s hard to set clear goals because, when you love doing something just for the sake of doing it, the thing itself becomes its own goal and reward.

I mean, I'd hop onto Codewars and solve challenges just for kicks. These challenges are fun, satisfying, and bring back that fuzzy, warm feeling, but at the end of the day, you're not really creating anything meaningful. It’s like someone shredding a Joe Satriani guitar solo: you're probably the only one really enjoying it.

It’s like a fix, you know what I’m saying?

I guess what I'm trying to say is that while it's all fun and games, without accountability or mentorship, I feel like I've gone about as far as I can on my own.

I crave mentorship to show me the ropes: I know what I want to build, but how should I build it? What's the best way, the coolest way, the safest way?

Why are you learning this technology when another would suit you better? Why use pointers when a simple variable does the job? Why build this whole algorithm yourself when there are already 53 libraries doing exactly what you want?

And I need accountability. Something to MAKE ME STICK TO THE PLAN. To know someone out there is counting on me. To have confidence that the thing I'm building has a purpose, an actual user, or even another fellow developer, waiting for it.

I’m genuinely afraid to take that leap of faith again, to jump back into the unknown, into a field that's very specialized, demanding, and filled with incredibly knowledgeable people, especially considering my limited professional experience. I’m worried the real punk kids will ask me what my favorite Ramones song is, and I’ll have no answer. I'm terrified I'll walk into a tech interview and hear "No ChatGPT allowed," and I’ll melt down faster than the T-1000 in Terminator 2.

But I also believe I'm a smart guy. Like, weirdly, specifically, not-sure-how-or-why-but-definitely-smarter-than-average smart. But smart nonetheless.

And I genuinely want to contribute.

Bottom line: I'm here, ready and willing to go beyond level one. Ready to leave the demo behind and dive into the full game. To push myself further and grow. As a coder, as a developer, as a software engineer, as a nerd in general.

So I guess what I'm ultimately saying is: if you're hiring, hit me up at jp.coutm@gmail.com!

(This text absolutely did NOT end the way I expected when I started typing, but I kinda like how it turned out. Also, if you made it this far, cheers! And tell me your favorite ice-cream flavor!)

From Messy Data to Super Mario Pipeline: My First Adventure in Data Engineering

Jampa Matos — Thu, 20 Jun 2024 23:24:21 +0000

Welcome to the thrilling tale of my very first automated data pipeline!

Imagine you’ve just been handed a database that looks like it’s been through Bowser’s castle and back. Yes, it’s that messy. The mission? To transform this chaos into a clean, analytics-ready dataset with as little human intervention as possible. Sounds like a job for Mario himself, right? Well, buckle up, because this is the story of how I tackled the subscriber data cleanup project, dodged fireballs, and came out victorious.

When I first opened the cademycode.db file, I felt like Mario entering a warp pipe into an unknown world. It was a mess. Missing values, inconsistent data types, and duplicates everywhere! But hey, every great adventure starts with a challenge, and this was mine.

In this post, I’ll take you through my journey of building my very first automated data pipeline. We’ll dive deep into the nitty-gritty details and share the ups and downs along the way. Whether you’re a fellow data plumber or just curious about the magical world of data cleaning, you’re in for a treat.

So grab a 1-Up mushroom, get comfy, and let’s embark on this data adventure together! Spoiler alert: There might be some gold coins and hidden blocks of wisdom along the way.

Ready to jump into the pipe? Let’s start with how I set up the project and the initial hurdles I had to overcome. Spoiler alert: There were quite a few!

Entering the Warp Pipe

Setting up this project was like entering a warp pipe into an unknown world. I knew the journey ahead would be filled with challenges, but I was ready to tackle them head-on.

Getting Started

First things first, I needed to clone the repository and set up my working environment. I created a directory called subscriber-pipeline and jumped right into it.

mkdir -p /home/jampamatos/workspace/codecademy/Data/subscriber-pipeline
cd /home/jampamatos/workspace/codecademy/Data/subscriber-pipeline

Next, I set up a virtual environment to keep my project dependencies isolated. If there's one thing Mario taught me, it's to always be prepared!

python3 -m venv venv
source venv/bin/activate

Tools and Technologies

Since there were no red flower or mushrooms to collect, here’s a list of the tools and technologies I used for this project:

Python: The hero of our story. I used Python for data manipulation and scripting.
SQLite: Our trusty sidekick. This lightweight database was perfect for managing the data.
**Pandas: **The power-up we needed to handle data manipulation with ease.
Jupyter Notebook: My go-to tool for exploring and experimenting with data.
Bash: The magical spell that automated our pipeline.

Initial Hurdles

Setting up the environment was smooth sailing until I encountered my first Goomba: installing the required Python packages. After a few head bumps, I finally managed to get everything installed.

pip install pandas sqlite3 jupyter

But wait, there’s more! I also needed to install some additional packages for logging and testing.

pip install unittest logging

Facing the First Boss: Database Connection

With everything set up, it was time to connect to the database. This felt like facing the first boss. I opened the cademycode.db file, unsure of what awaited me inside. Using SQLite, I established a connection and was ready to explore the data.

import sqlite3

con = sqlite3.connect('dev/cademycode.db')
print('Database connection established successfully.')

Suffice to say, the database was indeed as messy as Bowser’s castle. But that’s a story for the next section.

In the next part of our adventure, we'll dive into inspecting and cleaning the data. Get ready to battle missing values, inconsistent data types, and duplicates galore!

Battling the Data Monsters

With the setup complete and the database connection established, it was time to dive into the data. This part of the journey felt like battling hordes of Koopa Troopas. Every step revealed new challenges, but with determination (and some Italian pasta), I tackled them head-on.

Data Inspection

The first step was to inspect the data and understand the lay of the land. Using Pandas, I loaded the tables from cademycode.db into DataFrames and took a peek at what I was dealing with.

import pandas as pd

tables = pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table';", con)
table_names = tables['name'].tolist()

df = {table: pd.read_sql_query(f"SELECT * FROM {table}", con) for table in table_names}

for table, data in df.items():
    print(f"Table: {table}")
    print(data.head())

The output revealed the initial state of the data – missing values, inconsistent data types, and duplicates galore. It was like entering a haunted house in Luigi's Mansion!

(If you're curious about the output messages, you can check them at the project's Jupyter Notebook.)

Handling Missing Values

Next, I identified and handled the missing values. This was akin to collecting power-ups to boost my chances of success. For some columns, I filled the missing values with zeros, while for others, I used the median value.

students_df = df['cademycode_students'].copy()

# Fill missing values
students_df['job_id'].fillna(0, inplace=True)
students_df['current_career_path_id'].fillna(0, inplace=True)
students_df['num_course_taken'].fillna(students_df['num_course_taken'].median(), inplace=True)
students_df['time_spent_hrs'].fillna(students_df['time_spent_hrs'].median(), inplace=True)

Again, the choice on whether I filled data with zeroes or with median values is explained in the project's Jupyter Notebook, but in a nutshell it was because job_id and current_career_path_idwere given 0 to indicate 'unemployed' and 'not enrolled' status.

Correcting Data Types

The next challenge was correcting inconsistent data types. This felt like trying to fit puzzle pieces together. With a bit of Pandas magic, I converted columns to their appropriate data types.

# Convert data types
students_df['dob'] = pd.to_datetime(students_df['dob'], errors='coerce')
students_df['job_id'] = pd.to_numeric(students_df['job_id'], errors='coerce')
students_df['num_course_taken'] = pd.to_numeric(students_df['num_course_taken'], errors='coerce')
students_df['current_career_path_id'] = pd.to_numeric(students_df['current_career_path_id'], errors='coerce')
students_df['time_spent_hrs'] = pd.to_numeric(students_df['time_spent_hrs'], errors='coerce')

Dealing with Duplicates

No Mario adventure is complete without encountering duplicates – like those pesky Bullet Bills that keep reappearing! I identified and removed duplicate records to ensure the data was clean.

# Remove duplicates
students_df.drop_duplicates(inplace=True)
jobs_df_cleaned = df['cademycode_student_jobs'].drop_duplicates()

Extracting Nested Data

One of the trickiest parts was dealing with nested data in the contact_info column. It was like the water stage. To nail it, I had to write a function to extract the nested information and split it into separate columns.

Before we continue, we should notice that the data in contact_info was in the form of a json object, containing the mailing address and an email, such as:

{"mailing_address": "470 Essex Curve, Copan, Mississippi, 86309", "email": "cleopatra_singleton7791@inlook.com"}

(Here is a nice place to add that the data used in this project is fictional, so don't worry!)

So, in order to extract that, we could treat it like a json object after all. So that's what we did:

import json

def extract_contact_info(contact_info):
    try:
        info = json.loads(contact_info.replace("'", '"'))
        return pd.Series([info.get('mailing_address'), info.get('email')])
    except json.JSONDecodeError:
        return pd.Series([None, None])

students_df[['mailing_address', 'email']] = students_df['contact_info'].apply(extract_contact_info)
students_df.drop(columns=['contact_info'], inplace=True)

With the data cleaned and ready, I felt like I had just collected a Super Star power-up. But the adventure was far from over. Next up, I had to create the output CSV and ensure it was analytics-ready.

The Final Battle for Clean Data

With the data cleaned and ready, it was time for the final showdown: combining the data into a single, analytics-ready CSV. You know, like grabbing giant Bowser by the tail and throwing him around in Super Mario 64. There were obstacles to overcome, but I was determined to save the day (or in this case, the data).

Combining the Data

First, I needed to combine the cleaned data from multiple tables into a single DataFrame. Using Pandas, I performed the necessary joins to bring everything together:

# Merge dataframes
merged_df_cleaned = pd.merge(students_df, jobs_df_cleaned, how='left', left_on='job_id', right_on='job_id')
final_df_cleaned = pd.merge(merged_df_cleaned, df['cademycode_courses'], how='left', left_on='current_career_path_id', right_on='career_path_id')

Validating the Final Dataset

Once the data was combined, I needed the flagpole at the end of the level: to validate the final dataset. I checked for any inconsistencies or missing values that might have slipped through.

# Fill remaining missing values
final_df_cleaned = final_df_cleaned.assign(
    career_path_id=final_df_cleaned['career_path_id'].fillna(0),
    career_path_name=final_df_cleaned['career_path_name'].fillna('Unknown'),
    hours_to_complete=final_df_cleaned['hours_to_complete'].fillna(0)
)

Generating the Output CSV

With the data validated, it was time to generate the final CSV. I mean, even Super Mario Bros had a save game feature, right?:

# Save final DataFrame to CSV
final_df_cleaned.to_csv('dev/final_output.csv', index=False)

Overcoming Challenges

Of course, no epic battle is without its challenges. One of the biggest hurdles was ensuring that the final DataFrame retained all the original rows and that no data was lost during the merges. After some debugging (and a few extra lives), I successfully retained the integrity of the data.

Celebrating the Victory

Finally, with the output CSV generated and validated, it was a triumphant moment. I could rest knowing that the data was now clean and ready for analysis.

Or could I?

With the final CSV in hand, the next step was to ensure that the pipeline could run automatically with minimal intervention. This meant developing unit tests and logs to keep everything in check.

Our Princess Is in Another Castle

After all the hard work of cleaning and combining the data, it might feel like the job is done. But as any Mario fan knows, "our princess is in another castle!" The journey isn't complete until the pipeline is foolproof and can run automatically without constant supervision. This meant developing unit tests and logging to ensure everything runs smoothly.

The Importance of Unit Tests

Unit tests are like Mario's power-ups—they help you tackle challenges and keep you safe from unexpected pitfalls. I implemented unit tests to ensure the data integrity and functionality of the pipeline. These tests checked for things like schema consistency, the presence of null values, and the correct number of rows.

import unittest

class TestDataCleaning(unittest.TestCase):
    def test_no_null_values(self):
        self.assertFalse(final_df_cleaned.isnull().values.any(), "There are null values in the final table")

    def test_correct_number_of_rows(self):
        original_length = len(df['cademycode_students'])
        final_length = len(final_df_cleaned)
        self.assertEqual(original_length, final_length, "The number of rows differs after the merges")

    def test_schema_consistency(self):
        original_schema = set(df['cademycode_students'].columns)
        final_schema = set(final_df_cleaned.columns)
        original_schema.discard('contact_info')
        original_schema.update(['mailing_address', 'email'])
        self.assertTrue(original_schema.issubset(final_schema), "The final table schema does not include all original columns")

if __name__ == '__main__':
    unittest.main(argv=['first-arg-is-ignored'], exit=False)

Implementing Logging

Logging is essential for tracking the pipeline's execution and troubleshooting issues. Think of it as Mario's map—it helps you see where you've been and identify any trouble spots. I implemented logging to record each step of the pipeline, including updates and errors.

import logging

logging.basicConfig(filename='logs/data_pipeline.log', level=logging.INFO, 
                    format='%(asctime)s:%(levelname)s:%(message)s')

def log_update(message):
    logging.info(message)

def log_error(message):
    logging.error(message)

try:
    # Pipeline code...
    log_update("Pipeline executed successfully.")
except Exception as e:
    log_error(f"Error running the pipeline: {e}")
    raise

Creating the Changelog

To keep track of updates, I created a changelog that records version numbers, new rows added, and missing data counts.

def write_changelog(version, new_rows_count, missing_data_count):
    with open('logs/changelog.txt', 'a') as f:
        f.write(f"Version: {version}\n")
        f.write(f"New rows added: {new_rows_count}\n")
        f.write(f"Missing data count: {missing_data_count}\n")
        f.write("\n")

Challenges and Learnings

One of the biggest challenges was ensuring the unit tests covered all edge cases. Oh, when we see 'OK' in the test suite, that feeling is unbeatable! And, with thorough testing and logging, I ensured the pipeline was robust and reliable.

Conclusion

With unit tests and logging in place, I felt confident that my pipeline could handle anything thrown its way. It was a moment of triumph, like finally rescuing Princess Peach after a long adventure.

Now, all that's left was to create a Bash script to automate the pipeline and move the updated files to the production directory. Talk about more data plumbing fun!

Automating the Pipeline—Mario Kart Style

With the data cleaned, combined, and validated, and with unit tests and logging in place, it was time to put the pipeline on autopilot. Like Mario's Kart has to be race—ready, everything needed to run smoothly and efficiently, with no banana peels or red shells in sight.

Purpose of the Bash Script

The Bash script propelled the pipeline forward with speed and precision, you know, like shooting turtle shells at other racers (but a little less fun. Just a little!). It was designed to:

Execute the Python script that runs the data pipeline. 2.Check the changelog to determine if an update occurred. 3.Move the updated files from the working directory to the production directory.

The Script

Here's the Bash script that made it all possible:

#!/bin/bash

# Path to the Python script
PYTHON_SCRIPT="/home/jampamatos/workspace/codecademy/Data/subscriber-pipeline/main.py"

# Path to the production directory
PROD_DIR="/home/jampamatos/workspace/codecademy/Data/subscriber-pipeline/prod"

# Path to the changelog
CHANGELOG="/home/jampamatos/workspace/codecademy/Data/subscriber-pipeline/log/changelog.txt"

# Current version from the changelog
CURRENT_VERSION=$(grep -oP 'Version: \K.*' $CHANGELOG | tail -1)

# Execute the Python script
python3 $PYTHON_SCRIPT

# Check if the script executed successfully
if [ $? -eq 0 ]; then
    echo "Pipeline executed successfully."

    # New version from the changelog
    NEW_VERSION=$(grep -oP 'Version: \K.*' $CHANGELOG | tail -1)

    # Check if there was an update
    if [ "$CURRENT_VERSION" != "$NEW_VERSION" ]; then
        echo "Update detected. Moving files to production."

        # Move updated files to the production directory
        mv /home/jampamatos/workspace/codecademy/Data/subscriber-pipeline/dev/clean_cademycode.db $PROD_DIR/
        mv /home/jampamatos/workspace/codecademy/Data/subscriber-pipeline/dev/final_output.csv $PROD_DIR/

        echo "Files moved to production."
    else
        echo "No updates detected. No files moved to production."
    fi
else
    echo "Pipeline execution failed. Check logs for details."
fi

Challenges on the Track

Like any Mario Kart race, there were more than a few obstacles along the way. One of the trickiest parts was ensuring the script correctly identified updates and moved the files only when necessary. After a few laps of testing and tweaking, I had the script running smoothly.

Creating an Alias

To make running the script as easy as throwing a green shell, I created an alias. This allowed me to execute the script with a simple command, no matter where I was in the terminal.

alias run_pipeline="/home/jampamatos/workspace/codecademy/Data/subscriber-pipeline/run_pipeline.sh"

By adding this line to my ~/.bashrc file and reloading the shell, I could start the pipeline with a single command:

run_pipeline

With the Bash script in place, my pipeline was ready to zoom along the track with minimal human intervention. It was a satisfying moment, like crossing the finish line in first place.

Wrap-up

After navigating through a maze of messy data, cleaning and validating it, automating the process, and ensuring everything runs smoothly with unit tests and logging, we've finally crossed the finish line. It’s been a wild ride, but let's take a moment to reflect on our adventure.

Summary of Tasks

Throughout this project, we aimed to build a data engineering pipeline to transform a messy database of long-term canceled subscribers into a clean, analytics-ready dataset. Here's a summary of the key tasks we accomplished:

Setting Up the Project: We began by setting up our working directory and ensuring all necessary files and tools were in place.
Inspecting and Cleaning the Data: We imported the tables from cademycode.db into dataframes, inspected them for missing or invalid data, and performed various data cleaning operations. This included handling null values, correcting data types, and dealing with duplicates.
Creating the Output CSV: Using the cleaned data, we produced an analytics-ready SQLite database and a flat CSV file. We validated the final table to ensure no data was lost or duplicated during the joins.
Developing Unit Tests and Logs: We converted our Jupyter Notebook into a Python script. The script includes unit tests to check for updates to the database and to protect the update process. It also includes logging to track updates and errors.
Creating the Bash Script: We created a Bash script to handle running the Python script and moving updated files from the working directory to the production directory. The script checks the changelog to determine if an update occurred before moving the files.

Final Thoughts

Building my first automated data pipeline was an exciting and challenging journey. It felt like a typical Super Mario stage, filled with obstacles and power-ups. Along the way, I learned valuable lessons about data cleaning, automation, and the importance of thorough testing and logging.

Conclusion

In conclusion, this project successfully demonstrates how to build a robust data engineering pipeline that automates the transformation of raw data into a clean and usable format. By following a structured approach, we ensured that the pipeline is reliable, maintainable, and easy to understand. The inclusion of unit tests and logging provides additional safeguards and transparency, making it easier to monitor and debug the process.

This project not only serves as a valuable addition to my portfolio but also equips me with practical experience in handling real-world data engineering challenges. The skills and methodologies applied here are transferable to a wide range of data engineering tasks, ensuring I am well-prepared for future projects and roles in the field.

Thank you for joining me on this adventure! If you have any questions or comments, feel free to leave them below. I’d love to hear about your own data engineering experiences and any tips you might have. Until next time, keep racing towards your data goals and may your pipelines always be free of banana peels!

Pedaling Through Data: A Wheelie Fun Bike Rental Analysis with Python and PostgreSQL

Jampa Matos — Wed, 05 Jun 2024 19:00:46 +0000

Introduction

Hey everyone!

Ever wondered how weather affects bike rentals in the city? Well, Codecademy did too! As part of their Data Engineering Course, they handed me some data and said, "Analyze this!" So, I grabbed my Python, Jupyter Notebook, and PostgreSQL, and embarked on a data adventure to uncover the secrets of bike rental patterns.

In this post, I'll walk you through the process, share some cool insights, and maybe even crack a few jokes along the way. So, grab your helmet, and let's ride through this data journey together!

Data Preparation

First things first, let's talk data. For this project, Codecademy provided two datasets:

Citi Bike ridership data: This gives us the lowdown on when and where people are riding those snazzy blue bikes.
NOAA weather data: Because we need to know if it's raining cats and dogs or if it's sunny and perfect for a ride.

Cleaning and transforming the data is like tuning up your bike – it's gotta be done before you hit the road. Here are some highlights of what I did:

Dealt with missing values: Because incomplete data is like riding a bike with a flat tire.
Merged the datasets: Think of it as combining the best parts of two bikes into one super-bike.
Formatted columns: Making sure dates look like dates and not some weird alien language.

Here's a snippet of the code that got the job done (you can check out the entire code at the GitHub repository :

import pandas as pd

# Load the datasets
bike_data = pd.read_csv('citi_bike_data.csv')
weather_data = pd.read_csv('weather_data.csv')

# Handle missing values
bike_data.fillna(0, inplace=True)
weather_data.fillna(method='ffill', inplace=True)

# Merge datasets on the date column
merged_data = pd.merge(bike_data, weather_data, on='date')

# Format columns
merged_data['date'] = pd.to_datetime(merged_data['date'])

With the data prepped and ready, it was time to create the backbone of our analysis – the PostgreSQL database.

Setting Up PostgreSQL

Building a database schema is like designing the perfect bike frame – it needs to be sturdy and efficient. Here's the lowdown on how I set up my PostgreSQL database:

Tables: Created tables for bike rentals and weather data.
Relationships: Linked the tables on the date column because it's all about timing!

Here's a sneak peek at the SQL code for creating the tables:

CREATE TABLE bike_rentals (
    ride_id SERIAL PRIMARY KEY,
    date DATE,
    start_station VARCHAR(100),
    end_station VARCHAR(100),
    user_type VARCHAR(50)
);

CREATE TABLE weather (
    weather_id SERIAL PRIMARY KEY,
    date DATE,
    temperature FLOAT,
    precipitation FLOAT,
    snowfall FLOAT
);

After setting up the tables, I inserted the cleaned data into PostgreSQL. Smooth sailing so far!

Data Analysis

Now comes the fun part – analyzing the data! WiGitHub repositoryth our PostgreSQL database set up, it was time to dig into the data and uncover some insights. Here are the key questions we aimed to answer:

How does weather affect the number of bike rentals?
Are there specific weather conditions that lead to a spike or drop in bike rentals?

To tackle these questions, I used SQL to query the database and visualize the results with Python. Here’s an example of one of the SQL queries I used:

SELECT 
    br.date, 
    br.user_type, 
    COUNT(br.ride_id) AS trip_count, 
    w.temperature, 
    w.precipitation
FROM 
    bike_rentals br
JOIN 
    weather w ON br.date = w.date
GROUP BY 
    br.date, br.user_type, w.temperature, w.precipitation
ORDER BY 
    br.date;

This query gave us a detailed view of bike rentals per day, categorized by user type and correlated with temperature and precipitation.

Results and Discussion

Here are some of the cool insights we uncovered:

Temperature vs. Bike Rentals: Warmer days see more bike rentals. No surprises there! Who doesn't love a sunny ride?
Precipitation vs. Bike Rentals: Rainy days? Not so much. Bike rentals drop significantly when it’s raining.

Here’s a visualization of the temperature vs. trip counts:

import matplotlib.pyplot as plt

# Sample data for plotting
temperature = merged_data['temperature']
trip_counts = merged_data['trip_count']

plt.scatter(temperature, trip_counts)
plt.title('Temperature vs. Bike Rentals')
plt.xlabel('Temperature (°C)')
plt.ylabel('Trip Counts')
plt.show()

And another one for precipitation:

precipitation = merged_data['precipitation']

plt.scatter(precipitation, trip_counts)
plt.title('Precipitation vs. Bike Rentals')
plt.xlabel('Precipitation (mm)')
plt.ylabel('Trip Counts')
plt.show()

As expected, there’s a strong positive correlation between warm weather and bike rentals. On the flip side, rain is a big no-no for bike enthusiasts.

Conclusion

This project was a blast to work on and gave me some great insights into how weather affects bike rentals. It was a perfect blend of data cleaning, database management, and analysis – all while pedaling through Codecademy's Data Engineering Course.

If you have any thoughts, suggestions, or just want to share your own bike rental stories, drop a comment below! Let's keep the conversation rolling.

Code and Data

You can check out the complete code and datasets on my GitHub repository. Feel free to fork it, play around with the data, and maybe even come up with your own insights.

Thanks for reading, and happy riding!

Lazy Programmer's Guide to Automating Price Calculations

Jampa Matos — Thu, 23 May 2024 16:17:17 +0000

Introduction: Automating Price Calculations for the Lazy (and Efficient) Programmer

Let's be honest: programmers, whether seasoned or aspiring, are driven by a certain kind of laziness. But this isn't the couch-potato kind; it's the kind that fuels innovation — finding ways to get more done with less effort. Why spend hours on repetitive tasks when a few lines of code can do the job for you?

This mindset has been my guiding star as I navigate my dual roles. On my spare time, I code; at nine-to-five (seven-thirty-to-five-thirty, actually), I manage my dad's paint store. Among my various responsibilities, one of the most daunting is calculating the cost and selling prices of our products. Now, if you're familiar with Brazil's tax system, you'll understand the gravity of this task. For those who aren't, let me paint you a picture.

In Brazil, taxes are a labyrinthine mess. Beyond federal tax rates, each state has the liberty to set its own tax rates, creating a patchwork of regulations that would make any sane person’s head spin. Enter 'substituição tributária' (ST) — a regime where the tax burden is transferred from the seller to the manufacturer or importer. The idea is to streamline tax collection, but in practice, it often complicates matters further, especially when products cross state lines.

Calculating these costs manually is not just tedious; it's a soul-crushing endeavor. The repetitive nature of punching numbers into a calculator, cross-referencing tax tables, and double-checking figures is enough to make anyone yearn for a better way. That's where my programmer's "laziness" kicked in. I saw a clear need for automation — a way to take these convoluted calculations and simplify them into a process that’s as effortless as possible.

Thus, the web-based price calculator project was born. Armed with formulas from my accountant and a determination to streamline our pricing workflow, I set out to build a tool that could handle the heavy lifting. This post will take you through the journey of its creation, detailing the code, the challenges, and the solutions. Along the way, I’ll share insights into how you too can leverage programming to simplify complex tasks in your work or business. So, let's dive in and explore how we can turn lazy into efficient.

The Accountant's Formulas: Transforming Tedious Calculations into Computable Algorithms

In the world of business, especially one as intricate as a paint store, pricing products accurately is crucial. My accountant, the wizard behind our financial curtain, provided me with a set of formulas to calculate our costs and selling prices. These formulas consider various taxes and additional charges that are part and parcel of Brazil's complex tax landscape.

The general formula for calculating the cost is as follows:

Cost Price = Unit Price + ICMS(%) + IPI(%) + Freight(%) Selling Price = Cost Price + Markup(%) + Additional(%) + Federal Tax (fixed at 7%)

It’s important to note that each percentage is applied on top of the previous values, compounding the total cost incrementally.

But it's not as straightforward as plugging numbers into a formula. The calculation of ICMS (a state tax) depends on whether the product falls under 'substituição tributária' (ST) or not, and this is where things get interesting.

For products without ST (identified by CFOP codes 5101, 5102, 6101, and 6102):

If the ICMS rate is 4%, the effective ICMS in the formula becomes 13.27%.
If the ICMS rate is 12%, the effective ICMS in the formula becomes 7.32%.

For products with ST (identified by CFOP codes 5401, 5403, 5405, 6401, 6403, and 6405):

If the IPI rate is 0%, the ICMS in the formula is 19.7%.
If the IPI rate is 1.3%, the ICMS in the formula is 20.3%.
If the IPI rate is 3.25%, the ICMS in the formula is 21.26%.
If the IPI rate is 6.5%, the ICMS in the formula is 24%.

I don’t entirely understand the logic behind these values — they’re the magic numbers handed down by my accountant. But I trust her expertise, and these are the values we need to work with.

Manual Calculations: A Tedious Routine

Before automation, the process was as follows:

Gather Information: Collect the unit price, ICMS rate, IPI rate, freight percentage (if any), markup percentage, and additional charges.
Apply Formulas: Using the provided formulas, manually calculate the effective ICMS rate based on the CFOP code and applicable ICMS/ IPI rates.
Calculate Cost Price: Add up all the percentages and apply them to the unit price.
Calculate Selling Price: Add the calculated cost price to the markup, additional charges, and federal tax.
**Repeat: **Perform these steps for each product, using a calculator to ensure accuracy.

This manual process was not only time-consuming but also prone to errors. Every miscalculation could lead to incorrect pricing, impacting our profit margins or making us uncompetitive.

From Manual to Algorithmic

The beauty of these steps is that they form an algorithm, a series of computable steps. And this is where automation shines. By translating these steps into code, I could create a tool that performs these calculations instantly and accurately. No more tedious number crunching, no more manual errors — just a streamlined, efficient process that ensures we get our pricing right every time.

In the next section, I'll walk you through how I transformed these formulas into a web-based price calculator, turning hours of work into a task that takes mere seconds.

Implementing the First Part: From Supplier Invoices to a Functional MVP

In our paint store, every supplier sends us invoices in the form of XML files. These XML files are standardized by the Federal Revenue Service of Brazil, ensuring they follow a consistent structure. This consistency makes it feasible to automate the extraction of necessary data for our price calculations.

Technologies Used

To build this solution, we chose a stack that includes:

Flask: A lightweight web framework for Python that makes it easy to set up routes and handle requests.
Jinja2: The templating engine used by Flask to render HTML pages dynamically.
JavaScript (with jQuery): To handle client-side interactions and make AJAX requests for dynamic updates.

Setting Up Routes

For the initial implementation, we set up two main routes: indexand calculate.

`index`Route

The index route serves the main page of our application. It renders the form that allows users to upload an XML file, input the markup percentage, additional charges, and freight costs. We also set default values for these inputs to streamline the user experience.

@app.route('/')
def index():
    return render_template('index.html', product_data=[], markup=50, additional=5, freight=0, include_tax=False)

`calculate`Route

The calculateroute processes the uploaded XML file. It extracts the necessary data, applies the formulas provided by my accountant, and calculates the cost and selling prices. This route then renders the results back to the index.html template.

@app.route('/calculate', methods=['POST'])
def calculate():
    if 'xmlFile' not in request.files:
        return redirect(url_for('index'))

    file = request.files['xmlFile']
    if file.filename == '':
        return redirect(url_for('index'))

    markup = float(request.form['markup'])
    additional = float(request.form['additional'])
    freight = float(request.form['freight'])
    include_tax = 'federalTax' in request.form

    namespaces = {'nfe': 'http://www.portalfiscal.inf.br/nfe'}
    product_data = extract_data_from_xml(file, namespaces)

    for product in product_data:
        cost_price = calculate_cost_price(product['Valor Unitário'], product['Alíq. ICMS'], product['Alíq. IPI'], product['CFOP'], freight)
        selling_price = calculate_selling_price(cost_price, markup, additional, include_tax)
        product['Preço de Custo Final'] = cost_price
        product['Preço de Venda'] = selling_price

    return render_template('index.html', product_data=product_data, markup=markup, additional=additional, freight=freight, include_tax=include_tax)

Crafting the MVP

The Minimum Viable Product (MVP) focused on getting the basic functionality right. This included:

Form Setup: An HTML form for uploading XML files and inputting calculation parameters (markup, additional charges, freight, and federal tax inclusion).
Data Extraction: Using Python's xml.etree.ElementTree to parse the XML files and extract relevant product data.
Calculation Logic: Implementing the cost and selling price formulas in Python.
Result Display: Dynamically rendering the calculated prices back to the user through the HTML template.

Here’s the index.html template for the form and result display:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Calculadora de Preço de Venda</title>
    <link rel="stylesheet" href="/static/style.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type="application/json" id="productData">
        {{ product_data|tojson }}
    </script>
    <script type="application/json" id="additionalData">
        {{ additional }}
    </script>
    <script type="application/json" id="freightData">
        {{ freight }}
    </script>
    <script type="application/json" id="includeTaxData">
        {{ include_tax|tojson }}
    </script>
    <script src="/static/script.js"></script>
</head>
<body>
    <h1>Calculadora de Preço de Venda</h1>
    <form action="/calculate" method="post" enctype="multipart/form-data">
        <label for="xmlFile">Carregar XML:</label>
        <input type="file" id="xmlFile" name="xmlFile" accept=".xml"><br><br>

        <label for="markup">Markup (%):</label>
        <input type="number" id="markup" name="markup" step="0.1" value="50"><br><br>

        <label for="additional">Valor Adicional (%):</label>
        <input type="number" id="additional" name="additional" step="0.1" value="5"><br><br>

        <label for="freight">Frete (%):</label>
        <input type="number" id="freight" name="freight" step="0.1" value="0"><br><br>

        <label for="federalTax">Incluir Imposto Federal (7%):</label>
        <input type="checkbox" id="federalTax" name="federalTax" checked><br><br>

        <button type="submit">Calcular</button>
    </form>

    {% if product_data %}
        <h2>Dados do Formulário:</h2>
        <p>
            Markup Selecionado: {{ markup }}%<br>
            Valor Adicional Selecionado: {{ additional }}%<br>
            Frete Adicionado: {{ freight }}%<br>
            Incluir Imposto Federal: {{ 'Sim' if include_tax else 'Não' }}
        </p>
        <h2>Cálculo dos Preços:</h2>
        <table border="1">
            <tr>
                <th>Produto</th>
                <th>CFOP</th>
                <th>Custo Unit</th>
                <th>Aliq. ICMS</th>
                <th>Aliq. IPI</th>
                <th>Frete</th>
                <th>Imposto Federal</th>
                <th>Markup (%)</th>
                <th>Custo Final</th>
                <th>Venda Final</th>
            </tr>
            {% for product in product_data %}
            <tr>
                <td>{{ product['Nome do Produto'] }}</td>
                <td>{{ product['CFOP'] }}</td>
                <td>R$ {{ product['Valor Unitário'] }}</td>
                <td>{{ product['Alíq. ICMS'] }}</td>
                <td>{{ product['Alíq. IPI'] }}</td>
                <td>{{ freight }}%</td>
                <td>{{ 'Sim' if include_tax else 'Não' }}</td>
                <td><input type="number" class="markup-input" step="0.1" value="{{ markup }}" data-index="{{ loop.index0 }}"></td>
                <td>R$ {{ '%.2f' | format(product['Preço de Custo Final']) }}</td>
                <td>R$ {{ '%.2f' | format(product['Preço de Venda']) }}</td>
            </tr>
            {% endfor %}
        </table>
    {% endif %}
</body>
</html>

With this MVP in place, we had a functional tool that could handle the complexities of our pricing calculations, saving us time and reducing the potential for errors. But this was just the beginning.

Adding Interactivity: Editable Markup Fields for Dynamic Price Updates

In the real world, the need for flexibility in pricing is paramount. Sometimes, different products on the same invoice require different markup percentages. Manually re-uploading the XML file and recalculating prices each time a markup needs to be adjusted is not only inefficient but also prone to errors. To address this, I introduced an interactive, editable markup field that updates the selling price automatically. This is where JavaScript comes into play.

Enhancing User Experience with JavaScript

To achieve this dynamic behavior, we needed to:

Make the markup field editable directly within the product table.
Use JavaScript to detect changes in the markup field and trigger recalculations.
Send the updated data to the server via AJAX and update the selling price without refreshing the page.

Here's a detailed breakdown of the implementation:

1. Updating the HTML Template

First, we needed to ensure the markup field in our HTML table was editable and had the necessary attributes for our JavaScript to identify and manipulate it.

<td><input type="number" class="markup-input" step="0.1" value="{{ markup }}" data-index="{{ loop.index0 }}"></td>

This line creates an input field within the table cell, sets its initial value to the current markup, and adds a data-indexattribute to keep track of the product's position in the list.

2. JavaScript for Real-Time Updates

We then added a JavaScript file to handle the dynamic updates. This script listens for changes in the markup input fields and makes AJAX requests to update the prices accordingly.

Here’s the content of static/script.js:

$(document).ready(function() {
    const productData = JSON.parse(document.getElementById('productData').textContent);
    const additional = JSON.parse(document.getElementById('additionalData').textContent);
    const freight = JSON.parse(document.getElementById('freightData').textContent);
    const includeTax = JSON.parse(document.getElementById('includeTaxData').textContent);

    $('.markup-input').on('input', function() {
        let markup = $(this).val();
        let index = $(this).data('index');
        updatePrices(markup, index);
    });

    function updatePrices(markup, index) {
        $.ajax({
            url: '/update_prices',
            method: 'POST',
            contentType: 'application/json',
            data: JSON.stringify({
                markup: markup,
                index: index,
                product_data: productData,
                additional: additional,
                freight: freight,
                include_tax: includeTax
            }),
            success: function(data) {
                $('table').find('tr').eq(index + 1).find('td').eq(9).text('R$ ' + data.new_selling_price.toFixed(2));
            }
        });
    }
});

This script does the following:

Reads the product data and other relevant values from the hidden JSON elements in the HTML.
Listens for input events on the markup fields.
Sends an AJAX request to the server whenever a markup value changes, passing the updated markup and necessary data.
Updates the displayed selling price with the new value returned from the server.

3. Handling AJAX Requests in Flask

We needed a new route in our Flask app to handle the AJAX requests and perform the necessary recalculations.

Here’s the updated main.py:

@app.route('/update_prices', methods=['POST'])
def update_prices():
    data = request.get_json()
    index = data['index']
    markup = float(data['markup'])
    additional = data['additional']
    freight = data['freight']
    include_tax = data['include_tax']
    product_data = data['product_data']

    product = product_data[index]
    cost_price = calculate_cost_price(product['Valor Unitário'], product['Alíq. ICMS'], product['Alíq. IPI'], product['CFOP'], freight)
    new_selling_price = calculate_selling_price(cost_price, markup, additional, include_tax)

    return jsonify({'new_selling_price': new_selling_price})

This route:

Receives the updated markup and product data via a JSON payload.
Recalculates the cost and selling prices using the same formulas as before.
Returns the new selling price to the client, which JavaScript then updates in the table.

4. Adjusting the HTML Template

Finally, ensure that the HTML template includes the necessary scripts and hidden JSON data.

Here’s the updated index.html:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Calculadora de Preço de Venda</title>
    <link rel="stylesheet" href="/static/style.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type="application/json" id="productData">
        {{ product_data|tojson }}
    </script>
    <script type="application/json" id="additionalData">
        {{ additional }}
    </script>
    <script type="application/json" id="freightData">
        {{ freight }}
    </script>
    <script type="application/json" id="includeTaxData">
        {{ include_tax|tojson }}
    </script>
    <script src="/static/script.js"></script>
</head>
<body>
    <h1>Calculadora de Preço de Venda</h1>
    <form action="/calculate" method="post" enctype="multipart/form-data">
        <label for="xmlFile">Carregar XML:</label>
        <input type="file" id="xmlFile" name="xmlFile" accept=".xml"><br><br>

        <label for="markup">Markup (%):</label>
        <input type="number" id="markup" name="markup" step="0.1" value="50"><br><br>

        <label for="additional">Valor Adicional (%):</label>
        <input type="number" id="additional" name="additional" step="0.1" value="5"><br><br>

        <label for="freight">Frete (%):</label>
        <input type="number" id="freight" name="freight" step="0.1" value="0"><br><br>

        <label for="federalTax">Incluir Imposto Federal (7%):</label>
        <input type="checkbox" id="federalTax" name="federalTax" checked><br><br>

        <button type="submit">Calcular</button>
    </form>

    {% if product_data %}
        <h2>Dados do Formulário:</h2>
        <p>
            Markup Selecionado: {{ markup }}%<br>
            Valor Adicional Selecionado: {{ additional }}%<br>
            Frete Adicionado: {{ freight }}%<br>
            Incluir Imposto Federal: {{ 'Sim' if include_tax else 'Não' }}
        </p>
        <h2>Cálculo dos Preços:</h2>
        <table border="1">
            <tr>
                <th>Produto</th>
                <th>CFOP</th>
                <th>Custo Unit</th>
                <th>Aliq. ICMS</th>
                <th>Aliq. IPI</th>
                <th>Frete</th>
                <th>Imposto Federal</th>
                <th>Markup (%)</th>
                <th>Custo Final</th>
                <th>Venda Final</th>
            </tr>
            {% for product in product_data %}
            <tr>
                <td>{{ product['Nome do Produto'] }}</td>
                <td>{{ product['CFOP'] }}</td>
                <td>R$ {{ product['Valor Unitário'] }}</td>
                <td>{{ product['Alíq. ICMS'] }}</td>
                <td>{{ product['Alíq. IPI'] }}</td>
                <td>{{ freight }}%</td>
                <td>{{ 'Sim' if include_tax else 'Não' }}</td>
                <td><input type="number" class="markup-input" step="0.1" value="{{ markup }}" data-index="{{ loop.index0 }}"></td>
                <td>R$ {{ '%.2f' | format(product['Preço de Custo Final']) }}</td>
                <td>R$ {{ '%.2f' | format(product['Preço de Venda']) }}</td>
            </tr>
            {% endfor %}
        </table>
    {% endif %}
</body>
</html>

With these enhancements, we transformed a static form into an interactive tool that allows for real-time price adjustments. This not only streamlines our workflow but also provides flexibility in pricing, making the tool much more powerful and user-friendly. In the next section, I'll discuss potential future iterations and improvements to further enhance the functionality and usability of this project.

Future Iterations: Enhancing Functionality and User Experience

With the basic functionality in place, there are several enhancements that can make the price calculator even more robust and user-friendly. Here are the next steps I'm considering for future iterations:

1. Configurations Page

Currently, the tax rates and CFOP codes are hard-coded into the application. This approach works for an MVP, but it's not scalable. Tax rates can change, and new CFOP codes with different tax regimes can emerge. To address this, I plan to implement a configurations page where users can update these values directly within the app.

Editable Tax Rates and CFOP Codes: Allow users to add, edit, and delete tax rates and CFOP codes.
Persistent Settings: Store these configurations in a database to ensure they are retained between sessions.

2. Drag-and-Drop XML Upload

To improve the user experience, I want to add drag-and-drop functionality for XML uploads. This would make the process more intuitive and quicker.

Drag-and-Drop Interface: Implement a drag-and-drop zone for users to easily upload XML files.
Fallback to Button Upload: Maintain the current file upload button as a fallback option.

3. Hosting on Store’s Domain

Currently, the application is running on a Heroku server. To give it a more professional look and ensure better accessibility, I plan to host it on the store's domain.

Custom Domain: Migrate the app to be hosted on the store's domain.
Enhanced Accessibility: Ensure the app is easily accessible to all users, including employees and customers.

4. Improved Aesthetics with CSS

While functionality is king, aesthetics shouldn't be neglected. A well-designed interface can significantly enhance the user experience. I plan to use CSS to make the page more visually appealing.

Modern UI Design: Implement a clean, modern design for the app using CSS.
Responsive Design: Ensure the app works well on different devices, including desktops, tablets, and smartphones.

Conclusion

The journey of creating this web-based price calculator has been both challenging and rewarding. From automating tedious manual calculations to adding dynamic, real-time updates, each step has made the app more efficient and user-friendly. But the journey doesn't end here. With future iterations focusing on configurability, user experience, and professional hosting, the app will continue to evolve and serve its purpose even better.

Stay tuned for more updates as I continue to enhance and refine this project. If you're on a similar journey or have any suggestions, I'd love to hear from you! Let's keep innovating and making our lives (and jobs) a bit easier, one line of code at a time.

Golds and Dragons: the Quest of an AI Awakened World

Jampa Matos — Tue, 14 Feb 2023 01:03:15 +0000

So, I have just finished watching Tom Scott's latest video, on how new AI Language Models are a bit scaring, because we really don't know where we're heading from here. I won't paraphrase him here or anything, but, in a nutshell, he says that new technologies follow a typical sigmoid curve, with a slow start as they are being developed, a steep increase as new ideas reshape the technology, and a flattening of the curve as the technology saturates, and that he really don't know where in the curve technologies like ChatGPT are right now, meaning: we could be in the middle, where new, exciting ways to use AI will be developed, in the end, where we've reached the peek of what AI assistance as we know it, or in the very beginning, where AI Language Models are a thunder, a sign in the distance of everything that is about to change forever.

Unless you're living in a cave, you yourself have probably used some form of AI in the past months: maybe you played a little with generating crazy images by prompt with Dall-e, or discussed philosophy with ChatGPT for fun, but the reality is that AI powered programs are already a tool in a variety of industries and fields, from helping people write documents and essays, to assisting researchers in reading and cataloging information from papers. There have been instances of AI models mimicking established artists' styles to generate new images or text. I have even seen a lawyer on Twitter saying that ChatGPT wrote a document for him from scratch, with minor changes needed to be done, and that even if AI won't replace lawyers anytime soon, they might've just replaced interns.

But it seems to me that it is in computer science -- and in software development in general -- that these technologies show more prominent use. Not only ChatGPT, but things like GitHub's Copilot and Replit's Ghostwriter can turn hours and hours of googling and stack-overflowing in solid minutes of straight up problem solving. For the time I've been using these technologies, it seems to me as if the process of discussing coding with ChatGPT can feel like a junior-senior mentorship: there is back an forth, there is encouragement, there is learning and productive idea shaping. The AI make mistakes, that sometimes we have to point out for it. It corrects our misspelling code or catch one missed ';' that has been driving you crazy for days. We tell it what we want to achieve and it helps us develop the steps we need to follow to write an algorithm. It writes our documentation for us. It explains to us back that one colleague's code that simply does not make sense. There's a plethora of ways you can use these tools to make coding more -- and I really mean to use this word here -- fun.

When I prompted ChatGTP if it "think[s] [it is] a game changer in computer science and software engeneering?", it answered me that "AI language models have certainly been a game changer in the fields of computer science and software engineering, but it remains to be seen what the full extent of their impact will be". And this is something completely difficult to measure -- as Tom said, we still don't know where in the curve we are in right now, and as time rolls by, all will be clear, but as of now, we are in the dark. How can this technology improve in the near future? What challenges will it overcome -- and what others will it generate? How are we going to deal with the fact that machine learning systems require gargantuan amounts of data to be fed into it? Are we going to freely give in to reap the benefits of using this tech? And what about the data itself? Will it make AI systems to perpetuate existing biases and discrimination?

I feel like all of these questions highlight the need for continued reflection on the impact of AI, and the overboard considerations involved in their development and use. While the benefits of using AI language models are clear, we have to consider that, well, maybe they need to be weighted.

I started this post aiming it to be an essay, but I've realized halfway that I don't really know what I want to say about these technologies. I don't really understand them, not as much as I do understand what goes inside Ruby's kernel when I type and run some code. That is to say: if it, as a tool, works for me, than it works. It completes its job, to make my life a little bit easier. And as a tool, it is a wonder.

But, more often than not, a tool might not be just a tool. It may come with strings attached.

And if Tom had a minor existential breakthrough while trying to tidy up his mailbox, I might just have had mine while watching his video.

Nonetheless, we are now, again, in uncharted territory. What comes from it might be gold.

Or dragons.

by the way: while this post was completely written by me, I have used ChatGPT to brainstorm ideas and to fine tune my writing. It did even chose the four tags for me. I guess I'll have to thank it for that. So, thanks, ChatGPT!

Day 23 - The epiphany

Jampa Matos — Tue, 09 Nov 2021 22:33:47 +0000

Well, I've been doing a kind of "Today I learned" blog, but I've now decided to give Dev a go, and bring here all my insights at this whole coding path that I've decided to take.

Funny thing that I had to jump from day 11 to day 23, which makes for almost two weeks without thinking much about what I learned, but I guess it's because I've turned on the automatic at this point. You know, learning new stuff, trying to solve a coding problem, googling it up to find that answer to what to do next, playing videogames when I'm fed up, etc.

But then I came across this new problem -- the last exercise of the Fundamental part of The Odin Project. Nothing big, really, just you typical array of objects.

But then for some reason I froze before it. Couldn't fix it, couldn't solve it, whenever I found something that looked like a solution, the more it seemed to run through my fingers.

Weekend came and went and I'm still there, fighting myself and those little 10 lines of code.

And then, today, at work (and I obviously don't work with coding), it came to me, like a bucket full of cold water hitting me in the face: the solution.

Now, I've seen people talking about how Devs eventually have to stand up and take a walk and do other stuff that the solution eventually might come, and as far-fetch as it sounds, it seems to really work.

I guess I could call it an epiphany. Probably many of several ones yet to come.

Funny thing, the Solemnity of the Epiphany is the Catholic celebration of the Three Wise Kings that came to visit Jesus after he was born, following the Star of Bethlehem. The Brazilian folklore has a special celebration of this, with music, theatrical performances and such. My neighbor is part of the "King's troupe", and they were rehearsing yesterday.

It's a stretch, but, hey, I'll take whatever good news I can get.

Forem: Jampa Matos

Press Start to Continue

Talking about myself

Beat on the brat

For the Win!

Kick ass and chew bubble gum

... as time goes by

All Play and No Work Makes Jampa A Poor Boy

From Messy Data to Super Mario Pipeline: My First Adventure in Data Engineering

Entering the Warp Pipe

Getting Started

Tools and Technologies

Initial Hurdles

Facing the First Boss: Database Connection

Battling the Data Monsters

Data Inspection

Handling Missing Values

Correcting Data Types

Dealing with Duplicates

Extracting Nested Data

The Final Battle for Clean Data

Combining the Data

Generating the Output CSV

Overcoming Challenges

Celebrating the Victory

Our Princess Is in Another Castle

The Importance of Unit Tests

Implementing Logging

Creating the Changelog

Challenges and Learnings

Conclusion

Automating the Pipeline—Mario Kart Style

Purpose of the Bash Script

The Script

Challenges on the Track

Creating an Alias

Wrap-up

Summary of Tasks

Final Thoughts

Conclusion

Pedaling Through Data: A Wheelie Fun Bike Rental Analysis with Python and PostgreSQL

Introduction

Data Preparation

Setting Up PostgreSQL

Data Analysis

Results and Discussion

Conclusion

Code and Data

Lazy Programmer's Guide to Automating Price Calculations

Introduction: Automating Price Calculations for the Lazy (and Efficient) Programmer

The Accountant's Formulas: Transforming Tedious Calculations into Computable Algorithms

Manual Calculations: A Tedious Routine

From Manual to Algorithmic

Implementing the First Part: From Supplier Invoices to a Functional MVP

Technologies Used

Setting Up Routes

indexRoute

calculateRoute

Crafting the MVP

Adding Interactivity: Editable Markup Fields for Dynamic Price Updates

Enhancing User Experience with JavaScript

1. Updating the HTML Template

2. JavaScript for Real-Time Updates

3. Handling AJAX Requests in Flask

4. Adjusting the HTML Template

Future Iterations: Enhancing Functionality and User Experience

1. Configurations Page

2. Drag-and-Drop XML Upload

3. Hosting on Store’s Domain

4. Improved Aesthetics with CSS

Conclusion

Golds and Dragons: the Quest of an AI Awakened World

Day 23 - The epiphany

`index`Route

`calculate`Route