Python——处理Excel的10个常用操作

最新推荐文章于 2024-09-21 17:07:03 发布

bug鸭陆

最新推荐文章于 2024-09-21 17:07:03 发布

阅读量2.6k

点赞数 22

文章标签： python excel 开发语言

本文链接：https://blog.csdn.net/GLB_0627/article/details/140637889

版权

在这里插入图片描述

1. 读取 Excel 文件

使用 pandas 库读取 Excel 文件是一种常见的数据处理操作，它能够快速加载 Excel 中的表格数据，并将其转换为数据框架（DataFrame）。以下是一个简单的示例代码，演示如何使用 pandas 读取 Excel 文件。

import pandas as pd      # 读取 Excel 文件   file_path = 'example_data.xlsx'   df = pd.read_excel(file_path)      # 打印读取的数据框架   print("读取的数据框架：")   print(df)

在这个例子中，使用 pd.read_excel 函数读取了一个名为 ‘example_data.xlsx’ 的 Excel 文件。读取后，数据被存储在一个 pandas 数据框架中。

2. 写入 Excel 文件

在处理数据后，将结果写入新的 Excel 文件是一项常见的任务。使用 pandas 库，可以将处理过的数据写入新的 Excel 文件。以下是一个简单的示例代码，演示如何使用 pandas 将数据写入 Excel 文件。

import pandas as pd      # 创建示例数据框架   data = {       'Product': ['A', 'B', 'C'],       'Price': [25.5, 30.2, 15.8],       'Quantity': [10, 8, 4]   }      df = pd.DataFrame(data)      # 将数据框架写入 Excel 文件   df.to_excel('output_data.xlsx', index=False)

在这个例子中，使用 to_excel 函数将数据框架写入 Excel 文件。参数 index=False 表示不包含行索引信息。生成的 Excel 文件名为 ‘output_data.xlsx’。

3. 数据筛选与过滤

在数据分析中，经常需要根据特定条件筛选和过滤数据，以便只保留感兴趣的部分。使用 pandas 库，可以进行数据筛选和过滤。以下是一个简单的示例代码，演示如何使用 pandas 进行数据筛选与过滤。

import pandas as pd      # 创建示例数据框架   data = {       'Product': ['A', 'B', 'C', 'A', 'B'],       'Price': [25.5, 30.2, 15.8, 22.0, 18.5],       'Quantity': [10, 8, 4, 6, 2]   }      df = pd.DataFrame(data)      # 筛选 Price 大于 20 的数据   filtered_data = df[df['Price'] > 20]      # 打印筛选后的数据框架   print("Price 大于 20 的数据：")   print(filtered_data)

在这个例子中，使用了条件筛选，保留了 ‘Price’ 列大于 20 的行数据。你可以根据实际需求定义不同的筛选条件，以过滤符合条件的数据。

4. 数据排序

在 Excel 中，数据排序是一种常见的操作，可以更好地理解数据的结构和趋势。使用 pandas 库，可以对数据进行排序。以下是一个简单的示例代码，演示如何使用 pandas 对数据进行排序。

import pandas as pd      # 创建示例数据框架   data = {       'Product': ['B', 'A', 'C', 'D', 'A'],       'Price': [30.2, 25.5, 15.8, 40.0, 20.5],       'Quantity': [8, 10, 4, 2, 6]   }      df = pd.DataFrame(data)      # 按 'Product' 列升序排序   df_sorted = df.sort_values(by='Product')      # 打印排序后的数据框架   print("按 'Product' 列升序排序：")   print(df_sorted)

在这个例子中，使用 sort_values 函数按 ‘Product’ 列的值进行升序排序。也可以通过指定 ascending=False 参数来实现降序排序。这种排序方式使可以更容易地观察数据的特征和趋势。

5. 数据统计与汇总

通过pandas的统计函数，可以快速了解数据的统计信息，如均值、中位数等。

假设有一个包含销售数据的数据框架sales_data，其中包括产品销售额(sales_amount)、销售数量(quantity)和单价(unit_price)等列。我们将使用这个数据框架来演示如何进行全面的数据统计与汇总。

import pandas as pd      # 假设我们有一个包含销售数据的数据框架   data = {       'Product': ['A', 'B', 'C', 'A', 'B', 'A'],       'Sales_Amount': [100, 150, 200, 120, 180, 130],       'Quantity': [5, 3, 4, 6, 2, 5],       'Unit_Price': [20, 50, 50, 20, 90, 26]   }      sales_data = pd.DataFrame(data)      # 打印原始数据   print("原始数据：")   print(sales_data)      # 统计与汇总   mean_sales_amount = sales_data['Sales_Amount'].mean()   median_quantity = sales_data['Quantity'].median()   mode_product = sales_data['Product'].mode().values[0]   std_unit_price = sales_data['Unit_Price'].std()   min_sales_amount = sales_data['Sales_Amount'].min()   max_quantity = sales_data['Quantity'].max()      # 打印统计结果   print("\n统计与汇总结果：")   print(f"平均销售额：{mean_sales_amount}")   print(f"销售数量中位数：{median_quantity}")   print(f"产品销售频率最高的是：{mode_product}")   print(f"单价标准差：{std_unit_price}")   print(f"最小销售额：{min_sales_amount}")   print(f"最大销售数量：{max_quantity}")

这个例子中，使用了均值、中位数、众数、标准差、最小值和最大值等统计方法来全面了解销售数据的特征。通过运用这些统计函数，可以更好地理解数据的分布、趋势和离散程度，为进一步的数据分析和决策提供了基础。

6. 插入行与列

在处理 Excel 数据时，插入新的行和列是保持数据整洁和有序的关键步骤。使用 openpyxl 库，可以轻松地在 Excel 表格中插入新的行和列。以下是一些插入行和列的示例代码。

插入新的行

from openpyxl import Workbook      # 创建一个工作簿和工作表   workbook = Workbook()   sheet = workbook.active      # 原始数据   data = [       ["Name", "Age", "Country"],       ["Alice", 25, "USA"],       ["Bob", 30, "Canada"],   ]      # 将数据写入工作表   for row in data:       sheet.append(row)      # 打印原始数据   print("原始数据：")   for row in sheet.iter_rows(values_only=True):       print(row)      # 插入新的行（在第二行之后插入）   new_row_data = ["Charlie", 28, "UK"]   sheet.insert_rows(new_row_data, row_idx=2)      # 打印插入新行后的数据   print("\n插入新行后的数据：")   for row in sheet.iter_rows(values_only=True):       print(row)      # 保存工作簿   workbook.save("inserted_row.xlsx")

插入新的列

from openpyxl import Workbook      # 创建一个工作簿和工作表   workbook = Workbook()   sheet = workbook.active      # 原始数据   data = [       ["Name", "Age", "Country"],       ["Alice", 25, "USA"],       ["Bob", 30, "Canada"],   ]      # 将数据写入工作表   for row in data:       sheet.append(row)      # 打印原始数据   print("原始数据：")   for row in sheet.iter_rows(values_only=True):       print(row)      # 插入新的列（在第二列之后插入）   new_column_data = ["Female", "Male", "Female"]   sheet.insert_cols(values=new_column_data, col_idx=2)      # 打印插入新列后的数据   print("\n插入新列后的数据：")   for row in sheet.iter_rows(values_only=True):       print(row)      # 保存工作簿   workbook.save("inserted_column.xlsx")

这些示例代码演示了如何使用 insert_rows 和 insert_cols 方法在 Excel 表格中插入新的行和列。

7. 合并单元格

在 Excel 中，合并单元格是一种常用的操作，用于创建更复杂的表格结构或突出某些信息。使用 openpyxl 库，可以实现合并和取消合并单元格的操作。以下是一些合并单元格的示例代码。

合并单元格

from openpyxl import Workbook      # 创建一个工作簿和工作表   workbook = Workbook()   sheet = workbook.active      # 原始数据   data = [       ["Name", "Age", "Country"],       ["Alice", 25, "USA"],       ["Bob", 30, "Canada"],   ]      # 将数据写入工作表   for row in data:       sheet.append(row)      # 合并 A1 到 C1 的单元格   sheet.merge_cells('A1:C1')      # 在合并的单元格中写入标题   sheet['A1'] = 'Personal Information'      # 保存工作簿   workbook.save("merged_cells.xlsx")

取消合并单元格

from openpyxl import load_workbook      # 加载已存在的工作簿   workbook = load_workbook("merged_cells.xlsx")   sheet = workbook.active      # 取消合并 A1 到 C1 的单元格   sheet.unmerge_cells('A1:C1')      # 保存工作簿   workbook.save("unmerged_cells.xlsx")

在这个示例中，首先合并了 A1 到 C1 的单元格，创建了一个包含标题的大标题单元格。然后，演示了如何取消合并这些单元格。这种操作使得表格的布局更加灵活，可以根据实际需要进行定制。

8. 公式计算

在 Excel 中添加公式是一种常见的操作，可以实现自动计算，并随着数据的更新而动态调整。使用 openpyxl 库，可以轻松地在 Excel 中插入公式。以下是一个添加公式的示例代码。

from openpyxl import Workbook      # 创建一个工作簿和工作表   workbook = Workbook()   sheet = workbook.active      # 原始数据   data = [       ["Product", "Price", "Quantity", "Total"],       ["A", 25.5, 10, None],       ["B", 30.2, 8, None],   ]      # 将数据写入工作表   for row in data:       sheet.append(row)      # 添加公式计算 Total 列，Total = Price * Quantity   for row in range(2, sheet.max_row + 1):       sheet[f'D{row}'] = f'B{row} * C{row}'      # 保存工作簿   workbook.save("formulas.xlsx")

在这个例子中，通过循环遍历数据行，使用 Excel 公式 B(row) * C(row) 来计算 Total 列的值。这样，无论数据如何变化，Total 列都会自动更新。这种功能使得在 Excel 中进行复杂的数据计算变得更加方便和灵活。

9. 数据透视表

数据透视表是一种强大的数据分析工具，可以帮助我们快速透视和汇总数据。使用 pandas 的 pivot_table 函数，可以在 Python 中轻松创建数据透视表。以下是一个简单的示例代码。

import pandas as pd      # 创建示例数据框架   data = {       'Category': ['A', 'B', 'A', 'B', 'A', 'B'],       'Value': [10, 15, 20, 25, 30, 35],       'Quantity': [2, 3, 4, 5, 6, 7]   }      df = pd.DataFrame(data)      # 创建数据透视表   pivot_table = pd.pivot_table(df, values='Value', index='Category', columns='Quantity', aggfunc='sum', fill_value=0)      # 打印数据透视表   print("数据透视表：")   print(pivot_table)

在这个例子中，我们使用 pivot_table 函数根据 ‘Category’ 和 ‘Quantity’ 列创建了一个数据透视表。我们指定了值列为 ‘Value’，使用 ‘sum’ 函数进行汇总，如果某些组合不存在则用 0 填充。最后，打印了生成的数据透视表。

10. 批量操作

批量操作是在 Excel 中处理大量数据时提高效率的关键。使用循环和函数，可以对数据进行批量处理。以下是一个简单的示例代码，演示如何使用循环和函数批量操作 Excel 数据。

from openpyxl import Workbook      # 创建一个工作簿和工作表   workbook = Workbook()   sheet = workbook.active      # 原始数据   data = [       ["Product", "Price", "Quantity", "Total"],       ["A", 25.5, 10, None],       ["B", 30.2, 8, None],   ]      # 将数据写入工作表   for row in data:       sheet.append(row)      # 批量计算 Total 列的值（Total = Price * Quantity）   for row in range(2, sheet.max_row + 1):       price = sheet[f'B{row}'].value       quantity = sheet[f'C{row}'].value       total = price * quantity       sheet[f'D{row}'] = total      # 打印批量计算后的数据   print("批量计算后的数据：")   for row in sheet.iter_rows(values_only=True):       print(row)      # 保存工作簿   workbook.save("batch_operations.xlsx")

在这个例子中，使用循环遍历数据行，并批量计算了 ‘Total’ 列的值。通过使用循环，可以对整个数据集进行高效的操作，而不需要逐个手动处理每一行数据。