Today, let's talk about how to use Python to automatically generate daily data reports!
In fact, I think it's quite simple, the core is that you assemble the content template of the daily newspaper, and then hand over the amount of changes to python to fill, and the basic thing you need is python to process excel, word and ppt and other related libraries. Skillfully use them, and you'll be able to automate them all in one.
Daily newspapers are a problem that most migrant workers can't get around.
For managers, the daily report is the best way to manage in advance, and can understand the atmosphere and status of the team. But for employees, that's what to talk about. For repetitive tasks, I highly recommend using Python to make it modular and automated, so that we can work efficiently.
Let's take a look at the advantages of Python automated office through a case study of supplementing a sales daily report. This article simplifies the process of the case and includes the full code at the end of the article.
Detailed explanation of requirements
My friend's demand is like this, their usual sales data is recorded on Excel, and after being summarized, it will be counted by department. But at the beginning of this year, the leader suddenly asked to write a daily newspaper, and everyone wrote it for a month and found that they didn't check it, so they didn't continue to write it.
Now I am suddenly asked to hand in all the daily newspapers before this month tomorrow, which is equivalent to making up for nearly 120 days of daily newspapers from February to May. My friend sent all the documents related to his daily newspaper, and found that the final daily report effect is as follows.
Therefore, the requirements are relatively simple, you only need to read the daily data from the Excel table, process it with Python, and then write it into the Word document in turn, you can realize the batch generation of daily reports.
data processing
Before you start processing your data, you need to know what data you'll ultimately need. As shown in the figure below, there are two main categories in Word for target daily reports: the values marked in red are mainly composed of the data of the current day or the data calculated by them, and the tables marked in green are simpler, which are the data of the last seven days (sales quantity, sales amount, sales target, and completion).
First of all, we import the Pandas module for data processing
import pandas as pd
df = pd.read_excel("日报数据.xlsx")
df Output the result
After importing the data, we can then perform data calculations according to our needs. There are two main types of data operations, one is to use addition +, subtraction -, multiplication *, division / for data operations, and the other is to use statistical methods for data operations.
In the interactive environment, enter the following command:
df["日期"] = df["日期"].apply(lambda x:x.strftime("%Y-%m-%d"))
df["当日完成度"] = (df["销售金额"]/df["销售目标"]*100).round(1)
df["累计销售金额"] = df["销售金额"].cumsum()
df["当年完成度"] = (df["累计销售金额"]/2200000*100).round(1)
df["累计销售金额"] = (df["累计销售金额"]/10000).round(2)
df Output:
As you can see, the data content marked in red in the screenshot of the final result has been calculated. The green marked table is much simpler, using the data in the Pandas module.
In the interactive environment, enter the following command:
num = 10
df.iloc[num-7:num, :5] Output:
In this way, you can easily get a collection of daily report data for the last 7 days of a certain date.
Automatically generate daily reports
The python-docx module is usually used to automate Word operations using Python, and there are generally two ways to generate Word documents in batches: using add_ paragraph(), add_table() and other methods to add various content to Word documents. The other is what we are going to use this time, that is, to replace the text and table data in the original Word document according to the position.
In the interactive environment, enter the following command:
for index, rows in df.iterrows():
if index > 30:
doc.paragraphs[0].runs[1].text = rows[0]
doc.paragraphs[4].runs[4].text = rows[0]
doc.paragraphs[4].runs[6].text = str(rows[1])
doc.paragraphs[4].runs[8].text = str(rows[2])
doc.paragraphs[5].runs[1].text = str(rows[3])
doc.paragraphs[5].runs[3].text = str(rows[4])
doc.paragraphs[9].runs[2].text = str(rows[5])
doc.paragraphs[9].runs[7].text = str(rows[6])
table = doc.tables[0]
data_table = df.iloc[index-6:index+1,:5]
for i in range(7):
for j in range(5):
table.cell(i+1,j).text = str(df.iloc[i,j])
doc.save(f"销售日报-{rows[0]}.docx") Execute the code, and the output result:
As shown in the picture above, 120 recorded sales daily reports are written, and Python automated office is magical.
Because of its simple syntax and ease of use, Python has been called the "best programming language for beginners to learn". For all kinds of repetitive computer work at work, you can consider using Python to transform it into an automated program.
If you are a beginner to Python, you will find that the logic of this article is very simple, and you can even improve it. For example, the python-docx module has an advantage in reading Word documents, but when writing text to a template, you can consider using the docxtpl module (learn a little Jinja2 syntax).