ch7.data_cleaning_and_preparation

Ch 7. Data Cleaning and Preparation

Handling Missing Data in Pandas

Missing Data Representation:
- Pandas represents missing numeric data using NaN (Not a Number).
- The built-in Python None value is also treated as NA in object arrays.

Checking for Missing Data:

import pandas as pd
import numpy as np

string_data = pd.Series(['aardvark', 'artichoke', np.nan, 'avocado'])
print(string_data.isnull())

Output:

0    False
1    False
2     True
3    False
dtype: bool

isnull(): Returns a boolean mask indicating missing values.
notnull(): Returns the negation of isnull().

NA Handling Methods (Table 7-1):
- dropna(): Removes missing data.
- fillna(): Fills missing data with a specified value or method.
- isnull(): Identifies missing data.
- notnull(): Opposite of isnull().

Filtering Out Missing Data

Dropping Missing Data in Series:

data = pd.Series([1, np.nan, 3.5, np.nan, 7])
print(data.dropna())

Output:

0    1.0
2    3.5
4    7.0
dtype: float64

Equivalent to:
```
print(data[data.notnull()])
```

Dropping Missing Data in DataFrames:

data = pd.DataFrame([[1., 6.5, 3.], [1., np.nan, np.nan],
                     [np.nan, np.nan, np.nan], [np.nan, 6.5, 3.]])
cleaned = data.dropna()
print(cleaned)

Output:

     0    1    2
0  1.0  6.5  3.0

Dropping Rows/Columns with All Missing Values:

data.dropna(how='all')  # Drops rows where all values are NaN
data.dropna(axis=1, how='all')  # Drops columns where all values are NaN

Threshold-Based Dropping (Keeping Minimum Observations):

df.dropna(thresh=2)  # Keeps rows with at least 2 non-NA values

Filling in Missing Data

Replacing Missing Values with a Constant:
```
df.fillna(0)
```
Filling Different Columns with Different Values:
```
df.fillna({1: 0.5, 2: 0})
```
Modifying the DataFrame In-Place:
```
df.fillna(0, inplace=True)
```

Forward/Backward Fill Methods:

df.fillna(method='ffill')  # Forward fill
df.fillna(method='ffill', limit=2)  # Forward fill up to 2 missing values

Filling with Mean/Median:

data = pd.Series([1., np.nan, 3.5, np.nan, 7])
data.fillna(data.mean())

Output:

0    1.000000
1    3.833333
2    3.500000
3    3.833333
4    7.000000
dtype: float64

Fillna Function Arguments:
- value: Scalar or dict for filling.
- method: Interpolation method (ffill, bfill).
- axis: Axis to fill (default 0).
- inplace: Modify object without copying.
- limit: Maximum number of consecutive periods to fill.

Data Transformation in Pandas

Removing Duplicates

Duplicate rows can occur due to various reasons.
duplicated() returns a boolean Series indicating duplicate rows.
drop_duplicates() removes duplicate rows from a DataFrame.

import pandas as pd

data = pd.DataFrame({'k1': ['one', 'two'] * 3 + ['two'],
                     'k2': [1, 1, 2, 3, 3, 4, 4]})
print(data.duplicated())
print(data.drop_duplicates())

Output:

0    False
1    False
2    False
3    False
4    False
5    False
6     True
dtype: bool
   k1  k2
0  one   1
1  two   1
2  one   2
3  two   3
4  one   3
5  two   4

Dropping Duplicates Based on Specific Columns

data['v1'] = range(7)
print(data.drop_duplicates(['k1']))

Output:

   k1  k2  v1
0  one   1   0
1  two   1   1

Keeping the Last Occurrence of Duplicates

print(data.drop_duplicates(['k1', 'k2'], keep='last'))

Output:

   k1  k2  v1
0  one   1   0
1  two   1   1
2  one   2   2
3  two   3   3
4  one   3   4
6  two   4   6

Transforming Data Using a Function or Mapping

Example DataFrame

data = pd.DataFrame({'food': ['bacon', 'pulled pork', 'bacon',
                              'Pastrami', 'corned beef', 'Bacon',
                              'pastrami', 'honey ham', 'nova lox'],
                     'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})

Mapping Values to a New Column

meat_to_animal = {
    'bacon': 'pig',
    'pulled pork': 'pig',
    'pastrami': 'cow',
    'corned beef': 'cow',
    'honey ham': 'pig',
    'nova lox': 'salmon'
}

data['animal'] = data['food'].str.lower().map(meat_to_animal)
print(data)

Output:

      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
5     Bacon     8.0     pig
6  pastrami     3.0     cow
7  honey ham     5.0     pig
8  nova lox     6.0  salmon

Using a Lambda Function for Mapping

data['animal'] = data['food'].map(lambda x: meat_to_animal[x.lower()])

map() is useful for element-wise transformations and data cleaning.

Replacing Values in Pandas

Replacing Specific Values with replace()

import pandas as pd
import numpy as np

data = pd.Series([1., -999., 2., -999., -1000., 3.])
print(data.replace(-999, np.nan))

Output:

0       1.0
1       NaN
2       2.0
3       NaN
4   -1000.0
5       3.0
dtype: float64

Replacing Multiple Values at Once

data.replace([-999, -1000], np.nan)

Output:

0    1.0
1    NaN
2    2.0
3    NaN
4    NaN
5    3.0
dtype: float64

Replacing Different Values with Different Substitutes

data.replace([-999, -1000], [np.nan, 0])

Output:

0    1.0
1    NaN
2    2.0
3    NaN
4    0.0
5    3.0
dtype: float64

Using a Dictionary for Replacement
```
data.replace({-999: np.nan, -1000: 0})
```
Note:
- data.replace() is different from data.str.replace(), which is used for string substitution.

Renaming Axis Indexes

Transforming Index Labels Using map()

data = pd.DataFrame(np.arange(12).reshape((3, 4)),
                    index=['Ohio', 'Colorado', 'New York'],
                    columns=['one', 'two', 'three', 'four'])

transform = lambda x: x[:4].upper()
data.index = data.index.map(transform)
print(data)

Output:

       one  two  three  four
OHIO      0    1      2     3
COLO      4    5      6     7
NEW       8    9     10    11

Using rename() for Axis Label Changes

data.rename(index=str.title, columns=str.upper)

Output:

       ONE  TWO  THREE  FOUR
Ohio     0    1      2     3
Colo     4    5      6     7
New      8    9     10    11

Renaming Specific Index and Column Labels

data.rename(index={'OHIO': 'INDIANA'}, columns={'three': 'peekaboo'})

Output:

       one  two  peekaboo  four
INDIANA   0    1        2     3
COLO      4    5        6     7
NEW       8    9       10    11

Modifying the DataFrame In-Place

data.rename(index={'OHIO': 'INDIANA'}, inplace=True)
print(data)

Output:

       one  two  three  four
INDIANA   0    1      2     3
COLO      4    5      6     7
NEW       8    9     10    11

Key Points:
- replace() is useful for general value replacement in Series and DataFrames.
- rename() provides flexible renaming for index and column labels.
- Both methods can modify the object in-place using inplace=True.

Discretization and Binning in Pandas

Dividing Continuous Data into Bins with cut()

import pandas as pd
import numpy as np

ages = [20, 22, 25, 27, 21, 23, 37, 31, 61, 45, 41, 32]
bins = [18, 25, 35, 60, 100]
cats = pd.cut(ages, bins)
print(cats)

Output:

[(18, 25], (18, 25], (18, 25], (25, 35], (18, 25], ..., (25, 35], (60, 100], (35, 60], (35, 60], (25, 35]]
Length: 12
Categories (4, interval[int64]): [(18, 25] < (25, 35] < (35, 60] < (60, 100]]

Understanding the Output of cut()
- Returns a special Categorical object.
- cats.codes provides numerical labels for each bin.
- cats.categories lists the bin intervals.
```
print(cats.codes)  # [0, 0, 0, 1, 0, 0, 2, 1, 3, 2, 2, 1]
print(cats.categories)
```

Counting Values in Each Bin

print(pd.value_counts(cats))

Output:

(18, 25]      5
(35, 60]      3
(25, 35]      3
(60, 100]     1
dtype: int64

Customizing Bin Intervals (Open vs. Closed Intervals)

pd.cut(ages, [18, 26, 36, 61, 100], right=False)

Output:

[[18, 26), [18, 26), [18, 26), [26, 36), [18, 26), ..., [26, 36), [61, 100), [36, 61), [36, 61), [26, 36)]

Assigning Custom Labels to Bins

group_names = ['Youth', 'YoungAdult', 'MiddleAged', 'Senior']
labeled_cats = pd.cut(ages, bins, labels=group_names)
print(labeled_cats)

Output:

[Youth, Youth, Youth, YoungAdult, Youth, ..., YoungAdult, Senior, MiddleAged, MiddleAged, YoungAdult]

Automatically Creating Equal-Length Bins
```
data = np.random.rand(20)
print(pd.cut(data, 4, precision=2))
```
- precision=2 limits the decimal precision to two places.

Using qcut() for Quantile-Based Binning

data = np.random.randn(1000)  # Normally distributed data
cats = pd.qcut(data, 4)  # Divides into quartiles
print(pd.value_counts(cats))

Output:

(0.62, 3.928]     250
(-0.0265, 0.62]   250
(-0.68, -0.0265]  250
(-2.95, -0.68]    250
dtype: int64

Each bin has roughly the same number of data points.

Custom Quantiles in qcut()
```
print(pd.qcut(data, [0, 0.1, 0.5, 0.9, 1.]))
```
- Uses custom quantiles to define bins.
Key Differences Between cut() and qcut()
- cut(): Divides data into equal-width bins.
- qcut(): Divides data into equal-frequency bins based on quantiles.

Detecting and Filtering Outliers in Pandas

Creating a DataFrame with Normally Distributed Data

import pandas as pd
import numpy as np

data = pd.DataFrame(np.random.randn(1000, 4))
print(data.describe())

describe() provides summary statistics.

Finding Values Exceeding a Threshold in a Column
```
col = data[2]
print(col[np.abs(col) > 3])
```
- Identifies values in column 2 where absolute value exceeds 3.
Filtering Rows with Any Column Exceeding ±3
```
print(data[(np.abs(data) > 3).any(axis=1)])
```
- Uses any(axis=1) to check if any column in a row meets the condition.
Capping Values Outside a Specified Range (-3 to 3)
```
data[np.abs(data) > 3] = np.sign(data) * 3
print(data.describe())
```
- np.sign(data): Returns 1 or -1 depending on the sign of each value.
- Multiplied by 3 to cap values within the range.

Example Output of np.sign(data).head()

print(np.sign(data).head())

Output:

     0    1    2    3
0 -1.0  1.0 -1.0  1.0
1  1.0 -1.0  1.0 -1.0
2  1.0  1.0  1.0 -1.0
3 -1.0 -1.0  1.0 -1.0
4 -1.0  1.0 -1.0 -1.0

Key Takeaways:
- Use np.abs() to detect extreme values.
- any(axis=1) helps filter rows where any column exceeds a threshold.
- np.sign() simplifies capping outliers to a fixed limit.

Permutation and Random Sampling in Pandas

Randomly Reordering Rows with numpy.random.permutation()

import pandas as pd
import numpy as np

df = pd.DataFrame(np.arange(5 * 4).reshape((5, 4)))
sampler = np.random.permutation(5)
print(sampler)

Output (Example):

[3, 1, 4, 2, 0]

Generates a random order of row indices.

Reordering DataFrame Rows Using iloc or take()

print(df.take(sampler))

Output:

    0   1   2   3
3  12  13  14  15
1   4   5   6   7
4  16  17  18  19
2   8   9  10  11
0   0   1   2   3

Selecting a Random Subset Without Replacement

print(df.sample(n=3))

Output (Example):

    0   1   2   3
3  12  13  14  15
4  16  17  18  19
2   8   9  10  11

Sampling with Replacement

choices = pd.Series([5, 7, -1, 6, 4])
draws = choices.sample(n=10, replace=True)
print(draws)

Output (Example):

4    4
1    7
4    4
2   -1
0    5
3    6
1    7
4    4
0    5
4    4
dtype: int64

Key Takeaways:
- np.random.permutation(n): Shuffles row indices for random ordering.
- df.take(indices): Reorders DataFrame rows using specified indices.
- df.sample(n): Selects a random subset of n rows without replacement.
- df.sample(n, replace=True): Selects n rows with replacement.

String Manipulation in Python and Pandas

Built-in String Methods

Splitting Strings
```
val = 'a,b, guido'
print(val.split(','))
```
Output:
```
['a', 'b', ' guido']
```
- split(delimiter): Splits a string into a list based on the delimiter.

Trimming Whitespace

pieces = [x.strip() for x in val.split(',')]
print(pieces)

Output:

['a', 'b', 'guido']

strip(): Removes leading and trailing whitespace.

Joining Strings
```
'::'.join(pieces)
```
Output:
```
'a::b::guido'
```
- join(iterable): Joins elements of a list or tuple into a single string using the given separator.
Finding Substrings
```
print('guido' in val)   # True
print(val.index(','))   # 1
print(val.find(':'))    # -1
```
- in: Checks if a substring exists.
- index(substring): Returns the first occurrence index (raises an error if not found).
- find(substring): Returns the first occurrence index (-1 if not found).
Counting Substring Occurrences
```
print(val.count(','))
```
Output:
```
2
```
- count(substring): Counts occurrences of a substring.

Replacing Substrings

print(val.replace(',', '::'))  # 'a::b:: guido'
print(val.replace(',', ''))    # 'ab guido'

replace(old, new): Replaces occurrences of old with new.

Common String Methods in Python

Method	Description
`count(substring)`	Counts occurrences of a substring.
`endswith(suffix)`	Checks if string ends with `suffix`.
`startswith(prefix)`	Checks if string starts with `prefix`.
`join(iterable)`	Joins a sequence of strings using the given string as a delimiter.
`index(substring)`	Finds the first occurrence (raises `ValueError` if not found).
`find(substring)`	Finds the first occurrence (-1 if not found).
`rfind(substring)`	Finds the last occurrence (-1 if not found).
`replace(old, new)`	Replaces occurrences of `old` with `new`.
`strip()`	Removes leading and trailing whitespace.
`rstrip()`	Removes trailing whitespace.
`lstrip()`	Removes leading whitespace.
`split(delimiter)`	Splits the string into a list using `delimiter`.
`lower()`	Converts the string to lowercase.
`upper()`	Converts the string to uppercase.
`casefold()`	Converts to lowercase, handling regional variations.
`ljust(width)`	Left-aligns string and pads with spaces (or specified character).
`rjust(width)`	Right-aligns string and pads with spaces (or specified character).

Key Takeaways:
- Python provides powerful built-in string methods for manipulation.
- split(), join(), and replace() are commonly used for text processing.
- find() and index() help locate substrings, but index() raises an error if not found.
- strip(), lstrip(), and rstrip() are useful for removing whitespace.