pandas文件 IO
读取excel
pd.read_excel
read_excel方法的常用参数详解
read_excel(
io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=False, dtype=None, engine=None,
converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None,
keep_default_na=True, verbose=False, parse_dates=False, date_parser=None, thousands=None,
comment=None, skip_footer=0, skipfooter=0, convert_float=True, mangle_dupe_cols=True, **kwds
)
pandas.read_excel（io，sheet_name = 0，header = 0，names = None，index_col = None，usecols = None，squeeze = False,dtype = None, ...）
dtype = {'a': np.float64, 'b': np.int32, 'Name': str, 'Value': float}
io
值为str类型，文件路径，必须传
sheet_name
值为str或int类型
int类型时，默认值0，表示Sheet1表，依次分别向后表示，不存在该sheet表时抛出IndexError错误: list index out of range
str类型时，必须输入sheet表的全名，不存在该sheet表时抛出xlrd.biffh.XLRDError错误: No sheet named <'x'>
header
值为int类型或list类型
默认值0，表示从第一行开始读，输入值大于列的长度时会抛出ValueError错误: Passed header=7 but only 6 lines in file
list内的值也必须在列的长度范围内，如[2, 7]，超出会抛出IndexError错误: list index out of range
header=[1, 3]中的1表示按行索引读取第几行，3表示按行索引从第几行开始往后读取。
当输入的最后一个值为最后一行时，如：header=[2, 4]，显示方式为：
Empty DataFrame
Columns: [(2, 4), (bob, peter), (19, 18), (man, man)]
Index: []
当一列中含有空行时，header用list时不能包含空行
names
值为list或者str，默认None，一般使用list传参
指定表头的名称，list内的值跟表的列数要一一对应，即不能多也不能少，多或者少都会抛出ValueError错误: Number of passed names did not match number of header fields in the file
使用str类型传参时会循环出每一个字符在赋值给每一个表头，即不能多也不能少，多或者少都会抛出ValueError错误。
举栗子：
pd.read_excel(f_path, sheet_name=0, header=1, names='asdf')
a s d f
0 2 bob 19 man
1 3 alice 21 woman
2 4 peter 18 man
字符重复时，会自动在后面追加数字，从1开始，如：names='aaaa'
a a.1 a.2 a.3
0 2 bob 19 man
1 3 alice 21 woman
2 4 peter 18 man
index_col
值为int或str类型，默认None，指定第几列做为新的索引列
int时，按索引传参，超出列的长度后会抛出IndexError错误: list index out of range
str时，按第一行的列名传入，不存在该列名时抛出ValueError错误: Index age invalid
usecols
值为None、int、str、list类型，指定读取表格的指定列
None时，读取整个excel列
int时，读取从第一列开始到该数值（包含）结束的中间所有列
str时，只能按照excel的格式指定列，如"A: G"，读取A列到G列的所有列, 本公式中用（"A: G"）而非 ["A: G"]
list时，list的元素只能是int型，如：[1, 4],表示读取list内指定的第一和第四列
skiprows
值为int类型，默认None，跳过指定行(包括)之前的行开始读取
传入的值从1开始的任意int类型，超出行的长度不报错，显示为空，如下：
Empty DataFrame
Columns: []
Index: []
只剩最后一条数据时，显示如下：
Empty DataFrame
Columns: [4, peter, 18, man]
Index: []
nrows
值为int类型，默认None，只取前n行数据，按索引传参
传入值为0时，只取第一行，显示如下：
Empty DataFrame
Columns: [1, jack, 22, man]
Index: []
传入其余值时，只显示该值(包括)之前的行，超出行的长度后不报错，有多少行显示多少行
converters
值为dict类型，默认None，将指定的数据列转换为int、float、str等数据类型
num name age gender
001 jack 22 man
002 bob 19 man
003 alice 21 woman
004 peter 18 man
如上：是Excel中的原本数据形式，但是当我们读取出来可能就不是这个样子了，num以0开头的都不显示，如下：
num name age gender
0 1 jack 22 man
1 2 bob 19 man
2 3 alice 21 woman
3 4 peter 18 man
那这个时候就需要指定converters参数，将num列指定为str类型就可以了！可多个同时设置，如：converters={'num': str, 'age': str}
pandas读取excel文件时指定列的格式
import pandas as pd
list =pd.read_excel(file_name, dtype={'交易编号':str,'交易日期':'datetime64'},header =2)
pandas读取excel文件的所有sheetname
import pandas as pd
list =pd.read_excel(file_name,sheet_name =None).keys() #直接读取文件即可，不指定sheet_name，然后使用keys()获得 dict形式的 sheetname
DataFrame导入MySQL数据库：df.to_sql()
DataFrame.to_sql (name，con，schema = None，if_exists ='fail'，index = True，index_label = None，chunksize = None，dtype = None )
将存储在DataFrame中的记录写入SQL数据库。支持SQLAlchemy [R16]支持的数据库。可以新创建，附加或覆盖表。
参数：
name：string SQL表的名称。
con：sqlalchemy.engine.Engine或sqlite3.Connection 使用SQLAlchemy可以使用该库支持的任何数据库。为sqlite3.Connection对象提供了旧版支持。
schema：string，optional指定架构（如果数据库支持）。如果为None，请使用默认架构。
if_exists：{'fail'，'replace'，'append'}，默认'fail'如果表已存在的情况如下，fail：引发ValueError。
replace：在插入新值之前删除表。
append：将新值插入现有表。
index：布尔值，默认为True 将DataFrame索引写为列。使用index_label作为表中的列名。
index_label：字符串或序列，默认为None 索引列的列标签。如果给出None（默认）且 index为True，则使用索引名称。如果DataFrame使用MultiIndex，则应该给出一个sequence。
chunksize：int，可选. 行将一次批量写入的数量。默认情况下，所有行都将立即写入。
dtype：dict，可选. 指定列的数据类型。键应该是列名，值应该是SQLAlchemy类型，或sqlite3传统模式的字符串。
异常： ValueError异常 当表已经存在且if_exists为'fail'时（默认值）。
from sqlalchemy import create_engine
engine = create_engine("mysql+mysqldb://{}:{}@{}/{}".format('username', 'password', 'host:port', 'database'))
con = engine.connect()
df.to_sql(name='test', con=con, if_exists='append', index=False)
解决导入MySQL数据库时，数据类型改变的异常：
思路:通过建立一个字典，实现df和sql的对应
import sqlalchemy
from sqlalchemy.types import NVARCHAR,Float,Integer
dtype_dict = {'datefld': sqlalchemy.DateTime(),
'intfld': sqlalchemy.types.INTEGER(),
'strfld': sqlalchemy.types.VARCHAR(length=255),
'floatfld': sqlalchemy.types.Float(precision=3, asdecimal=True),
'booleanfld': sqlalchemy.types.Boolean
}
data.to_sql(name='some_table', con=engine, if_exists='append', index=False, dtype = dtype_dict)
高级办法-自动识别并生成对应字典（推荐）
import sqlalchemy
from sqlalchemy.types import NVARCHAR,Float,Integer
#可以通过dir(sqlalchemy.types)查看它所包含的所有数据类型
def mapping_df_types(df):
dtypedict = {}
for i, j in zip(df.columns, df.dtypes):
if "object" in str(j):
dtypedict.update({i: NVARCHAR(length=255)})
if "float" in str(j):
dtypedict.update({i: Float(precision=2, asdecimal=True)})
if "int" in str(j):
dtypedict.update({i: Integer()})
return dtypedict
mapping_df_types(data_list_new)
#输出:
{'id': NVARCHAR(length=255),
'branch': NVARCHAR(length=255),
'trade_type': NVARCHAR(length=255),
'counterparty': NVARCHAR(length=255),
'currency': NVARCHAR(length=255),
'amount': Integer(),
'rate': Float(precision=2, asdecimal=True),
'date_value': NVARCHAR(length=255),
'date_maturity': NVARCHAR(length=255),
'tenor': Integer(),
'interests': Float(precision=2, asdecimal=True),
'amount_total': Float(precision=2, asdecimal=True),
'mature': NVARCHAR(length=255)}
**dtype Description**
bool_ Boolean (True or False) stored as a byte
int_ Default integer type (same as C long; normally either int64 or int32)
intc Identical to C int (normally int32 or int64)
intp Integer used for indexing (same as C ssize_t; normally either int32 or int64)
int8 Byte (-128 to 127)
int16 Integer (-32768 to 32767)
int32 Integer (-2147483648 to 2147483647)
int64 Integer (-9223372036854775808 to 9223372036854775807)
uint8 Unsigned integer (0 to 255)
uint16 Unsigned integer (0 to 65535)
uint32 Unsigned integer (0 to 4294967295)
uint64 Unsigned integer (0 to 18446744073709551615)
float_ Shorthand for float64.
float16 Half precision float: sign bit, 5 bits exponent, 10 bits mantissa
float32 Single precision float: sign bit, 8 bits exponent, 23 bits mantissa
float64 Double precision float: sign bit, 11 bits exponent, 52 bits mantissa
complex_ Shorthand for complex128.
complex64 Complex number, represented by two 32-bit floats (real and imaginary components)
complex128 Complex number, represented by two 64-bit floats (real and imaginary components)
输出excel
to_excel()
to_excel(self, excel_writer, sheet_name='Sheet1', na_rep='', float_format=None,columns=None, header=True, index=True, index_label=None,startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None,
inf_rep='inf', verbose=True, freeze_panes=None)
常用参数解析
- excel_writer : string or ExcelWriter object File path or existing ExcelWriter目标路径
- sheet_name : string, default ‘Sheet1’ Name of sheet which will contain DataFrame,填充excel的第几页
- na_rep : string, default ”,Missing data representation 缺失值填充
- float_format : string, default None Format string for floating point numbers
- columns : sequence, optional，Columns to write 选择输出的的列。
- header : boolean or list of string, default True Write out column names. If a list of string is given it is assumed to be aliases for the column names
- index : boolean, default True，Write row names (index)
- index_label : string or sequence, default None， Column label for index column(s) if desired. If None is given, andheader and index are True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex.
- startrow :upper left cell row to dump data frame
- startcol :upper left cell column to dump data frame
- engine : string, default None ，write engine to use - you can also set this via the options，io.excel.xlsx.writer, io.excel.xls.writer, andio.excel.xlsm.writer.
- merge_cells : boolean, default True Write MultiIndex and Hierarchical Rows as merged cells.
- encoding: string, default None encoding of the resulting excel file. Only necessary for xlwt,other writers support unicode natively.
- inf_rep : string, default ‘inf’ Representation for infinity (there is no native representation for infinity in Excel)
- freeze_panes : tuple of integer (length 2), default None Specifies the one-based bottommost row and rightmost column that is to be frozen
读写csv
read_csv函数会读取逗号分隔文件，可以把set参数设置为 , 指明使用制表符分隔
df = pd.read_csv('../data/filename.csv', sep =' ')
#写入csv文件时，不写入行名（用index参数控制)
df.to_csv('../data/filename.csv',index = False)
to_pickle 方法(二进制格式保存)
调用 to_pickle方法，将以二进制格式保存数据，文本编辑器打开时，只会看到乱码
pickle文件扩展名可以是： .p , .pkl, .pickle
#保存到 pickle文件
df.to_pickle('../data/filename.pickle')
#从 pickle文件提取
df = pd.read_pickle('../data/filename.pickle')
创建 DataFrame
创建 Series
Series有两列，一列是索引，一列是值
s1 = pd.Series(['AAA','BBB'],index=['id','name'])
Out:
id AAA
name BBB
dtype: object
s1.index
Out[1]: Index(['id', 'name'], dtype='object')
s1.values
Out[2]: array(['AAA', 'BBB'], dtype=object)
s1.keys() #keys是Series对象的一个方法，它是index属性的别名
Out[3]: Index(['id', 'name'], dtype='object')
Series的属性和方法
Series的属性：loc, iloc, ix, dtype, dtypes, T, shape, size, values
Series的方法：append, corr, cov, describe, drop_duplicates, equals ,get_values, hist, min, max, mean, median, mode, quantile, replace, sample, sort_values, to_frame, transpose, unique
使用Series对象字典DataFrame
df = pd.DataFrame({'col1':['data1',1],'col2':['data2',2],'col3':['data3',3]},index=['id','name'])
Out:
col1 col2 col3
id data1 data2 data3
name 1 2 3
Series的布尔子集
df.ages > df.ages.mean() #提取 ages列中大于该列平均值的列，返回 bool
创建空 DataFrame
# 创建一个空的 DataFrame
df = pd.DataFrame(columns=['A', 'B', 'C', 'D'])
#以list为基础，创建 DataFrame
df = pd.DataFrame({'col_name':list})
pandas 索引操作
1. set_index() 直接把某列设为index
把没有index的 df 的某列设为index
df.set_index('col_name')
2. reset_index() 把某列设为index替代旧的index
在获得新的index，原来的index变成数据列，保留下来。
不想保留原来的index，使用参数 drop=True，默认 False。
df.reset_index(drop=True)
按索引操作dataframe
#先根据组合条件得到待删除行的index，保存为list, 再删除
delete_index = data_list[(data_list.机构 == 'NA') | (data_list.起息日<= date_end)].index.tolist() #取得符合条件的行索引
data_list_new = data_list.drop(delete_index)
增删改查操作
新增
使用字典为dataframe添加一条行记录
import pandas as pd
data = pd.DataFrame()
a = {"x":1,"y":2}
data = data.append(a,ignore_index=True) #注意：ignore_index必须带上，否则会有错误提示
print(data)
x y
0 1.0 2.0
为一个dataframe添加一条带索引值的行记录
import pandas as pd
data = pd.DataFrame() #得到一个空df
series = pd.Series({"x":1,"y":2},name="a") #使用字典添加一条记录，索引值为'a'
data = data.append(series)
print(data)
x y
a 1.0 2.0
指定索引位置添加行记录（可用于修改已有的记录）
import numpy as np
df = pd.DataFrame(np.random.randint(1, 10, (3, 3)), index=['one', 'one', 'two'], columns=['col1', 'col2', 'col3'])
df:
col1 col2 col3
one 9 4 4
one 1 7 2
two 1 6 8
new_data = pd.Series({'col1': 'new', 'col2': 'new', 'col3': 'new'})
new_data:
col1 new
col2 new
col3 new
dtype: object
df.iloc[0] = new_data #把记录插入在第一行的位置
df:
col1 col2 col3
one new new new
one 1 7 2
two 1 6 8
删除
import pandas as pd
import numpy as np
a=np.array([[1,2,3],[4,5,6],[7,8,9]])
df1=pd.DataFrame(a,index=['row0','row1','row2'],columns=list('ABC'))
print(df1)
Out[0]:
A B C
row0 1 2 3
row1 4 5 6
row2 7 8 9
df2=df1.copy()
#删除/选取某列含有特定数值的行
#df1=df1[df1['A'].isin([1])]
#df1[df1['A'].isin([1])] 选取df1中A列包含数字1的行
df1=df1[~df1['A'].isin([1])]
#通过~取反，选取不包含数字1的行
print(df1)
Out[1]:
A B C
row1 4 5 6
row2 7 8 9
#删除/选取某行含有特定数值的列
cols=[x for i,x in enumerate(df2.columns) if df2.iat[0,i]==3]
#利用enumerate对row0进行遍历，将含有数字3的列放入cols中
print(cols)
Out[2]:
['C']
#df2=df2[cols] 选取含有特定数值的列
df2=df2.drop(cols,axis=1) #利用drop方法将含有特定数值的列删除
print(df2)
Out[3]:
A B
row0 1 2
row1 4 5
row2 7 8
修改
修改DataFrame里的某个值统一修改为另一个值
df.replace('None',np.nan)
批量修改一个dataframe里某列中的值
先建一个字典，如下：
branches_dic = {'工商银行有限公司广州分行':'广州分行','工商银行有限公司深圳分行':'深圳分行','工商银行有限公司汕头分行':'汕头分行','工商银行有限公司上海分行':'上海分行'}
#使用下面的语句将其一次性全部替换：
for i,j in branches_dic.items():
df.loc[df.分行名称==i,'分行名称']=j #在df的分行名称一列中查找i，并且将它替换为j
查询
loc 和 iloc 获取行子集
语法：df.loc[[行],[列]] ， df.iloc[[行],[列]]
loc是基于索引标签获取行子集（行名），iloc是基于行索引获取行子集（行号）
df.loc[[0,99,999]] #选择第1行，100行和1000行
df.iloc[-1] #获取最后一行数据，如果是loc，则会报错，要写成：df.loc[df.shape[0]-1]
df.loc[:,['col1','col2']] #提取'col1','col2'列的所有行
df.iloc[:,:3] #提取前三列
df.iloc[:,list(range(3,6))] #使用list，提取第3列（含）至第5列（含）
df.iloc[:,0:6:2] #提取 0：6之间，步长为2的列，即第 0，2，4列
head() 和 tail()
获取 DataFrame的头部或尾部几条行子集
在一列中查找符合条件的值
data_list_new = data_list[~data_list['备注'].isin([条件])].reset_index()
查找一列中是否包含列表中的元素
currency = ['USD','HKD','EUR','JPY','GBP']
currency_join = '|'.join(currency) #即：'USD|HKD|EUR|JPY|GBP'
df.col.str.contains(currency_join) #返回bool值
在一列中查找包含的字符串
sql_data_new = sql_data[sql_data.branch.str.contains(字符串)]
#其中，sql_data.branch.str.contains(字符串) 和这一句等效：
sql_data_new.loc[:,'trade_type'] =='字符串' #也等于这一句： sql_data_new.trade_type =='字符串' ，返回bool值
查询列中符合条件的所有行记录
#使用数据帧的方法
df[(df.age>=25) & (df.address =='Hanoi')]
#使用Query函数
df.query('age>=25 & address =="Hanoi"')
#使用loc函数
df.loc[(df.age>=25) & (df.address =='Hanoi')]
当dataframe的索引列为日期时，如果要查找某个日期段的记录，可以用下面的命令:
df.loc[startdate:enddate]
#使用loc函数查询符合条件的列
df.loc[(df.age>=25),['name','gender','address']]
#在一列中查询datetime类型的数据
date_col = ff[ff.apply(lambda x: isinstance(x,datetime))]
#type(x) == datetime 总是不成功，因为type不考虑继承，而isinstance考虑继承
筛选全为0的行
#筛选全为0的行
df.loc[~(df==0).all(axis=1)]
#筛选不全为0的行
df.loc[(df==0).all(axis=1)]
pandas 行操作
删掉dataframe里的空行
df=df[~(df['col'].isnull())] #删掉空行
df=dropna(axis=0) #删除有空值的行，使用参数axis=0
如果是在读取Excel文件时就要删除空行，可以用以下命令dropna(axis=0)：
data_list =pd.read_excel(file_path +'\'+ file_name, header =0).dropna(axis=0)
删掉最后一行(或最后几行）
df.drop(df.tail(1).index)
pandas 列操作
删掉一列
df.drop(['列名']，axis=1, inplace = True)
将一列转化为str
代码如下：
# apply（）方法
num[0] = num[0].apply(str) # 这里num[0]:取的是第一列，在我的代码中实际意义是一列时间列，形如：2019-06-18
可能下面的方式更好：
num['时间'] = num['时间'].apply(lambda x: x.strftime('%Y-%m-%d')) # 可以指定时间str的格式
将某一列设置为str，主要是将时间列转为str类型，然后提取某一天的所有数据。
插入一列
#按默认（最后一列）插入新列
data_list['new'] =''
#按列索引序号插入指定位置
data_list_new.insert(0,'id','')
pandas的计算
小数位数的控制
官网：http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.round.html
控制台打印时显示的2位小数：
pd.set_option('precision', 2)
#或
pd.set_option('display.float_format',lambda x: '%.2f' % x)
id列只保留两位小数
df["id"]=df["id"].round(2)
单独列还可以dtype=np.int64指定，多个列用dtype={"id":np.int64,"name":str}
df_y = pd.DataFrame(np.random.choice(2, batch_size), dtype=np.int64, columns=["y"])
如果是所有列：借助demicals保持高精度
value_a = np.around(np.random.normal(0, 1, (batch_size, col)), decimals=5, out=None)某一列str转数字类型： pd.to_numeric(s)
实际修改数据精度：
df = pd.DataFrame(np.random.random([3, 3]),
columns=['A', 'B', 'C'], index=['first', 'second', 'third'])
df
A B C
first 0.028208 0.992815 0.173891
second 0.038683 0.645646 0.577595
third 0.877076 0.149370 0.491027
df.round(2)
A B C
first 0.03 0.99 0.17
second 0.04 0.65 0.58
third 0.88 0.15 0.49
df.round({'A': 1, 'C': 2})
A B C
first 0.0 0.992815 0.17
second 0.0 0.645646 0.58
third 0.9 0.149370 0.49
decimals = pd.Series([1, 0, 2], index=['A', 'B', 'C'])
>>> df.round(decimals)
A B C
first 0.0 1 0.17
second 0.0 1 0.58
third 0.9 0 0.49
插值计算之一： pd.interpolate()
x = pd.Series([1,2,np.nan,np.nan,6])
print(x.interpolate())
#输出
0 1.000000
1 2.000000
2 3.333333
3 4.666667
4 6.000000
dtype: float64
插值计算之二：from scipy.interpolate import interp1d
import numpy as np
import matplotlib.pyplot as plt
from scipy.interpolate import interp1d
from scipy import stats
x = np.linspace(0,10,10)
y = np.exp(-x/3.0)
f = interp1d(x,y)
f2 = interp1d(x,y,kind='cubic')
xnew = np.linspace(0,10,40)
plt.plot(x,y,'o',xnew,f(xnew),'-',xnew,f2(xnew),'--')
plt.legend(['data','linear','cubic'],loc='best')
plt.show()
beta,alpha,r_value,p_value,std_err = stats.linregress(x,y)
print('beta:',beta)
print('alpha:',alpha)
print('r_value:',r_value)
print('p_value:',p_value)
print('std_err:',std_err)
运行结果:
beta: -0.0866897899932637
alpha: 0.7485702038527071
r_value: -0.9136257511776316
p_value: 0.00021916674187652294
std_err: 0.013638772033747677
列的计算
通过一列，判断生成另一列
df['B'] = df['A'].map(lambda a : 1 if a>0 else 0) #根据列A新增列B，A中大于0的值为1，否则为0
#df有四列，现在要用现金流，来生成两列‘流入’和‘流出’
df['流入'] = df.现金流.apply(lambda x: x if x>0 else 0)
df['流出'] = df.现金流.apply(lambda x: 0 if x>0 else x)
通过两列计算，生成新列
map和lambda的搭配使用，可以将两个等长的序列进行运算，生成新的序列。利用这个方法，可以利用DataFrame中已知列的数据生成新列。具体如下：
df['new_col']=list(map(lambda x,y: function(x,y), df['col1'], df['col2']))
通过以上操作，把col1和col2两个等长的序列代入了function(x,y)函数，两列中对应的数据两两计算，生成了map对象。
再用list()函数把map对象转化为序列，就可以填入DataFrame生成一个新列new-col了。
第三种情形：使用df.apply(lambda x: function(), axis = 1)
主要是DataFrame.apply函数的应用，如果设置axis参数为1则每次函数每次会取出DataFrame的一行来做处理，如果axis为1则每次取一列。
如代码所示，判断如果城市名中含有ing字段且年份为2016，则新列test值赋为1，否则为0.
import numpy as np
import pandas as pd
data = {'city': ['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen', 'Hangzhou', 'Chongqing'],
'year': [2016,2016,2015,2017,2016, 2016], 'population': [2100, 2300, 1000, 700, 500, 500]}
frame = pd.DataFrame(data, columns = ['year', 'city', 'population', 'debt'])
def function(a, b):
if 'ing' in a and b == 2016:
return 1
else:
return 0
print(frame, '
')
frame['test'] = frame.apply(lambda x: function(x.city, x.year), axis = 1)
一个例子，三种方法
链接：https://www.jianshu.com/p/94e00fda5f63
根据下面的数据集，添加一列qualifications 当满足sy 大于或等于5，assets大于等于1000000时为yes,否则为no。
data = {'name':['Tom','Bulbasaur','Charmander','Squirtle','Caterpie'],'sy':[1,3,5,6,3],'assets':[1000000,400000,2050000,170000,5600000]}
df = pd.DataFrame(data)
"""方法一:通过索引遍历每个值，再通过条件给新增位置赋值"""
for i in df.index:
if (df.loc[i,'sy']>=5) & (df.loc[i,'assets']>=1000000):
df.loc[i,'qualifications'] = 'yes'
else:
df.loc[i,'qualifications'] = 'no'
"""方法二：通过np的where方法判断赋值"""
df['qualifications'] = np.where((df['sy']>=5) & (df['assets']>=1000000),'yes','no')
"""方法三:用apply方法设置一个函数"""
def f1(d):
if (d['sy']>=5) & (d['assets']>=1000000):
return 'yes'
else:
return 'no'
df['qualifications'] = df[['sy','assets']].apply(f1,axis=1) # axis = 1是改变传入数据的轴向
根据两列，计算加权值
df:
| bond_code | our_side | price_clean | amount_par |
| --------- | -------- | ----------- | ---------- |
| 180016 | 买入 | 101.4015 | 10000 |
| 180016 | 买入 | 100.4935 | 2000 |
| 180016 | 买入 | 100.4935 | 2000 |
| 180016 | 买入 | 100.908 | 5000 |
| 180016 | 买入 | 101.0196 | 3000 |
| 180016 | 卖出 | 102.5296 | 10000 |
| 180016 | 卖出 | 103.0034 | 9000 |
| 180016 | 卖出 | 103.1334 | 2000 |
| 180016 | 卖出 | 101.4718 | 1000 |
#方法一（逐步计算）：
grouped = df.groupby('our_side')
get_wavg = lambda g: np.average(g['price_clean'],weights=g['amount_par'])
wavg = grouped.apply(get_wavg)
#方法二（函数式）：
def wavg(values,weights,by):
return (values*weights).groupby(by).sum()/weights.groupby(by).sum()
wavg(df.price_clean,df.amount_par,df.our_side)
wavg:
买入 101.07
卖出 102.73
dtype: float64
pandas自动生成数据
根据列表生成随机数据
import numpy as np
countries = np.array(["US", "UK", "GR", "JP"])
key = countries[np.random.randint(0, 4, 20)]
#输出：
array(['JP', 'US', 'UK', 'US', 'US', 'JP', 'UK', 'GR', 'US', 'UK', 'UK',
'US', 'UK', 'GR', 'US', 'UK', 'US', 'US', 'JP', 'JP'], dtype='<U2')
生成日期序列
dates = pd.date_range("20130101", periods=6)
#输出：
DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03', '2013-01-04',
'2013-01-05', '2013-01-06'],
dtype='datetime64[ns]', freq='D')
生成6*4的 dataframe
df = pd.DataFrame(np.random.randn(6, 4), index=dates, columns=list("ABCD"))
#输出：
A B C D
2013-01-01 1.433950 -0.238960 -0.927613 -1.151130
2013-01-02 2.245523 -1.033523 -0.870130 -0.520500
2013-01-03 -1.386891 -0.686143 1.122175 1.453693
2013-01-04 0.917329 -0.820655 0.972674 -0.819141
2013-01-05 -0.481533 1.008935 -0.536159 -0.555727
2013-01-06 -1.117042 -0.358691 1.661648 -0.861112
生成连续日期（日期列表，或月末日期列表）
startdate = '2020-01-01'
enddate ='2020-12-31'
day_list = pd.date_range(start_date,end_date,freq='D')
month_list = pd.date_range(start_date,end_date,freq='M')
分别生成：
DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-04',
'2020-01-05', '2020-01-06', '2020-01-07', '2020-01-08',
'2020-01-09', '2020-01-10',
...
'2020-12-22', '2020-12-23', '2020-12-24', '2020-12-25',
'2020-12-26', '2020-12-27', '2020-12-28', '2020-12-29',
'2020-12-30', '2020-12-31'],
dtype='datetime64[ns]', length=366, freq='D')
DatetimeIndex(['2020-01-31', '2020-02-29', '2020-03-31', '2020-04-30',
'2020-05-31', '2020-06-30', '2020-07-31', '2020-08-31',
'2020-09-30', '2020-10-31', '2020-11-30', '2020-12-31'],
dtype='datetime64[ns]', freq='M')
#注意type:
type(month_list)
pandas.core.indexes.datetimes.DatetimeIndex
如果要把.DatetimeIndex转化为正常的list，则可以用以下命令：
month_list = pd.Series(month_list)
month_list
Out[100]:
0 2020-01-31
1 2020-02-29
2 2020-03-31
3 2020-04-30
4 2020-05-31
5 2020-06-30
6 2020-07-31
7 2020-08-31
8 2020-09-30
9 2020-10-31
10 2020-11-30
11 2020-12-31
生成一列各类型都有的df
df2 = pd.DataFrame(
...: {
...: "A": 1.0,
...: "B": pd.Timestamp("20130102"),
...: "C": pd.Series(1, index=list(range(4)), dtype="float32"),
...: "D": np.array([3] * 4, dtype="int32"),
...: "E": pd.Categorical(["test", "train", "test", "train"]),
...: "F": "foo",
...: }
...: )
A B C D E F
0 1.0 2013-01-02 1.0 3 test foo
1 1.0 2013-01-02 1.0 3 train foo
2 1.0 2013-01-02 1.0 3 test foo
3 1.0 2013-01-02 1.0 3 train foo
pandas时间处理
排序
按各列名排序
data_list_new = data_list_new.sort_values(by=['起息日','机构','项目']).reset_index(drop= True)
pandas数据清洗
缺失值与空值处理
1.df.dropna()函数
相关概念
函数具体解释
空值：在pandas中，空值就是空字符串 “”
缺失值：np.nan（缺失数值），pd.naT（缺失时间），或None（缺失字符串）
DataFrame.dropna(axis=0, how='any', thresh=None, subset=None, inplace=False)
函数作用：删除含有空值的行或列
axis:维度，axis=0表示index行,axis=1表示columns列，默认为0
how:"all"表示这一行或列中的元素全部缺失（为nan）才删除这一行或列，"any"表示这一行或列中只要有元素缺失，就删除这一行或列
thresh:一行或一列中至少出现了thresh个才删除。
subset：在某些列的子集中选择出现了缺失值的列删除，不在子集中的含有缺失值得列或行不会删除（有axis决定是行还是列）
inplace：刷选过缺失值得新数据是存为副本还是直接在原数据上进行修改。
data.dropna(how = 'all') # 传入这个参数后将只丢弃全为缺失值的那些行
data.dropna(axis = 1) # 丢弃有缺失值的列（一般不会这么做，这样会删掉一个特征）
data.dropna(axis=1,how="all") # 丢弃全为缺失值的那些列
data.dropna(axis=0,subset = ["Age", "Sex"]) # 丢弃‘Age’和‘Sex’这两列中有缺失值的行
例子：
df = pd.DataFrame({"name": ['Alfred', 'Batman', 'Catwoman'],"toy": [np.nan, 'Batmobile', 'Bullwhip'],
"born": [pd.NaT, pd.Timestamp("1940-04-25"),pd.NaT]})
df
Out[0]:
name toy born
0 Alfred NaN NaT
1 Batman Batmobile 1940-04-25
2 Catwoman Bullwhip NaT
df.dropna()
Out[1]:
name toy born
1 Batman Batmobile 1940-04-25
df.dropna(axis=1) #delete col
Out[2]:
name
0 Alfred
1 Batman
2 Catwoman
df.dropna(how='all') #所有值全为缺失值才删除
Out[3]:
name toy born
0 Alfred NaN NaT
1 Batman Batmobile 1940-04-25
2 Catwoman Bullwhip NaT
df.dropna(thresh=2) #至少出现过两个缺失值才删除
Out[4]:
name toy born
1 Batman Batmobile 1940-04-25
2 Catwoman Bullwhip NaT
df.dropna(subset=['name', 'born']) #删除subset中的含有缺失值的行或列
Out[5]:
name toy born
1 Batman Batmobile 1940-04-25
df.fillna()
DataFrame.fillna(value=None, method=None, axis=None, inplace=False, limit=None, downcast=None, **kwargs)
函数作用：填充缺失值
value:需要用什么值去填充缺失值
axis:确定填充维度，从行开始或是从列开始
method：ffill:用缺失值前面的一个值代替缺失值，如果axis =1，那么就是横向的前面的值替换后面的缺失值，如果axis=0，那么则是上面的值替换下面的缺失值。backfill/bfill，缺失值后面的一个值代替前面的缺失值。注意这个参数不能与value同时出现
limit:确定填充的个数，如果limit=2，则只填充两个缺失值。
示例：
df = pd.DataFrame([[np.nan, 2, np.nan, 0],[3, 4, np.nan, 1],
[np.nan, np.nan, np.nan, 5],[np.nan, 3, np.nan, 4]],
columns=list('ABCD'))
df
Out[0]:
A B C D
0 NaN 2.0 NaN 0
1 3.0 4.0 NaN 1
2 NaN NaN NaN 5
3 NaN 3.0 NaN 4
df.fillna(axis=1,method='ffill') #横向用缺失值前面的值替换缺失值
Out[1]:
A B C D
0 NaN 2.0 2.0 0.0
1 3.0 4.0 4.0 1.0
2 NaN NaN NaN 5.0
3 NaN 3.0 3.0 4.0
df.fillna(axis=0,method='ffill') #纵向用缺失值上面的值替换缺失值
Out[2]:
A B C D
0 NaN 2.0 NaN 0
1 3.0 4.0 NaN 1
2 3.0 4.0 NaN 5
3 3.0 3.0 NaN 4
df.fillna(0)
Out[3]:
A B C D
0 0.0 2.0 0.0 0
1 3.0 4.0 0.0 1
2 0.0 0.0 0.0 5
3 0.0 3.0 0.0 4
values = {'A':0,'B':1,'C':2,'D':3}
df.fillna(value=values) #不同的列用不同的值填充
Out[4]:
A B C D
0 0.0 2.0 2.0 0
1 3.0 4.0 2.0 1
2 0.0 1.0 2.0 5
3 0.0 3.0 2.0 4
df.fillna(value=values,limit=1) #对每列出现的替换值有次数限制，此处限制为一次
Out[5]:
A B C D
0 0.0 2.0 2.0 0
1 3.0 4.0 NaN 1
2 NaN 1.0 NaN 5
3 NaN 3.0 NaN 4
df.isna()和df.isnull()
df.isna() 和 df.notna()，可以判断出None、pd.NaT、np.NaN 三种类型的缺失值；
- 但是空字符串 “” 、0，不会认为是缺失值；
- 另外对于4字符的字符串“None”、“null” ，也不会认为是缺失值。
- df.isnull() 等同于 df.isna()
- df.notnull() 等同于 df.notna()
缺失值和空值被转换后会怎么样？
字符串类型缺失值 None：
None 被 astype(str)转换后，会变成一个4字符的字符串"None" (注意它会被isna() 识别为 False, 相当于一个正常的字符串)，极具迷惑性。
None 被 pd.to_datetime()转换后，仍然是一个NoneType类型的 None缺失值，没变。
None 被 astype(np.int64) 或 np.int32() 转换时，会提示int()函数不接受NoneType类型的参数，转换无法进行。
数值类型缺失值 np.NaN：
np.NaN 被astype(str)转换后，会变成一个3字符的字符串"nan"。
被 pd.to_datetime() 转换后，会变成 NaTType类型，即时间缺失值，仍然可以被isna()识别出来。
np.NaN 被np.int32() 转化时，会提示 float 类型的 NaN 不能被转换为 integer 类型，因为np.NaN 本身确实是个一个float类型。如果被np.float32() 转换后，则转换前后没有区别，仍然是 np.NaN 浮点型。
时间类型缺失值 pd.NaT：
pd.NaT 被astype(str) 转换后，会变成一个3字符的字符串"NaT"。
被 pd.to_datetime() 转换前后无变化，仍然是 NaTType类型的缺失值。
pd.NaT 被np.int32() 转化后，会变成一个巨长的整数。被np.float32() 转换时，会提示 NaTType类型的缺失值无法转换。
检查dataframe是否有空值
确定空值位置：
在导入dataframe之后，一个非常好的习惯就是及时检查一下每一列是否有空值，用下面的语句可以简单实现这个需求：
df.isnull().any()
#上面的这行代码可以返回各个列是否有空值的情况，结果如下：
A False
B True
C True
D False
dtype: bool
这样我们可以得知， B列和C列有空值存在, 在用到这两列数据的时候，需要考虑对空值的处理方法。
处理空值的两种办法：删除 or 填充
删除：删除空值主要使用下面这个语句：
df.dropna()
df.dropna(axis=0, how='any', thresh=None, subset=None, inplace=False)
df.dropna(axis = 0, subset = ['A'] ) #subset非常有用，本句删除所有列A 空值所在行
axis代表删除整行or整列，默认axis = 0， 也就是删除行。
how默认为‘any’， 也就是删除任何含有空值的行/列。此外how = ‘all’， 则会删除全部为空值的行/列。
thresh可以用来限定删除有几个空值的行/列，如thresh = 3， 则删除有3个空值的行/列。
填充：填充空值主要使用下面这个语句：
df.fillna( )
df.fillna(value=None, method=None, axis=None, inplace=False, limit=None, downcast=None, **kwargs)
value可以是一个值（标量），比如我们用一列的均值来填充该列的所有空值：
df['column_name'].fillna(value = df['column_name'].mean()]
#value 也可以是 dict, Series, 甚至 DataFrame，比如我们可以用字典来实现对不同的列填充不同的值：
df.fillna({'column_name_A': 0,'column_name_B': 100})
两个series相加，根据常识我们可以知道index不同的地方会变成空值，如下图
s1 = pd.Series([1, 2, 3, 4], index=['a', 'b', 'c', 'd'])
s2 = pd.Series([10, 20, 30, 40], index=['c', 'd', '```e', 'f'])
s1 + s2
a NaN
b NaN
c 13.0
d 24.0
e NaN
f NaN
如果我们希望空值的地方被原series中的值填充，即得到以下效果：
a 1
b 2
c 13
d 24
e 30
f 40
可以使用add而不是加号来进行：
s1.add(s2, fill_value = 0)
pandas异常处理
SettingWithCopyWarning:
#新增一列type,将原df.trade_type中包含'买入返售金融资产'字符的，重新设定为'buy',否则就改成'sell'
sql_data_new['type']=np.where(sql_data_new.trade_type.str.contains('买入返售金融资产'),'buy','sell')
运行时，会遇到以下SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead
See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view
按照官网的解释，采用的方法是如下这样：
mask = sql_data_new.trade_type.str.contains('买入返售金融资产')
sql_data_new.loc[mask, 'types'] = 'buy'
尴尬就是得至少执行两次，才能分别赋值'buy','sell'
显示完整行或列
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.read_excel ValueError
sql="SELECT * FROM {dbs} where buysell_type not like '%外汇兑换%'".format(dbs=db_name_spot)
custom_fx_list = pd.read_sql(sql,engine)
运行出现：ValueError: unsupported format character '?' (0x5916) at index 86
这种情况：%在字符串中作为格式化字符串的关键字，当其后为诸如n、c、s时进行正常转义；而出现上述代码时即返回错误。
解决方法：
1.使用%%，即表示非关键字的%（推荐）；
2.使用%，有些情况下适用。
即：
sql="SELECT * FROM {dbs} where buysell_type not like '%%外汇兑换%%'".format(dbs=db_name_spot)
custom_fx_list = pd.read_sql(sql,engine)
参考资料
https://www.cnpython.com/qa/93519
pandas的新功能（不常用的用法，但很好用）
https://www.jb51.net/article/167136.htm