数据处理笔记-大创

SQL的模糊查询

# %:表示任意0个或多个字符，中文使用%%
SELET 字段 FROM 表 WHERE u_name LIKE '%111%'

# _:表示任意单个字符，用来限制表达式的字符长度
SELECT * FROM 表 WHERE u_name LIKE '_1__'

# []:表示括号内字符中的一个
# [1-7]、[a-e]:表示一系列字符
SELECT * FROM 表 WHERE u_name LIKE '[hpq]1'

# [^]:表示不在括号内的单个字符
# [^1-6]、[^a-e]:表示不包括一系列字符
SELECT * FROM 表 WHERE u_name LIKE '[^1-5]22'

python操作

dataframe操作：

###dataframe的创建和column,index名修改
# 创建
df = pd.DataFrame({'col1':series1, 'col2':series2})
# 更改column,index名
df.columns = ['new_col1', 'new_col2']
df.index = [1,2,3]
df = df.rename(columns={'col1':'new_col1','col2':'new_col2'})

### dataframe的遍历
# 按行遍历:迭代(insex, Series)对
for ind, row in df.iterrows():
    print(ind)
    print(row['col'])
# 按列遍历:迭代(列名, Series)对
for col_name, col in df.iteritems():
    print(col)
# 元组迭代
for row in df.itertuples():
　　print(row)
# Series遍历
df['col'] = df['col'].apply(lambda x:x+1)
df['col'] = df['col'].apply(func)

### dataframe的筛选操作
# 判断非空、包含字符（字符串/正则表达式）
df[(df['col'].notna()) & (df['col2'].str.contains('abc')) &
   df['col3'].str.match(r"[0-9]+")].reset_index(drop=True)
# 删除某行或某列
df = df.drop('col1',axis=1)
df = df.drop('row1',axis=0)
df = df.drop(col_list, axis=1)
df = df.drop(index_list, axis=0)
# 选择df的某一行或某一个元素
df.iloc[i]
df.loc[i,'col']
## dataframe的groupby
# 显示所有数据
df.groupby('col').apply(lambda x:x[:])
# 将groupby数据的某一列转为列表
df.groupby('col')['col2'].apply(lambda x:x[:]).to_list()
# 得到分组后的组名
df.groupby('col')['col2'].apply(lambda x:x[:]).index
## 将groupby后统计的值映射回原表的所有行上
# method1:
df1.groupby('col1')['col2'].transform(func) # func可自定义（传入的参数为一组下的Series）
df1.groupby('col1')['col2'].transform('mean') # 直接调用的统计方法:'mean','sum','count'...
# method2:
# how='outer'表示针对所有值（外连接），how='inter'表示针对同时存在的样本（内连接）
pd.merge(df1, df2, how='outer', on='col')
# 对groupby的每个组进行统计，分别返回一个结果
df1.groupby('col1').agg('min','mean')
## 在groupby下每组中找出众数
# 存在多个众数时选取最小的众数
from scipy import stats
df.groupby('A').agg(lambda x:stats.mode(x)[0][0]).reset_index()
# 存在多个众数时选取最大的众数
from scipy import stats
df.groupby('A').agg(lambda x: x.value_counts().index[0]).reset_index()
# 找出所有众数
df.groupby('A').agg(pd.Series.mode).reset_index()

其他操作总结：

### 一阶差分操作（后一行分别减去前一行的值）
df.diff()

### 列表操作
# 两个列表合并
list1.extend(list2)
# 列表的条件遍历（字典等同理）
[val for val in listt if val==1]
# 将列表字符串转成列表
listt = eval(list_str)
# 浅拷贝和深拷贝元素生成列表（字典等同理）
listt = [val]*n # 浅拷贝
import copy
listt = [val]
for i in range(1,n): # 深拷贝
    listt.append(copy.deepcopy(val))
# 统计列表中元素的数量
from collections import Counter
num_list = Counter(listt)
# 找出列表中某个值val的所有索引
ll,tmp = [],-1
for i in range(listt.count(val)):
    tmp = listt.index(val, tmp+1, len(listt))
    ll.append(tmp)
# 列表删除元素[倒序删除，否则很可能数组越界]（字典等同理）
for val in listt[::-1]:
    if val==0:
        listt.remove(val)

### 字典操作
# 字典的生成
dictt = dict(zip(list1,list2))

### 集合操作
# 增加一个元素/一个集合
set1.add(val)
set1.update({1,2,3})
# 删除元素
set1.remove(1)
set1.pop()
# 取交集
set1 & set2
set1.intersection(set2)
# 取并集
set1 | set2
set1.union(set2)
# 取差集
set1.difference(set2)
set1-(set1&set2)
# set1是set2的子集时，返回True，否则返回False
set1 <= set2 

PyMySQL

1 数据库连接

连接数据库之前，确保完成以下事项：

• 创建了数据库TESTDB，以及该库中的表EMPLOYEE
• EMPLOYEE包含字段有FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME
• 连接数据库TESTDB使用的用户名为 “testuser” ，密码为 “test123”
• 机子上安装了Python MySQLdb模块

创建实例

import pymysql
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute()  方法执行 SQL 查询版本号
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
 
print ("Database version : %s " % data)
 
# 关闭数据库连接
db.close()

#### 输出结果
# Database version : 5.5.20-log

2 创建并插入数据库表

import pymysql
 
# 打开数据库连接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )
 
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 使用 execute() 方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
 
# 使用预处理语句创建表（创建空表，字段名 数据类型和空间大小 是否要求非空）
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
cursor.execute(sql)

# 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
# 另一种写法
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s',  %s,  '%s',  %s)" %('Mac', 'Mohan', 20, 'M', 2000)

try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 如果发生错误则回滚
   db.rollback()

# 关闭数据库连接
db.close()

3 数据库查询操作

• fetchone()：该方法获取下一个查询结果集
• fetchall()：接收全部的返回结果行

未完待续。