pandas入门与进阶（三）

0. 常用方法和技巧

# 将数据随机打散
from sklearn.utils import shuffle
df_shuffle = shuffle(df)

# 字符串方法str只能用于Series，只能在字符串列上使用，不能数字列上使用
# 替换掉温度的后缀℃
df.loc[:, "bWendu"] = df["bWendu"].str.replace("℃", "").astype('int32')

# 查看温度类型的计数
df["wendu_type"].value_counts()

work_dir="./course_datas/c15_excel_split_merge"
splits_dir=f"{work_dir}/splits"
# 判断路径下有无该文件夹
import os
if not os.path.exists(splits_dir):
    os.mkdir(splits_dir)

# 对列名重命名
df.rename(columns={"ymd":"月份"})

1. 组合条件对数据查询

# 1. 直接查询
df[
    (df["bWendu"]<=30) 
    & (df["yWendu"]>=15) 
    & (df["tianqi"]=='晴') 
    & (df["aqiLevel"]==1)]

# 2. 使用query()，@用于使用外部变量
df.query("bWendu<=30 & yWendu>=15 & tianqi=='晴'")
df.query("yWendu<=@high_temperature & yWendu>=@low_temperature")

# 3. 筛选出某列值是否在列表中
df = df[df['col_name'].isin([1, 2, 13, 18, 25])]

2. df数据存Mysql数据库

from sqlalchemy import create_engine
import pymysql

engine = create_engine("mysql+mysqlconnector://root:123456@127.0.0.1:3306/test", echo=False)
# 或者
ms_engine = create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(DATABASE['user'], DATABASE['password'], DATABASE['host'], DATABASE['port'], DATABASE['database']))
# 每次都是覆盖新建表
df.to_sql(name='table_name', con=engine, if_exists="replace", index=True, index_label='id')
# 追加新数据到表中
df_new.to_sql(name='table_name', con=engine, if_exists="append")

# 查询建表的SQL语句
print(engine.execute("show create table student").first()[1])

• 示例

def cteatTable(df_name, table_name):
    """ 数据库建表 """
    try:
        ms_engine = create_engine(
            'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(
                DATABASE['user'], DATABASE['password'], DATABASE['host'],
                DATABASE['port'], DATABASE['database']))

        df_name.to_sql(table_name, ms_engine, if_exists='replace', index=True, index_label='id')
    except Exception as e:
        raise Exception("数据库连接出现问题:" + str(e))
    finally:
        ms_engine.dispose()

3. Pandas和数据库查询语言SQL的对比

# 1. SELECT数据查询
sql = """
    SELECT PassengerId, Sex, Age, Survived
    FROM titanic
    LIMIT 5;
"""
df[["PassengerId", "Sex", "Age", "Survived"]].head(5)

# 2. WHERE按条件查询
sql = """
    SELECT *
    FROM titanic
    where Sex='male' and Age>=20.0 and Age<=40.0
    LIMIT 5;
"""
condition = (df["Sex"]=="male") & (df["Age"]>=20.0) & (df["Age"]<=40.0)
condition.value_counts()

# 3. in和not in的条件查询
sql = """
    SELECT *
    FROM titanic
    where Pclass in (1,2)
    LIMIT 5;
"""
df[df["Pclass"].isin((1,2))].head()
df[~df["Pclass"].isin((1,2))].head()

# 4. groupby分组统计
sql = """
    SELECT 
        sum(Survived),
        mean(Age),
        mean(Fare)
    FROM titanic
    group by Survived,Sex
"""
df.groupby(["Survived", "Sex"]).agg({"Survived":np.sum, "Age":np.mean, "Fare":np.mean})

# 5. JOIN数据关联
sql = """
    SELECT *
    FROM 
        rating join movies 
        on(rating.movieId=movies.movieId)
    limit 5
"""
df_merged = pd.merge(left=df_rating, right=df_movies, on="movieId").head(5)

# 6. UNION数据合并
sql = """
    SELECT city, rank
    FROM df1
    
    UNION ALL
   
    SELECT city, rank
    FROM df2;
"""
pd.concat([df1, df2])

# 7. Order Limit先排序后分页
sql = """
    SELECT *
    from titanic
    order by Fare
    limit 5
"""
df.sort_values("Fare", ascending=False).head(5)

4. 数据透视stack、unstack、pivot的语法

# 实现数据统计
df_group = df.groupby([df["pdate"].dt.month, "Rating"])["UserID"].agg(pv=np.size)
df_stack = df_group.unstack()
df_stack.plot()
# unstack和stack是互逆操作
df_group = df_stack.stack()
# pivot数据透视
df_reset = df_group.reset_index()
df_pivot = df_reset.pivot("pdate", "Rating", "pv")
df_pivot.plot()

5. 计算同比环比指标的3种方法

• 环比：表示本次统计段与相连的上次统计段之间的比较。
  • 比如2010年中国第一季度GDP为G2010Q1亿元，第二季度GDP为G2010Q2亿元，则第二季度GDP环比增长（G2010Q2-G2010Q1)/G2010Q1；
• 同比：即同期相比，表示某个特定统计段今年与去年之间的比较。
  • 比如2009年中国第一季度GDP为G2009Q1亿元，则2010年第一季度的GDP同比增长为（G2010Q1-G2009Q1)/G2009Q1。

1. pct_change 方法直接算好了"(新-旧)/旧"的百分比
2. shift 方法用于移动数据，但是保持索引不变
3. diff 方法用于新值减去旧值

# 新的df，为每个月的平均最高温
df = df[["bWendu"]].resample("M").mean()
# 将索引按照日期升序排列
df.sort_index(ascending=True, inplace=True)

# 方法1：pandas.Series.pct_change
df["bWendu_way1_huanbi"] = df["bWendu"].pct_change(periods=1)
df["bWendu_way1_tongbi"] = df["bWendu"].pct_change(periods=12)

# 方法2：pandas.Series.shift
# 环比
series_shift1 = df["bWendu"].shift(periods=1)
df["bWendu_way2_huanbi"] = (df["bWendu"]-series_shift1)/series_shift1
# 同比
series_shift2 = df["bWendu"].shift(periods=12)
df["bWendu_way2_tongbi"] = (df["bWendu"]-series_shift2)/series_shift2

# 方法3. pandas.Series.diff
# 环比
series_diff1 = df["bWendu"].diff(periods=1)
df["bWendu_way3_huanbi"] = series_diff1/(df["bWendu"]-series_diff1)
# 同比
series_diff2 = df["bWendu"].diff(periods=12)
df["bWendu_way3_tongbi"] = series_diff2/(df["bWendu"]-series_diff2)

6. 对一列列值按指定列表顺序重新输出DF

• 修改 category，再用 sort_values 排序
• 本质上是修改底层默认排序的顺序

# list1, list2 为指定的顺序列表
# 'columns1', 'columns2' 为具体某列的列名
df['columns1'] = df['columns1'].astype('category')
df['columns1'].cat.reorder_categories(list1, inplace=True)

df['columns2'] = df['columns2'].astype('category')
df['columns2'].cat.reorder_categories(list2, inplace=True)
# 可一次性按多列排序
df.sort_values(['columns1','columns2'],inplace = True)
df