<jupyter_start><jupyter_text>Database-style DataFrame merges<jupyter_code>employee_data = DataFrame({'employee_name': ['Rafferty', 'Jones', 'Steinberg', 'Robinson', 'Smith', 'Jasper'],
                 'DepartmentID': [31, 33, 33, 34, 34, 'N/A']})
department_data = DataFrame({'DepartmentID': [31, 33, 34, 35],
                 'DepartmentName': ['Sales', 'Engineering', 'Admin', 'Marketing']})
employee_data
department_data
pd.merge(employee_data, department_data, on = 'DepartmentID')
df3 = DataFrame({'employee_name': ['Rafferty', 'Jones', 'Steinberg', 'Robinson', 'Smith', 'Jasper'],
                 'department_id': [31, 33, 33, 34, 34, 'N/A']})
df4 = DataFrame({'DepartmentID': [31, 33, 34, 35],
                 'DepartmentName': ['Sales', 'Engineering', 'Admin', 'Marketing']})
pd.merge(df3, df4, left_on = 'department_id', right_on = 'DepartmentID')[['employee_name', 'DepartmentID', 'DepartmentName']]
pd.merge(employee_data, department_data, on = 'DepartmentID', how = 'left')
pd.merge(employee_data, department_data, on = 'DepartmentID', how = 'right')
pd.merge(employee_data, department_data, on = 'DepartmentID', how = 'outer')<jupyter_output><empty_output><jupyter_text>Data Cleaning - Removing Duplicates<jupyter_code>data = DataFrame({'k1': ['one'] * 3 + ['two'] * 4,
                  'k2': [1, 1, 2, 3, 3, 4, 4]})
data
data.duplicated()
data.drop_duplicates()
data['v1'] = range(7)
data.drop_duplicates(['k1'])
data
data.drop_duplicates(['k1', 'k2'], keep='last')<jupyter_output><empty_output><jupyter_text>Data Cleaning - Missing Value Handling<jupyter_code>data = DataFrame([[1.0, 6.5, 8], 
                  [3, np.NAN, np.NAN], 
                  [np.NAN, np.NAN, np.NAN], 
                  [np.NAN, 6.5, 9], 
                  [3, -1, 8], 
                  [3, 6.5, 8]])
data
clean_data = data.dropna()
clean_data
def count_missing(x):
  return sum(x.isnull())
total =  data.shape[0]
missing_stat = data.apply(count_missing, axis = 0)
# print total
missing_stat.apply(lambda x: x / float(total))
data.fillna(value = '?', inplace = False)
data.fillna(value = {0:'other', 1:2, 2:5}, inplace = False)
data.fillna(data.mean(), inplace = False)
data.fillna(data.median(), inplace = False)
data.median()
mode = data.mode()
data.fillna(mode.iloc[0], inplace = False)
# data.fillna(value = {0:mode.loc[0][0], 
#                     1:mode.loc[0][1], 
#                     2:mode.loc[0][2]}, inplace = False)<jupyter_output><empty_output><jupyter_text>Detecting and filtering outliers<jupyter_code>np.random.seed(12345)
data = DataFrame(np.random.randn(1000, 4))
data.describe()
col = data[3]
col[np.abs(col) > 3]
outliers = (np.abs(data) > 3).any(1)
data[outliers]
data[(np.abs(data) <= 3).all(1)]
data[np.abs(data) > 3] = np.sign(data) * 3
data.describe()<jupyter_output><empty_output><jupyter_text>Permutation and random sampling<jupyter_code>df = DataFrame(np.arange(5 * 4).reshape((5, 4)))
print len(df)
sampler = np.random.permutation(len(df))
sampler
df
df.take(sampler)
df.take(np.random.permutation(len(df))[:3])<jupyter_output><empty_output><jupyter_text>Sampling w/ Replacement<jupyter_code>bag = np.array([5, 7, -1, 6, 4])
sampler = np.random.randint(0, len(bag), size = 10)
sampler
draws = bag.take(sampler)
draws<jupyter_output><empty_output><jupyter_text>Sampling w/o Replacement<jupyter_code>import random
sampler = random.sample(xrange(len(bag)), 4)
draws = bag.take(sampler)
draws<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18450826805918188, 'fraction_numerical': 0.03510298810559907, 'mean_word_length': 4.7275747508305646, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 32, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '447622', 'license': 'Apache-2.0', 'n_tokens_mistral': 1427, 'n_tokens_neox': 1295, 'n_words': 242, 'path': 'ml-workshop/src/Data-Wrangling.ipynb', 'repo_name': 'yao-matrix/mProto', 'size': '3447'}