Excel 是数据处理最常用的工具,pandas 是数据分析的利器。那么,Excel 一些常见的操作, pandas 如何实现呢?网上有两篇挺有意思的文章,对此做了详细介绍。我结合自己在学习博文过程中的理解,总结成文。
本篇介绍 pandas 的 DataFrame
对列 (Column) 的处理方法。示例数据请从这里下载。
增加计算列
pandas 的 DataFrame
,每一行或每一列都是一个序列 (Series
)。比如:
import pandas as pd
df1 = pd.read_excel('http://pbpython.com/extras/excel-comp-data.xlsx');
此时,用 type(df1['city']
,显示该数据列(column)的类型是 pandas.core.series.Series
。理解每一列都是 Series
非常重要,因为 pandas 基于 numpy,对数据的计算都是整体计算。深刻理解这个,才能理解后面要说的诸如 apply()
函数等。
如果列名 (column name)没有空格,则列有两种方式表达:
df1['city']
df1.city
如果列名有空格,或者创建新列(即该列不存在,需要创建,第一次使用的变量),则只能用第一种表达式。
假设我们要对三个月的数据进行汇总,可以使用下面的方法。实际上就是创建一个新的数据列:
# 由于是创建,不能使用 df.Total
df1['Total'] = df1['Jan'] + df1['Feb'] + df1['Mar']
df1['Jan']
到 df1['Mar']
都是 Series
,所以使用 +
号,可以得到三个 Series
对应位置的数据合计。
当然,也可以用下面的方式:
df1['total'] = df1.Jan + df1.Feb + df1.Mar
增加条件计算列
假设现在要根据合计数 (Total 列),当 Total 大于 200,000 ,类别为 A,否则为 B。在 Excel 中实现用的是 IF
函数,但在 pandas 中需要用到 numpy 的 where
函数:
df1['category'] = np.where(df1['total'] > 200000, 'A', 'B')
在指定位置插入列
上面方法增加的列,位置都是放在最后。如果想要在指定位置插入列,要用 dataframe.insert()
方法。假设我们要在 state
列后面插入一列,这一列是 state
的简称 (abbreviation)。在 Excel 中,根据 state 来找到 state 的简称 ,一般用 VLOOKUP
函数。我们用两种方法来实现,第一种方法,简称来自 Python 的 dict。
数据来源:
state_to_code = {"VERMONT": "VT", "GEORGIA": "GA", "IOWA": "IA", "Armed Forces Pacific": "AP", "GUAM": "GU",
"KANSAS": "KS", "FLORIDA": "FL", "AMERICAN SAMOA": "AS", "NORTH CAROLINA": "NC", "HAWAII": "HI",
"NEW YORK": "NY", "CALIFORNIA": "CA", "ALABAMA": "AL", "IDAHO": "ID", "FEDERATED STATES OF MICRONESIA": "FM",
"Armed Forces Americas": "AA", "DELAWARE": "DE", "ALASKA": "AK", "ILLINOIS": "IL",
"Armed Forces Africa": "AE", "SOUTH DAKOTA": "SD", "CONNECTICUT": "CT", "MONTANA": "MT", "MASSACHUSETTS": "MA",
"PUERTO RICO": "PR", "Armed Forces Canada": "AE", "NEW HAMPSHIRE": "NH", "MARYLAND": "MD", "NEW MEXICO": "NM",
"MISSISSIPPI": "MS", "TENNESSEE": "TN", "PALAU": "PW", "COLORADO": "CO", "Armed Forces Middle East": "AE",
"NEW JERSEY": "NJ", "UTAH": "UT", "MICHIGAN": "MI", "WEST VIRGINIA": "WV", "WASHINGTON": "WA",
"MINNESOTA": "MN", "OREGON": "OR", "VIRGINIA": "VA", "VIRGIN ISLANDS": "VI", "MARSHALL ISLANDS": "MH",
"WYOMING": "WY", "OHIO": "OH", "SOUTH CAROLINA": "SC", "INDIANA": "IN", "NEVADA": "NV", "LOUISIANA": "LA",
"NORTHERN MARIANA ISLANDS": "MP", "NEBRASKA": "NE", "ARIZONA": "AZ", "WISCONSIN": "WI", "NORTH DAKOTA": "ND",
"Armed Forces Europe": "AE", "PENNSYLVANIA": "PA", "OKLAHOMA": "OK", "KENTUCKY": "KY", "RHODE ISLAND": "RI",
"DISTRICT OF COLUMBIA": "DC", "ARKANSAS": "AR", "MISSOURI": "MO", "TEXAS": "TX", "MAINE": "ME"}
如果我们想根据 dict 的 key
找到对应的值,可以使用 dict.get()
方法,这个方法在找不到 key 的时候,不会抛出异常,只是返回 None。比如
state_to_code.get('TEXAS') # 返回 TX
state_to_code.get('TEXASS') # 返回 None
dict.get()
方法参数为 key,是一个标量值。我们并不能像下面这样把整列都传给这个方法,比如下面这样:
df1['abbrev'] = state_to_code.get(df1['state'])
所以我们需要先构造一个 Series (abbrev)
,然后把 abbrev
赋值给 df1['abbrev']
:
abbrev = df1['state'].apply(lambda x: state_to_code.get(x.upper()))
df1['abbrev'] = abbrev # 在后面插入列
df1.insert(6, 'abbr', abbrev) # 在指定位置插入列
apply()
函数值得专门写一篇,暂且不细说。
Vlookup 函数功能实现
实现类似 Excel 的 VLookup
功能,可以用 dataframe.merge()
方法。为此,需要将 state_to_code
这个 dict
的数据加载到 DataFrame
中。这里提供两种方法。
方法1: 把数据放在 excel 工作表中,然后读取 Excel 文件加载。数据如下:
excel_file = pd.ExcelFile('excel-comp-data.xlsx')
df_abbrev = pd.read_excel(excel_file, sheetname = 'abbrev')
df2 = df1.merge(df_abbrev, on='state') # 类似数据库的 inner join,不匹配数据不会显示
VLookup
函数根据位置来匹配,merge()
方法根据列名来匹配。因为上面语句中没有指定连接类型,不匹配的记录不会显示。如果需要将 df1
的数据全部显示出来,需要指定 merge()
方法的 how
参数:
df3 = df1.merge(df_abbrev, on='state', how='left') # 类似数据库的 left join
方法2:直接将 state_to_code
加载到 DataFrame
。但因为 state_to_code
全部是标量值 (scalar values),方法有一点不同,如下:
# 将 state_to_code 直接加载到 DataFrame
abbr2 = pd.DataFrame(list(state_to_code.items()), columns=['state', 'abbr'])
参考
Common Excel Tasks Demonstrated in Pandas
Common Excel Tasks Demonstrated in Pandas - Part 2