Lecture 16

During the course of doing data analysis and modeling, a significant amount of time is spent on data preparation: loading, cleaning, transforming, and rearranging. Such tasks are often reported to take up 80% or more of an analyst's time.
In pandas, we've adopted a convention used in the R programming language by referring to missing data as NA, which stands for not available. In statistics applications, NA data may either be data that does not exist or that exists but was not observed.
When cleaning up data for analysis, it is often important to do analysis on the missing data itself to identify data collection problems or potential biases in the data caused by missing data.

Filtering Out Missing Data

 import pandas as pd
from numpy import nan as NA
 
data = {'state': ['Ohio', 'Ohio', NA, 'Nevada', 'Nevada', 'Nevada'],
'year': [2000, 2001, 2001, 2002, 2002, 2003],
'pop': [1.5, 1.7, 3.6, 2.4, 2.9, NA]}
frame = pd.DataFrame(data)

 >>> frame
    state  year  pop
0    Ohio  2000  1.5
1    Ohio  2001  1.7
2     NaN  2001  3.6
3  Nevada  2002  2.4
4  Nevada  2002  2.9
5  Nevada  2003  NaN

 >>> frame.info()
RangeIndex: 6 entries, 0 to 5
Data columns (total 3 columns):
state    5 non-null object
year     6 non-null int64
pop      5 non-null float64
dtypes: float64(1), int64(1), object(1)
memory usage: 272.0+ bytes

 >>> frame.isnull()
   state   year    pop
0  False  False  False
1  False  False  False
2   True  False  False
3  False  False  False
4  False  False  False
5  False  False   True

 >>> frame.dropna()
 
 
    state  year  pop
0    Ohio  2000  1.5
1    Ohio  2001  1.7
3  Nevada  2002  2.4
4  Nevada  2002  2.9

 >>> frame['debt']=NA
>>> frame
    state  year  pop  debt
0    Ohio  2000  1.5   NaN
1    Ohio  2001  1.7   NaN
2     NaN  2001  3.6   NaN
3  Nevada  2002  2.4   NaN
4  Nevada  2002  2.9   NaN
5  Nevada  2003  NaN   NaN

 >>> frame=frame.dropna(axis=1,how="all")
 
    state  year  pop
0    Ohio  2000  1.5
1    Ohio  2001  1.7
2     NaN  2001  3.6
3  Nevada  2002  2.4
4  Nevada  2002  2.9
5  Nevada  2003  NaN

 # only keep rows with at least 3 non-NaN values
>>> frame.dropna(thresh=3)
    state  year  pop
0    Ohio  2000  1.5
1    Ohio  2001  1.7
3  Nevada  2002  2.4
4  Nevada  2002  2.9

Filling in missing data

 >>> frame.fillna(0)
 
    state  year  pop
0    Ohio  2000  1.5
1    Ohio  2001  1.7
2       0  2001  3.6
3  Nevada  2002  2.4
4  Nevada  2002  2.9
5  Nevada  2003  0.0

 >>> frame.fillna({'state':'Ohio',
...:               'pop': 2})
    state  year  pop
0    Ohio  2000  1.5
1    Ohio  2001  1.7
2    Ohio  2001  3.6
3  Nevada  2002  2.4
4  Nevada  2002  2.9
5  Nevada  2003  2.0

Removing Duplicates

 >>> data = pd.DataFrame({'k1': ['one', 'two'] * 3 + ['two'],
....:   'k2': [1,3,2,3,3,4,4], 'v1':[0,1,2,3,4,5,5]})

 >>> data
    k1  k2  v1
0  one   1   0
1  two   3   1
2  one   2   2
3  two   3   3
4  one   3   4
5  two   4   5
6  two   4   5

 >>> data.duplicated()
0    False
1    False
2    False
3    False
4    False
5    False
6     True
dtype: bool

 >>> data.drop_duplicates()
    k1  k2  v1
0  one   1   0
1  two   3   1
2  one   2   2
3  two   3   3
4  one   3   4
5  two   4   5

 >>> data.drop_duplicates(['k1','k2'])
    k1  k2  v1
0  one   1   0
1  two   3   1
2  one   2   2
4  one   3   4
5  two   4   5
# keep="last", False

Transforming Data

 >>> 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
 
          food  ounces
0        bacon     4.0
1  pulled pork     3.0
2        bacon    12.0
3     Pastrami     6.0
4  corned beef     7.5
5        Bacon     8.0
6     pastrami     3.0
7    honey ham     5.0
8     nova lox     6.0

 >>> lowercased = data['food'].apply(
...:            lambda x: x.lower())
>>> lowercased
0          bacon
1    pulled pork
2          bacon
3       pastrami
4    corned beef
5          bacon
6       pastrami
7      honey ham
8       nova lox

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

 >>> data['animal'] = lowercased.map(meat_to_animal)
>>> data
          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

Replacing Values

 >>> data.replace("pig","pig-1")
          food  ounces  animal
0        bacon     4.0   pig-1
1  pulled pork     3.0   pig-1
2        bacon    12.0   pig-1
3     Pastrami     6.0     cow
4  corned beef     7.5     cow
5        Bacon     8.0   pig-1
6     pastrami     3.0     cow
7    honey ham     5.0   pig-1
8     nova lox     6.0  salmon

 >>> data.replace(["pig","cow"],"pig-2")
          food  ounces  animal
0        bacon     4.0   pig-2
1  pulled pork     3.0   pig-2
2        bacon    12.0   pig-2
3     Pastrami     6.0   pig-2
4  corned beef     7.5   pig-2
5        Bacon     8.0   pig-2
6     pastrami     3.0   pig-2
7    honey ham     5.0   pig-2
8     nova lox     6.0  salmon

many to many, by dict

 >>> data.replace({"pig":"pig-1","cow":"cow-1"})

Detecting and Filtering Outliers

 >>> data[data['ounces']>=8]=NA
>>> data
          food  ounces  animal
0        bacon     4.0     pig
1  pulled pork     3.0     pig
2          NaN     NaN     NaN
3     Pastrami     6.0     cow
4  corned beef     7.5     cow
5          NaN     NaN     NaN
...
>>> data = data.dropna()

Computing Dummy Variables

 >>> dummies = pd.get_dummies(data['animal'])
>>> data.join(dummies)
          food  ounces  animal  cow  pig  salmon
0        bacon     4.0     pig    0    1       0
1  pulled pork     3.0     pig    0    1       0
3     Pastrami     6.0     cow    1    0       0
4  corned beef     7.5     cow    1    0       0
6     pastrami     3.0     cow    1    0       0
7    honey ham     5.0     pig    0    1       0
8     nova lox     6.0  salmon    0    0       1

	import pandas as pd
	db =pd.read_excel('db_top.xlsx')

	# pd.read_csv('...csv',encoding='utf-8')
	# pd.read_excel('db_top.xlsx', sheet_name="Sheet1")
	# pd.read_excel('db_top.xlsx', sheet_name=0, index_col=0\
	header=1, usecols=[0,2])

	# 载入excel文件，sheet=0为第一个sheet，index_col指定某一列为index
	# header=1指定某一行为header，uscecols提取哪几列

	director.to_excel(excel_writer='director.xlsx', sheet_name="rank",\
	encoding='utf-8')

	# director.to_excel(excel_writer='director.xlsx', index=False,\
	columns=[...], na_rep=0, inf_rep=0)

	excelpath = '...'
	writer = pd.ExcelWriter(excelpath, engine="xlsxwriter")
	df1.to_excel(writer, sheet_name="first")
	df2.to_excel(writer, sheet_name="second")
	df3.to_excel(writer, sheet_name="third")

	writer.save()

	# string
	with open('pi_digits.txt') as file_object:
	contents = file_object.read()
	print(contents.rstrip())

	# pi_digits.txt
	3.1415926535
	8979323846
	2643383279

Python Programming

Lecture 16 Data Cleaning, Language and Text

16.1 Handling Missing Data

Filtering Out Missing Data

Removing Duplicates

Transforming Data

16.2 Handling Chinese Language and Text

Loading .xlsx file

Saving to .xlsx

Reading from a text File

Writing to a File

Appending to a File

Summary

	import pandas as pd
	from numpy import nan as NA

	data = {'state': ['Ohio', 'Ohio', NA, 'Nevada', 'Nevada', 'Nevada'],
	'year': [2000, 2001, 2001, 2002, 2002, 2003],
	'pop': [1.5, 1.7, 3.6, 2.4, 2.9, NA]}
	frame = pd.DataFrame(data)

	>>> frame
	state year pop
	0 Ohio 2000 1.5
	1 Ohio 2001 1.7
	2 NaN 2001 3.6
	3 Nevada 2002 2.4
	4 Nevada 2002 2.9
	5 Nevada 2003 NaN

	>>> frame.info()
	RangeIndex: 6 entries, 0 to 5
	Data columns (total 3 columns):
	state 5 non-null object
	year 6 non-null int64
	pop 5 non-null float64
	dtypes: float64(1), int64(1), object(1)
	memory usage: 272.0+ bytes

	>>> frame.isnull()
	state year pop
	0 False False False
	1 False False False
	2 True False False
	3 False False False
	4 False False False
	5 False False True

	>>> frame.dropna()


	state year pop
	0 Ohio 2000 1.5
	1 Ohio 2001 1.7
	3 Nevada 2002 2.4
	4 Nevada 2002 2.9

	>>> frame['debt']=NA
	>>> frame
	state year pop debt
	0 Ohio 2000 1.5 NaN
	1 Ohio 2001 1.7 NaN
	2 NaN 2001 3.6 NaN
	3 Nevada 2002 2.4 NaN
	4 Nevada 2002 2.9 NaN
	5 Nevada 2003 NaN NaN

	>>> frame=frame.dropna(axis=1,how="all")

	state year pop
	0 Ohio 2000 1.5
	1 Ohio 2001 1.7
	2 NaN 2001 3.6
	3 Nevada 2002 2.4
	4 Nevada 2002 2.9
	5 Nevada 2003 NaN

	# only keep rows with at least 3 non-NaN values
	>>> frame.dropna(thresh=3)
	state year pop
	0 Ohio 2000 1.5
	1 Ohio 2001 1.7
	3 Nevada 2002 2.4
	4 Nevada 2002 2.9

	>>> frame.fillna(0)

	state year pop
	0 Ohio 2000 1.5
	1 Ohio 2001 1.7
	2 0 2001 3.6
	3 Nevada 2002 2.4
	4 Nevada 2002 2.9
	5 Nevada 2003 0.0

	>>> frame.fillna({'state':'Ohio',
	...: 'pop': 2})
	state year pop
	0 Ohio 2000 1.5
	1 Ohio 2001 1.7
	2 Ohio 2001 3.6
	3 Nevada 2002 2.4
	4 Nevada 2002 2.9
	5 Nevada 2003 2.0

	filename = 'pi_digits.txt'

	with open(filename) as file_object:
	lines = file_object.readlines()

	filename = 'programming.txt'

	with open(filename, 'w') as file_object:
	file_object.write("I love programming.")

	filename = 'programming.txt'
	with open(filename, 'w') as file_object:
	file_object.write("I love programming.")
	file_object.write("I love creating new games.")

	filename = 'programming.txt'
	with open(filename, 'w') as file_object:
	file_object.write("I love programming.\n")
	file_object.write("I love creating new games.\n")

	>>> data = pd.DataFrame({'k1': ['one', 'two'] * 3 + ['two'],
	....: 'k2': [1,3,2,3,3,4,4], 'v1':[0,1,2,3,4,5,5]})

	>>> data
	k1 k2 v1
	0 one 1 0
	1 two 3 1
	2 one 2 2
	3 two 3 3
	4 one 3 4
	5 two 4 5
	6 two 4 5

	>>> data.duplicated()
	0 False
	1 False
	2 False
	3 False
	4 False
	5 False
	6 True
	dtype: bool

	>>> data.drop_duplicates()
	k1 k2 v1
	0 one 1 0
	1 two 3 1
	2 one 2 2
	3 two 3 3
	4 one 3 4
	5 two 4 5

	>>> data.drop_duplicates(['k1','k2'])
	k1 k2 v1
	0 one 1 0
	1 two 3 1
	2 one 2 2
	4 one 3 4
	5 two 4 5
	# keep="last", False

	>>> 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

	food ounces
	0 bacon 4.0
	1 pulled pork 3.0
	2 bacon 12.0
	3 Pastrami 6.0
	4 corned beef 7.5
	5 Bacon 8.0
	6 pastrami 3.0
	7 honey ham 5.0
	8 nova lox 6.0

	>>> lowercased = data['food'].apply(
	...: lambda x: x.lower())
	>>> lowercased
	0 bacon
	1 pulled pork
	2 bacon
	3 pastrami
	4 corned beef
	5 bacon
	6 pastrami
	7 honey ham
	8 nova lox

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

	>>> data['animal'] = lowercased.map(meat_to_animal)
	>>> data
	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

	>>> data.replace("pig","pig-1")
	food ounces animal
	0 bacon 4.0 pig-1
	1 pulled pork 3.0 pig-1
	2 bacon 12.0 pig-1
	3 Pastrami 6.0 cow
	4 corned beef 7.5 cow
	5 Bacon 8.0 pig-1
	6 pastrami 3.0 cow
	7 honey ham 5.0 pig-1
	8 nova lox 6.0 salmon

	>>> data.replace(["pig","cow"],"pig-2")
	food ounces animal
	0 bacon 4.0 pig-2
	1 pulled pork 3.0 pig-2
	2 bacon 12.0 pig-2
	3 Pastrami 6.0 pig-2
	4 corned beef 7.5 pig-2
	5 Bacon 8.0 pig-2
	6 pastrami 3.0 pig-2
	7 honey ham 5.0 pig-2
	8 nova lox 6.0 salmon

	>>> data[data['ounces']>=8]=NA
	>>> data
	food ounces animal
	0 bacon 4.0 pig
	1 pulled pork 3.0 pig
	2 NaN NaN NaN
	3 Pastrami 6.0 cow
	4 corned beef 7.5 cow
	5 NaN NaN NaN
	...
	>>> data = data.dropna()

	>>> dummies = pd.get_dummies(data['animal'])
	>>> data.join(dummies)
	food ounces animal cow pig salmon
	0 bacon 4.0 pig 0 1 0
	1 pulled pork 3.0 pig 0 1 0
	3 Pastrami 6.0 cow 1 0 0
	4 corned beef 7.5 cow 1 0 0
	6 pastrami 3.0 cow 1 0 0
	7 honey ham 5.0 pig 0 1 0
	8 nova lox 6.0 salmon 0 0 1

	filename = 'programming.txt'
	message.py
	with open(filename, 'a') as file_object:
	file_object.write("I also love finding meaning in large datasets.\n")
	file_object.write("I love creating apps that can run in a browser.\n")