1 python连接mysql的几种方式

a SQLAlchemy

import pymysql
pymysql.install_as_MySQLdb()
import sqlalchemy
from sqlalchemy import create_engine

engine = sqlalchemy.create_engine('mysql+pymysql://用户名:密码@IP:3306/datafrog05_adventure')
sql = 'select * from dw_customer_order' 
gather_customer_order = pd.read_sql_query(sql,engine)

b PyMySQL

import pymysql

pip install PyMySQL

# 为了兼容mysqldb，只需要加入

pymysql.install_as_MySQLdb()

# 打开数据库连接
conn = pymysql.connect(host='*.*.*.*',
                       port=3306,
                       user='*',
                       passwd='*',
                       charset = 'utf8'
                       )

# 使用 cursor() 方法创建一个游标对象 cursor                       
cursor = conn.cursor()
# 使用 execute()  方法执行 SQL 查询
cursor.execute("show databases;")
cursor.execute("use database_name;")
cursor.execute("show tables;")
cursor.execute("select * from tables_name")

# 使用 fetchone() 方法获取单条数据;使用 fetchall() 方法获取所有数据
data = cursor.fetchall()
for item in data:
    print(item[0])

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

2 查看数据类型的几种方式

a 维度查看 df.shape()

gather_customer_order.shape()

b 数据表基本信息（维度、列名称、数据格式、所占空间等）：df.info()

gather_customer_order.info()

c 每一列数据的格式：df.dtypes

gather_customer_order.dtypes

3 时间转字符串类型等，延伸时间函数总结

先对时间格式进行判断:

type(gather_customer_order['create_year_month'][0])

Dataframe一开始默认的格式是 int64的，可以用 df.info() 来查看,然后我们利用pd.to_datetime() 函数转化为datetime格式，默认是 datetime64[ns] 类型，后面中括号表示时间间隔是 ns。转化完成后，datetime其实是个时间戳形式，也就是在excel表格显示到毫秒，或是power BI 中展示需要文本格式，所以我们需要再转换为str 或者 object, 代码如下：

# 时间转字符串：strftime()函数，字符串转时间：strptime()函数
gather_customer_order['create_date']=pd.to_datetime(gather_customer_order.create_date,format="%Y%m%d")
gather_customer_order['create_year_month'] = gather_customer_order['create_date'].apply(lambda x : x.strftime('%Y-%m'))

4 可能DataFrame中存在科学计数法，请自行探索如何解决

DataFrame中float类型数据默认以科学计数法显示，可以在pd.set_option设置display.float_format参数来以小数显示，比如:

#设置显示到小数点后3位
pd.set_option('display.float_format', lambda x: '%.3f' % x)

#另一种方法：取消科学计数法显示
import numpy as np
np.set_printoptions(suppress=True, threshold=np.nan)

5 计算上下两行之间相除或相乘等一系列方法的函数使用方法罗列

a diff()函数

numpy.diff(a, n=1,axis=-1)
dataframe.diff(periods,axis)

b shift 函数：相邻两行进行加减乘除

先shift()，将一列所有数据向上或向下平移，再进行加减乘除

c pct_change()

DataFrame.pct_change(periods=1, fill_method=‘pad’, limit=None, freq=None, **kwargs)

表示当前元素与先前元素的相差百分比，当然指定periods=n,表示当前元素与先前n 个元素的相差百分比。

6 总结DataFrame与Series类型区别

区别1: Series和DataFrame的创建

pd.Series(data=None, index=None, dtype=None, name=None)
data：可以是数组、可迭代对象、字典等；
index：设置 Series 索引；
dtype：设置 Series 元素数据类型；
name：设置 Series 名称

pd.DataFrame(data=None, index=None, columns=None, dtype=None)
data：可以是数组、可迭代对象、字典等；
index：设置 DataFrame 的行索引；
columns：设置 DataFrame 的列名；
dtype：设置 DataFrame 的元素数据类型

区别2：Series 基本属性：序列值 - values

通过 pandas.Series 的 values 属性可以取出序列所有值。

s = pd.Series([1,2,3,4], index=['a','b','c','d'])
s.values

区别3：Series 基本属性：序列名 - name

通过 pandas.Series 的 name 属性可以设置该序列的名字。可以其他DataFrame或Series类型合并，和DataFrame合并时无需用rename再修改列名，序列名就是列名。

s = pd.Series([1,2,3,4], index=['a','b','c','d'])
s.name = 'sample'

7 总结DataFrame专用的排序函数和列表的排序函数

dt.sort_index(ascending=False, axis=1)#按列索引进行降序排序,按行的索引进行排序
dt.sort_values(by='columns_name')#按指定列的值进行排序
dt.sort_values(by='row_name', axis=1)#按指定行的值进行排序

#对原列表进行排序，完成排序后，原列表变为有序列表。默认情况（不传入任何参数时）按字典顺序排序。
list.sort(key=None,reverse=False)
# sorted不会改变原列表元素的位置，而是重新生成一个副本。
sorted(data, cmp=None, key=None, reverse=False)

8 总结存储到mysql的几种形式（覆盖、追加等）

DataFrame.to_sql(name, con, schema=None, 
if_exists='fail', index=True, index_label=None, 
chunksize=None, dtype=None, method=None)

to_sql() 方法的 if_exists 参数用于当目标表已经存在时的处理方式，默认是 fail，即目标表存在就失败，另外两个选项是 replace 表示替代原表，即删除再创建，append 选项可在原表添加数据，如果不存在可以新建表。

9 总结DataFrame的去重方法，多种

a drop_duplicates(个人比较倾向的方法)

data.drop_duplicates(subset='列名',keep='first',inplace=True)

b unique()

grouped_month['user_id'].unique().map(len)
grouped_month['user_id'].unique().apply(lambda x: len(x))

10 总结Series转化为列表的方式

# 方法1
df['a'].values.tolist()
# 方法2
df['a'].tolist()
# 方法3
list(Series.values)

11 总结Series与列表的区别

列表：输出结果在中括号中，且各元素之间以逗号分隔。空列表为：a = []

序列：竖着的形式展示数据,并且有索引。空Series为 a = pd.Series([]), 显然，列表可以作为Series的一部分。

12 总结append\extend等列表追加元素的区别及适用范围

列表.append(数据)：在末尾追加一个值，这个最常用
列表.extend(Iterable)：将另一个列表（也可以）的全部内容追加到末尾
注意知识点：使用 append 追加的是将后面的列表作为一个整体追加到最后了，而使用 extend 追加的是将里面的元素依次追加到最后。

13 函数agg的用法及适用范围

groupby功能：分组
groupby + agg(聚集函数们)：分组后，对各组应用一些函数，如’sum’,‘mean’,‘max’,‘min’…

#对data1列，取各组的最小值，名字还是data1
df.groupby(['key1']).agg({'data1':'min'})
df.groupby(['key1']).agg({'data1':['min','max']})

14 DataFrame中的loc,iloc的用法和区别

a 标签索引 - loc

使用 loc 方法可以对 Dataframe 通过标签索引，可以同时对行和列索引。（推荐！）
df.loc[row,col] ：标签索引，选择 row 行 col 列的数据。
df.loc[row1:row2,col1:col2] ：连续切片，选择 row1 行至 row2 行的 col1 列至 col2 列（包含）的数据。
df.loc[row1:row2:s,col1:col2:s] ：等距连续切片，选择 row1 行至 row2 行步长为 s 的 col1 列至 col2 列
（包含）步长为 s 的数据。
df.loc[[row1,row2],[col1,col2]] ：不连续切片，选择 row1 行和 row2 行的 col1 列和 col2 列的数据。
逗号前为行索引，逗号后为列索引。如果没有逗号，默认是按行进行。连续切片时区间为 前闭后闭。

b 位置索引 - iloc

使用 iloc 方法可以对 Dataframe 通过位置索引，可以同时对行和列索引。（推荐！）
df.iloc[row,col] ：位置索引，选择 row 行 col 列的数据。
df.iloc[row1:row2,col1:col2] ：连续切片，选择 row1 行至 row2 行的 col1 列至 col2 列（不包含）的数据。
df.iloc[row1:row2:s,col1:col2:s] ：等距连续切片，选择 row1 行至 row2 行步长为 s 的 col1 列至 col2 列
（不包含）步长为 s 的数据。
df.iloc[[row1,row2],[col1,col2]] ：不连续切片，选择 row1 行和 row2 行的 col1 列和 col2 列的数据。
逗号前为行索引，逗号后为列索引。如果没有逗号，默认是按行进行。连续切片时区间为 前闭后开。

15 筛选函数的总结

a .isin(list(df['chinese_city']) 用于筛选条件过多，无法一一用等于“==”去筛选条件

#筛选销售前十城市，10月11月自行车销售数据
gather_customer_order_10_11[gather_customer_order_10_11['chinese_city'].isin(list(gather_customer_order_city_head['chinese_city']))]

b ~ 取对立面

#筛选2019年1月至11月自行车数据，这里使用的是‘~’取对立面
day_list = list(pd.Series(gather_customer_order_month.create_year_month.drop_duplicates()).values)[11:]
gather_customer_order_month_1_11 = gather_customer_order_month.loc[~(gather_customer_order_month.create_year_month.isin(day_list))]

c | ="or",用于一列筛选多个条件时，注意不能用 &

#筛选10月11月自行车数据
gather_customer_order_10_11 = gather_customer_order[(gather_customer_order['create_year_month']=='2019-10')|(gather_customer_order['create_year_month']=='2019-11')]

16 个人对DataFrame的groupby的理解

总体来看，groupby如图起到了拆分的关键作用，他和apply相互搭配使用：

groupby.png

# 用法
df_age_purchase=df.groupby("Age").agg({"Purchase":"sum"}).reset_index().rename(columns={"Purchase":"Purchase_amount"})

groupby(["",""])+agg({"":"","":""})+reset_index().rename(columns={},inplace=True)

17 DataFrame合并

a append - 横向拼接

pandas.DataFrame 的 append() 方法可以进行简单拼接。
DataFrame.append(other, ignore_index=False, sort=None) 向 DataFrame 中添加新的行，如果添加的列名
不在 DataFrame 对象中，将会被当作新的列进行添加。
常用参数：
other：可以是 DataFrame、series、dict、list等；
ignore_index：是否重设索引；
sort：最好规定是否排序（ True or False ）从而避免出现警告信息。

df1 = pd.DataFrame([['a', np.nan], ['b', 5], ['c', 8]], columns=['key1', 'value1'],index=['x','z','y'])
df2 = pd.DataFrame([['a', 100], ['b', 10], ['c', 100]], columns=['key2', 'value2'],index=['x','z','y'])
df1.append(df2,sort=True)

b concat - 纵向拼接

使用 pd.concat() 函数可以将多个 DataFrame 进行拼接，默认是纵向拼接。
常用参数：
axis=1 表示横向拼接；
ignore_index=True 表示重置索引。

df1 = pd.DataFrame([['a', np.nan], ['b', 5], ['c', 8]], columns=['key', 'value'],index=['x','z','y'])
df2 = pd.DataFrame([['a', 100], ['b', 10], ['c', 100]], columns=['key', 'value'],index=['x','z','y'])
pd.concat([df1, df2]) # 默认纵向拼接

c concat - 横向拼接

pd.concat() 函数参数 axis=1 时表示横向拼接。

pd.concat([df1, df2], axis=1)

c join - left 合并

DataFrame.join(other, on=None, how='left', lsuffix='', rsuffix='', sort=False) 默认按左表索引对齐
合并。
常用参数：
other：可以是 DataFrame、Series、list of DataFrames 等；
how：控制合并方法, 包括‘right’,'inner','outer';
lsuffix/rsuffix：当两个表格有相同列时，直接合并会报错，需使用该参数区分两列。

df1 = pd.DataFrame({'PE':[10,20,25,30]},index=list('abcd'))
df2 = pd.DataFrame({'PB':[2.5,3.2,2.5]},index=list('adg'))
df1.join(df2) # 默认按左表index进行合并

d merge - 重复列合并

使用 pd.merge() 函数或 DataFrame.merge() 方法可以方便地合并含有相同列的两个 DataFrame。
使用 merge 方法合并，默认是 inner 方式，并且默认忽视索引。
常用参数：
on：控制按哪一列合并；
how：控制合并方式；
left_index/right_index：按索引合并；
left_on/right_on：当两个 DataFrame 两列意义相同但列名不同时，可以使用该参数按该列合并。

df1 = pd.DataFrame({'PE':[10,20,25,30],
'ROE':[0.12,0.25,0.13,0.23]}, index=list('abcd'))
df2 = pd.DataFrame({'PB':[2.5,3.2,2.5],
'ROE':[0.12,0.23,0.13]}, index=list('adg'))
pd.merge(df1,df2) # 可以使用 pd.merge() 函数
df1.merge(df2) # 也可以使用 DataFrame.merge() 方法

18 易错知识点分享

a split和切片:

#本次项目用了两个日期分段方法，一个是strftime
gather_customer_order['create_year_month'] = gather_customer_order['create_date'].apply(lambda x : x.strftime('%Y-%m'))
# 一个是split和切片
customer_birth_year = sales_customer_order_11['birth_date'].astype('str').str.split('-').str[0]

注意：用split和切片的方法获取日期时，birth_date列的数据类型为series,需要转为str，并且可以为series直接命名为birth_year,合并后直接成为列名。

b int 数据类型转换: pd.to_numeric

# pd.to_numeric
sales_customer_order_11['birth_year'] = pd.to_numeric(sales_customer_order_11['birth_year'], errors='coerce')

注意这里的pd.to_numeric方法必须加errors='coerce'，否则数列中有nan值会报错，加上说明忽视空值。

e 年龄分层: pd.cut()

listBins = [30,34,39,44,49,54,59,64] 
listLabels = ["30-34","35-39","40-44","45-49","50-54","55-59","60-64"]
sales_customer_order_11['age_level']=pd.cut(sales_customer_order_11['customer_age'], bins=listBins, labels=listLabels,
       include_lowest=True)
sales_customer_order_11.head()

注意：listBins 必须比listlabels 要多一个元素，否则会报错！

f 计算前十城市环比: pct_change

city_top_list = city_list
order_top_x = pd.Series([])
amount_top_x = pd.Series([])
for i in city_top_list:
    #print(i)
    a=gather_customer_order_city_10_11.loc[gather_customer_order_city_10_11['chinese_city']==i]['order_num'].pct_change()
    b=gather_customer_order_city_10_11.loc[gather_customer_order_city_10_11['chinese_city']==i]['sum_amount'].pct_change()
    order_top_x=order_top_x.append(a).fillna(0)
    amount_top_x =amount_top_x.append(b).fillna(0)

注意: 循环的pct_change()必须在a,b变量的末尾，如果在order_top_x.append(a)时再做pct_change(), 算出来的数据会出现连续，而不是一行空值一行结果，也就是pct_change排放位置的选择要注意，另外，要记得fillna(0)。

19 心得体会总结:

总体来看，做完DataFrame数据处理之后，其实可以发现python基础的数据类型，list, tuple, set, Series, ndarray，这几个总是在相互转化，层层相套。例如，我们可以建立一个空列表，然后通过遍历循环把数据用append追加，最后用pd.DataFrame可以转化为dataframe格式，相似，我们还可以建立空的 pd.Series([]),不过Series可以通过索引和dataframe合并。

其实，从表面看我们无法改变DataFrame中的元素，但是如果通过lambda函数，就可以对整个表格进行遍历，相同道理的还有，map(), apply(), mapapply() 这些函数通过组合的方式就可以达到我们的目的和要求，所以，数据分析在做的就是数据转化工作，不断的拆分、应用和合并，函数和方法就是一条链条，直接打通我们想要的结果。

Adventure Works Cycles公司月度销售项目知识点总结