The story of how I, who thought 'Excel is fine!', fell in love with Python DataFrames #64

1. The first 'What on earth!'

When I started learning Python, this was the first spell I encountered.

import pandas as pd

df = pd.DataFrame({
    "名前": ["太郎", "花子", "次郎"],
    "年齢": [25, 30, 22]
})

...Wait, why are you going out of your way to type out a table by hand in code!?

I was thinking, 'No way, if it's like this, Excel is fine!' as a full-blown retort in my heart.

The goal should have been to quickly read and analyze Excel or CSV data right in front of me!

It's faster to click and type with a mouse, and you can even color the cells.
To me at the time, this looked like nothing more than a mysterious, roundabout tool for typing data by hand.

At this point, I didn't understand the true terror (or convenience) of pandas at all.

2. The moment of impact: The arrival of 'read' and surpassing Excel

Thinking that, I kept my distance from Python as an 'Excel fundamentalist' for a while, but one day, I made up my mind to look into it properly, and I finally found it.

'You don't actually have to type it by hand, you can read existing Excel or CSV files'?

In other words, this👇 was what Python really wanted to do.

import pandas as pd

# CSVファイルを読み込み
df = pd.read_csv("sales.csv") # 👈これ！

# Excelファイルを読み込み
df2 = pd.read_excel("sales.xlsx") # 👈そしてこれ！

Yes, it's here! This is exactly what I really wanted to do!

'Huh? Is it good to use read?' What a shock.

At this moment, the world of pandas = DataFrame suddenly awakened before my eyes into a tool that surpasses Excel for cooking data at lightning speed.

Yes, the true role of pandas was a
'universal chef' for reading and cooking external data
.

• pd.read_csv("path + filename") to open the door to a CSV

• pd.read_excel("path + filename") to open the treasure chest of an Excel file

At this moment, pandas suddenly became cute.

3. The 'What is df?' problem (The mystery of the nickname)

No sooner had I understood 'read' than I hit the next wall.
It's 'df', df!

Everyone writes 'df' as if it's obvious, but what is that? What is it an abbreviation for?

Me: 'What is df an abbreviation for?'
Junior colleague: 'It's DataFrame. I'm defining it.'
Me: '...What do you mean by defining lol Speak Japanese, please.'

What I finally understood was that 'DataFrame's nickname is df.'
In short, it was a programming-world naming ceremony where you say, 'I'm going to give this loaded data the name "df" now!''

In other words, writing this👇 is...

df1 = pd.read_csv("sales_1月.csv") # 1月のデータちゃん！
df2 = pd.read_csv("sales_2月.csv") # 2月のデータちゃん！
df3 = pd.read_csv("sales_3月.csv") # 3月のデータちゃん！

'I'll call the January DataFrame "df1"!'—it was just a nickname.

Once you get used to it, it's just an abbreviation.
But at first, it was truly a mystery...

I finally understood! So that's how you write it!
Now that I've reached the starting line, I understand what I can do.

4. The magic of cross-referencing: Liberation from copy-paste hell

As a result of reaching the starting line, the world I see has changed.

'Huh? Calculations in one go?'
'I don't have to fight the copy-paste battle across multiple files? lol'

Opening multiple files in Excel, aggregating them, then opening and closing another file...
I don't even know what to say.

While carrying the anxiety of slipping up (making mistakes), it feels like I'm constantly doing Egashira 2:50's 'DOON!'.

However, once I mastered the df magic, it was over in an instant.

• Monthly totals, averages, and maximum values with a single line of code.

• Combining multiple DataFrames together (merging), and calculating for the entire period is also instant.

Pandas was amazing.

That hassle of processing multiple files ends in an instant, as if it never existed.

1. df magic: A spell to end copy-paste hell

2. A spell to finish aggregation and calculations in an instant

Work that I used to do bit by bit with SUM functions in Excel is finished in one go.

# 「売上」列の合計を秒速で出す！
print(df["売上"].sum())

# 「利益」列の平均値を瞬時に計算！
print(df["利益"].mean())

# 最大/最小の「数量」をサクッと知りたい！
print(df["数量"].max()) 
print(df["数量"].min())

3. The spell to 'smash' files together

Combine monthly and branch-specific files vertically into one massive DataFrame. No more copy-pasting!

# 1月、2月、3月のデータちゃんを一瞬で縦に結合！
all_data = pd.concat([df1, df2, df3])

# 合体したデータの最初の数行を見てみる
print(all_data.head())

4. The spell to narrow down data by specifying conditions

Requests like 'I only want to see data for XX' can be solved in one go without using the filter function!

# 「太郎」さんのデータだけを抽出！
taro_data = df[df["名前"] == "太郎"]

# 「年齢」が30歳以上の人だけをピックアップ！
adult_data = df[df["年齢"] >= 30]

At this moment, I was convinced:
'I am no longer a manual Excel artisan'! I am an alchemist who manipulates data'!

5. However, columns are demanding

After learning that calculations were possible, the next thing waiting for me was meeting the columns.

'Columns (column names)'—they are truly demanding.

For example, if the column name in the January file is 'Product Name' but it becomes 'Item Name' in the February file, Panda-san will turn away in annoyance.

'If the column names are even slightly off, your romance with Panda-san will end immediately!'

In other words, to make this romance (successful automated processing) work, it is an absolute requirement that the format and order of the original files are not off by even a single character.

'To leverage Python's lightning-fast processing, you need to perfectly organize your data'—this is the depth of data analysis.

To avoid upsetting my beloved Panda-san, I vowed to become a demon of data organization.

6. Summary: I can never go back to being a manual Excel artisan

At first, I genuinely thought, 'Why not just use Excel instead of typing tables in Python?'

But in reality...

1. With read_csv / read_excel, you can load existing data at lightning speed!

2. Once you learn df (DataFrame), aggregation and calculation become a breeze!

3. You can completely escape the copy-paste hell of crossing multiple files!

4. However, column names have a delicacy where not even a single character mismatch is allowed!

Once you know this, you can never go back to being a manual Excel artisan.
From today on, both you and I are alchemists who manipulate DataFrames!

Is there anyone around you who is still fighting in the hell of copy-pasting?
Please, by all means, teach them the nickname of this panda and the 'read' df magic!

7. Related Articles