文章目录
使用python-pandas处理excel数据时，常用命令汇总
数据处理
导入输出文件
导入文件：
df = pd.read_excel('文件路径/文件名.xlsx')
#读取指定sheet
df = pd.read_excel("test.xlsx", engine="openpyxl", sheet_name="表1")
导出文件：
df.to_excel('输出文件名.xlsx')
查询数据
iloc 与 loc 查询的区别：
iloc的意思是基于索引（index-based selection），输入为索引，也就是，行是（0，1，2，3，4）中的值（行索引），列是（0, 1, 2,…）中的值（列索引）。
loc的意思是基于标签（label-based selection），输入为标签，也就是，行是（0，1，2，3，4）中的值，列是（‘列名1’, ‘列名2’, ‘列名3’,…）中的值。
可以理解为
iloc输入的是坐标，loc输入的是具体含义。两个方法容易被混淆的点，在于这里行坐标（0, 1, 2, 3, 4,…）恰好也是具体含义，但iloc中的数字与loc中的数字真实含义不同。
也正是因为这个差距，容易导致一个错误。
在对数据进行切片操作时，iloc与Python中 (:)的含义相同，包含（:）前的值，不包含（:）后的值。
而在loc中使用(:)不仅包含（:）前的值，也包含（:）后的值。
-
根据行索引查找某行数据：
df.loc[行索引]或者df.iloc[行索引]
-
根据行索引查找某一段数据：
df.iloc[a:b]或者df[a,b]或者df.loc[a,b]——a为起始行索引，b为接数行索引
注意：
df.iloc[a:b]与df[a,b]效果一样，都不包含b行的值；df.loc[a,b]包含b行的值 -
根据索引查找某行某列数据：
df.loc[a,'列名']——a为行索引，第二个参数对应输入所需列名
可以查找多列数据：df.loc[a,['列名A','列名B']]
或者：
df.iloc[a,b]——a行索引，b为列索引
原表：
# 读取文件
import pandas as pd
df = pd.read_csv('match.csv')
#查找行索引为1的数据
print(df.loc[1])
输出：
Name Jae Crowder
Team Boston Celtics
Number 99.0
Position SF
Age 25.0
Height 6-6
Weight 235.0
College Marquette
Salary 6796117.0
Name: 1, dtype: object
# 根据据行索引查找某一段数据
df.loc[1:5]
输出如下：
Name Team Number Position Age Height Weight College Salary
1 Jae Crowder Boston Celtics 99.0 SF 25.0 6-6 235.0 Marquette 6796117.0
2 John Holland Boston Celtics 30.0 SG 27.0 6-5 205.0 Boston University NaN
3 R.J. Hunter Boston Celtics 28.0 SG 22.0 6-5 185.0 Georgia State 1148640.0
4 Jonas Jerebko Boston Celtics 8.0 PF 29.0 6-10 231.0 NaN 5000000.0
5 Amir Johnson Boston Celtics 90.0 PF 29.0 6-9 240.0 NaN 12000000.0
# 根据索引查找某行某列数据
print(df.loc[1,'Name'])
输出如下：
Jae Crowder
#查找某一行多列数据
print(df.loc[1,['Name','Team']])
输出如下：
Name Jae Crowder
Team Boston Celtics
Name: 1, dtype: object
#查找多行多列数据
print(df.loc[1:4,['Name','Team']])
输出如下：
Name Team
1 Jae Crowder Boston Celtics
2 John Holland Boston Celtics
3 R.J. Hunter Boston Celtics
4 Jonas Jerebko Boston Celtics
- 查看文件最后5行的数据
import pandas as pd
df = pd.read_csv('match.csv')
1\ 查看最后5行所有数据
print(df.tail(5))
2\ 查看最后5行指定列数据
#方法一
print(df[['Name','Team','College','Salary']].tail(5))
#方法二
print(df.loc[df.index[-5:],['Name','Team','College','Salary']])
- 多个条件查询数据1
import pandas as pd
df = pd.read_csv('match.csv')
# 查找Team=Boston Celtics，且Number >= 40的数据,注意数字不用加引号
print(df[(df['Team']=='Boston Celtics') & (df['Number'] >= 40) ])
- 多条件查询2
#查找某列出席拿过的多个值
print(df[df['Position'].isin(['PG','SF','C'])])
或者：
print(df.query("Position=='PG' | Position=='C' | Position=='SF'"))
或者：
print(df.query("Position in ('PG', 'C', 'SF')"))
增加列
df.insert(loc=2, column='列名', value='值') # 在最后一列后，插入列值全为“值”3的“列名”列
筛选列
将原表中的某列或某几列复制到新表
导入原表：
import pandas as pd
df = pd.read_csv('match.csv')
df
筛选列
df1=df[["Name","Team","Age","Salary"]]
df1
筛选某列出现过的值，并展示其余列的数据，注意：无法筛选出这一列的所有空值
import pandas as pd
df = pd.read_csv('match.csv')
#筛选TEAM是Utah Jazz的信息
df.loc[df['Team']=='Utah Jazz']
删除列
删除Excel列：
df1为删除列后的表，df为原表
# labels=["列A", "列B"] 表示删除那一列或哪几列；axis = 1表示删除列
df1 = df.drop(labels=["College","Salary"],axis = 1)
删除行
- 用索引删除Excel中的行：
df为原表，df1为删除后表
# df.drop()表示删除，df.index()确定索引，
删除某一行
df1 = df.drop(df.index[0],inplace = False) # 删除索引为0的行，inplace=False不改变原表
删除多行（索引连续）
df1 = df.drop(df.index[2:4],inplace = False) # 删除索引为2和3的行，inplace=False不改变原表
删除空值行
data.dropna(how = ‘all’) # 传入这个参数后将只丢弃全为缺失值的那些行
data.dropna(axis = 1) # 丢弃有缺失值的列（一般不会这么做，这样会删掉一个特征）
data.dropna(axis=1,how=“all”) # 丢弃全为缺失值的那些列
data.dropna(axis=0,subset = [“Age”, “Sex”]) # 丢弃‘Age’和‘Sex’这两列中有缺失值的行
- 删除值全部为空的每一行
data.dropna(axis=0,how='all') - 删除表中任何含有空值的行
data.dropna(axis=0,how='any') - 删除表中全部为NaN的列
data.dropna(axis=1, how='all') - 删除表中任何含有空值的列
data.dropna(axis=1, how='any') - 删除表中某列含有空值的行
data.dropna(axis=0,subset = ["a", "b"])
#创建data
data = pd.DataFrame({'a': [1, 2, 4, np.nan,7, 9], 'b': ['a', 'b', np.nan, np.nan, 'd', 'e'], 'c': [np.nan, 0, 4, np.nan, np.nan, 5], 'd': [np.nan, np.nan, np.nan, np.nan, np.nan, np.nan]})
输出：
a b c d
0 1.0 a NaN NaN
1 2.0 b 0.0 NaN
2 4.0 NaN 4.0 NaN
3 NaN NaN NaN NaN
4 7.0 d NaN NaN
5 9.0 e 5.0 NaN
1. 删除值全部为空的每一行
data.dropna(axis=0,how='all')
输出：
a b c d
0 1.0 a NaN NaN
1 2.0 b 0.0 NaN
2 4.0 NaN 4.0 NaN
4 7.0 d NaN NaN
5 9.0 e 5.0 NaN
2. 删除表中任何含有空值的行
data.dropna(axis=0,how='any')
输出：
a b c d
3. 删除表中全部为NaN的列
data.dropna(axis=1, how='all')
输出：
a b c
0 1.0 a NaN
1 2.0 b 0.0
2 4.0 NaN 4.0
3 NaN NaN NaN
4 7.0 d NaN
5 9.0 e 5.0
4. 删除表中任何含有空值的列
data.dropna(axis=1, how='any')
输出：
0
1
2
3
4
5
5. 删除表中某列含有空值的行
data.dropna(axis=0,subset = ["a", "b"])
输出：
a b c d
0 1.0 a NaN NaN
1 2.0 b 0.0 NaN
4 7.0 d NaN NaN
5 9.0 e 5.0 NaN
插入行
df.insert_rows(1) 在第一行前插入一行
修改列名
修改数据中的表头名称
#columns={'A':'A1','B':'B1'}表示修改的列修改为什么；rename()表示重命名
df.rename(columns={'Name':'姓名','Age':'年龄'}, inplace = True) #inplace表示修改原表
#输出结果如下：
隐藏列
df.style.hide_index().hide_columns(['XX','XX','XX'])
替换列值
-
df.loc[df['列名']=='原值'] = '替换值'#替换整行
-
df.replace({'列名':{'值1':'替换值1','值2':'替换值2'}}，inplace=True)
把一列中某个值替换为另一个值
-
单个元素替换
将某列为空值，对应另一列的值改为指定值
import pandas as pd
import numpy as np
df = pd.read_csv('match.csv',nrows=10)
df.loc[df['College'].isnull(), 'Salary'] = 'qita'
df
4. 替换某列空值为指定值
import pandas as pd
import numpy as np
df = pd.read_csv('match.csv',nrows=10)
df.loc[df['College'].isnull(), 'College'] = '其他'
df
- 一次性替换空值和非空值
import pandas as pd
import numpy as np
df = pd.read_csv('match.csv',nrows=10)
df['College'] = df['Salary'].isna().map({True:0, False:1})
df
单元格赋值
df[‘A1’]=‘赋值’
一列去除重复值
data=df.drop_duplicates(subset=['去重列'],keep='first',inplace=False)
#subset需要去除重复的列，first保留去重的方式，inplace为False不修改df
隐藏索引
只是隐藏索引，导出文件，仍会导出索引
df.style.hide_index()
设置索引列
将某一列作为索引列：
import pandas as pd
df = pd.read_csv('match.csv')
df.set_index('Name', inplace=True)
df
# 输出结果:
取消索引列，恢复默认索引:
#使用reset_index()取消索引
df.reset_index()
#输出结果：
reset_index() 方法将 DataFrame 索引重置为默认数字索引，在以下情况下特别有用：
- 执行数据整理时——尤其是过滤数据或删除缺失值等预处理操作，会导致较小的 DataFrame 具有不再连续的数字索引
- 当索引应该被视为一个常见的 DataFrame 列时
- 当索引标签没有提供有关数据的任何有价值的信息时
求交集（A∩B）
# 使用 ndarrays 创建2张表，都又共同字段“学号”
# 使用 ndarrays 创建表
dataA = {'学号':['1', '2', '3','4','5'],'姓名':['张三','里斯','王五','查理','roise']}
A = pd.DataFrame(dataA)
dataB = {'学号':['0','1', '2', '3','4','5','7'],'年龄':['20','23','22','34','33','12','28'],'成绩':['无','89','90','67','88','78','90']}
B = pd.DataFrame(dataB)
print(A)
print(B)
输出：
A 学号 姓名
0 1 张三
1 2 里斯
2 3 王五
3 4 查理
4 5 roise
B 学号 年龄 成绩
0 0 20 无
1 1 23 89
2 2 22 90
3 3 34 67
4 4 33 88
5 5 12 78
6 7 28 90
#求交集
A.merge(B) #第一种写法
pd.merge(A, B, on=['学号']) #第二种写法， on表示用哪个字段合并,可以多个
均输出：
学号 姓名 年龄 成绩
0 1 张三 23 89
1 2 里斯 22 90
2 3 王五 34 67
3 4 查理 33 88
4 5 roise 12 78
求A与B不共有的部分（(A+B)-(A∩B)）
求A表与B表的不重复的部分，即（A+B）-（A∩B）
注意：求差集时，若A表与B表的表头一致，则直接返回对应差集，若表头不一致，填充NA
#test
dataA = {'学号':['1', '2', '3','4','5','6'],'年龄':['23','22','34','33','12','27'],'成绩':['89','90','67','88','78','98']}
A = pd.DataFrame(dataA)
dataB = {'学号':['0','1', '2', '3','4','5','7'],'年龄':['20','23','22','34','33','12','28'],'成绩':['无','89','90','67','88','78','90']}
B = pd.DataFrame(dataB)
print(A)
print(B)
输出：
学号 年龄 成绩
0 1 23 89
1 2 22 90
2 3 34 67
3 4 33 88
4 5 12 78
5 6 27 98
学号 年龄 成绩
0 0 20 无
1 1 23 89
2 2 22 90
3 3 34 67
4 4 33 88
5 5 12 78
6 7 28 90
####
res = pd.concat([A,B]).drop_duplicates(subset=['学号'],keep=False)
res
输出：
res 学号 年龄 成绩
5 6 27 98
0 0 20 无
6 7 28 90
求差集（A-B）
现有表A，B，A与B中有重复数据C，要找出A表中有B表中没有的数据，即A与B 的差集（A-B），也即（A-C）
首先用交集求出C：df_a.merge(df_b) 或者 pd.merge(df_a,df_b,on = ['共有字段'])
再用A减去A与B的交集C ：pd.concat([df_a,df_b]).drop_duplicates(subset=['XXX'],keep=False)
注意：A与C最好表头一致，如果不一致，会多出很多列，也可以后面删除
#实例
dataA = {'学号':['1', '2', '3','4','5','6'],'年龄':['23','22','34','33','12','27'],'成绩':['89','90','67','88','78','98']}
B = pd.DataFrame(dataA)
dataB = {'学号':['0','1', '2', '3','4','5','7'],'年龄':['20','23','22','34','33','12','28'],'成绩':['无','89','90','67','88','78','90']}
A = pd.DataFrame(dataB)
print(A)
print(B)
输出：
A 学号 年龄 成绩
0 0 20 无
1 1 23 89
2 2 22 90
3 3 34 67
4 4 33 88
5 5 12 78
6 7 28 90
B 学号 年龄 成绩
0 1 23 89
1 2 22 90
2 3 34 67
3 4 33 88
4 5 12 78
5 6 27 98
#求交集A∩B
C = A.merge(B)
C
输出：
C 学号 年龄 成绩
0 1 23 89
1 2 22 90
2 3 34 67
3 4 33 88
4 5 12 78
chaji = pd.concat([A,C]).drop_duplicates(subset=['学号'],keep=False)
chaji
输出：
A-C 学号 年龄 成绩
0 0 20 无
6 7 28 90
@
@
@
分享：
方法一：
如果现在要把A中有且B中没有的数据，也就是新增部分，加到B表中去，该如何操作呢？
其实就是A与B求差集（A-C）后，再与B求并集
在上面的基础上进行如下操作
res = pd.merge(B,chaji, on=['学号','年龄','成绩'], how='outer')
res
输出：
学号 年龄 成绩
0 1 23 89
1 2 22 90
2 3 34 67
3 4 33 88
4 5 12 78
5 6 27 98
6 0 20 无
7 7 28 90
方法二：A+B后，选择性看是否需要去重
直接求A与B 的并集，如果B中与A中字段数据不一致，需要按字段将数据去重，去重的时候，默认保留上一行，不保证数据准确性，最好A与B的表头一致。
dataA = {'学号':['1', '20', '3','4','5','6'],'年龄':['23','000','34','33','12','27'],'成绩':['89','90','67','88','78','98']}
B = pd.DataFrame(dataA)
dataB = {'学号':['0','1', '20', '3','4','5','7'],'年龄':['20','23','22','34','33','12','28'],'成绩':['无','89','90','67','88','78','90']}
A = pd.DataFrame(dataB)
print(A)
print(B)
输出：可以注意到B中明显有一个错误数据——学号：20
A 学号 年龄 成绩
0 0 20 无
1 1 23 89
2 20 22 90
3 3 34 67
4 4 33 88
5 5 12 78
6 7 28 90
B 学号 年龄 成绩
0 1 23 89
1 20 000 90
2 3 34 67
3 4 33 88
4 5 12 78
5 6 27 98
# A与B求并集
res = pd.merge(B,A, on=['学号','年龄','成绩'], how='outer')
res
输出：
学号 年龄 成绩
0 1 23 89
1 20 000 90
2 3 34 67
3 4 33 88
4 5 12 78
5 6 27 98
6 0 20 无
7 20 22 90
8 7 28 90
#去重，如果数据重复，自动保存上边那条，不保证结果准确性
res1 = res.drop_duplicates(['学号'])
res1
输出：
学号 年龄 成绩
0 1 23 89
1 20 000 90
2 3 34 67
3 4 33 88
4 5 12 78
5 6 27 98
6 0 20 无
8 7 28 90
去除重复行
#原始数据
dataA = {'学号':['0','1','0','1','0','1'],'年龄':['20','23','20','23','20','24'],'成绩':['89','90','89','90','89','96']}
df = pd.DataFrame(dataA)
输出：
学号 年龄 成绩
0 0 20 89
1 1 23 90
2 0 20 89
3 1 23 90
4 0 20 89
5 1 24 96
- 删除所有重复行
注意：必须每一列数据都一样才会删除
---删除所有重复行---
df_res = df.drop_duplicates()
输出:
学号 年龄 成绩
0 0 20 89
1 1 23 90
5 1 24 96
- 删除指定列重复的数据
最后只保留学号去重后，最上边的数据，学号为“1”，年龄为“24”的数据也被删除
---删除“学号”列重复的数据---
res = df.drop_duplicates(['学号'])
res
输出：
学号 年龄 成绩
0 0 20 89
1 1 23 90
- 保留最后出现的值，使用关键字keep=‘last’
保留了学号为“1”，年龄为“24”那行
也可以按照多列去重
---去重后，保留最后一次出现的数据---
res = df.drop_duplicates(['学号'], keep='last')
res
输出：
学号 年龄 成绩
4 0 20 89
5 1 24 96
#按照多列去重
res = df.drop_duplicates(['学号', '年龄'], keep='last')
res
输出：
学号 年龄 成绩
3 1 23 90
4 0 20 89
5 1 24 96
透视
数据透视表是Excel的另一个神器，本质上是一系列的表格重组整合的过程。这里用的案例来自知乎，Excel数据透视表有什么用途：（https://www.zhihu.com/question/22484899/answer/39933218 ）
问题：需要汇总各个区域，每个月的销售额与成本总计，并同时算出利润
通过Excel的数据透视表的操作最终实现了下面这样的效果：
python实现：对于这样的分组的任务，首先想到的就是pandas的groupby，代码写起来也简单，思路就是把刚才Excel的点鼠标的操作反映到代码命令上：
df = pd.read_excel('test.xlsx', sheet_name='销售统计表')
df['订购月份'] = df['订购日期'].apply(lambda x:x.month)
df2 = df.groupby(['订购月份', '所属区域'])[['销售额', '成本']].agg('sum')
df2['利润'] = df2['销售额'] - df2['成本']
df2
Out[]:
销售额 成本 利润
订购月份 所属区域
1 南京 134313.61 94967.84 39345.77
常熟 177531.47 163220.07 14311.40
无锡 316418.09 231822.28 84595.81
昆山 159183.35 145403.32 13780.03
苏州 287253.99 238812.03 48441.96
2 南京 187129.13 138530.42 48598.71
常熟 154442.74 126834.37 27608.37
无锡 464012.20 376134.98 87877.22
昆山 102324.46 86244.52 16079.94
苏州 105940.34 91419.54 14520.80
... ... ...
11 南京 286329.88 221687.11 64642.77
常熟 2118503.54 1840868.53 277635.01
无锡 633915.41 536866.77 97048.64
昆山 351023.24 342420.18 8603.06
苏州 1269351.39 1144809.83 124541.56
12 南京 894522.06 808959.32 85562.74
常熟 324454.49 262918.81 61535.68
无锡 1040127.19 856816.72 183310.48
昆山 1096212.75 951652.87 144559.87
苏州 347939.30 302154.25 45785.05
[60 rows x 3 columns]
也可以使用pandas里的pivot_table函数来实现：
df3 = pd.pivot_table(df, values=['销售额', '成本'], index=['订购月份', '所属区域'] , aggfunc='sum')
df3['利润'] = df3['销售额'] - df3['成本']
df3
pandas的pivot_table的参数index/ columns/ values和Excel里的参数是对应上的（当然，我这话说了等于没说，数据透视表里不就是行/列/值吗还能有啥。）
groupby的功能很全面，内置了很多aggregate函数，能够满足大部分的基本需求，如果你需要一些其他的函数，可以搭配使用apply和lambda。
格式调整
修改列宽
- 只修改Jupyter notebook显示，可以使用Style功能，对导出表无影响
### Test data
df.style.set_properties(subset=['Name'], **{'width': '200px'})
#输出结果如下：
- 修改导出表列宽
python修改导出excel列宽
合并单元格
df.merge_cells(‘A1:F1’)
绘图
因为Excel画出来的图能够交互，能够在图上进行一些简单操作，所以这里用的python的可视化库是plotly，开始之前，首先加载plotly包。
import plotly.offline as off
import plotly.graph_objs as go
off.init_notebook_mode()
柱状图
excel画的：
df = pd.read_excel("plot.xlsx", sheet_name='高等教育入学率')
trace1 = go.Bar(
x=df['国家'],
y=df[1995],
name='1995',
opacity=0.6,
marker=dict(
color='powderblue'
)
)
trace2 = go.Bar(
x=df['国家'],
y=df[2005],
name='2005',
opacity=0.6,
marker=dict(
color='aliceblue',
)
)
trace3 = go.Bar(
x=df['国家'],
y=df[2014],
name='2014',
opacity=0.6,
marker=dict(
color='royalblue'
)
)
layout = go.Layout(barmode='group')
data = [trace1, trace2, trace3]
fig = go.Figure(data, layout)
off.plot(fig)
雷达图
用Excel画的：
df = pd.read_excel('plot.xlsx', sheet_name='政治治理')
theta = df.columns.tolist()
theta.append(theta[0])
names = df.index
df[''] = df.iloc[:,0]
df = np.array(df)
trace1 = go.Scatterpolar(
r=df[0],
theta=theta,
name=names[0]
)
trace2 = go.Scatterpolar(
r=df[1],
theta=theta,
name=names[1]
)
trace3 = go.Scatterpolar(
r=df[2],
theta=theta,
name=names[2]
)
trace4 = go.Scatterpolar(
r=df[3],
theta=theta,
name=names[3]
)
data = [trace1, trace2, trace3, trace4]
layout = go.Layout(
polar=dict(
radialaxis=dict(
visible=True,
range=[0,1]
)
),
showlegend=True
)
fig = go.Figure(data, layout)
off.plot(fig)
画起来比Excel要麻烦得多。
总体而言，如果画简单基本的图形，用Excel是最方便的，如果要画高级一些的或者是需要更多定制化的图形，使用python更合适。
995
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
