有这样一张电票数据表:2020转贴现清单.csv (实际68行数据,含标题行)
今天我们用它来练习用python (含 Pandas, pymysql)和 MySQL 如何对它进行一些基础的操作.
导入数据
首先, 我们导入它
import pandas as pd
#本开关用于控制pandas中float数据的显示方式,关闭科学计数方式,显示小数点后两位.
pd.options.display.float_format='{:.2f}'.format
#表1是清单，表2是承兑行额度表
file1_path = 'C:/Users/Desktop/PY/2020转贴现清单.csv'
file2_path = 'C:/Users/Desktop/PY/剩余额度.csv'
#用read_csv读取,只取四列usecols("票号", "票面金额(元)"，"票面到期日"，"承兑行"),注意cols从0开始数.
bill_list = pd.read_csv(file1_path, usecols=(0,4,9,17),header=0)
其它的导入方式还有:
• pd.read_csv(filename_path)：从CSV文件导入数据
• pd.read_table(filename_path)：从限定分隔符的文本文件导入数据
• pd.read_excel(filename_path)：从Excel文件导入数据
• pd.read_sql(query, connection_object)：从SQL表/库导入数据
• pd.read_json(json_string)：从JSON格式的字符串导入数据
• pd.read_html(url)：解析URL、字符串或者HTML文件，抽取其中的tables表格
• pd.read_clipboard()：从你的粘贴板获取内容，并传给read_table()
• pd.DataFrame(dict)：从字典对象导入数据，Key是列名，Value是数据
得到的bill_list如下图:
bill_list
Out[7]:
票据号码 票面金额 到期日 承兑行
0 130558100301420200703673063725 2000000.00 2020-11-01 中国民生银行
1 110329007403220200618660897195 1000000.00 2020-11-02 中国农业银行
2 110329007403220200618660897218 1000000.00 2020-11-02 中国农业银行
3 110429000615420200619662041630 1000000.00 2020-11-02 中国银行
4 130558401822120200804695066745 8000000.00 2020-11-02 中国民生银行
.. ... ... ... ...
63 110458602041820200528647586166 1500000.00 2020-11-28 中国银行
64 130133100013120200527645905735 1000000.00 2020-11-28 交通银行
65 130133100013120200527645905743 1000000.00 2020-11-28 交通银行
66 130558100301420200730692349173 2000000.00 2020-11-28 中国民生银行
67 130558506700420200630670941661 1455514.85 2020-12-30 中国民生银行
[68 rows x 4 columns]
bill_list是一个DataFrame, 大小是[68 rows x 4 columns], 下面我们再对它进行一些操作, 注意体会各个命令的实际效果
**查看DataFrame的信息**
list(bill_list)
Out[6]: ['票据号码', '票面金额', '到期日', '承兑行']
bill_list.columns
Out[8]: Index(['票据号码', '票面金额', '到期日', '承兑行'], dtype='object')
bill_list.values
Out[3]:
array([['130558100301420200703673063725', 2000000.0, '2020-11-01', '中国民生银行'],
['110329007403220200618660897195', 1000000.0, '2020-11-02', '中国农业银行'],
['110329007403220200618660897218', 1000000.0, '2020-11-02', '中国农业银行'],
['110429000615420200619662041630', 1000000.0, '2020-11-02', '中国银行'],
['130558401822120200804695066745', 8000000.0, '2020-11-02', '中国民生银行'],
['130558100301420200804694990785', 2273800.0, '2020-11-03', '中国民生银行'],
['130558506700420200630670941661', 1455514.85, '2020-12-30','中国民生银行']], dtype=object)
关于df.values, dtype=object, 它大概是这么个意思
其它的操作还有:
• df.head(n)：查看DataFrame对象的前n行（不加参数，默认前10行）
• df.tail(n)：查看DataFrame对象的最后n行（不加参数，默认后10行）
• df.shape()：查看行数和列数（维度查看）
• df.info()：查看索引、数据类型和内存信息
• df.describe()：查看数值型列的汇总统计
• s.value_counts(dropna=False)：查看Series对象的唯一值和计数
• df.apply(pd.Series.value_counts)：查看DataFrame对象中每一列的唯一值和计数
• df.dtypes：查看每一列的数据类型（扩展：df['two'].dtypes，查看“two”列的类型）
• df.isnull()：查看空置(注：空置部分会用true显示，不是空置False显示)（扩展：df['two'].isnull，查看“two”这一列的空置）
• df.values：查看数据表的值
• df.columns：查看列名称
数据排序
按票据号码升序排列, axis =0 是按列, 也可以不写,默认. 如果 axis = 1, 就是按行, 就必须写.
bill_list.sort_values(by='票据号码',axis =0)
Out[9]:
票据号码 票面金额 到期日 承兑行
12 110258100201120200310594392608 6300000.00 2020-11-10 中国工商银行
24 110258650027020200515637250023 2900000.00 2020-11-15 中国工商银行
42 110258800287820200820704406617 1550000.00 2020-11-20 中国工商银行
52 110258800287820200825707679003 2250000.00 2020-11-25 中国工商银行
59 110258800287820200827710725997 1200000.00 2020-11-27 中国工商银行
.. ... ... ... ...
35 190729000121820200724687172545 2000000.00 2020-11-19 中国农业银行
36 190729000121820200724687172762 2000000.00 2020-11-19 中国农业银行
37 190729000121820200724687172779 2000000.00 2020-11-19 中国农业银行
38 190729000121820200724687182977 2000000.00 2020-11-19 中国农业银行
39 190729000121820200724687182985 2000000.00 2020-11-19 中国农业银行
[68 rows x 4 columns]
如果想排序的有两列,比如按'票面金额'倒序(由大到小),按'到期日'顺序(由小到大),可以这么写:
bill_list.sort_values(by=['票面金额','到期日'],axis =0,ascending = [False, True])
Out[10]:
票据号码 票面金额 到期日 承兑行
32 131022100018820200518638790084 35060806.35 2020-11-18 上海浦东发展银行
18 131060200017220200515637681036 30428000.00 2020-11-13 上海浦东发展银行
19 131022100018820200513636202745 30000000.00 2020-11-13 上海浦东发展银行
13 130133700007220200511633966401 12000000.00 2020-11-11 交通银行
4 130558401822120200804695066745 8000000.00 2020-11-02 中国民生银行
.. ... ... ... ...
3 110429000615420200619662041630 1000000.00 2020-11-02 中国银行
25 130158100008620200515637990749 1000000.00 2020-11-15 交通银行
44 130558506700420200520640035647 1000000.00 2020-11-20 中国民生银行
64 130133100013120200527645905735 1000000.00 2020-11-28 交通银行
65 130133100013120200527645905743 1000000.00 2020-11-28 交通银行
[68 rows x 4 columns]
数据切片
所谓切片, 其实就是按照一定的条件选择你想要数据
• df.isin([5])：判断全部数据值中是否有5
• dr[col].isin([5])：判断列col中是否有5
• df[col]：根据列名，并以Series的形式返回列
• df[[col1, col2]]：以DataFrame形式返回多列
• s.iloc[0]：按位置选取行数据
• s.loc['index_one']：按索引选取行数据
• df.iloc[0,:]：返回第一行
• df.iloc[0,0]：返回第一列的第一个元素
• df.ix[0] 或 df.ix[raw] ：ix函数可以根据行位置或行标签选择行数据
注：loc函数根据行标签进行行选择；
iloc函数根据行位置进行行选择；
ix函数可以根据行位置选择也可以根据行标签选择。
今天我们用df.isin(), 它是这样使用的df[col].isin(list), 用来查看某一列col中包含list的记录, 它会返回一列布尔值, 如果你要提取这样切片的结果,你得用:df[df[col].isin(list)]
比如:
查询'承兑行'一列中包含'中国民生银行'的记录:
xyz = bill_list1['承兑行'].isin(['中国民生银行'])
xyz
Out[13]:
票据号码
130558100301420200703673063725 True
110329007403220200618660897195 False
110329007403220200618660897218 False
110429000615420200619662041630 False
130558401822120200804695066745 True
110458602041820200528647586166 False
130133100013120200527645905735 False
130133100013120200527645905743 False
130558100301420200730692349173 True
130558506700420200630670941661 True
Name: 承兑行, Length: 68, dtype: bool
type(bill_list1['承兑行'].isin(['中国民生银行']))
Out[14]: pandas.core.series.Series
用这个结果, 再去和df组合一次,才能筛选出你想要的符合条件的记录:
bill_list1[bill_list1['承兑行'].isin(['中国民生银行'])]
Out[15]:
票面金额 到期日 承兑行
票据号码
130558100301420200703673063725 2000000.00 2020-11-01 中国民生银行
130558401822120200804695066745 8000000.00 2020-11-02 中国民生银行
130558100301420200804694990785 2273800.00 2020-11-03 中国民生银行
130558100301420200706673782421 2000000.00 2020-11-04 中国民生银行
130558100301420200706673838648 2000000.00 2020-11-04 中国民生银行
130558100301420200709676161852 2000000.00 2020-11-07 中国民生银行
...
两个小TIPS
- 由于columns的文字中可能含有空格,不当引用时会报KeyError错误, 影响后面的引用, 为了消除这个影响,我们要对它进行处理
例如, 通过构建函数,来自动净化Dataframe的columns,仅仅只是columns,别的不行
def columns_strip(df_name):
columns=list(df_name.columns)
columns_new =[i.strip() for i in columns]
df_name.columns = columns_new
columns_strip(bill_list) #去除columns里的空格
- 下面这段代码将Series中的带千分位的数字(其实是str)去掉千分符再换为数字float,注意,要以索引为操作对象.
不转换会遇到类似的报错: ValueError: could not convert string to float: '2,000,000.00'
bill_amount=bill_list['票面金额'] #pandas.core.series.Series
for i in bill_amount.index:
bill_amount[i]=bill_amount[i].replace(',','')
bill_amount[i]=float(bill_amount[i])
新生成的bill_amount还是pandas.core.series.Series, 但是要注意,这里修改好之后, bill_list里的票面金额也变了!!! 因为这个是对索引操作的
bill_amount[i]=float(bill_amount[i])也可以写成: s.astype(float)：将Series中的数据类型更改为float类型
bill_list1 = bill_list.set_index('票据号码') #单索引
#以.set_index([x,y])方式,设置双索引,用于约束条件,要把有重复的列放在前面
#bill_list2 = bill_list.set_index(['承兑行','票据号码']) #双索引
bill_dict = bill_list1.to_dict('dict')
#bill_dict 是一个三个大字典套嵌的字典， 它包括：{{票号：票面金额}，{票号：到期日}，{票号：承兑行}}
bill_dict
Out[27]:
{'票面金额': {'130558100301420200703673063725': 2000000.0,
'110329007403220200618660897195': 1000000.0,
'110329007403220200618660897218': 1000000.0,
...
'130133100013120200527645905743': 1000000.0,
'130558100301420200730692349173': 2000000.0,
'130558506700420200630670941661': 1455514.85},
'到期日': {'130558100301420200703673063725': '2020-11-01',
'110329007403220200618660897195': '2020-11-02',
'110329007403220200618660897218': '2020-11-02',
...
'130133100013120200527645905743': '2020-11-28',
'130558100301420200730692349173': '2020-11-28',
'130558506700420200630670941661': '2020-12-30'},
'承兑行': {'130558100301420200703673063725': '中国民生银行',
'110329007403220200618660897195': '中国农业银行',
'110329007403220200618660897218': '中国农业银行',
'110429000615420200619662041630': '中国银行',
'130558401822120200804695066745': '中国民生银行',
...
'130133100013120200527645905743': '交通银行',
'130558100301420200730692349173': '中国民生银行',
'130558506700420200630670941661': '中国民生银行'}}
使用时，要分别先取三个大字典的键值对 ：这三个字典都是以 "票号"为 key的
bill_dict_amount = bill_dict['票面金额']
bill_dict_maturity = bill_dict['到期日']
bill_dict_bank = bill_dict['承兑行']
bill_dict_amount
Out[28]:
{'130558100301420200703673063725': 2000000.0,
'110329007403220200618660897195': 1000000.0,
'110429000615420200619662041630': 1000000.0,
...
'130558100301420200730692349173': 2000000.0,
'130558506700420200630670941661': 1455514.85}
bill_dict_bank
Out[29]:
{'130558100301420200703673063725': '中国民生银行',
'110329007403220200618660897195': '中国农业银行',
'110329007403220200618660897218': '中国农业银行',
'110429000615420200619662041630': '中国银行',
...
'130558100301420200730692349173': '中国民生银行',
'130558506700420200630670941661': '中国民生银行'}
之所以设置成这样,是因为票据号码是唯一的,用它做了index对于以后的引用和线性运算会有很大的方便.