安全审计之Pandas常用代码

0x00 前言

在审计中除了使用Excel和SQL来进行处理分析一些数据，也会经常用到Pandas来做一些分析，而使用Pandas做分析的定位主要分为两点：1、当数据量过大的时候，普通Excel已经很难便捷的进行审计分析；2、当审计场景已经流程化，则需要固话成自动化的时候。

以下是我收集整理的代码组合，代码非原创（我是代码搬运工）。

0x01 常用的代码组合

1. 数据去重：

data.drop_duplicates(subset=['A','B'],keep='first',inplace=True)

代码中subset对应的值是列名，表示只考虑这两列，将这两列对应值相同的行进行去重。
默认值为subset=None表示考虑所有列。

keep='first'表示保留第一次出现的重复行，是默认值。keep另外两个取值为"last"和False，
分别表示保留最后一次出现的重复行和去除所有重复行。

inplace=True表示直接在原来的DataFrame上删除重复项，而默认值False表示生成一个副本。

2. 透视表 - pivot_table：

pivot_table(data, values=None, index=None, columns=None,aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All')

pivot_table有四个最重要的参数index、values、columns、aggfunc

index：Excel透视表中‘行’，主键位。

values：Excel透视表中‘值’，统计位。

columns：Excel透视表中‘列’，没杂用过位。

aggfunc：设置函数位，默认为mean（计算均值），常用函数为count或者使用numpy的sum函数。

data1=pd.pivot_table(data,index=['主账号'],values=['源IP地址'],aggfunc='count')# 做数据透视表

data_account_num=pd.pivot_table(data,index=['XXX1','XXX2',"XXX3"],values=['XXX4'],aggfunc='count')# 做数据透视表
data_account_num.rename(columns={'XXX1':'XXX2'},inplace=True)
data_add=data_account_num.sort_values('XXX',ascending=False)  # 按某列进行排序

3. 筛选透视表中值大于1的数据，并排序：

data1[data1['值']>1].sort_values('值',ascending=True)

4. 按索引值进行合并两张表

pd.merge(data1,data2,right_on='主账号',left_index=True,how='right').sort_values('主账号',ascending=True)

pandas.merge(left, right, how: str = 'inner', on=None, left_on=None, right_on=None, left_index: bool = False, right_index: bool = False, sort: bool = False, suffixes=('_x', '_y'), copy: bool = True, indicator: bool = False, validate=None) → 'DataFrame'

left：   参与合并的左侧DataFranme
right：  参与合并的右侧DataFranme
how：    默认为inner，可设为inner/outer/left/right
on：     根据某个字段进行连接，必须存在于两个DateFrame中（若未同时存在，则需要分别使用left_on和right_on来设置）
left_on：      左连接，以DataFrame1中用作连接键的列
right_on：     右连接，以DataFrame2中用作连接键的列
left_index：   将DataFrame1行索引用作连接键
right_index：  将DataFrame2行索引用作连接键
sort：         根据连接键对合并后的数据进行排列，默认为True
suffixes：     对两个数据集中出现的重复列，新数据集中加上后缀_x,_y进行区别

4.1 内连接(Inner)

pd.merge(data1,data2, left_on='主账号',right_on='主账号').sort_values('主账号',ascending=True)

注：以最少数的表为索引表

4.2 右链接(Right)

pd.merge(data1,data2, right_on='主账号',left_on='主账号',how='right').sort_values('主账号',ascending=True)

4.3 左链接(Left)

pd.merge(data1,data2, right_on='主账号',left_on='主账号',how='left').sort_values('主账号',ascending=True)

4.4 全链接(Outer)

pd.merge(data1,data2, right_on='主账号',left_on='主账号',how='outer').sort_values('主账号',ascending=True)

5. 两个Excel表进行合并

pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,keys=None, levels=None, names=None, verify_integrity=False)

concat函数是在pandas底下的方法，可以将数据根据不同的轴作简单的融合

objs: series，dataframe或者是panel构成的序列lsit
axis： 需要合并链接的轴，0是行，1是列
join：连接的方式 inner，或者outer

data5 = pd.concat([data3,data4],axis=0)

6. 筛选匹配固定字符内容

import pandas as pd # 导包 
data=pd.read_csv('0201-0303-674286.csv') 
data=data[data['操作命令'].isnull().values == False] # 匹配的内容字段中不能用空值，该行代码解决
data1=data[data['主帐号ID'].str.contains('shiyan520')]

7. 导入相关日志文件

import pandas as pd # 导包 

data=pd.read_excel('XXX.xlsx') # 读取excel文件
data=pd.read_csv('XXX.csv') 
data=pd.read_csv(open('XXX.csv',encoding='utf-8')，sheet_name='XXX').iloc[:,:-1] # 读取csv文件

8. 导出相关审计结果

data1.to_excel('111.xlsx',index=0) # 输出excel文件 
data1.to_csv('222.csv',encoding='utf-8',index=0) # 输出csv文件，可指定文件编码

9. 筛选大于或小于某时间节点数据

data1=data[data['TIME']<'08:00:00:000']
data2=data[data['TIME']>'21:00:00:000']

10. 解决主账号ID或从账号ID中存在空格的情况

def judge(a):# 自定义向量化处理数据
    return a.rstrip()
data['主帐号']=data.apply(lambda data:judge(data['主帐号ID']),axis=1)
def judge(a):# 自定义向量化处理数据
    return a.rstrip()
data['从帐号ID']=data.apply(lambda data:judge(data['从帐号']),axis=1)

注：
'xxxxxx'.strip(to_strip=None)去除两边空白
'xxxxxx'.lstrip(to_strip=None)去除左边空白
'xxxxxx'.rstrip(to_strip=None)去除右边空白

11. 删除某条或某规则下数据

del data['从帐号']
del data ['主帐号ID']

12. 单字段进行去重

data1=data['主帐号ID']
data2=data1[-data1.duplicated()]

13. 重复数据与现有数据统计

data_chongfu=data1[data1.duplicated()].count()[0]
print('重复数据有%s行'%data_chongfu) # 查看表重复数量
data2=data1[-data1.duplicated()] # 剔除重复数据
print('现有数据有%s行'%data2.shape[0])

14. 删除某行或某列或某条件下数据

drop方法的用法：drop(labels, axis=0, level=None, inplace=False, errors='raise')
--  axis为0时表示删除行，axis为1时表示删除列
--  labels接受String或Array。代表删除的行或列的标签。无默认
-- levels接受int或者索引名。代表标签所在级别。默认为None。
-- inplace接受boolean。代表操作是否对原数据生效。默认为False。

>>>import pandas as pd
>>>import numpy as np
>>>df = pd.DataFrame(np.arange(12).reshape(3,4), columns=['A', 'B', 'C', 'D'])
>>>df
   A   B   C   D
0  0   1   2   3
1  4   5   6   7
2  8   9  10  11
#Drop columns,两种方法等价
>>>df.drop(['B', 'C'], axis=1)
   A   D
0  0   3
1  4   7
2  8  11
>>>df.drop(columns=['B', 'C'])
   A   D
0  0   3
1  4   7
2  8  11
# 第一种方法下删除column一定要指定axis=1,否则会报错
>>> df.drop(['B', 'C'])
ValueError: labels ['B' 'C'] not contained in axis
#Drop rows
>>>df.drop([0, 1])

   A  B   C   D
2  8  9  10  11
>>> df.drop(index=[0, 1])
   A  B   C   D
2  8  9  10  11

-------------------------------------------------------

import pandas as pd

df1 = pd.DataFrame([['Snow','M',22],['Tyrion','M',32],['Sansa','F',18],['Arya','F',14]], columns=['name','gender','age'])
print(df1)

print('---------删除行或列:DataFrame.drop()--------')
# drop默认对原表不生效，如果要对原表生效，需要加参数：inplace=True

print("----删除单行----")
df2=df1.drop(labels=0)   # axis默认等于0，即按行删除，这里表示按行删除第0行
print(df2)

print("------删除多行------")
# 通过labels来控制删除行或列的个数，如果是删多行/多列，需写成labels=[1,3]，不能写成labels=[1:2],用:号会报错
# 删除指定的某几行（非连续的）
df21=df1.drop(labels=[1,3],axis=0)   # axis=0 表示按行删除，删除第1行和第3行
print(df21)

# 要删除连续的多行可以用range(),删除连续的多列不能用此方法
df22=df1.drop(labels=range(1,4),axis=0)   # axis=0 表示按行删除，删除索引值是第1行至第3行的正行数据
print(df22)

print("----删除单列----")
df3=df1.drop(labels='gender',axis=1)  # axis=1 表示按列删除，删除gender列
print(df3)

print("----删除多列----")
# 删除指定的某几列
df4=df1.drop(labels=['gender',"age"],axis=1)  # axis=1 表示按列删除，删除gender、age列
print(df4)

15. 导入某文件夹中同类型数据文件

import glob
list=glob.glob('*')
for i in list:print('【'+i+'】')

# 查看文件夹内，同类csv的所有字段

import pandas as pd # 导包 
import glob
list=glob.glob('*.csv')
file=glob.glob('*.csv')[0]

for i in list:
    print('【'+i+'】')
    data=pd.read_csv(i)
    print(data.columns.tolist())

16. 处理操作日志中重复数据

file='xxxxxxx.csv'
data1=pd.read_csv(file)
print('原始数据有%s行'%data1.shape[0])
data_chongfu=data1[data1.duplicated()].count()[0]
print('重复数据有%s行'%data_chongfu) # 查看表重复数量
data2=data1[-data1.duplicated()] # 剔除重复数据
print('现有数据有%s行'%data2.shape[0])
data_sum=data_chongfu+data2.shape[0]
# print('重复数据+现有数据：%s+%s=%s'%(data_chongfu,data2.shape[0],data_sum))
print('原始数据有%s行，重复数据+现有数据%s行'%(data1.shape[0],data_sum))
file_new=file[:18]+'共%s条_原有%s条_已去重%s条.csv'%(data2.shape[0],data1.shape[0],data_chongfu)
data2.to_csv(file_new,index=0)			

17. 合并字段

data['XXX+XXX']=data['XXX']+data['XXX'] # 合并字段

18. 重命名字段

data.rename(columns={'XXX':'XXX'},inplace=True) # 进行重命名

19. 对日志中某字段数进行排序并筛选大于某值的数据

data.sort_values(by='XXX',ascending=False)
data=data[data['XXX']>3].sort_values('XXX',ascending=True) # 将账号大于3的筛选并正排序

20. 部分取值相关代码

data.columns.tolist() # 取所有列名为一个列表

data.max()-data.min() # 取每列的最大值和最小值的差值，里边注意不能有值是字符串型的

data1=data.iloc[:,1:] # 取除第一列外的所有数据

21. Pandas链接数据库

# 导入必要模块
import pandas as pd
from sqlalchemy import create_engine

# 初始化数据库连接，使用pymysql模块
# MySQL的用户：root, 密码:root, 端口：3306,数据库：mydba
engine = create_engine('mysql+pymysql://root@localhost:3306/operation')

# 查询语句，选出employee表中的所有数据
sql = '''
      select * from work_operation;
      '''

# read_sql_query的两个参数: sql语句， 数据库连接
df = pd.read_sql_query(sql, engine)

# 输出employee表的查询结果
# print(df)

# # 新建pandas中的DataFrame, 只有id,num两列
# df = pd.DataFrame({'id':[1,2,3,4],'num':[12,34,56,89]})

# # 将新建的DataFrame储存为MySQL中的数据表，不储存index列
# df.to_sql('mydf', engine, index= False)

print('Read from and write to Mysql table successfully!')
         
Series中的str处理

# Series.str 常用的属性和方法有rstrip，.contains，split，upper，count，replace，extract等

addr = pd.Series([
'Washington, D.C. 20003',
'Brooklyn, NY 11211-1755',
'Omaha, NE 68154',
'Pittsburgh, PA 15211']) # 数据例子

addr.str.upper() # 将所有的字符串变为大写
addr.str.count(r'\d') # 计算出数字有多少个

regex = (r'(?P<city>[A-Za-z ]+), ' # 一个或更多字母
r'(?P<state>[A-Z]{2}) '        # 两个大写字母
r'(?P<zip>\d{5}(?:-\d{4})?)')  # 可选的4个延伸数字
addr.str.replace('.', '').str.extract(regex) # 将.替换为空，并用正则提出来有效信息，变为dataframe

22. 统计处理时长

import datetime

#程序开始时：
sum_begin = datetime.datetime.now()

#程序结束时：
sum_end = datetime.datetime.now()
sum_run_time = str((sum_end-sum_begin).seconds)
print("用时:" + sum_run_time +"秒")

23. 读取第一行做表头，指定添加字段名称

import pandas as pd

pd.read_csv(file, header=None, names = ['a','b','c'] )

#python读取csv数据（添加列名，指定分隔方式）
#现有CSV/EXCEL文件一个，为简化期间，为一个3x3的数据文件，内容如下：
#1,2,3
#2,1,3
#3,2,1
#用pandas.read读取以后，第一行自动被识别为columns，造成数据出错
#   1 2 3
#0 2 1 3
#1 3 2 1

#有没有什么命令可以添加自定义的columns的名字，比如我想命名为 A, B, C三列，该怎么操作呢？
#pd.read_csv(file, header=None, names = [‘a‘,‘b‘,‘c‘] )

24. 读取excel的所有sheet名

df = pd.read_excel("xxx.xlsx", None)
df.keys()

xl = pd.ExcelFile('xxx.xlsx')
xl.sheet_names  # see all sheet names

25. 读取多个sheet合并到一个文件夹

import pandas as pd
import glob

list1=glob.glob('*.xlsx')

writer=pd.ExcelWriter('111.xlsx')

for i in list1:
    keshi=i.split('--')[-1].split('.')[0]
    a=pd.read_excel(i)
    data=a[a['科室'].str.contains(keshi)]
    data.to_excel(writer,keshi,index=0)
writer.save()

data=pd.ExcelFile('111.xlsx')

sheet_names=data.sheet_names

data=pd.read_excel('111.xlsx',sheet_names[0])

for sheet in sheet_names[1:]:
    data1=pd.read_excel('111.xlsx',sheet)
    data=pd.concat([data,data1])
    
data.to_excel('333.xlsx',index=0)

26. 将目录下所有excel的所有sheet读取并合到一张表

import pandas as pd
import os

# 将excel中的sheet名称放入列表
sheet_names = sheet_list
# 将excel文件名称放入列表
xlsx_names = [x for x in os.listdir() if x.endswith(".xlsx")]

writer = pd.ExcelWriter('result.xlsx',engine='openpyxl')

num = 1
for sheet_name in sheet_names:
    df = None
    for xlsx_name in xlsx_names:
        _df = pd.read_excel('119.xlsx', sheet_name=sheet_name)
        if df is None:
            df = _df
        else:
            df = pd.concat([df, _df], ignore_index=True)
    # 下面的保存文件处填写writer，结果会不断地新增sheet，避免循环时被覆盖
    df.to_excel(excel_writer=writer, sheet_name=sheet_name, encoding="utf-8", index=False)
    print(sheet_name + "  保存成功！共%d个，第%d个。" % (len(sheet_names),num))
    num += 1

writer.save()
writer.close()

0x02 其它功能性代码组合

1. 对某列的内容分列

import pandas as pd
employees = pd.read_excel("../Employees.xlsx",index_col="ID")
employees .head()

Employees.xlsx 文件样例：

ID     Full Name
1     Syed Abbas
2     Catherine Abel
3     Kim Abercrombie

1.1 对 Full Name 进行切割分列

# df = employees["Full Name"].str.split()  # str.split()不写参数，会把分隔的内容放在一个列表里
# df

df = employees["Full Name"].str.split(expand=True)  # expand=True  可以把用分割的内容直接分列
employees["FirstName"]=df[0]
employees["LastName"]=df[1]
print(employees)

1.2 把分隔后的FistName 都变成大写

employees["FirstName"]=employees["FirstName"].str.upper()
print(employees)

1.3 把分隔的LastName都变成小写 

employees["LastName"]=employees["LastName"].str.lower()
print(employees)

2. 设置单元格条件格式1—指定值字体变色、指定值设置背景色

import pandas as pd

AuditData = pd.read_excel(../单点登录记录.xlsx)

2.1 用def函数对数值低于60的标记红色字体
def low_score_red(s): # s 表示每个数值
    color = 'red' if s<60 else 'black'
    return f"color:{color}"

AuditData.style.applymap(low_score_red,subset=['test1','test2','test3'])

2.2 用def函数对每个字段中最高数值标记背景色为绿色
def highest_score_green(col):
    return ['background-color:lime' if s == col.max() else 'background-color:white' for s in col]

AuditData.style.applymap(highest_score_green,subset=['test1','test2','test3'])

2.3 同时引用两个函数
# .applymap：对subset所有列的值一起操作
# .apply：对subset里的每一列的值操作

AuditData.style.applymap(low_score_red,subset=['test1','test2','test3'])\
.apply(highest_score_green,subset=['test1','test2','test3'])

3. 设置单元格条件格式2—迷你条形图、用颜色深浅表示值大小

import pandas as pd
AuditData = pd.read_excel(../单点登录记录.xlsx,index_col="ID")

3.1 对每一列的值设置迷你条形图来表示数值的大小
# .style.bar（颜色，作用的数值区）

AuditData.style.bar(color="skyblue",subset=['test1','test2','test3'])

3.2 用颜色深浅来表示数值的大小，值越大颜色越深
# 需要引入seaborn的调色板：light_palette
import seaborn as sns

# sns.light_palette:调色板，里面的参数分别是要填充的颜色
color_map = sns.light_palette('green',as_cmap=True)

# .background_gradient() 表示填充subset参数里所有值的背景色（按列去识别大小填充）
AuditData.style.background_gradient(color_map,subset=['test1','test2','test3'])

4. 更改DataFrame的某些值

1、更改DataFrame中的数据，原理是将这部分数据提取出来，重新赋值为新的数据。
2、需要注意的是，数据更改直接针对DataFrame原数据更改，操作无法撤销，如果做出更改，需要对更改条件做确认或对数据进行备份。

import pandas as pd
df1 = pd.DataFrame([['Snow','M',22],['Tyrion','M',32],['Sansa','F',18],['Arya','F',14]], columns=['name','gender','age'])

print("--------更换单个值----------")
# loc和iloc 可以更换单行、单列、多行、多列的值
df1.loc[0,'age']=25      # 思路：先用loc找到要更改的值，再用赋值（=）的方法实现更换值
df1.iloc[0,2]=25         # iloc：用索引位置来查找

# at 、iat只能更换单个值
df1.at[0,'age']=25      # iat 用来取某个单值,参数只能用数字索引
df1.iat[0,2]=25         # at 用来取某个单值,参数只能用index和columns索引名称
print(df1)

5. 插入新增列、行

import pandas as pd

df1 = pd.DataFrame([['Snow','M',22],['Tyrion','M',32],['Sansa','F',18],['Arya','F',14]], columns=['name','gender','age'])

print("----------在最后新增一列---------------")
print("-------案例1----------")
# 在数据框最后加上score一列，元素值分别为：80，98，67，90
df1['score']=[80,98,67,90]   # 增加列的元素个数要跟原数据列的个数一样
print(df1)

print("-------案例2----------")
print("---------在指定位置新增列:用insert（）--------")
# 在gender后面加一列城市
# 在具体某个位置插入一列可以用insert的方法
# 语法格式：列表.insert(index, obj)
# index --->对象 obj 需要插入的索引位置。
# obj ---> 要插入列表中的对象（列名）

col_name=df1.columns.tolist()                   # 将数据框的列名全部提取出来存放在列表里
print(col_name)

col_name.insert(2,'city')                      # 在列索引为2的位置插入一列,列名为:city，刚插入时不会有值，整列都是NaN
df1=df1.reindex(columns=col_name)              # DataFrame.reindex() 对原行/列索引重新构建索引值

df1['city']=['北京','山西','湖北','澳门']   # 给city列赋值
print(df1)

print("----------新增行---------------")
# 重要！！先创建一个DataFrame，用来增加进数据框的最后一行
new=pd.DataFrame({'name':'lisa',
                  'gender':'F',
                  'city':'北京',
                  'age':19,
                  'score':100},
                 index=[1])   # 自定义索引为：1 ，这里也可以不设置index
print(new)

print("-------在原数据框df1最后一行新增一行，用append方法------------")
df1=df1.append(new,ignore_index=True)   # ignore_index=True,表示不按原来的索引，从0开始自动递增
print(df1)

6. Series、DataFrame数据的loc、iloc、ix 查询 / 读取

一、DataFrame的数据查询 / 提取

1、对单列、多列进行访问读取

     -- 对单列数据的访问：DataFrame的单列数据为一个Series。根据DataFrame的定义可以知晓DataFrame 是一个带有标签的二维数组，每个标签相当每一列的列名；如：df.a df['a']

     --  对多列数据访问：访问DataFrame多列数据可以将多个列索引名称视为一个列表，df[['a','b']]

 2、对多行进行访问读取

 （1）如果只是需要访问DataFrame某几行数据的实现方式则采用数组的选取方式，使用“:”。

 （2）head和tail也可以得到多行数据，但是用这两种方法得到的数据都是从开始或者末尾获取的连续数据；默认参数为访问5行，只要在方法后方的“()”中填入访问行数即可实现目标行数的查看。

3、查看访问DataFrame中的数据——loc,iloc方法介绍

（1）loc方法是针对DataFrame索引名称的切片方法，如果传入的不是索引名称，那么切片操作将无法执行；利用loc方法，能够实现所有单层索引切片操作。

         loc方法使用方法：DataFrame.loc[ 行索引名称或条件 , 列索引名称 ]   # 闭区间（含最后一个值）

（2）iloc和loc区别：是iloc接收的必须是行索引和列索引的位置。

         iloc方法的使用方法：DataFrame.iloc[ 行索引位置 ,  列索引位置 ]   # 开区间（不含最后一个值）

（3）注意点：

         -- 使用loc方法和iloc实现多列切片，其原理的通俗解释就是将多列的列名或者位置作为一个列表或者数据传入。

         --  使用loc，iloc方法可以取出DataFrame中的任意数据。

         --  loc内部还可以传入表达式，结果会返回满足表达式的所有值。

         - - loc更加灵活多变，代码的可读性更高，iloc的代码简洁，但可读性不高。具体在数据分析工作中使用哪一种方法，根据情况而定，大多数时候建议使用loc方法。

         -- 在loc使用的时候内部传入的行索引名称如果为一个区间，则前后均为闭区间；iloc方法使用时内部传入的行索引位置或列索引位置为区间时，则为前闭后开区间。

4、查看访问DataFrame中的数据——切片方法之ix

（1）ix方法更像是loc和iloc两种切片方法的融合。ix方法在使用时既可以接收索引名称也可以接收索引位置。

（2）其使用方法：DataFrame.ix[ 行索引的名称或位置或者条件, 列索引名称或位置 ]

（3）控制ix方法需要注意以下几点：

        Ø 使用ix方法时，当索引名称和位置存在部分重叠时，ix默认优先识别名称。
        Ø 尽量使用列索引名称，而非列索引位置，主要用来保证代码可读性。
        Ø 使用列索引位置时，需要注解，同样保证代码可读性。
        Ø 除此之外ix方法还有一个缺点，就是在面对数据量巨大的任务的时候，其效率会低于loc和iloc方法，所以在日常的数据分析工作中建议使用loc和iloc方法来执行切片操作。

6.1 代码部分：

import pandas as pd

print("-------创建一维Series数据------------")
# 创建方式1：
# s1=pd.Series([90,86,70],index=['leo','kate','john'])
# print(s1)
# 创建方式2：
dict={'leo':90,'kate':86,'john':70}
s1=pd.Series(dict)
print(s1)

print("-----绝对位置查找----")
print(s1[0])

print("-----标签查找-----")
print(s1['kate'])

print("-----列表标签查找----")
print(s1[['john','kate']])

print("----条件表达式查找----")
print(s1[s1>80])

-------------------------------------------------------------------

import pandas as pd

print("=========创建一组DataFrame数据---使用date_range创建时间索引=======")

date=pd.date_range('20100101',periods=6)

df=pd.DataFrame(np.random.randn(6,4),
                index=date,
                columns=list('abcd'))

print(df.head())
print(df.index)    # 打印数据框的索引
print(df.values)   # 打印数据框内的元素值

print("---------读取单列、多列-------")
print(df.a)
print(df['a'])
print(df[['a','b']])     # 读取多列

print("------读取多行-----------")
print(df[0:4])
print(df.head(3))   # 打印前3行数据

print("------读取多行、多列-----------")
print(df.loc['2010-01-01':'2010-01-04',['a','b']])   # loc是闭区间，尾部包含
print(df.iloc[:4,[0,1]])                               # iloc是开区间，尾部不包含
print(df.ix[:4,['a','b']])                           # ix 是开区间，尾部不包含
print(df.loc[df.index<'20100105',['a','b']])      # loc 第一个参数可以用条件提取

-------------------------------------------------------------------

import pandas as pd
# 创建DataFrame
df = pd.DataFrame([['Snow','M',22],['Tyrion','M',32],['Sansa','F',18],['Arya','F',14]], 
                  columns=['name','gender','age'])
print(df)
print("----------提取多行、多列-----------")
print(df[['gender','age']])
print(df.loc[1:3,['gender','age']])   # loc是闭区间，尾部包含
print(df.iloc[1:3,[1,2]])              # iloc是开区间，尾部不包含
print(df.iloc[:,1:3])
# 读取第1行到第2行的数据
print(df[1:3])
print(df.iloc[1:3])

#读取第1行和第3行，第0列到第2列,不包括第二列
print(df.iloc[[1,3],0:2])

#读取倒数第3行到倒数第1行的数据，iloc不包含最后一行
print(df[-3:-1])
print(df.iloc[-3:-1])

0x03 基础代码含义大全 ①

1. 创建空DataFrame

df=pd.DataFrame()

2. 判断DataFrame是否为空

df.empty

3. DataFrame去重

df.drop_duplicates()

4. 替换DataFrame中指定字符串

df.replace({"2003-05-10": "2008-08-08"}, regex=True, inplace=True)

5. 填充DataFrame中Nan

import pandas as pd
data=[[101,90],[102,99],[103,]]
df=pd.DataFrame(data=data,columns=['id','score'])
df.fillna("未知", inplace=True)

6. 重塑DataFrame

import pandas as pd
data=[['101',90,'2003-05-10'],['102',99,'2003-05-12'],['103',105,'2003-05-11']]
df=pd.DataFrame(data=data,columns=['id','score','dt'])

#重塑 以dt作为index,id的值作为列名，去填充对应score的值
print df.pivot(index='dt', columns='id', values='score').reset_index()

7. DataFrame每列数据类型

df.dtypes

8. DataFrame行数列数

df.shape

9. DataFrame列名

df.columns.values

10. 修改列名

df.rename(columns={'id':'studentID', 'score':'studentScore'}, inplace=True)

11. 删除列

df.drop(['id'], axis=1)

12. 增加列

df['id2']= pd.Series(range(df.shape[0]))

13. 某列转换成int类型

df['id'] = df['id'].astype('int')
或
df['id']=df['id'].map(lambda x:int(x))

14. 选出某列转List

df["id"].values.tolist()

15. 对每列求和

df.loc['合  计'] = df.apply(lambda x: x.sum())
注意：需要注意map()与apply()的区别，map中的函数作用于Series中的每一个元素，而apply中的函数作用于DataFrame一列或一行。

16. 按多列排序

df.sort_values(['colNameA','colNameB'], ascending=False)

17. 某列最小值

int(df[['score']].min())

18. Union ALL

pd.concat([df1,df2])

19. Left/Right/Inner/Outer Join

import pandas as pd
data=[['101',90,'2003-05-10'],['102',99,'2003-05-10'],['103',105,'2003-05-11']]
df=pd.DataFrame(data=data,columns=['id','score','dt'])

data2=[('101','1'),('102','0')]
df2=pd.DataFrame(data=data2,columns=['id','sex'])

#left join
left_join_df=df.merge(right=df2, how="left", on="id")

#right join
# right_join_df=df.merge(right=df2, how="right", on="id")
# 或
right_join_df=df.merge(right=df2, how="right", left_on="id",right_on="id")

#inner join
inner_join_df=df.merge(right=df2, how="inner", left_on="id",right_on="id")

#outer join
outer_join_df=df.merge(right=df2, how="outer", on="id")

总结：left join类似于SQL的left join。如A left join B，产生A的完整集，B中没有匹配上的以nan代替。同理 right join、inner join、outer join。
pandas中DataFrame中merge，默认是inner join。

20. 筛选数据

#这些操作类似于Sql中的where条件。
import pandas as pd
data=[['101',90,'2003-05-10'],['102',99,'2003-05-10'],['103',105,]]
df=pd.DataFrame(data=data,columns=['id','score','dt'])

#且
print df[(df['id'].isin(['101','102']) & (df['score']==90))]
#取反
print df[~df['id'].isin(['101','102'])]
#不等式
print df[df['score']>=99]
#过滤掉非空
print df[df['dt'].notnull()]

21. 分组排序聚合

import pandas as pd
data=[
    (1,80,90,1,2),
    (2,60,80,1,2),
    (3,70,90,1,3),
    (4,90,80,1,3),
    (5,60,90,1,3),
    (6,50,80,2,3),
    (7,80,90,2,3),
    (8,70,70,2,1),
    (9,90,90,2,1),
    (10,80,90,2,1)
]
df=pd.DataFrame(data=data,columns=['id','language','math','grade','class'])

#分组求和---按grade,class分组，对language,math分别求和
print df.groupby(['grade','class'])['language','math'].sum().reset_index()

#分组排序求TopN----按grade,class分组,取每组math最大的topN

print pd.concat([subGroup.sort_values(['math'],ascending=False).head(2)
  for subGroupName,subGroup in df.groupby(['grade','class'])])

22. List-Tuple转DataFrame

data=[(101,90),(102,99),(103,99)]
df=pd.DataFrame(data=data,columns=['id','score'])

23. List-List转DataFrame

data=[[101,90],[102,99],[103,99]]
df=pd.DataFrame(data=data,columns=['id','score'])

24. List-Dict转DataFrame

data=[{'id':101,'score':90},{'id':102,'score':99},{'id':103,'score':99}]
df=pd.DataFrame(data=data)

25. excel转DataFrame

df=pd.read_excel(io="excelPath.xlsx", sheetname="sheetName")

26. Csv文件转DataFrame

#注：读取Tsv文件,将分割符换成\t即可。
df=pd.read_csv(filePath,sep=',',names=['colNameA','colNameB'...])

27. Json文件转DataFrame

df=pd.read_json(filePath, lines=True)

28. DataFrame保存成Csv文件

df.to_csv("csvResult.csv",index=False,header=True,sep=',',encoding='utf-8-sig')

29. DataFrame保存成Json文件

df.to_json(path_or_buf="jsonData.json",orient='records', lines=True)

30. Sql执行结果转DataFrame

df=pd.read_sql(sql=sql,con=conn)

0x04 基础代码含义大全 ②

1. 引用库和pandas结构缩写

DataFrame 别名 df
Series    别名 s

import pandas as pd
import numpy as np

2. 数据导入

pd.read_csv(filename)      # 导入csv格式文件中的数据
pd.read_table(filename)    # 导入有分隔符的文本 (如TSV) 中的数据
pd.read_excel(filename)    # 导入Excel格式文件中的数据
pd.read_sql(query, connection_object)      # 导入SQL数据表/数据库中的数据
pd.read_json(json_string)  # 导入JSON格式的字符，URL地址或者文件中的数据
pd.read_html(url)          # 导入经过解析的URL地址中包含的数据框 (DataFrame) 数据
pd.read_clipboard()        # 导入系统粘贴板里面的数据
pd.DataFrame(dict)         # 导入Python字典 (dict) 里面的数据，其中key是数据框的表头，value是数据框的内容。

3. 数据导出

df.to_csv(filename)       # 将数据框 (DataFrame)中的数据导入csv格式的文件中
df.to_excel(filename)     # 将数据框 (DataFrame)中的数据导入Excel格式的文件中
df.to_sql(table_name,connection_object)      # 将数据框 (DataFrame)中的数据导入SQL数据表/数据库中
df.to_json(filename)      # 将数据框 (DataFrame)中的数据导入JSON格式的文件中

4. 数据查看与验证

df.head(n)      # 查看前n行数据
df.tail(n)      # 查看后n行数据
df.shape        # 查看数据的行数与列数
df.info()       # 查看数据 (DataFrame) 的索引、数据类型及内存信息
df.describe()   # 对于数据类型为数值型的列，查询其描述性统计的内容
df.apply(pd.Series.value_counts)      # 查询数据 (Data Frame) 中每个列的不同数据值出现次数统计

5. 数据的选取

df[[col1, col2]]      # 以新的数据(DataFrame)的形式返回选取的列
s.iloc[0]             # 按照位置选取
s.loc['index_one']    # 按照索引选取
df.iloc[0,:]          # 选取第一行
df.iloc[0,0]          # 选取第一行的第一个元素

6. 数据清洗

df.columns = ['a','b']      # 重命名数据框的列名称
pd.isnull(df)               # 检查数据中空值出现的情况，并返回一个由布尔值(True,Fale)组成的列
pd.notnull(df)              # 检查数据中非空值出现的情况，并返回一个由布尔值(True,False)组成的列
df.dropna()                 # 移除数据框 DataFrame 中包含空值的行
df.dropna(axis=1)           # 移除数据框 DataFrame 中包含空值的列
df.fillna(x)                # 将数据框 DataFrame 中的所有空值替换为 x
s.fillna(s.mean())          # 将所有空值替换为平均值
s.astype(float)             # 将数组(Series)的格式转化为浮点数
s.replace(1,'one')          # 将数组(Series)中的所有1替换为'one'
s.replace([1,3],['one','three'])               # 将数组(Series)中所有的1替换为'one', 所有的3替换为'three'
df.rename(columns=lambda x: x + 2)             # 将全体列重命名
df.rename(columns={'old_name': 'new_ name'})   # 将选择的列重命名
df.set_index('column_one')                     # 改变索引
df.rename(index = lambda x: x+ 1)              # 改变全体索引

7. 数据过滤、排序、分组

# 过滤
df[df[col] > 1]                      # 选取数据框df中对应行的数值大于1的全部列
df[(df[col] > 1) & (df[col] < 2)]    # 选取数据框df中对应行的数值大于1，并且小于2的全部列

# 排序
df.sort_values(col1)                 # 按照数据框的列col1升序(ascending)的方式对数据框df做排序
df.sort_values(col2,ascending=False) # 按照数据框的列col2降序(descending)的方式对数据框df做排序
df.sort_values([col1,col2],ascending=[True,False])     # 按照数据框的列col1升序，col2降序的方式对数据框df做排序

# 分组
df.groupby(col)                      # 按照某列对数据框df做分组
df.groupby([col1,col2])              # 按照列col1和col2对数据框df做分组
df.groupby(col1)[col2].mean()        # 按照列col1对数据框df做分组处理后，返回对应的col2的平均值
df.apply(np.mean)                    # 对数据框df的每一列求平均值
df.apply(np.max,axis=1)              # 对数据框df的每一行求最大值

8. 数据的连接、组合

# 组合
df1.append(df2)                      # 在数据框df1的末尾添加数据框df2，其中df1和df2的列数应该相等
pd.concat([df1, df2],axis=1)         # 在数据框df1的列最后添加数据框df2,其中df1和df2的行数应该相等

# 连接
df1.join(df2,on=col1,how='inner',,lsuffix='_l',rsuffix='_r') # 对数据框df1和df2做内连接，其中连接的列为col1,为左右连接数据列添加后缀

9. 数据统计

df.mean()          # df中每一列的平均值
df.describe()      # df每一列的描述性统计
df.corr()          # df中每一列与其他列的相关系数
df.count()         # df中每一列的非空值个数
df.max()           # df中每一列的最大值
df.min()           # df中每一列的最小值
df.median()        # df中每一列的中位数
df.std()           # df中每一列的标准差

0x05 结尾语

以上是收集整理的，经常会用到的代码公式，还有可能会用到的一些代码函数。