01-pandas高频操作

pandas入门级高频操作

#读取数据
pd.read_csv(file.csv)
#取列
df['col1','col2']
#条件筛选
df[df['col1']=='xx']
#缺失值删除：删除name为空的行
df.dropna(subset = ['name'])
#去重
df.drop_duplicates(subset = ['col1'])
df.duplicated() #布尔值，行是否重复
df['col'].duplicated() #布尔值，列是否重复
#重命名
df.rename(columns = {'col1':'recol1','col2':'recol2'})
#修改列 salary加倍
df['salary'] *= 2
#排序
df_sorted = df.sort_values(by=['col1', 'col2', 'col3'], ascending=[True, False, True])
#分组统计
df.groupby('列名').agg(统计函数)
#修改字段类型 col1改成int类型
df['col1'] = df['col1'].astype('int')
#垂直连结（上下合并）垂直 concat，axis=0，ignore_index=True”
result = pd.concat([df1, df2, df3], axis=0, ignore_index=True)
#行转列 透视
df.pivot(index = x,columns = x,values = x)
eg:df_wide = df_long.pivot(
    index='姓名',      # 行索引
    columns='科目',    # 要展开成列的字段
    values='分数'      # 填入单元格的值
).reset_index()
#列转行 融合
pd.melt(df,id_vars = x,var_name = x,value_name = x)
eg:df_long = pd.melt(df, id_vars=['姓名'], var_name='科目', value_name='分数')
#整体转置 行直接变成列
df_transposed = df.T
#返回前n行
df.head(n)
#标签索引和位置索引
df.loc[df['A'] > 20, 'B']  # A>20 的行，取 B 列
df.iloc[0:3]   # 取前3行 注意：不包含位置 3！

pandas与MYSQL操作类比-基础

pandas与MYSQL操作类比-实战

#排序并赋予序号
mysql：row_number() over (partition by xx order by xx) as rn
pandas:df['col'] = range(1,len(df)+1) #从1开始
pandas:df['col'] = range(len(df)) #从0开始
#条件赋值
mysql:if condition then B1 else B2 end as col
pandas:df['col'] = np.where(condition,B1,B2)
#正则/like
mysql:col REGEXP'x[a-zA-Z0-9]*'/col like 'x%'
pandas:df['col'].str.startswith('x') #开头
       df['col'].str.endswith('x') #结尾
       df['col'].str.contains('x') #中间包含
       df['col'].match('x') 
#与、或、非
mysql:and or not
pandas:& | ~ #优先级高于四则运算，要添加好括号
#聚合函数（groupby）（max/min、count()、sum()、avg()）
mysql: SELECT col, MAX(val) FROM table GROUP BY col;
pandas: df.groupby('col')['val'].max()

mysql: SELECT col, MIN(val) FROM table GROUP BY col;
pandas: df.groupby('col')['val'].min()

mysql: SELECT col, COUNT(*) FROM table GROUP BY col;
pandas: df.groupby('col').size()

mysql: SELECT col, COUNT(val) FROM table GROUP BY col; （忽略 NULL）
pandas: df.groupby('col')['val'].count()

mysql: SELECT col, SUM(val) FROM table GROUP BY col;
pandas: df.groupby('col')['val'].sum()

mysql: SELECT col, AVG(val) FROM table GROUP BY col;
pandas: df.groupby('col')['val'].mean()
#窗口函数（max/min/count/sum/avg）over (partiton by order by)
SELECT 
    SUM(val) OVER (PARTITION BY col1 ORDER BY col2) AS running_sum,
    AVG(val) OVER (PARTITION BY col1 ORDER BY col2) AS running_avg,
    COUNT(*) OVER (PARTITION BY col1 ORDER BY col2) AS running_count,
    MIN(val) OVER (PARTITION BY col1 ORDER BY col2) AS running_min,
    MAX(val) OVER (PARTITION BY col1 ORDER BY col2) AS running_max
FROM table;
pandas:
# 无需排序，直接分组 transform
g = df.groupby('col1')['val']
df['sum_over']   = g.transform('sum')      # 分区内总和
df['min_over']   = g.transform('min')      # 分区内最小值
df['max_over']   = g.transform('max')      # 分区内最大值
df['count_over'] = g.transform('size')     # 分区内行数（含 NaN）
df['avg_over']   = g.transform('mean')     # 分区内平均值
#需要排序 !!!注意不能直接groupby().sort_values()
df = df.sort_values(['col1', 'col2'])
g = df.groupby('col1')['val']
df['running_sum']   = g.cumsum()               # 累积和
df['running_min']   = g.cummin()               # 累积最小（从开头到当前行的最小值）
df['running_max']   = g.cummax()               # 累积最大
df['running_count'] = g.cumcount() + 1         # 累积计数（从1开始）
df['running_avg']   = g.expanding().mean().reset_index(level=0, drop=True)  # 累积平均
#去重（distinct）
mysql：select distinct col1,col2 from table
pandas:
df['col1'].unique() #单列去重
df[['col1','col2']].drop_duplicates() #多列去重
#表连接（left join/right join/inner join）
mysql：table a left/right/inner join b on a.col = b.col
pandas:pd.merge(a, b, on='col', how='left'/'right'/'inner')
pd.merge(a, b, left_on='id', right_on='customer_id', how='left')#左右表列名不同时
#限制空值（col is null）
mysql:where col is null
pandas:df[df['col'].isnull()]/df[df['col'].isna()]