Shulou(Shulou.com)06/02 Report--

This "how to use Python to achieve automatic generation of data reports" article, the article sample code is very detailed, with a certain reference value, interested friends must refer to, for "how to use Python to achieve automatic generation of data reports", Xiaobian sorted out the following knowledge points, please follow the pace of the editor step by step slowly understand, then let us enter the topic.

Development tools

Python version: 3.6.4

Related modules:

Pandasxlwingsmatplotlib module

Xlwingsmatplotlib module

Matplotlib module

And some modules that come with Python.

Environment building

Install Python and add it to the environment variable, and pip installs the relevant modules you need.

The original data are as follows, including the name of fruits and vegetables, date of sale, sales quantity, average price, average cost, total income, total cost, total profit and so on.

Main code

First import the related library and use pandas to read the original data.

Import pandas as pdimport xlwings as xwimport matplotlib.pyplot as plt# alignment data pd.set_option ('display.unicode.ambiguous_as_wide', True) pd.set_option (' display.unicode.east_asian_width', True) # read data df = pd.read_csv (r "fruit_and_veg_sales.csv") print (df)

The results are as follows

There are 1000 lines of sales data.

Use the xlwings library to create an Excel workbook, create a table in the workbook named fruit_and_veg_sales, and then copy the original data into it.

# create the original data table and copy the data wb = xw.Book () sht = wb.sheets ["Sheet1"] sht.name = "fruit_and_veg_sales" sht.range ("A1") .options (index=False) .value = d

After taking the original data, create a visualization table, the Dashboard table, in the workbook.

# create table wb.sheets.add ('Dashboard') sht_dashboard = wb.sheets (' Dashboard')

Now we have an Excel workbook that contains two worksheets. The fruit_and_veg_sales table has our data, while the Dashboard table is blank.

Let's use pandas to process the data and generate the data information for the Dashboard table.

The first two tables of the DashBoard table, one is the profit table of the product, the other is the sales quantity table of the product.

The PivotTable function of pandas is used.

# sales quantity PivotTable pv_quantity_sold = pd.pivot_table (df, index=' category', values=' sales quantity', aggfunc='sum') print (pv_quantity_sold)

The data are as follows

Here, the data is queried first, and the date found is listed as object, which cannot be grouped and summarized.

So we use pd.to_datetime () to convert its format, and then group it according to time to get the data of each month.

The last groupby will provide the fourth data information for the Dashboard table

# date data for the top 8 of total revenue gb_top_revenue = (df.groupby (df ["date of sale"]) .sum () .sort _ values ('total revenue (US dollars), ascending=False) .head (8)) [["sales volume", "total revenue (US dollars)", "total cost (US dollars)", "total profits (US dollars)]] print (gb_top_revenue)

The date of the top 8 of total income, the results are as follows

Now that we have four pieces of data, we can attach them to the Excel

# set the background color, from the A1 cell to the rectangular range of the Z1000 cell sht_dashboard.range ('A1Plus Z1000'). Color = (198,224,180) # column width sht _ dashboard.range (' ARAUB'). Column_width = 2.22print (sht_dashboard.range ('B2'). Api.font_object.properties.get ()) # B2 cell Text content, font, font size, bold, color, Line height (main title) sht_dashboard.range ('B2'). Value =' sales data report 'sht_dashboard.range (' B2'). Api.font_object.name.set ('boldface') sht_dashboard.range ('B2'). Api.font_object.font_size.set (48) sht_dashboard.range (' B2'). Api.font_object.bold.set (True) sht_dashboard.range ('B2'). Api.font_object.color.set ([0] 0,0]) sht_dashboard.range ('B2'). Row_height = 61.The rectangle from cell B2 to cell W2, the thickness and color of the lower border sht_dashboard.range (' B2sht_dashboard.range W2'). Api.get_border (which_border=9) .weight.set (4) sht_dashboard.range ('B2VRW2'). Api.get_border (which_border=9). Color.set ([0,176) 80]) # Total revenue chart name, font, font size, bold, Color (subtitle) sht_dashboard.range ('M2'). Value =' revenue of each product 'sht_dashboard.range (' M2'). Api.font_object.name.set ('boldface') sht_dashboard.range ('M2'). Api.font_object.font_size.set (20) sht_dashboard.range (' M2'). Api.font_object.bold.set (True) sht_dashboard.range ('M2'). Api.font_object.color.set ([0] 0,0]) # the dividing line of the main title and subtitle, weight, color, linetype sht_dashboard.range ('L2'). Api.get_border (which_border=7) .weight.set (3) sht_dashboard.range (' L2'). Api.get_border (which_border=7). Color.set ([0,176,80]) sht_dashboard.range ('L2'). Api.get_border (which_border=7). Line_style.set (- 4115)

First configure some basic content, such as text, color background, borders, etc., as shown in the following figure

Use the function to batch generate the format of four tables

# Table generation function .def create_formatted_summary (header_cell, title, df_summary, color): "Parameters-header_cell: location of the cell in the upper left corner of Str Place data title: Str title of current form df_summary: DataFrame table data color: Str form fill color "" # optional form fill color colors = {"purple": [(112,48,160), (161,98,208)], "blue": [(01124192), (155,194,230)] "green": [(0,176,80), (169,208,142)], "yellow": [(255,192,0), (255,217,102)]} # set the column width of the table title sht_dashboard.range (header_cell). Column_width = 1.5 # get the number of rows and rows of the cell row, col = sht_dashboard.range (header_cell). Row Sht_dashboard.range (header_cell). Column # sets the title of the table and related information, such as font size, row height, left center alignment, color, bold, background color of the table, etc. Summary_title_range = sht_dashboard.range ((row Col) summary_title_range.value = title summary_title_range.api.font_object.font_size.set (14) summary_title_range.row_height = 32.5 # Vertical alignment summary_title_range.api.verticalalignment = xw.constants.HAlign.xlHAlignCenter summary_title_range.api.font_object.color.set ([255,255) ) summary_title_range.api.font_object.bold.set (True) sht_dashboard.range ((row, col), (row) Col + len (df_summary.columns) + 1). Color = colors [color] [0] # Darker color # set table content, starting cell, data fill, font size, bold, color fill summary_header_range = sht_dashboard.range ((row + 1) Col + 1)) summary_header_range.value = df_summary summary_header_range = summary_header_range.expand ('right') summary_header_range.api.font_object.font_size.set (11) summary_header_range.api.font_object.bold.set (True) sht_dashboard.range ((row + 1, col), (row + 1) Col + len (df_summary.columns) + 1). Color = colors [color] [1] # Darker color sht_dashboard.range ((row + 1, col + 1), (row + len (df_summary), col + len (df_summary.columns) + 1)). Autofit () for num in range (1, len (df_summary) + 2,2): sht_dashboard.range ((row + num, col) (row + num, col + len (df_summary.columns) + 1). Color = colors [color] [1] # find the last row of the table last_row = sht_dashboard.range ((row + 1, col + 1). Expand ('down'). Last_cell.row side_border_range = sht_dashboard.range ((row + 1, col), (last_row) Col)) # add a colored border side_border_range.api.get_border (which_border=7) .weight.set (3) side_border_range.api.get_border (which_border=7) .color.set (colors [color] [1]) side_border_range.api.get_border (which_border=7). Line_style.set (- 4115) # generate 4 tables create_formatted_summary ('B5') 'Revenue per product', pv_total_profit, 'green') create_formatted_summary (' B17 revenue, 'sales per product', pv_quantity_sold, 'purple') create_formatted_summary (' F17 revenue, monthly sales', gb_date_sold, 'blue') create_formatted_summary (' F5 earnings, 'daily revenue ranking Top8, gb_top_revenue,' yellow')

The results are as follows

As you can see, rows of data have been processed by Python and become clear tables at a glance.

Finally, draw a matplotlib chart, add a logo picture, and save the Excel file

# Chinese display plt.rcParams ['font.sans-serif'] = [' Songti SC'] # use Matplotlib to draw visual chart, pie chart fig, ax= plt.subplots (figsize= (6,3) pv_total_profit.plot (color='g', kind='bar', ax=ax) # add chart to Excelsht_dashboard.pictures.add (fig, name='ItemsChart', left=sht_dashboard.range ("M5"). Left Top=sht_dashboard.range ("M5"). Top, update=True) # add logo to Excellogo = sht_dashboard.pictures.add (image= "pie_logo.png", name='PC_3', left=sht_dashboard.range ("J2") .left Top=sht_dashboard.range ("J2"). Top+5, update=True) # set the size of logo logo.width = 54logo.height = 5 save Excel file wb.save (rf "fruit and vegetable sales report .xlsx")

You need to set up the Chinese display here, otherwise there will be no Chinese display, only one box.

Get the final fruit and vegetable sales report

What can python do Python is a programming language, built in many effective tools, Python is almost omnipotent, the language is easy to understand, easy to start, powerful, in many fields, such as the most popular big data analysis, artificial intelligence, Web development and so on.

The above is all the contents of the article "how to use Python to generate data reports automatically". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.