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