The Reality of Data
In textbooks, data is clean. In reality, data is missing, duplicated, inconsistent, and scattered across multiple files. Data scientists spend roughly 80% of their time cleaning and wrangling data. Pandas is designed to make this “grunt work” efficient.
Duplicates and Noise
Common cleaning tasks include removing duplicate rows and inconsistent strings.
Interactive Lab
import pandas as pd
df = pd.DataFrame({
'k1': ['one', 'two'] * 3 + ['two'],
'k2': [1, 1, 2, 3, 3, 4, 4]
})
print("Original DF:\n", df)
# Identify duplicates
print("\nIs Duplicate:\n", df.duplicated())
# Remove duplicates
print("\nCleaned DF:\n", df.drop_duplicates())
Expected output
Original DF:
k1 k2
0 one 1
1 two 1
2 one 2
3 two 3
4 one 3
5 two 4
6 two 4
Is Duplicate:
0 False
1 False
2 False
3 False
4 False
5 False
6 True
dtype: bool
Cleaned DF:
k1 k2
0 one 1
1 two 1
2 one 2
3 two 3
4 one 3
5 two 4
1
2import pandas as pd
3
4df = pd.DataFrame({
5 'k1': ['one', 'two'] * 3 + ['two'],
6 'k2': [1, 1, 2, 3, 3, 4, 4]
7})
8
9print("Original DF:\n", df)
10
11
12print("\nIs Duplicate:\n", df.duplicated())
13
14
15print("\nCleaned DF:\n", df.drop_duplicates())
16
Sometimes you need to apply a custom function to every element or row in a DataFrame.
map(): Used on a Series to substitute values based on a dictionary or function.apply(): Used on a DataFrame to apply a function along an axis (rows or columns).
Interactive Lab
import pandas as pd
df = pd.DataFrame({
'food': ['bacon', 'pulled pork', 'bacon', 'Pastrami', 'corned beef'],
'ounces': [4, 3, 12, 6, 7.5]
})
# Mapping food to animal source
meat_to_animal = {
'bacon': 'pig',
'pulled pork': 'pig',
'pastrami': 'cow',
'corned beef': 'cow'
}
# Normalize string and map
df['animal'] = df['food'].str.lower().map(meat_to_animal)
print("Transformation results:\n", df)
Expected output
Transformation results:
food ounces animal
0 bacon 4.0 pig
1 pulled pork 3.0 pig
2 bacon 12.0 pig
3 Pastrami 6.0 cow
4 corned beef 7.5 cow
1
2import pandas as pd
3
4df = pd.DataFrame({
5 'food': ['bacon', 'pulled pork', 'bacon', 'Pastrami', 'corned beef'],
6 'ounces': [4, 3, 12, 6, 7.5]
7})
8
9
10meat_to_animal = {
11 'bacon': 'pig',
12 'pulled pork': 'pig',
13 'pastrami': 'cow',
14 'corned beef': 'cow'
15}
16
17
18df['animal'] = df['food'].str.lower().map(meat_to_animal)
19print("Transformation results:\n", df)
20
Combining Datasets: Merge, Join, and Concatenate
In a relational database model, data is split across tables. Pandas allows you to bring them together.
Merge (SQL Join)
Merge connects rows in DataFrames based on one or more keys.
Interactive Lab
import pandas as pd
df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})
df2 = pd.DataFrame({'key': ['a', 'b', 'd'], 'data2': range(3)})
# Inner Join (default)
merged = pd.merge(df1, df2, on='key')
print("Merged Data (Inner):\n", merged)
# Outer Join
outer = pd.merge(df1, df2, on='key', how='outer')
print("\nMerged Data (Outer):\n", outer)
Expected output
Merged Data (Inner):
key data1 data2
0 b 0 1
1 b 1 1
2 b 6 1
3 a 2 0
4 a 4 0
5 a 5 0
Merged Data (Outer):
key data1 data2
0 a 2.0 0.0
1 a 4.0 0.0
2 a 5.0 0.0
3 b 0.0 1.0
4 b 1.0 1.0
5 b 6.0 1.0
6 c 3.0 NaN
7 d NaN 2.0
1
2import pandas as pd
3
4df1 = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'a', 'b'], 'data1': range(7)})
5df2 = pd.DataFrame({'key': ['a', 'b', 'd'], 'data2': range(3)})
6
7
8merged = pd.merge(df1, df2, on='key')
9print("Merged Data (Inner):\n", merged)
10
11
12outer = pd.merge(df1, df2, on='key', how='outer')
13print("\nMerged Data (Outer):\n", outer)
14
Concatenate
Concatenate “stacks” DataFrames on top of each other or side-by-side.
Interactive Lab
import pandas as pd
import numpy as np
s1 = pd.Series([0, 1], index=['a', 'b'])
s2 = pd.Series([2, 3, 4], index=['c', 'd', 'e'])
print("Concatenated Series:\n", pd.concat([s1, s2]))
Expected output
Concatenated Series:
a 0
b 1
c 2
d 3
e 4
dtype: int64
1
2import pandas as pd
3import numpy as np
4
5s1 = pd.Series([0, 1], index=['a', 'b'])
6s2 = pd.Series([2, 3, 4], index=['c', 'd', 'e'])
7
8print("Concatenated Series:\n", pd.concat([s1, s2]))
9
Reshaping and Pivoting
Changing the layout of a DataFrame (Long to Wide format or vice versa) is essential for visualization and certain machine learning models.
Interactive Lab
import pandas as pd
df = pd.DataFrame({
'date': ['2021-01-01', '2021-01-01', '2021-01-02'],
'variable': ['temp', 'humidity', 'temp'],
'value': [22.5, 60, 23.0]
})
# Pivot to wide format
pivoted = df.pivot(index='date', columns='variable', values='value')
print("Pivoted (Wide) Data:\n", pivoted)
Expected output
Pivoted (Wide) Data:
variable humidity temp
date
2021-01-01 60.0 22.5
2021-01-02 NaN 23.0
1
2import pandas as pd
3
4df = pd.DataFrame({
5 'date': ['2021-01-01', '2021-01-01', '2021-01-02'],
6 'variable': ['temp', 'humidity', 'temp'],
7 'value': [22.5, 60, 23.0]
8})
9
10
11pivoted = df.pivot(index='date', columns='variable', values='value')
12print("Pivoted (Wide) Data:\n", pivoted)
13
Advanced String Manipulation
Pandas provides a .str accessor for performing vectorized string operations.
Interactive Lab
import pandas as pd
names = pd.Series([' Alice ', 'bOB', ' Charlie ', 'd_avid'])
# Strip whitespace and capitalize
clean_names = names.str.strip().str.capitalize()
print("Clean names:\n", clean_names)
# Regex operations
print("\nContains 'a'?", clean_names.str.contains('a'))
Expected output
Clean names:
0 Alice
1 Bob
2 Charlie
3 D_avid
dtype: object
Contains 'a'? 0 False
1 False
2 True
3 True
dtype: bool
1
2import pandas as pd
3
4names = pd.Series([' Alice ', 'bOB', ' Charlie ', 'd_avid'])
5
6
7clean_names = names.str.strip().str.capitalize()
8print("Clean names:\n", clean_names)
9
10
11print("\nContains 'a'?", clean_names.str.contains('a'))
12
In the next module, we’ll dive into Time-Series analysis, one of Pandas’ strongest features.