极客笔记Python-使用Pandas和XlsxWriter进行工作
简介
Python是一种通用编程语言，其灵活性和易用性是众所周知的。Python有丰富的扩展库，其中包括Pandas和XlsxWriter两个强大的数据处理和Excel操作库。这些库结合起来，可以提供一个独立的工作环境来进行数据处理和报告生成。
Pandas库是一个开源数据分析和处理库，可以用于数据清理、数据分析和数据可视化。XlsxWriter库是一个Python模块，提供了创建Excel 2007+ XLSX格式文件的功能，并且允许制定格式和图表。
安装
首先，我们需要安装Pandas和XlsxWriter库。为此，请打开命令行并键入以下命令：
pip install pandas
pip install XlsxWriter
Pandas
Pandas库允许我们读取和写入各种类型的文件，如Excel、CSV、JSON、HTML等。在本节中，我们将讨论如何读取Excel文件并对其进行分析和操作。
我们假设有一个名为data.xlsx的Excel文件，包含以下内容：
| Name | Age | Gender | Department |
|---|---|---|---|
| John | 25 | Male | IT |
| Sarah | 33 | Female | Marketing |
| Peter | 28 | Male | Finance |
| Hannah | 23 | Female | Marketing |
| Tom | 35 | Male | IT |
现在，我们将使用Pandas读取该文件，进行一些显示和操作。首先，我们导入Pandas库：
import pandas as pd
接下来，我们使用read_excel函数读取Excel文件中的内容。我们可以指定Excel文件的路径，或使用与此脚本位于同一目录中的文件，如下例所示：
df = pd.read_excel('data.xlsx')
print(df)
输出如下：
Name Age Gender Department
0 John 25 Male IT
1 Sarah 33 Female Marketing
2 Peter 28 Male Finance
3 Hannah 23 Female Marketing
4 Tom 35 Male IT
现在，我们来创建一个新的DataFrame对象，并向其添加新列和行：
new_df = pd.DataFrame({'Name': ['Alex', 'Jenny', 'Bob'], 'Age': [30, 22, 38], 'Gender': ['Male', 'Female', 'Male'], 'Department': ['IT', 'HR', 'Marketing']})
df = pd.concat([df, new_df])
print(df)
输出如下：
Name Age Gender Department
0 John 25 Male IT
1 Sarah 33 Female Marketing
2 Peter 28 Male Finance
3 Hannah 23 Female Marketing
4 Tom 35 Male IT
0 Alex 30 Male IT
1 Jenny 22 Female HR
2 Bob 38 Male Marketing
现在，我们将使用groupby函数将数据按部门进行分组，并计算每个部门中平均年龄：
grouped_data = df.groupby('Department')['Age'].mean()
print(grouped_data)
输出如下：
Department
Finance 28.0
HR 22.0
IT 30.0
Marketing 30.333333
Name: Age, dtype: float64
这个操作将数据分组并执行聚合函数。我们也可以通过以下方式来分组：
grouped_data = df.groupby(['Department', 'Gender'])['Age'].mean()
print(grouped_data)
输出如下：
Department Gender
Finance Male 28.0
HR Female 22.0
IT Male 30.0
Marketing Female 28.0
Male 34.0
Name: Age, dtype: float64
XlsxWriterXlsxWriter库允许我们创建各种类型的Excel工作簿，包括带格式的单元格、数据验证、图表等。在本节中，我们将学习如何使用XlsxWriter库创建一个简单的Excel工作簿，并向其中添加一些格式化单元格、数据验证和图表。
首先，我们导入XlsxWriter库：
import xlsxwriter
接下来，我们创建一个新的Excel工作簿，并向其添加一个名为“Sheet1”的工作表：
workbook = xlsxwriter.Workbook('output.xlsx')
worksheet = workbook.add_worksheet('Sheet1')
然后，我们可以向工作表中添加一些数据：
worksheet.write('A1', 'Name')
worksheet.write('B1', 'Age')
worksheet.write('C1', 'Gender')
worksheet.write('D1', 'Department')
worksheet.write('A2', 'John')
worksheet.write('B2', 25)
worksheet.write('C2', 'Male')
worksheet.write('D2', 'IT')
worksheet.write('A3', 'Sarah')
worksheet.write('B3', 33)
worksheet.write('C3', 'Female')
worksheet.write('D3', 'Marketing')
worksheet.write('A4', 'Peter')
worksheet.write('B4', 28)
worksheet.write('C4', 'Male')
worksheet.write('D4', 'Finance')
worksheet.write('A5', 'Hannah')
worksheet.write('B5', 23)
worksheet.write('C5', 'Female')
worksheet.write('D5', 'Marketing')
worksheet.write('A6', 'Tom')
worksheet.write('B6', 35)
worksheet.write('C6', 'Male')
worksheet.write('D6', 'IT')
现在，我们将使用XlsxWriter功能添加一些格式化单元格。让我们为“Name”列的标题单元格添加一些格式，以使其字体更大、更粗：
bold_format = workbook.add_format({'bold': True, 'font_size': 14})
worksheet.write('A1', 'Name', bold_format)
此外，我们还可以使用以下代码添加日期格式的单元格：
date_format = workbook.add_format({'num_format': 'dd/mm/yy'})
worksheet.write('F1', 'Date', bold_format)
worksheet.write('F2', '01/01/2022', date_format)
接下来，我们将使用XlsxWriter添加一些数据验证。例如，我们可以在Department列中创建一个下拉列表，以限制可选值。为此，我们将定义一个名为“departments”的列表，其中包含可用部门的名称。然后，我们将使用DataValidation功能添加一个下拉列表，将其应用于Department列的单元格：
departments = ['IT', 'Marketing', 'Finance', 'HR']
validation_format = workbook.add_format({'bg_color': 'yellow'})
worksheet.write('D1', 'Department', bold_format)
cell_format = workbook.add_format()
cell_format.set_locked(True)
worksheet.data_validation('D2:D1000', {'validate': 'list', 'source': departments, 'input_title': 'Department', 'input_message': 'Please select the department:', 'error_title': 'Invalid Department', 'error_message': 'Please enter a valid department name.', 'error_style': 'stop', 'error_format': validation_format, 'dropdown_format': cell_format})
将此代码添加到我们的脚本中后，我们将获得一个下拉列表，其中包含“IT”、“Marketing”、“Finance”和“HR”等部门名称。
最后，我们将使用XlsxWriter创建一个图表。例如，我们可以创建一个条形图，用于显示每个部门的平均年龄：
chart_format = workbook.add_format({'bg_color': 'white'})
chart = workbook.add_chart({'type': 'bar'})
chart.add_series({'categories': '=Sheet1!D2:D6', 'values': '=Sheet1!B2:B6', 'data_labels': {'value': True, 'font': {'size': 14}}, 'fill': {'color': 'blue'}, 'border': {'color': 'black'}, 'name': 'Average Age'})
chart.set_title({'name': 'Average Age by Department', 'name_font': {'size': 16, 'bold': True}})
chart.set_x_axis({'name': 'Department', 'name_font': {'size': 14}, 'label_font': {'size': 12}})
chart.set_y_axis({'name': 'Age', 'name_font': {'size': 14}, 'label_font': {'size': 12}})
chart.set_legend({'font': {'size': 12}})
chart.set_plotarea({'border': {'color': 'black', 'width': 1}, 'fill': {'color': 'white'}});
worksheet.insert_chart('G1', chart, {'x_offset': 25, 'y_offset': 10, 'x_scale': 2, 'y_scale': 1})
workbook.close()
将此代码添加到我们的脚本中后，将在Excel工作簿中添加一个新工作表，其中包含一个可视化的条形图，显示每个部门的平均年龄。
结论
Pandas和XlsxWriter都是强大的Python库，用于数据处理、分析和Excel操作。本文介绍了如何使用它们来读写Excel文件、设置格式化单元格、添加数据验证和创建图表。这些功能可以帮助我们有效地处理数据，生成报告，提高工作效率。