<jupyter_start><jupyter_text>Table of Contents1&nbsp;&nbsp;Handling Missing Data1.1&nbsp;&nbsp;Filtering Out Missing Data1.2&nbsp;&nbsp;Filling In Missing Data2&nbsp;&nbsp;Data Transformation2.1&nbsp;&nbsp;Removing Duplicates2.2&nbsp;&nbsp;Transforming Data Using a Function or Mapping2.3&nbsp;&nbsp;Replacing Values2.4&nbsp;&nbsp;Renaming Axis Indexes2.5&nbsp;&nbsp;Detecting and Filtering Outliers2.6&nbsp;&nbsp;Permutation and Random Sampling2.7&nbsp;&nbsp;Computing Indicator/Dummy Variables3&nbsp;&nbsp;String Manipulation3.1&nbsp;&nbsp;Basic String methods3.2&nbsp;&nbsp;Regular Expressions3.3&nbsp;&nbsp;Vectorized String Functions in pandas<jupyter_code>import pandas as pd
import numpy as np
import builtins

def print(*args, **kwargs):
    builtins.print(*args, **kwargs, end='\n\n')<jupyter_output><empty_output><jupyter_text>Handling Missing Data The way that missing data is represented in pandas objects is somewhat imperfect, but it is functional for a lot of users. For numeric data, pandas uses the floating-point value **NaN** (Not a Number) to represent missing data. We call this a **sentinel value** that can be easily detected<jupyter_code>string_data = pd.Series(['aardvark', 'artichoke', np.nan, 'avocado'])
string_data
string_data.isnull()
string_data.isnull().sum()
# The built-in Python None value is also treated as NA in object arrays

string_data[0] = None
string_data.isnull()
pd.options.display.max_colwidth = 500
df = pd.read_csv(r'examples/NA_handling_methods.csv', names=['Description'], sep='\n')
df.index = list(range(1, 5))

def f1(x):
    x = x.split(' ')
    return x.pop(0)


df['Argument'] = df['Description'].map(f1) # get the first word

def f2(x):
    x = x.split(' ')
    return " ".join(x[1:])

df['Description'] = df['Description'].map(f2) # remove the first word

df = df.reindex(columns=['Argument', 'Description'])
print("NA handling methods")
df<jupyter_output>NA handling methods<jupyter_text>Filtering Out Missing DataThere are a few ways to filter out missing data. While you always have the option to do it by hand using **pandas.isnull** and **boolean indexing**, the **dropna** can be helpful.<jupyter_code># On a Series, it returns the Series with only the non-null data and index values

from numpy import nan as NA
data = pd.Series([1, NA, 3.5, NA, 7])
data.dropna()
# data[data.notnull()]<jupyter_output><empty_output><jupyter_text>With DataFrame objects, things are a bit more complex. You may want to drop rows or columns that are all NA or only those containing any NAs. dropna by **default drops any row containing a missing value**<jupyter_code>data = pd.DataFrame([[1., 6.5, 3.], [1., NA, NA],
                     [NA, NA, NA], [NA, 6.5, 3.]])
data
cleaned = data.dropna()
cleaned
# Passing how='all' will only drop rows that are all NA

data.dropna(how='all')
# data.dropna(how='any')
# To drop columns in the same way, pass axis=1

data[4] = NA
data
data.dropna(axis=1, how='all')<jupyter_output><empty_output><jupyter_text>A related way to filter out DataFrame rows tends to concern time series data. **Suppose you want to keep only rows containing a certain number of observations**. You can indicate this with the **thresh** argument<jupyter_code>df = pd.DataFrame(np.random.randn(7, 3))
df.iloc[:4, 1] = NA
df.iloc[:2, 2] = NA
df
print(df.dropna())
print(df.dropna(thresh=2))
df
print(df.dropna(subset=[1, 2], how='any').shape)
print(df.dropna(subset=[1, 2], how='all').shape)<jupyter_output>(3, 3)

(5, 3)<jupyter_text>Filling In Missing Data<jupyter_code>df.fillna(0)
# Calling fillna with a dict, you can use a different fill value for each column

df.fillna({1: 0.5, 2: 0})
# fillna returns a new object, but you can modify the existing object in-place
_ = df.fillna(0, inplace=True)
df
df = pd.DataFrame(np.random.randn(6, 3))
df.iloc[2:, 1] = NA
df.iloc[4:, 2] = NA
df
df.fillna(method='ffill')
df.fillna(method='ffill', limit=2)
# we might pass mean or median of a series

data = pd.Series([1., NA, 3.5, NA, 7])
data.fillna(data.mean())<jupyter_output><empty_output><jupyter_text>**Arguments** - **Description** for fillna**value** - Scalar value or dict-like object to use to fill missing values**method** - Interpolation; by default 'ffill' if function called with no other arguments**axis** - Axis to fill on; default axis=0**inplace** - Modify the calling object without producing a copy**limit** - For forward and backward filling, maximum number of consecutive periods to fill  Data Transformation Removing Duplicates<jupyter_code>data = pd.DataFrame({'k1': ['one', 'two'] * 3 + ['two'],
                    'k2': [1, 1, 2, 3, 3, 4, 4]})
data<jupyter_output><empty_output><jupyter_text>The DataFrame method **duplicated** returns a boolean Series indicating whether each row is a duplicate (has been observed in a previous row) or not.<jupyter_code>data.duplicated()
# drop_duplicates returns a DataFrame where the duplicated array is False

data.drop_duplicates()
# Suppose we had an additional column of values and wanted to 
# filter duplicates only based on the 'k1' column

data['v1'] = range(7)
data
data.drop_duplicates(['k1'])<jupyter_output><empty_output><jupyter_text>Transforming Data Using a Function or MappingFor many datasets, you may wish to perform some transformation based on the values in an array, Series, or column in a DataFrame.<jupyter_code>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]})
data
# Suppose you wanted to add a column indicating the 
# type of animal that each food came from

meat_to_animal = {
 'bacon': 'pig',
 'pulled pork': 'pig',
 'pastrami': 'cow',
 'corned beef': 'cow',
 'honey ham': 'pig',
 'nova lox': 'salmon'
}
lowercased = data['food'].str.lower()
lowercased
data['animal'] = lowercased.map(meat_to_animal)
# or
# data['food'].map(lambda x: meat_to_animal[x.lower()])
data<jupyter_output><empty_output><jupyter_text>Replacing ValuesFilling in missing data with the fillna method is a special case of more general value replacement. As you’ve already seen, map can be used to modify a subset of values in an object but **replace** provides a simpler and more flexible way to do so<jupyter_code>data = pd.Series([1., -999., 2., -999., -1000., 3.])
data
print(data.replace(-999, np.nan))
print(data.replace([-999, -1000], np.nan))
print(data.replace([-999, -1000], [np.nan, 0]))
print(data.replace({-999: np.nan, -1000: 0}, inplace=True))<jupyter_output>0       1.0
1       NaN
2       2.0
3       NaN
4   -1000.0
5       3.0
dtype: float64

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

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

None<jupyter_text>Renaming Axis Indexesaxis labels can be transformed by a function or mapping of some form to produce new, differently labeled objects.<jupyter_code>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()

# Like a Series, the axis indexes have a map method

print(data.index.map(transform))

# -----------
trans = lambda x: x[:2].upper()
print(data.index.map(trans))
data.index = data.index.map(transform)
data<jupyter_output><empty_output><jupyter_text>If you want to create a transformed version of a dataset without modifying the original, a useful method is **rename**<jupyter_code>data.rename(index=str.title, columns=str.upper)
# rename can be used in conjunction with a dict-like object
# providing new values for a subset of the axis labels

data.rename(index={'OHIO': 'INDIANA'},
            columns={'three': 'peekaboo'}, inplace=True)
data
# ToD: Discretization and Binning<jupyter_output><empty_output><jupyter_text>Detecting and Filtering OutliersFiltering or transforming outliers is largely a matter of applying array operations.<jupyter_code>data = pd.DataFrame(np.random.randn(1000, 4))
data.describe()
col = data[0]
col[np.abs(col) > 3]<jupyter_output><empty_output><jupyter_text>To select all rows having a value exceeding 3 or –3, you can use the any method on a boolean DataFrame<jupyter_code>data[(np.abs(data) > 3).any(axis=1)]<jupyter_output><empty_output><jupyter_text>Values can be set based on these criteria. Here is code to cap values outside the interval –3 to 3<jupyter_code>data[np.abs(data) > 3] = np.sign(data) * 3
data.describe()
# The statement np.sign(data) produces 1 and –1 values based on 
# whether the values in data are positive or negative

np.sign(data).head()<jupyter_output><empty_output><jupyter_text>Permutation and Random Sampling Permuting (randomly reordering) a Series or the rows in a DataFrame is easy to do using the numpy.random.permutation function. Calling permutation with the length of the axis you want to permute produces an array of integers indicating the new ordering<jupyter_code>df = pd.DataFrame(np.arange(20).reshape((5, 4)))
sampler = np.random.permutation(5)
sampler<jupyter_output><empty_output><jupyter_text>That array can then be used in iloc-based indexing or the equivalent **take** function<jupyter_code>df
df.take(sampler)<jupyter_output><empty_output><jupyter_text>To select a random subset **without replacement**, you can use the **sample** method on Series and DataFrame<jupyter_code>df.sample(n=3)
#  sample with replacement

choices = pd.Series([5, 7, -1, 6, 4])
draws = choices.sample(n=10, replace=True)
draws<jupyter_output><empty_output><jupyter_text>Computing Indicator/Dummy Variables<jupyter_code>train = pd.read_csv('http://bit.ly/kaggletrain')
train.head()
# create a dummy column for Sex column

# using map method
train['Sex_male'] = train.Sex.map({'female': 0, 'male': 1})
train.head()
# using get_dummies
  
pd.get_dummies(train.Sex)<jupyter_output><empty_output><jupyter_text>Generally, **k** categorical values, then use **k-1** dummy variables to represent it.for Sex, we have to categorical values so we need only 1 variable to encode all the given information. So we drop the first column.<jupyter_code>pd.get_dummies(train.Sex, prefix='Sex').iloc[:, 1:]
train.Embarked.value_counts()
embarked_dummies = pd.get_dummies(train.Embarked, prefix='Embarked').iloc[:, 1:]
train = pd.concat([train, embarked_dummies], axis=1)
train.head()
# pass columns to get_dummies makes it more easy

train = pd.read_csv('http://bit.ly/kaggletrain')

pd.get_dummies(train, columns=['Sex', 'Embarked'])
# drop the first column after get_dummies

pd.get_dummies(train, columns=['Sex', 'Embarked'], drop_first=True)
# example of MovieLens 1M dataset

mnames = ['movieid', 'title', 'genres']
movies = pd.read_table(r'MovieLens-1M/movies.dat', sep='::', header=None,
                       names=mnames, engine='python')
movies.head()
all_genres = []

for x in movies.genres:
    all_genres.extend(x.split('|'))
genres = pd.unique(all_genres)
genres
# One way to construct the indicator DataFrame is to start with a
# DataFrame of allzeros

zero_matrix = np.zeros((len(movies), len(genres)))
dummies = pd.DataFrame(zero_matrix, columns=genres)<jupyter_output><empty_output><jupyter_text>Now, iterate through each movie and set entries in each row of dummies to 1. To do this, we use the dummies.columns to compute the column indices for each genre<jupyter_code>gen = movies.genres[0]
print(gen.split('|'))
dummies.columns.get_indexer(gen.split('|'))<jupyter_output>['Animation', "Children's", 'Comedy']<jupyter_text>Then, we can use .iloc to set values based on these indices<jupyter_code>for i, gen in enumerate(movies.genres):
    indices = dummies.columns.get_indexer(gen.split('|'))
    dummies.iloc[i, indices] = 1
# join with movies

movies_windic = movies.join(dummies.add_prefix('Genre_'))
movies_windic.iloc[0]<jupyter_output><empty_output><jupyter_text>String Manipulation Basic String methods<jupyter_code>val = 'a,b, guido'

pieces = [x.strip() for x in val.split(',')]
pieces
"::".join(pieces)<jupyter_output><empty_output><jupyter_text>**built-in String methods**|Argument | Description    ||---|---||count|Return the number of non-overlapping occurrences of substring in the string.     ||endswith|Returns True if string ends with suffix.     ||startswith|Returns True if string starts with prefix.    ||join|Use string as delimiter for concatenating a sequence of other strings. ||index|Return position of first character in substring if found in the string; raises ValueError if not found.  ||find|Return position of first character of rst occurrence of substring in the string; like index, but returns –1  if          not found.||rfind|Return position of first character of last occurrence of substring in the string; returns –1 if not found.   ||replace|Replace occurrences of string with another string.||strip, rstrip, lstrip|Trim whitespace, including newlines; equivalent to x.strip() (and rstrip, lstrip, respectively)                      for each element||split|Break string into list of substrings using passed delimiter.    ||lower|Convert alphabet characters to lowercase.    ||upper|Convert alphabet characters to uppercase.   ||casefold|Convert characters to lowercase, and convert any region-specific variable character combinations to a common comparable form.   |ljust, rjust|Left justify or right justify, respectively; pad opposite side of string with spaces (or some other fill character) to return a string with a minimum width   |  Regular Expressions The re module functions fall into three categories: pattern matching, substitution,and splitting. Naturally these are all related; a regex describes a pattern to locate in thetext, which can then be used for many purposes.<jupyter_code>import re
text = "foo    bar\tbaz   \tqux"
text
re.split('\s+', text)<jupyter_output><empty_output><jupyter_text>When you call re.split('\s+', text), the regular expression is first compiled, and then its split method is called on the passed text. You can compile the regex yourself with **re.compile**, forming a reusable regex object Creating a regex object with re.compile is highly recommended if you intend toapply the same expression to many strings; doing so will save CPU cycles<jupyter_code>regex = re.compile('\s+')
regex.split(text)
text = """Dave <EMAIL>
Steve <EMAIL>
Rob <EMAIL>
Ryan <EMAIL>
"""
pattern = r'[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'

# re.IGNORECASE makes the regex case-insensitive
regex = re.compile(pattern, flags=re.IGNORECASE)
regex.findall(text)<jupyter_output><empty_output><jupyter_text>**match** and **search** are closely related to findall. While findall returns all matchesin a string, search returns only the first match. More rigidly, match only matches atthe beginning of the string **search** returns a special match object for the first email address in the text. For thepreceding regex, the match object can only tell us the start and end position of thepattern in the string<jupyter_code>m = regex.search(text)
m
text[m.start(): m.end()]
print(regex.match(text))
print(regex.sub('REDACTED', text))<jupyter_output>Dave REDACTED
Steve REDACTED
Rob REDACTED
Ryan REDACTED<jupyter_text>segment each address into its three components: username, domain name, and domain suffix<jupyter_code>pattern = r'([A-Z0-9._%+-]+)@([A-Z0-9.-]+)\.([A-Z]{2,4})'
regex = re.compile(pattern, flags=re.IGNORECASE)
m = regex.match('<EMAIL>')
m.groups()
regex.findall(text)
print(regex.sub(r'Username: \1, Domain: \2, Suffix: \3', text))<jupyter_output>Dave Username: dave, Domain: google, Suffix: com
Steve Username: steve, Domain: gmail, Suffix: com
Rob Username: rob, Domain: gmail, Suffix: com
Ryan Username: ryan, Domain: yahoo, Suffix: com<jupyter_text>**Regular expression methods**|Argument| Description||-|-||findall| Return all non-overlapping matching patterns in a string as a list||finditer| Like findall, but returns an iterator||match |Match pattern at start of string and optionally segment pattern components into groups; if the pattern matches, returns a match object, and otherwise None||search| Scan string for match to pattern; returning a match object if so; unlike match, the match can be anywhere in the string as opposed to only at the beginning||split| Break string into pieces at each occurrence of pattern||sub, subn|Replace all (sub) or first n occurrences (subn) of pattern in string with replacement expression; use symbols \1, \2, ... to refer to match group elements in the replacement string|  Vectorized String Functions in pandas<jupyter_code>data = {'Dave': '<EMAIL>', 'Steve': '<EMAIL>',
        'Rob': '<EMAIL>', 'Wes': np.nan}

data = pd.Series(data)
data
data.isnull()<jupyter_output><empty_output><jupyter_text>You can apply string and regular expression methods can be applied (passing a lambda or other function) to each value using data.map, but it will fail on the NA (null) values. To cope with this, Series has array-oriented methods for string operations that skip NA values. These are accessed through Series’s str attribute; for example,<jupyter_code>data.str.contains('gmail')<jupyter_output><empty_output><jupyter_text>Regular expressions can be used, too, along with any re options like IGNORECASE<jupyter_code>pattern
data.str.findall(pattern, flags=re.IGNORECASE)
 matches = data.str.match(pattern, flags=re.IGNORECASE)<jupyter_output><empty_output><jupyter_text>There are a couple of ways to do vectorized element retrieval. Either use **str.get** or index into the str attribute<jupyter_code>matches<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11998410806515693, 'fraction_numerical': 0.01526760883137522, 'mean_word_length': 5.215167548500882, 'pattern_counts': {'":': 1, '<': 127, '<?xml version=': 0, '>': 130, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '31', 'dir': 'jupyter-structured-clean-dedup', 'id': '652721', 'license': 'Unlicense', 'n_tokens_mistral': 5847, 'n_tokens_neox': 5600, 'n_words': 1907, 'path': 'pandas/Data Cleaning and Preparation.ipynb', 'repo_name': 'veb-101/Numpy-Pandas-Matplotlib-Tutorial', 'size': '17678'}