Case import
As shown in the figure below, query the quantity and total amount of commercial color TVs sold by salesperson Zhou Deyu, and the quantity and total amount of refrigerators sold by salesperson Wang Xuemin.
Figure 1.Raw data table
Wenxin is a prompt word
Open Baidu's "Wenxin Yiyan" and enter the prompt word: "You are a pandas expert, and the file path is: D:/Samples/Sales - Multi-Condition Query.xlsx." In the first worksheet of the Excel file, A1:D11 is the given data, and the A-D columns are "Salesperson", "Merchandise", "Quantity" and "Total" data respectively. Use pandas to import the data of the Excel file,The first line is the index row,Only import the first 11 rows,The first 4 columns of data,The engine is"openpyxl",Query the quantity and total amount of the color TV sold by the salesperson Zhou Deyu,The quantity and total amount of the refrigerator sold by the salesperson Wang Xuemin,The query result is output in the form of a two-dimensional table。 ”
The results are as follows:
To accomplish this task, we need to first use the pandas library to read the data in the Excel file, and query the quantity and total amount of color TVs and refrigerators sold by salespeople Zhou Deyu and Wang Xuemin based on specific conditions. Here's how to do it in Python:
import pandas as pd
# 读取Excel文件
file_path = 'D:/Samples/销售情况-多条件查询.xlsx'
# 设置读取选项，只读取前11行，前4列，并且第一行为索引
read_options = {'sheet_name': 0, 'header': 0, 'nrows': 11, 'usecols': 'A:D', 'engine': 'openpyxl'}
# 读取数据
df = pd.read_excel(file_path, **read_options)
# 查询销售人员周德宇卖商品彩电的数量和总额
zhou_deyu_tv_sales = df[(df['销售人员'] == '周德宇') & (df['商品'] == '彩电')].agg({'数量': 'sum', '总额': 'sum'}).to_frame().T
zhou_deyu_tv_sales.columns = ['数量', '总额']
zhou_deyu_tv_sales.index = ['周德宇-彩电']
# 查询销售人员王学敏卖商品冰箱的数量和总额
wang_xuemin_fridge_sales = df[(df['销售人员'] == '王学敏') & (df['商品'] == '冰箱')].agg({'数量': 'sum', '总额': 'sum'}).to_frame().T
wang_xuemin_fridge_sales.columns = ['数量', '总额']
wang_xuemin_fridge_sales.index = ['王学敏-冰箱']
# 将两个查询结果合并
result = pd.concat([zhou_deyu_tv_sales, wang_xuemin_fridge_sales])
# 输出查询结果
print(result) In this script, we first use pd.read_excel functions to read the Excel file, and specify the read options, including reading only the first 11 rows, the first 4 columns, and using openpyxl as the engine. We then filter the data based on the criteria of the salesperson and the product, and use the AGG function to calculate the sum of the eligible quantities and totals. Next, we set the index and column names for the query results, and use pd.concat to merge the two query results into a single DataFrame. Finally, we print out the results of the query.
Note: Before running the code, make sure you have installed the pandas library and the openpyxl library. If it is not installed, you can use the pip install pandas openpyxl command to install it. Also, make sure that the path of the Excel file is correct and that the Excel file is accessible. If the path or file name contains non-ASCII characters, the path string may need to be encoded appropriately.
Python editor runs
Figure 2. Python running results
The tools used in this collection are as follows:
Wenxin Yiyan: Version 3.5
Python Editor: 3.9.0
Disclaimer: The above results are the actual operation results, if you encounter failure to run, you can leave a message in the comment area. Part of the data comes from the Internet, if there is any infringement, please inform us by private message, thank you!