<jupyter_start><jupyter_text>CH.7 Data Cleaning and Preparation  1. Missing Data<jupyter_code>import pandas as pd
import numpy as np
string_data = pd.Series(['aardvark','artichoke',np.nan,'avocado'])
string_data
string_data.isnull()
string_data[0] = None
string_data.isnull()
from numpy import nan as NA
data = pd.Series([1,NA,3.5,NA,7])
data.dropna()
data[data.notnull()]
data = pd.DataFrame([[1.,6.5,3.],[1.,NA,NA],[NA,NA,NA],[NA,6.5,3.]])
cleaned = data.dropna()
data
cleaned
data.dropna(how='all')
data[4] = NA
data
data.dropna(axis=1,how='all')
df = pd.DataFrame(np.random.randn(7,3))
df.iloc[:4,1] = NA
df.iloc[:2,2] = NA
df
df.dropna()
df.dropna(thresh=2)  # thresh
# fill na
df.fillna(0)
df.fillna({1:0.5,2:0})
_ = 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)
data = pd.Series([1.,NA,3.5,NA,7])
data.fillna(data.mean())  # fill NA with mean<jupyter_output><empty_output><jupyter_text>2. Data Transformation<jupyter_code>data = pd.DataFrame({'k1':['one','two']*3+['two'],'k2':[1,1,2,3,3,4,4]})
data
data.duplicated()
data.drop_duplicates()
data['v1'] = range(7)
data.drop_duplicates(['k1'])  # drop duplicates in k1
data.drop_duplicates(['k1','k2'],keep='last')<jupyter_output><empty_output><jupyter_text>3. Data transformation witn function and mapping<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
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)
data
data['food'].map(lambda x:meat_to_animal[x.lower()])<jupyter_output><empty_output><jupyter_text>4. Replacing Values<jupyter_code>data=pd.Series([1.,-999.,2.,-999.,-1000.,3.])
data
data.replace(-999, np.nan)
data.replace([-999,-1000],np.nan)
data.replace([-999,-1000],[np.nan,0])<jupyter_output><empty_output><jupyter_text>5. Renaming Axis Indexes<jupyter_code>data = pd.DataFrame(np.arange(12).reshape((3,4)),
                 index=['Ohio','Colorada','New York'],
                 columns=['one','two','three','four'])
transform = lambda x: x[:4].upper()
data.index.map(transform)
data.index=data.index.map(transform)
data
data.rename(index=str.title,columns=str.upper)
data.rename(index={'OHIO':'INDIANA'},columns={'three':'peekaboo'})
data.rename(index={'OHIO':'INDIANA'},inplace=True)
data<jupyter_output><empty_output><jupyter_text>6. Discretization and Binning<jupyter_code>ages=[20,22,25,27,21,23,37,31,61,45,41,32]
bins=[18,25,35,60,100]
cats=pd.cut(ages,bins)
cats
print(cats.codes)
print(cats.categories)
pd.value_counts(cats)
pd.cut(ages,[18,26,36,61,100],right=False)
group_names=['Youth','YoungAdult','MiddleAged','Senior']
pd.cut(ages,bins, labels=group_names)
data=np.random.rand(20)
pd.cut(data,4,precision=2)
data=np.random.randn(1000)
cats=pd.qcut(data,4)
cats
pd.value_counts(cats)<jupyter_output><empty_output><jupyter_text>7. Outliers<jupyter_code>data = pd.DataFrame(np.random.randn(1000,4))
data.describe()
col = data[2]
col[np.abs(col)>3]
data[(np.abs(data)>3).any(1)]
data[np.abs(data)>3] = np.sign(data)*3
data.describe()
np.sign(data).head()<jupyter_output><empty_output><jupyter_text>8. Permutation and Random Sampling<jupyter_code>df = pd.DataFrame(np.arange(5*4).reshape((5,4)))
sampler = np.random.permutation(5)
print(sampler)
df
df.take(sampler)
df.sample(n=3)
choices = pd.Series([5,7,-1,6,4])
draws = choices.sample(n=10,replace=True)
draws<jupyter_output><empty_output><jupyter_text>9. Indicator/Dummy Variables<jupyter_code>df = pd.DataFrame({'key':['b','b','a','c','a','b'],'data1':range(6)})
pd.get_dummies(df['key'])
dummies = pd.get_dummies(df['key'],prefix='key')
df_with_dummy = df[['data1']].join(dummies)
df_with_dummy
mnames=['movie_id','title','generes']
movies=pd.read_table('movies.dat',sep='::',header=None,names=mnames)
movies[:10]
all_generes = []
for x in movies.generes:
    all_generes.extend(x.split('|'))
    
generes = pd.unique(all_generes)    
generes
zero_matrix = np.zeros((len(movies),len(generes)))
dummies = pd.DataFrame(zero_matrix, columns=generes)
gen = movies.generes[0]

print(gen.split('|'))
print(dummies.columns.get_indexer(gen.split('|')))
for i, gen in enumerate(movies.generes):
    indices = dummies.columns.get_indexer(gen.split('|'))
    dummies.iloc[i,indices]=1
    
movies_windic = movies.join(dummies.add_prefix('Genre_'))
movies_windic.iloc[0]
np.random.seed(12345)
values=np.random.rand(10)
values
bins=[0,0.2,0.4,0.6,0.8,1]
pd.get_dummies(pd.cut(values,bins))<jupyter_output><empty_output><jupyter_text>10. String Manipulation<jupyter_code>val = 'a,b, guido'
print(val.split(','))

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

first,second,third = pieces
print(first+'::'+second+'::'+third)
print('[1]', '::'.join(pieces))
print('[2]', 'guido'in val)
print('[3]', val.index(','))
print('[4]', val.find(':'))
print('[5]', val.count(','))
print('[6]', val.replace(',','::'))
print('[7]', val.replace(',',''))<jupyter_output>[1] a::b::guido
[2] True
[3] 1
[4] -1
[5] 2
[6] a::b:: guido
[7] ab guido<jupyter_text>11. Regular Expressions<jupyter_code>import re

text = "foo bar\t baz \tqux"
print(re.split('\s+',text))

regex = re.compile('\s+')
print(regex.split(text))
print(regex.findall(text))
text="""Dave <EMAIL>
Steve <EMAIL>
Rob <EMAIL>
Ryan <EMAIL>
"""
pattern=r'[A-Z0.9._%+-]+@[A-Z0.9.-]+\.[A-Z]{2,4}'

regex=re.compile(pattern, flags=re.IGNORECASE)
print(regex.findall(text))

m = regex.search(text)
print(m)

print(text[m.start():m.end()] )
print(regex.match(text))
print(regex.sub('REDACATED',text))
pattern = r'([A-Z0.9._%+-]+)@([A-Z0.9.-]+)\.([A-Z]{2,4})'
regex = re.compile(pattern,flags=re.IGNORECASE)
m = regex.match('<EMAIL>')
print(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>12. Vectorized String Functions in pandas<jupyter_code>data={'Dave':'<EMAIL>','Steve':'<EMAIL>',
     'Rob':'<EMAIL>','Wes':np.nan}
data = pd.Series(data)
data
data.isnull()
data.str.contains('gmail')
pattern
data.str.findall(pattern,flags=re.IGNORECASE)
matches = data.str.match(pattern,flags=re.IGNORECASE)
matches
data.str[:5]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.22150858849887975, 'fraction_numerical': 0.03823749066467513, 'mean_word_length': 7.175824175824176, 'pattern_counts': {'":': 0, '<': 55, '<?xml version=': 0, '>': 58, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '508562', 'license': 'MIT', 'n_tokens_mistral': 2991, 'n_tokens_neox': 2854, 'n_words': 372, 'path': '01. Python_Tutorials/5_Data_Cleaning_and_Preparation.ipynb', 'repo_name': 'OH-Seoyoung/Data_Analysis_Club', 'size': '6754'}