用Python轻松搞定Excel中的20个常用操作-DataFrame(1)

冷落清秋节

已于 2024-03-10 17:18:30 修改

阅读量2.9k

点赞数 2

分类专栏： python语言编程文章标签： python

于 2020-07-24 11:26:08 首次发布

本文链接：https://blog.youkuaiyun.com/u011731135/article/details/107516157

版权

python语言编程专栏收录该内容

1 篇文章

订阅专栏

本文详细介绍使用Python的pandas库和Excel进行数据处理的方法，包括数据读取、生成、计算、修改、统计、抽样、查找、可视化及存储等。对比两种工具在数据处理上的异同，提供具体代码实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

明人不说暗话，此篇来源于“AI科技大本营”。重新整理一方面是巩固一下python知识，一方面是学习一下excel，毕竟用python要比excel熟练一些。

Excel与Python都是数据分析中常用的工具，本文将使用(Excel)+代码(Python)的方式来演示这两种工具是如何实现数据的读取、生成、计算、修改、统计、抽样、查找、可视化、存储等数据处理中的常用操作！

主要使用的包是pandas，数据类型为dataframe 、series。

1. 读取本地excel数据

（1）excel：文件-打开-选取本地文件，即可。

（2）python:使用pandas读入格式为dataframe

df=pd.read_excel('./xxx.xlsx')

2. 数据生成，以生成10*2的0—1均匀分布随机数矩阵为例，在Excel中需要使用rand()函数生成随机数，并手动拉取指定范围。

（1）excel:

可以在最前面插入一行，添加两列的列名

（2）python

df=pd.DataFrame(np.random.rand(10,2),columns=['col1','col2'])
print(df)
#打印结果
#        col1      col2
# 0  0.641778  0.872395
# 1  0.194709  0.242921
# 2  0.051777  0.500563
# 3  0.055431  0.778999
# 4  0.245488  0.786610
# 5  0.727214  0.894453
# 6  0.752469  0.840307
# 7  0.626647  0.009278
# 8  0.755790  0.368945
# 9  0.514407  0.000967

关于numpy.random.rand()的说明：
Create an array of the given shape and populate it with random samples from a uniform distribution over ``[0, 1)``.

3. 数据保存到本地

（1）excel：文件-保存\另存为-编辑名称-确定

（2）python：可以保存成xlsx或者csv文件

df.to_excel('./df.xlsx',
            sheet_name='sheet1',  # 指定保存的sheet名称
            index=True,  # 是否有索引
            header=False,  # 是否有列名
            encoding='utf-8'  # 数据编码格式，一般含有中文的数据表为防止中文乱码，指定为utf-8
            )
df.to_csv('./df.csv',
          index=False,  # 是否有索引
          header=True,  # 是否有列名
          encoding='utf-8'  # 数据编码格式
          )

没有列名，有index的excel：

4. 按照指定要求筛选数据

（1）excel：选中一列-筛选-数字筛选-选择“大于”、‘小于’、‘大于或等于’等编辑数值-确定。可以同时选中多列，对每一列设置筛选条件。

（2）python:如果使用多个条件的筛选只需要使用&(并)与|(或)操作符实现

res=df[df['col1']>0.3]
res=df[(df['col1']>0.3) & (df['col2']<0.9)]

5. 划分数据区间，并将结果插入新的列

（1）excel：

在Excel中我们可以将光标放在指定位置并右键增加一行/列，当然也可以在添加时对数据进行一些计算，比如我们就可以使用IF函数(=IF(B2>0.5,"高","低"))，将col2大于0.5的设为高，低于0.5的设为低，添加一列在最后

（2）python

使用cut: 可以分割多个区间

# Use `cut` when you need to segment and sort data values into bins. This function is also useful for going from a continuous variable to a categorical variable.

bins = [0, 0.5, max(df['col2'])]  # 单调递增的区间
group_names = ['低', '高']
df['col3'] = pd.cut(df['col2'],#数据对象
                    bins=bins,  # The computed or specified bins.
                    labels=group_names
                    )
print(df)
#        col1      col2 col3
# 0  0.982312  0.692577    高
# 1  0.400765  0.603758    高
# 2  0.682921  0.074459    低
# 3  0.511042  0.541177    高
# 4  0.308394  0.813810    高
# 5  0.940179  0.610750    高
# 6  0.224397  0.565077    高
# 7  0.746176  0.285790    低
# 8  0.496694  0.066871    低
# 9  0.720848  0.766405    高


# 4个分位数，3个区间，3个标签
print(pd.cut(np.array([1, 7, 5, 8, 6, 3]), bins=[0, 3, 7, 10], labels=["bad", "medium", "good"]))
# 指定区间个数，3个标签
print(pd.cut(np.array([1, 7, 5, 8, 6, 3]), bins=3, labels=["bad", "medium", "good"]))
# [bad, good, medium, good, good, bad]
# Categories (3, object): [bad < medium < good]

二分类的问题，使用if要更简单。

df['col3']=df['col2'].map(lambda x: '高' if x>0.5 else '低')
print(df)
#        col1      col2 col3
# 0  0.926839  0.935780    高
# 1  0.101346  0.777723    高
# 2  0.025822  0.959141    高
# 3  0.679671  0.014903    低
# 4  0.719962  0.500163    高
# 5  0.927373  0.522112    高
# 6  0.803613  0.046791    低
# 7  0.758018  0.502218    高
# 8  0.323275  0.088631    低
# 9  0.925016  0.030785    低

6. 删除数据列

（1）excel：选中列-右键-删除

（2）python

del df['col2'] #不需要赋值
print(df)

7. 数据排序

（1）excel: 选中一列-排序-升序、降序

（2）python

df['col3'] = [1, 1, 2, 2, 3, 3, 5, 5, 6, 3]

df.sort_values(by=['col3', 'col2'],  # 可以是多列排序。首先根据col3排序，再根据col2排序
               ascending=True,  # 升序
               inplace=True  # 更新df，不需要再赋值
               )
print(df)
#        col1      col2  col3
# 0  0.905416  0.036492     1
# 1  0.917556  0.252902     1
# 2  0.325744  0.639247     2
# 3  0.911594  0.886373     2
# 4  0.637318  0.192728     3
# 9  0.121061  0.297367     3
# 5  0.603966  0.659399     3
# 7  0.748784  0.345880     5
# 6  0.928521  0.978531     5
# 8  0.143519  0.897249     6

8. 数据填充

（1）excel：选中列-查找-定位-空值-填充（选择向上填充、向下填充、向左填充、向右填充）

这个好像不能对定位的所有空值统一填充

（2）python：

判断数据为空

df = pd.DataFrame(np.random.rand(10, 2), columns=['col1', 'col2'])
df['col3'] = [1, 1, 2, 2, np.nan, 3, None, 5, 6, 3] #空值表示：可以用None，也可以用np.nan
df['col4'] = [3, None, 4, 2, 5, 3, 5, 7, None, None]

print(df)
#        col1      col2  col3  col4
# 0  0.966256  0.808913   1.0   3.0
# 1  0.080497  0.218030   1.0   NaN
# 2  0.847410  0.774164   2.0   4.0
# 3  0.345594  0.919620   2.0   2.0
# 4  0.405093  0.781240   NaN   5.0
# 5  0.100573  0.764298   3.0   3.0
# 6  0.095014  0.064710   NaN   5.0
# 7  0.754620  0.065826   5.0   7.0
# 8  0.882961  0.181004   6.0   NaN
# 9  0.900389  0.686134   3.0   NaN

print(df.isnull().sum()) #统计各列所包含的空值个数
# col1    0
# col2    0
# col3    2
# col4    3
# dtype: int64

print(df['col3'].isnull().sum()) #统计1列所包含的空值个数

print(df['col3'].isnull().all()) #判断是否一列都是空值
print(df.isnull().all())#判断每一列是否都是空值

print(df['col3'].isnull().any()) #判断是否一列包含空值
print(df.isnull().any())#判断每一列是否都包含空值

print(pd.isnull(df.iloc[1,3]))#判断一个元素是否为空
print(pd.isnull(df['col3']))#判断一列中每个元素是否为空

数据填充

# 用df中的值填充：
# pad / ffill: propagate last valid observation forward to next valid,从前往后
# backfill / bfill: use NEXT valid observation to fill gap ，从后往前填充
df.fillna(value=None,
          method='ffill',  # 'backfill', 'bfill', 'pad', 'ffill', None}
          axis=1,  # =1按列分前后，=0按行分前后
          inplace=True  # 替换源df
          )

# 用固定值填充：
df.fillna(value=100,
          method=None,
          axis=None,
          inplace=True  # 替换源df
          )

# 可以指定不同的列填充不同的值
values = {'col3': 1.5, 'col4': 3.5}
df.fillna(value=values,
          limit=2,  # 指定填充前几个空值
          inplace=True)
print(df)

使用插值

# 使用插值填充空值
df.interpolate(method='linear',  # 一般线性插值
               axis=1,  # * 0: fill column-by-column, * 1: fill row-by-row
               limit=3,  # 填充个数
               inplace=True)
print(df)
# 关于axis=1，0在不同的方法中意义不一样

指定位置填充

import pandas as pd
import numpy as np

#指定位置填充
d=pd.DataFrame(data=[[1,'a'],[2,'b'],[3,'c']],columns=['num','name'])
print(d)
#    num name
# 0    1    a
# 1    2    b
# 2    3    c
d.loc[d.num==1,'name']=np.nan
print(d)
#    num name
# 0    1  NaN
# 1    2    b
# 2    3    c
d.iloc[1,1]='m'
print(d)
#    num name
# 0    1  NaN
# 1    2    m
# 2    3    c

9. 数据去重

（1）excel：选中一列-数据-删除重复项

（2）python

# 根据所有列判断去重
df.drop_duplicates(subset=None,  # column label
                   keep='first',  # {'first', 'last', False}, default 'first'
                   inplace=True)
# 根据指定列去重
df.drop_duplicates(subset=['col3', 'col4'],  # column label
                   keep='first',  # {'first', 'last', False}, default 'first'
                   inplace=True)

10. 修改数据格式

（1）excel：选中一列数据-右键-设置单元格格式。常用设置日期、时间格式、数据小数位、数字转文本、分数。

（2）python

设置时间、日期格式，日期转字符串、字符串转日期。还可以实现timedelta时间加减。

from datetime import datetime
df = pd.DataFrame(np.random.rand(5, 2), columns=['col1', 'col2'])
df['time_str'] = ['2020-07-01','2020-07-02','2020-07-03','2020-07-04','2020-07-05']  # 空值表示：可以用None，也可以用np.nan
print(df.dtypes)
# col1        float64
# col2        float64
# time_str     object

#字符串转日期
df['time']=pd.to_datetime(df['time_str'])
#字符串转日期
df['time']=df['time_str'].map(lambda x:datetime.strptime(x,'%Y-%m-%d'))
print(df.dtypes)
print(df)
# col1               float64
# col2               float64
# time_str            object
# time        datetime64[ns]

#日期转字符串
df['col3']=df['time'].dt.strftime('%Y-%m-%d')#只能在日期格式中使用.dt
#日期转字符串
df['col3']=df['time'].map(lambda x:datetime.strftime(x,'%Y-%m-%d'))
print(df.dtypes)
#col1               float64
# col2               float64
# time_str            object
# time        datetime64[ns]
# col3                object

数字转文本，保留小数位等都可以用lambda实现。

字符串格式转换

df = pd.DataFrame()
df['col3'] = ['This is ','a','demo','None','None']  # 空值表示：可以用None，也可以用np.nan
df['col4']=df['col3'].str.lower()
df['col5']=df['col3'].str.upper()
df['col6']=df['col3'].str.title()
print(df)
#        col3      col4      col5      col6
# 0  This is   this is   THIS IS   This Is 
# 1         a         a         A         A
# 2      demo      demo      DEMO      Demo
# 3      None      none      NONE      None
# 4      None      none      NONE      None

11. 交换指定列

python：通过修改列号实现

df = pd.DataFrame(np.random.rand(5, 3), columns=['col1', 'col2','col3'])
print(df)
colms=df.columns[[0,2,1]] #本质上df.columns是一个arr
df1=df[colms]
print(df1)

data=np.array(['col1','col2','col3'])
data=data[[0,2,1]] #arr可以采用这种方式交换
print(data)
# ['col1' 'col3' 'col2']

#直接指定获取哪些列
df2=df[['col1','col3']]
print(df2)

12.合并多列

（1）excel ：=CONCATENATE(A9,G9,B9) #字符、数值均可

（2）python：只能拼接字符串

df = pd.DataFrame(np.random.rand(5, 3), columns=['col1', 'col2', 'col3'])
df['col4'] = ['a', 'b', 'c', 'd', 'e']
print(df)
df['col5'] = df['col1'].map(lambda x: str(x)) + df['col4']
print(df)

13. 将一列拆分成多列

（1）excel：选中-数据-分列（按数据类型：文本、数值，一般以分隔符进行分割）

（2）python：以分隔符划分，或根据固定长度

df = pd.DataFrame()
df['col4'] = ['a,b', 'b,c', 'c,d', 'd,e', 'e,f']
print(df)
#   col4
# 0  a,b
# 1  b,c
# 2  c,d
# 3  d,e
# 4  e,f

df['col5'] = df['col4'].map(lambda x: x.split(','))

df['col5'] = df['col4'].str.split(',')
print(df)
#   col4    col5
# 0  a,b  [a, b]
# 1  b,c  [b, c]
# 2  c,d  [c, d]
# 3  d,e  [d, e]
# 4  e,f  [e, f]

14. 分组计算

（1）excel：根据col3分组，求col2和。首先根据col3排序，选中-数据-分类汇总（指定分组字段，指定汇总项，指定方式求和、计数、最大、最小等）

（2）python

df = pd.DataFrame(np.random.rand(5, 2), columns=['col1', 'col2'])
df['col3'] = [1, 4, 3, 1, 1]
df['col4'] = [1, 2, 3, 1, 2]
print(df)

# 根据一列分组
df1 = df.groupby('col4')['col1'].mean().reset_index()
# 根据多列分组
df2 = df.groupby(['col4', 'col3'])['col1'].mean().reset_index()
print(df1)

import pandas as pd
import numpy as np

#apply
d=pd.DataFrame([[1,1],[1,2],[2,3],[2,4],[3,5]],columns=['num','name'])
print(d)
#    num  name
# 0    1     1
# 1    1     2
# 2    2     3
# 3    2     4
# 4    3     5
d1=d.apply(lambda x: np.mean(x),axis=0) #对行求均值
print(d1)
# num     1.8
# name    3.0
# dtype: float64

def func2(x):
    return np.mean(x)
d2=pd.DataFrame(d.apply( func2,axis=0) )#对行求均值
print(d2)
#         0
# num   1.8
# name  3.0

d3=d.groupby(['num'])['name'].mean().reset_index()
print(d3)

#聚合apply
def func3(x,n): #x是一个df
    x['new']=np.mean(x['name'])+x['name']*n
    return x
d4=d.groupby(['num']).apply(func3,n=2) #返回对d本身的操作#可传递参数
print(d4)

#返回多个值或其他复杂操作，可以采用for
for i,groupi in d.groupby('num'):
    groupi...

15. 对数据进行一些计算

（1）excel统计某列大于阈值的个数：=COUNTIFS(B2:B11,">=0.5")

(2) python

print(df[df['col2']>0.5].shape[0])

16. 对数据进行统计计算

（1）excel:对一列数据进行求和、求平均值、最大值、最小值、均值、标准差等，或使用相应的公式。

（2）python：使用describe可以返回的一些统计值：计数、均值、标准差、最小、最大、分位数

df = pd.DataFrame(np.random.rand(5, 2), columns=['col1', 'col2'])
df['col3'] = [1, 4, 3, 1, 1]
df['col4'] = [1, 2, 3, 1, 2]
print(df.describe())
#            col1      col2      col3     col4
# count  5.000000  5.000000  5.000000  5.00000
# mean   0.687754  0.692217  2.000000  1.80000
# std    0.245867  0.267455  1.414214  0.83666
# min    0.283134  0.287656  1.000000  1.00000
# 25%    0.672184  0.586431  1.000000  1.00000
# 50%    0.715304  0.755626  1.000000  2.00000
# 75%    0.883402  0.854979  3.000000  2.00000
# max    0.884744  0.976392  4.000000  3.00000

17. 数据直方图

python:

plt.hist(df['col1'])
plt.show()

18. 数据采样

python:

df.sample(20) # 抽取20

19. 数据透视表

（1）excel：插入-数据透视表（选择数据区域，新工作表）

根据需求将3列分别拖拽进：图例（横轴），轴（纵轴），值（可下拉-值字段设置：均值、求和等）

在新数据表中得到结果：

（2）python：注意pivot没有aggfunc,行列不能有重复值。

import pandas as pd
import numpy as np

df=pd.read_excel('C:\\Users\\xmm\\Desktop\\demo.xlsx')
print(df)

df1=df.pivot_table(index='col3',columns='col2',values='col1',aggfunc='mean') #{'mean', 'sum', 'count'}
print(df1)
# col2         1         2         3
# col3
# 1     0.730405       NaN       NaN
# 2     0.167546       NaN       NaN
# 3     0.516227       NaN       NaN
# 4     0.791348       NaN       NaN
# 6          NaN  0.198996       NaN
# 7          NaN  0.061514       NaN
# 8          NaN       NaN  0.568059
# 10         NaN       NaN  0.202170

#可以选择填充值 #df in pandas.core.frame
df1=df.pivot_table(index='col3',columns='col2',values='col1',aggfunc='mean'
                   ,fill_value=0) #{'mean', 'sum', 'count'}
print(df1)
# col2         1         2         3
# col3                              
# 1     0.730405  0.000000  0.000000
# 2     0.167546  0.000000  0.000000
# 3     0.516227  0.000000  0.000000
# 4     0.791348  0.000000  0.000000
# 6     0.000000  0.198996  0.000000
# 7     0.000000  0.061514  0.000000
# 8     0.000000  0.000000  0.568059
# 10    0.000000  0.000000  0.202170

20. 使用vlookup查找数据

(1)excel : =VLOOKUP() #(查找值，数据表，列序数)

（2）python:可以直接merge

df1=pd.DataFrame()
df1['col1']=[1,2,3,4,5,6,7,8,9]
df1['col2']=['a','b','c','d','e','f','g','h','j']
df2=pd.DataFrame(np.array([3,4,5,6]).reshape(-1,1),columns=['col1']) #df2有一列数据
print(df1)
print(df2)
res=pd.merge(left=df2,right=df1,on='col1')
print(res)
#    col1 col2
# 0     3    c
# 1     4    d
# 2     5    e
# 3     6    f

写在最后

发现一个将python和excel结合使用的名为 Grid studio 的开源项目，它是一个基于网页的表格应用，完全结合了 Python 和 Excel 的优势。感动：Python 与 Excel 终于在一起了项目地址：https://github.com/ricklamers/gridstudio