Python数据分析之清洗

1
2
3
import pandas as pd
import numpy as np
string_data = pd.Series(['aardvark', 'artichoke', np.nan, 'avocado'])

1
string_data

1
string_data.isnull()

1
2
#None也被视为NA
string_data[0] = None

1
string_data.isnull()

1
df = pd.DataFrame({'dropna':'祛除缺失值','fillna':'填充','isnull':'检查是','notnull':'检查不是'},index = ['1','2','3','4'])

1
df.iloc[1] #一些方法

1
from numpy import nan as NA

1
data = pd.Series([1,NA,3.5,NA,7])

1
data.dropna() #删除缺失值

1
data[data.notnull()] #等价写法

1
data = pd.DataFrame([[1., 6.5, 3.], [1., NA, NA],[NA, NA, NA], [NA, 6.5, 3.]])

1
data

1
2
cleaned = data.dropna()
data

1
cleaned

1
data.dropna(how = 'all')

1
2
data[4] = NA
data

1
data.dropna(1,how = 'all')

1
2
3
4
df = pd.DataFrame(np.random.randn(7,3))
df.iloc[:4,1 ]= NA
df.iloc[:2,2] = NA
df

1
df.dropna() #有NA值就删除该行

1
df.dropna(thresh=2) #删除具有两个NA值的行

1
df.fillna(0) #以0填充

1
df.fillna({1:0.5,2:0}) #对不同的行使用不同的填充数

1
2
3
_ = df.fillna(0,inplace=True)
#作用于原对象
df

1
2
3
4
df = pd.DataFrame(np.random.randn(6,3))
df.iloc[2:,1] = NA
df.iloc[4:,2] = NA
df

1
df.fillna(method='ffill') #前向

1
df.fillna(method = 'ffill',limit=2) #数量限制

1
2
data = pd.Series([1.,NA,3.5,NA,7])
data.fillna(data.mean()) #以平均值填充

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

1
data

1
data.duplicated()

1
data.drop_duplicates()

1
2
data['v1'] = range(7)
data.drop_duplicates(['k1'])

1
data.drop_duplicates(['k1','k2'])

1
data.drop_duplicates(['k1','k2'],keep = 'last')

1
2
3
4
5
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]})

1
data

1
2
3
4
5
6
7
8
meat_to_animal = {
'bacon': 'pig',
'pulled pork': 'pig',
'pastrami': 'cow',
'corned beef': 'cow',
'honey ham': 'pig',
'nova lox': 'salmon'
}

1
2
lowercased = data.food.str.lower()
lowercased

1
data['animal'] = lowercased.map(meat_to_animal)

1
data

1
data['food'].map(lambda x: meat_to_animal[x.lower()])

1
data = pd.Series([1., -999., 2., -999., -1000., 3.])

1
data

1
data.replace(-999,np.nan)

1
data.replace([-999,-1000],[np.nan,0]) #替换多个值

1
data.replace({-999:np.nan,-1000:0}) #等价写法

1
2
3
4
data = pd.DataFrame(np.arange(12).reshape((3, 4)),
index=['Ohio', 'Colorado', 'New York'],
columns=['one', 'two', 'three', 'four'])

1
transform = lambda x: x[:4].upper()

1
data.index.map(transform)

1
data

1
data.index = data.index.map(transform) #作用于原对象

1
data

1
data.rename(index = str.title,columns = str.upper)

1
data.rename(index={'OHIO': 'INDIANA'},columns={'three': 'peekaboo'}) #特定替换

1
data.rename(index = {'OHIO':'INDIANA'},inplace = True) #作用于原对象

1
data

1
ages = [20,22,25,27,21,23,37,31,61,45,41,32]

1
bins = [18,25,35,60,100]

1
2
cats = pd.cut(ages,bins)
cats

1
cats.codes

1
cats.categories

1
pd.value_counts(cats)

1
pd.cut(ages,[18,26,36,61,100],right=False)

1
group_names = ['Y','YA','WA','S']

1
pd.cut(ages,bins,labels = group_names)

1
2
data = np.random.rand(20)
pd.cut(data,4,precision=2) #precision控制小数点数量

1
2
3
data = np.random.randn(1000)
cats = pd.qcut(data,4)
cats

1
pd.value_counts(cats)

1
pd.qcut(data,[0,0.1,0.5,0.9,1.])

1
data = pd.DataFrame(np.random.randn(1000,4))

1
data.describe()

1
col = data[2]

1
col[np.abs(col) > 3]

1
data[(np.abs(data) > 3).any(1)]

1
2
3
data[np.abs(data) > 3]
= np.sign(data) * 3
data.describe

1
np.sign(data).head() #产生1和-1'

1
data.iloc[938] #验证

1
df = pd.DataFrame(np.arange(5*4).reshape((5,4)))

1
sampler = np.random.permutation(5)

1
sampler

1
df

1
df.take(sampler)

1
df.sample(n=3)

1
2
choicesv
= pd.Series([5,7,-1,6,4])

1
draws = choicesv.sample(n = 10,replace = True)

1
draws

1
df = pd.DataFrame({'key': ['b', 'b', 'a', 'c', 'a', 'b'], 'data1': range(6)})

1
df

1
pd.get_dummies(df.key)

1
dummies = pd.get_dummies(df.key,prefix = 'key')

1
2
df_with_dummy = df[['data1']].join(dummies)
df_with_dummy

1
mnames = ['movie_id', 'title', 'genres']

1
2
movies = pd.read_table('/Users/meininghang/Downloads/pydata-book-2nd-edition/datasets/movielens/movies.dat',
sep = '::',header = None,names = mnames)

1
movies[:10]

1
all_genres = []

1
2
for x in movies.genres:
all_genres.extend(x.split('|'))

1
2
genres = pd.unique(all_genres)
genres

1
zero_matrix = np.zeros((len(movies),len(genres)))

1
dummies = pd.DataFrame(zero_matrix,columns = genres)

1
gen = movies.genres[0]

1
gen.split('|')

1
dummies.columns.get_indexer(gen.split('|'))

1
2
3
for i,gen in enumerate(movies.genres):
indices = dummies.columns.get_indexer(gen.split('|'))
dummies.iloc[i,indices] = 1

1
movies_windic = movies.join(dummies.add_prefix('Genre_'))

1
movies_windic.iloc[0]

1
np.random.seed(12345)

1
values = np.random.rand(10)

1
values

1
bins = [0,0.2,0.4,0.6,0.8,1]

1
pd.get_dummies(pd.cut(values,bins))

1
2
val = 'a,b,
guido'

1
val.split(' , ') #切

1
2
3
['a,b,
guido']

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

1
2
['a', 'b', 'guido']

1
f,s,t = pieces

1
f + '::' + s + '::' + t 

1
2
'a::b::guido'

1
'guido' in val

1
2
True

1
val.index(',')

1
2
1

1
val.find(':')

1
2
-1

1
val.count(',')

1
2
2

1
val.replace(',','::')

1
2
3
'a::b::
guido'

1
val.replace(',',' ')

1
2
3
'a b
guido'

1
import re

1
2
3
text = 'foo
bart baz
tqux'

1
re.split('s+',text)

1
2
['foo', 'bar', 'baz', 'qux']

1
regex = re.compile('s+')

1
regex.split(text)

1
2
['foo', 'bar', 'baz', 'qux']

1
regex.findall(text)

1
2
3
4
['
', 't ', '
t']

1
regex.search(text) #返回第一个匹配结果

1
2
3
<_sre.SRE_Match object; span=(3, 7), match='
'>

1
2
3
4
5
6
7
8
text = """Dave dave@google.com
Steve steve@gmail.com
Rob rob@gmail.com
Ryan ryan@yahoo.com
"""
pattern = r'[A-Z0-9._%+-]+@[A-Z0-9.-]+.[A-Z]{2,4}'
# re.IGNORECASE makes the regex case-insensitive
regex = re.compile(pattern, flags=re.IGNORECASE)

1
regex.findall(text)

1
2
['dave@google.com', 'steve@gmail.com', 'rob@gmail.com', 'ryan@yahoo.com']

1
regex.sub('READCTED',text)

1
2
'Dave READCTEDnSteve READCTEDnRob READCTEDnRyan READCTEDn'

1
2
3
pattern = r'([A-Z0-9._%+-]+)@([A-Z0-9.-]+).([A-Z]{2,4})'
regex = re.compile(pattern, flags=re.IGNORECASE)
m = regex.match('wesm@bright.net')

1
m.groups()

1
2
('wesm', 'bright', 'net')

1
regex.findall(text) #返回分组结果

1
2
3
4
5
[('dave', 'google', 'com'),
('steve', 'gmail', 'com'),
('rob', 'gmail', 'com'),
('ryan', 'yahoo', 'com')]

1
regex.sub(r'Username: 1, Domain: 2, Suffix: 3',text)

1
2
'Dave Username: dave, Domain: google, Suffix: comnSteve Username: steve, Domain: gmail, Suffix: comnRob Username: rob, Domain: gmail, Suffix: comnRyan Username: ryan, Domain: yahoo, Suffix: comn'

1
2
data = {'Dave': 'dave@google.com', 'Steve': 'steve@gmail.com',
'Rob': 'rob@gmail.com', 'Wes': np.nan}

1
data = pd.Series(data)

1
data

1
2
3
4
5
6
7
8
9
10
Dave
dave@google.com
Rob
rob@gmail.com
Steve
steve@gmail.com
Wes
NaN
dtype: object

1
data.isnull()

1
2
3
4
5
6
7
8
9
10
Dave
False
Rob
False
Steve
False
Wes
True
dtype: bool

1
data.str.contains('gmail')

1
2
3
4
5
6
7
8
9
10
Dave
False
Rob
True
Steve
True
Wes
NaN
dtype: object

1
data.str[:5]

1
2
3
4
5
6
7
8
9
10
Dave
dave@
Rob
rob@g
Steve
steve
Wes
NaN
dtype: object