Python Programming
Lecture 16 Data Cleaning, Language and Text
16.1 Handling Missing Data
-
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
16.2 Handling Chinese Language and Text
Loading .xlsx file
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提取哪几列
Character Encoding: ASCII, Unicode, UTF-8, , GBK
Saving to .xlsx
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)
Multiple Sheets
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()
Reading from a text File
# string
with open('pi_digits.txt') as file_object:
contents = file_object.read()
print(contents.rstrip())
# pi_digits.txt
3.1415926535
8979323846
2643383279
Making a List of Lines from a File
filename = 'pi_digits.txt'
with open(filename) as file_object:
lines = file_object.readlines()
Writing to a File
filename = 'programming.txt'
with open(filename, 'w') as file_object:
file_object.write("I love programming.")
Writing to a File
-
The second argument, 'w', tells Python that we want to open the file in write mode. You can open a file 198 Chapter 10 in read mode ('r'), write mode ('w'), append mode ('a'), or a mode that allows you to read and write to the file ('r+'). If you omit the mode argument, Python opens the file in read-only mode by default.
-
Python can only write strings to a text file. If you want to store numerical data in a text file, you'll have to convert the data to string format first using the str() function.
filename = 'programming.txt'
with open(filename, 'w') as file_object:
file_object.write("I love programming.")
file_object.write("I love creating new games.")
I love programming.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")
I love programming.
I love creating new games.
Appending to a File
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")
I love programming.
I love creating new games.
I also love finding meaning in large datasets.
I love creating apps that can run in a browser.
Summary
- Pandas
- Reading: Python for Data Analysis, Chapter 7
- Reading: Python Crash Course, Chapter 10.1