数据的分析与预处理

1.连接数据库读取数据

import MySQLdb
db =mysql.connect("localhost","root","12345678","student")
cursor=db.cursor()

##插入数据
sql="""insert into student
("chen",20,"anhui")"""
##取出数据库里的数据打印出来
datas=cursor.fetchall()
for data in datas:
  print(data[1])
print(cursor.rowcount)#打印行数
##动态插入，使用占位符实现
title="title"
url="www.baidu.com"
sql="""insert into student
(0,"%s","%s","chen")"""
##更新操作Update
区别是
sql="""update article set title="%s" where id="%d" """

try:
   cursor.execute(sql%(title,url))
   db.commit()
except:
   db.rollback()
db.close()

2.数据转换
(1)数据合并

left1=pd.DataFrame({'key':['a','b','c','d','a','c'],'value':range(6)})
right1=pd.DataFrame({'group_val':[3.5,7]},index=['a','b'])

key	value

0 a 0
1 b 1
2 c 2
3 d 3
4 a 4
5 c 5

group_val

a 3.5
b 7.0

pd.merge(left1,right1,left_on='key',right_index=True,how='outer')#加了how='outer'就是求并集，否则是交集

key	value	group_val

0 a 0 3.5
4 a 4 3.5
1 b 1 7.0
2 c 2 NaN
5 c 5 NaN
3 d 3 NaN

left1.join(right1,on='key')

key	value	group_val

0 a 0 3.5
1 b 1 7.0
2 c 2 NaN
3 d 3 NaN
4 a 4 3.5
5 c 5 NaN

(2)数据去重

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

 k1  k2

0 one 1
1 one 1
2 one 2
3 two 3
4 two 3
5 two 4
6 two 4

data.duplicated()
data["v1"]=range(7)
data.drop_duplicates(['k1'])
data.drop_duplicates(['k1'，'k2'],take_last=True)

(3)数据替换

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

0 1.0
1 NaN
2 2.0
3 NaN
4 0.0
5 3.0
dtype: float64

np.random.seed(12345)
data=pd.DataFrame(np.random.randn(1000,4))
data.describe()

0 1 2 3
count 1000.000000 1000.000000 1000.000000 1000.000000
mean -0.067684 0.067924 0.025598 -0.002298
std 0.998035 0.992106 1.006835 0.996794
min -3.428254 -3.548824 -3.184377 -3.745356
25% -0.774890 -0.591841 -0.641675 -0.644144
50% -0.116401 0.101143 0.002073 -0.013611
75% 0.616366 0.780282 0.680391 0.654328
max 3.366626 2.653656 3.260383 3.927528

data[(np.abs(data)>3).any(1)]
#任何一列大于3则打印出来
data[np.abs(data)>3]=np.sign(data)*3
data.describe()
#将值限制在-3～3之间

0 1 2 3
count 1000.000000 1000.000000 1000.000000 1000.000000
mean -0.067623 0.068473 0.025153 -0.002081
std 0.995485 0.990253 1.003977 0.989736
min -3.000000 -3.000000 -3.000000 -3.000000
25% -0.774890 -0.591841 -0.641675 -0.644144
50% -0.116401 0.101143 0.002073 -0.013611
75% 0.616366 0.780282 0.680391 0.654328
max 3.000000 2.653656 3.000000 3.000000