常见数据整理包括列处理、行处理、值处理、排序、筛选等。本节在《智能分析ChatGPT+Excel+Python超强组合玩转数据分析》一书的基础上补充更多数据整理方法。使用ChatGPT自动生成代码，能使数据整理工作事半功倍。[大谦Excel，dqexcel点com]
列操作
列操作是表数据最常见的操作，包括添加列、转换列、插入列、根据条件得到新列、改变列名、改变列数据的值、改变列数据的数据类型、改变列数据的显示格式、删除列等操作。
图3-1 通过转换得到新列
图3-1所示工作表中，前3列是给定数据，分别表示一系列圆的圆心横坐标、纵坐标和圆半径。现在要求添加1列，表示圆的面积。计算圆的面积可以有多种方法，下面举4种方法。
如图3-1所示，鼠标单击单元格D1，在公式栏输入“=PY(”进入Python模式，然后输入下面的代码，用数学公式圆面积等于圆周率pi乘以半径的平方得到“面积”列。
import math #导入math包
df=xl("A1:C12",headers=True) #引用数据
df['area']=math.pi*df['r']**2 #计算圆面积并添加新列area
df.area #引用area列单击键盘上的Ctrl+Enter键，得到图3-1中的D列数据。这种方法直接用半径列数据进行计算，通过矢量运算得到结果。
下面使用apply函数结合匿名函数计算得到面积数据。单元格F1中在Python模式下在公式栏输入下面代码：
df['area']=df['r'].apply(lambda x: math.pi*x**2)单击键盘上的Ctrl+Enter键，得到图3-1中的F列数据。注意匿名函数的写法，在lambda后面跟的是变量名称x。
下面使用transform函数结合匿名函数计算得到面积数据。单元格I1中在Python模式下在公式栏输入下面代码：
df['area']=df['r'].transform(lambda x: math.pi*x**2)单击键盘上的Ctrl+Enter键，得到图3-1中的I列数据。
也可以将计算公式写成自定义函数，然后用apply函数或transform函数调用该函数进行计算。图3-2中，单元格D1中在Python模式下在公式栏输入下面代码：
import math
def carea(x):
return(math.pi*x**2)
df=xl("A1:C12",headers=True)
df['area']=df['r7;].apply(carea) #用自定义函数carea进行计算，生成新列area
df.area单击键盘上的Ctrl+Enter键，得到图3-2中的D列数据。
本例首先创建一个carea函数计算圆的面积，然后在主程序中用apply函数调用carea进行计算。
图3-2 用自定义函数计算圆的面积
下面根据已有列数据，用条件判断得到新列。
图3-3中，根据C列圆的半径长度判断圆的大小，判断规则为：
- 半径<=3为小圆
- 3<半径<=5为中圆
- 半径>5为大圆
单元格D1中在Python模式下在公式栏输入下面代码：
df=xl("A1:C12",headers=True)
df['size']=pd.cut(df['r'],[0,3,5,10],labels=['小','中','大']) #用cut函数处理，得到新列size
df.loc[:,'size'] #引用size列，用loc方法引用单击键盘上的Ctrl+Enter键，D1单元格返回一个Series对象。以Excel值的形式进行显示，得到图3-3中的D列数据。这里用到了pandas的cut函数，将排序后的连续数据转换为等级数据。相当于半径数据落在0到3范围内时标记为“小”，落在3-5范围内时标记为“中”，落在5-10范围内时标记为“大”。
图3-3 根据条件判断得到新列
下面修改列的名称，将列名Cx, Cy和r修改为圆心x坐标、圆心y坐标和半径。
如图3-4所示，单元格D1中在Python模式下在公式栏输入下面代码：
import math
df=xl("A1:C12",headers=True)
df['area']=df['r'].transform(lambda x: math.pi*x**2) #匿名函数+transform方法
df['size']=pd.cut(df['r'],[0,3,5,10],labels=['小','中','大'])
#给多个列重命名，用到字典
df.rename(columns={'cx':'圆心x坐标','cy':'圆心y坐标','r':'半径','area':'面积','size':'大小'})单击键盘上的Ctrl+Enter键，D1单元格返回一个DataFrame对象。显示为Excel值，得到图3-4所示工作表中D-H列的数据。代码通过转换得到了area列和size列，并用rename函数将它们的名称改为面积和大小。columns参数的值用一个字典表示，字典中每个键值对表示修改前后的列名。
图3-4 修改列名
下面修改列的数据类型。将转换得到的area列的数据类型修改为字符串类型。
如图3-5所示，单元格D1中在Python模式下在公式栏输入下面代码：
import math
df=xl(‘A1:C12’,headers=True)
df[‘area’]=df[‘r’].transform(lambda x: math.pi*x**2)
df[‘area’]= df[‘area’].astype(str) #改变area列的数据类型为字符串类型单击键盘上的Ctrl+Enter键，D1单元格返回一个Series对象。以Excel值的形式显示，得到图3-5中D列的数据。使用Series对象的astype函数将area列数据转换为字符串类型。
图3-5 修改列的数据类型
行操作
常见的行操作包括直接添加新行、通过计算得到新行、插入行、修改行名、修改行数据和删除行等。下面结合实例介绍通过计算得到新行。
图3-6所示工作表中前3列为给定数据，表示不同水果的销量和销售金额。现在要求在底部添加总计行，计算水果的总销量和总销售金额。
单元格E1中在Python模式下在公式栏输入下面代码：
df=xl("A1:C8",headers=True)
df=df.set_index('商品名称') #将“商品名称”列设置为索引列
df.loc['总计']=df.sum() #添加“总计”行，用sum方法对各列求和
df #输出df单击键盘上的Ctrl+Enter键，E1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-6中单元格区域E1:G10内的数据。使用DataFrame对象的set_index方法将“商品名称”列设置为索引列，该列不参与计算。用DataFrame对象的sum方法计算新的“总计”行的值。
图3-6 增加行
数据排序
图3-7中的工作表给出了一些人员的工资数据。现在要求将实发工资和应发工资作为第一排序条件和第二排序条件对数据进行升序排列。
单元格K1中在Python模式下在公式栏输入下面代码：
df=xl("A1:I11",headers=True)
df=df.sort_values(by=['实发工资', '应发工资'], ascending=True) #多条件排序单击键盘上的Ctrl+Enter键，K1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-7中K-T列的数据。用DataFrame对象的sort_values方法进行排序，用方法的by参数指定排序依据，用ascending参数指定排序方向，值为True时表示升序排序，值为False时表示降序排列。
观察图3-7工作表中K-T列数据，可见数据已经按照实发工资进行了升序排列。当实发工资相同时，按照第二条件应发工资进行升序排列。
图3-7 数据排序
数据筛选
图3-8所示工作表中A1:C11中为给定数据，是一些人员的年龄和学历数据。现在要求从中筛选出年龄大于25岁，学历为本科的人员数据。
单元格E1中在Python模式下在公式栏输入下面代码：
df=xl("A1:C11",headers=True)
df[(df['年龄']>25) & (df['学历']=='本科')] #用布尔索引实现筛选单击键盘上的Ctrl+Enter键，E1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-8中E1:H5内的满足要求的数据。本例使用DataFrame对象的布尔索引来实现数据筛选。
图3-8 数据排序
数据排名
生活中经常遇到排名问题，比如根据学生成绩排名、根据运动员运动成绩排名等。排名的方法有多种，如中国式排名、美国式排名等。
图3-9所示工作表中A1:B9中是一些同学的考试分数。现在要求根据分数对同学进行排名。单元格D1中在Python模式下在公式栏输入下面代码：
df=xl("A1:B9",headers=True)
#用rank方法排名，排名数字转换为整数，避免出现类似2.00的情况
df['排名']=(100-df['分数']).rank(method='min').astype(int)
df=df.sort_values(by=['排名'], ascending=True)单击键盘上的Ctrl+Enter键，D1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-9中D1:G9内的数据。用Series对象的rank方法进行排名。本例中method参数的值为’min’，称为中国式排名。
首先按分数从大到小排序，分数最多的排第一，每个同学都有一个唯一的序号。不同的排名方法，区别在于怎么处理分数相同的情况。当method参数的值为’min’，取相同分数的最小序号作为他们的共同名次，下一名取其当前序号。如本例中有2个92分，对应序号分别为3和4，取其最小值，所以两位同学并列第3名。下一个名次取他的当前序号，即5。
注意，默认时会根据分数从小到大排名，分数最少的排第一。所以，用100减去当前分数后进行排名，得到正确结果。
图3-9 中国式排名
当method参数的值为’average’时，分数相同的同学的名次取它们序号的平均值，下一名次取其当前序号。此时称为美国式排名。单元格D1中在Python模式下在公式栏输入下面代码：
图3-10 美国式排名
df=xl("A1:B9",headers=True)
df['排名']=(100-df['分数']).rank(method='average').astype(int)
df=df.sort_values(by=['排名'], ascending=True)单击键盘上的Ctrl+Enter键，D1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-10中D1:G9内的数据。可见，分数都为92的同学现在名次都为3.5，即他们序号3和4的平均值。下一名次取其当前序号，即5。
当method参数的值为’max’时，分数相同的同学的名次取它们序号的最大值，下一名次取其当前序号。单元格D1中在Python模式下在公式栏输入下面代码：
df=xl("A1:B9",headers=True)
df['排名']=(100-df['分数']).rank(method='max').astype(int)
df=df.sort_values(by=['排名'], ascending=True)单击键盘上的Ctrl+Enter键，D1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-11中D1:G9内的数据。可见，分数都为92的同学现在名次都为4，即他们序号3和4的最大值。下一名次取其当前序号，即5。
图3-11 最大值排名
当method参数的值为’first’时，分数相同的同学的名次按他们在原始数据中出现的先后顺序排名，下一名次取其当前序号。单元格D1中在Python模式下在公式栏输入下面代码：
df=xl("A1:B9",headers=True)
df['排名']=(100-df['分数']).rank(method='first').astype(int)
df=df.sort_values(by=['排名'], ascending=True)单击键盘上的Ctrl+Enter键，D1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-12中D1:G9内的数据。可见，分数都为92的两位同学中，在原始数据中先出现的排第3，后出现的排第4。下一名次取其当前序号，即5。最后整个排名名次按顺序排列。
图3-12 首值排名
当method参数的值为’dense’时，分数相同的同学的名次取他们序号的最小值，跟中国式排名相同。不同的是，下一名次不取其当前序号，而是紧接着前面的共同排名取。例如，假设前面两个同学的共同排名为第3名，则下一名次取4，紧接着取。这种排名称为紧凑排名。单元格D1中在Python模式下在公式栏输入下面代码：
df=xl("A1:B9",headers=True)
df['排名']=(100-df['分数']).rank(method='dense').astype(int)
df=df.sort_values(by=['排名'], ascending=True)单击键盘上的Ctrl+Enter键，D1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-13中D1:G9内的数据。可见，分数都为92的两位同学，共同名次取他们序号的最小值3。下一名次紧接着取，即取4。
图3-13 紧凑排名
数据随机抽样
数据随机抽样是从一个给定的数据集中随机抽取给定数量的数据作为新的样本。每次抽取的数据可以是整行数据，也可以是单列中的单个数据。
图3-14所示工作表中A-G列给出了107个订单的信息，现在要求从中随机抽取10个订单的信息。单元格J1中在Python模式下在公式栏输入下面代码，用DataFrame对象的sample方法随机抽取，参数n指定抽取的数据行数。
df=xl("A1:E108",headers=True)
df.sample(n=10)单击键盘上的Ctrl+Enter键，J1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-14中J列及后面各列数据。
图3-14 数据随机抽样
如果只从某列数据中随机抽取，可以先通过索引得到该列，然后用Series对象的sample方法进行抽取。下面从“订单号”列中进行随机抽取。单元格H1中在Python模式下在公式栏输入下面代码：
df=xl("A1:E108",headers=True)
df['订单号'].sample(n=10)单击键盘上的Ctrl+Enter键，H1单元格返回一个Series对象。以Excel值的形式显示，得到图3-14中H列的数据。
宽表转长表
图3-15所示工作表中，A-C列列出了获得不同奖项的运动员的名字。现在要求将该表转换为E-F列所显示的样子，即宽表转成长表。
单元格E1中在Python模式下在公式栏输入下面代码：
df=xl("A1:C6",headers=True)
df.melt(var_name='奖项', value_name='球员')单击键盘上的Ctrl+Enter键，E1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-15中E列和F列的数据。代码中使用了DataFrame对象的melt方法实现转换。
图3-15 宽表变长表
爆炸
一个Series对象中的元素包含列表、元组、字符串和NumPy数组等对象时，通过爆炸操作可以将Series对象转换为元素为单一基本数据类型数据的Series对象。Python中用Series对象的explode方法实现爆炸。
图3-16 数据爆炸
在图3-16所示工作表中，单元格B1中在Python模式下在公式栏输入下面代码：
s=pd.Series([(11,45),'Python',[],[3, 4],np.array([1,2])])
s.explode()单击键盘上的Ctrl+Enter键，E1单元格返回一个Series对象。以Excel值的形式显示，得到图3-16中B列和C列的数据。B列是索引列，C列是爆炸后得到的新Series对象的值。
合并多个表的数据
Excel内置Python可以在一个工作表中实现对其他工作表对象的引用。这样，可以用Excel内置Python实现多个表数据的合并。Python中合并表数据的方法有多种，包括pandas包的merge函数、DataFrame对象的concat方法、join方法等。
本例用pandas包的merge函数合并图3-17所示的3个工作表中的数据。关联3个表的变量是“姓名”。最后实现将学生的语文、数学和英语成绩合并到一张表中。
(a) (b) (c)
图3-17 给定3个工作表中的数据
在如图3-18所示的Sheet4工作表中，单元格A1中在Python模式下在公式栏输入下面代码：
#将3个表的数据导入到3个DataFrame
df1=xl("Sheet1!A1:B9",headers=True)
df2=xl("Sheet2!A1:B9",headers=True)
df3=xl("Sheet3!A1:B9",headers=True)
#合并前两个DataFrame，键为“姓名”，采用外联接
merged_df=pd.merge(df1,df2,on='姓名',how='outer')
#在上步基础上合并第3个DataFrame
merged_df=pd.merge(merged_df,df3,on='姓名',how='outer')单击键盘上的Ctrl+Enter键，A1单元格返回一个DataFrame对象。以Excel值的形式显示，得到图3-18中Sheet4工作表中的数据。merge函数的how参数可以有其他设置选项，大家可以自己进行测试。
图3-18 合并多个工作表中的数据