Python Batch Production radar chart

The boss wants to draw a radar chart, but what about the data groups? Not a point of Excel to draw it, then you can use Python for batch production, get the style as follows:

First make a demo of Excel, scoring for Excel random number generation:

1 =int ((RAND () +4) *10)/10

Add tags, etc. to get the Excel style as follows (part, total 32 lines):

Then the next step is to open the Python code, this article is based on python3.4 to write

1 WB = Load_workbook (filename=r ' C:\Users\Administrator\Desktop\ data indicator. xlsx ')  # #读取路径 2     ws = Wb.get_sheet_by_ Name ("Sheet1")  # #读取名字为Sheet1的sheet表 3  4     info_id = [] 5     info_first = [] 6  7     for row_a in range (2, (+):  # # Traverse line 2nd to 32 row 8         id = Ws.cell (row=row_a, column=1). Value  # # Traverse 2nd line to 32 row, 1th column 9         info_id.append (ID) 10 For     col in range (2, 9):  # #读取第1到9列11 First         = Ws.cell (row=1, Column=col). Value12         Info_first.append (first)  # #得到1到8列的标签13     info_data = []15     for Row_num_btou in range (2, Len (info_id) + 2):  # # Traverse line 2nd to 32 row         _empty = []  # #建立一个空数组作为临时储存地, each line break is emptied for the         I in range (2, 9):  # # traversing rows 2nd through 32, 2nd to 9th column             Data_excel = Ws.cell (Row=row_num_btou, column=i). value19             If data_excel = none:20                 pass21             else:22                 row_ Empty.append (Data_excel)  # #将单元格信息储存进去23         info_data.append (row_empty)

In-Step explanation:

To read an Excel table:

1 WB = Load_workbook (filename=r ' C:\Users\Administrator\Desktop\ data indicator. xlsx ')  # #读取路径2     ws = Wb.get_sheet_by_ Name ("Sheet1")  # #读取名字为Sheet1的sheet表

Need to use library:

1 Import xlsxwriter

1 from OPENPYXL import Load_workbook

At the command indicator, enter:

1 pip Install Xlsxwriter

Wait for the installation, and the following libraries are the same:

The first column ID is stored, and the first row of labels, the values below the label are stored in:

    info_id = []    info_first = []    info_data = []

After reading the data, you need to format the write:

1 workbook = Xlsxwriter. Workbook (' c:\\users\\administrator\\desktop\\result.xlsx ') 2     worksheet = Workbook.add_worksheet ()  # Create a Sheet Object 3     #字体格式4     font = Workbook.add_format (5         {' Border ': 1, ' align ': ' Center ', ' font_size ': one, ' Font_ Name ': ' Microsoft Black '})  # #字体居中, number 11th, Microsoft Ya Black, give general information for 6     #写下第一行第一列的标签7     worksheet.write (0, 0, ' sku ', font) 8     # # Set the column width of the picture 9     worksheet.set_column (0, Len (info_first) + 1, one)  # Set the width of the column len (info_first) + 1 to 11

Write the label data to a new Excel table:

1 #新建一个excel保存结果 2     workbook = Xlsxwriter. Workbook (' c:\\users\\administrator\\desktop\\result.xlsx ') 3     worksheet = Workbook.add_worksheet ()  # Create a Sheet Object 4     #字体格式 5     font = Workbook.add_format (6         {' Border ': 1, ' align ': ' Center ', ' font_size ': one, ' Font_nam E ': ' Microsoft Black '})  # #字体居中, 11th, Microsoft Ya Black, give general information for 7     #写下第一行第一列的标签 8     worksheet.write (0, 0, ' sku ', font) 9     # # Set the column width of the picture     worksheet.set_column (0, Len (info_first) + 1, one)  # Set the width of the column len (info_first) + 1 to 1111     # # # Write tags     for k in range (0,7):         worksheet.write (0, K + 1, info_first[k], font)     #写入最后一列标签16     Worksheet.write (0, Len (info_first) + 1, ' Radar Chart ', font)

Make Radar chart:

1     #设置雷达各个顶点的名称 2     labels = Np.array (info_first) 3     #数据个数 4     data_len = Len (info_first) 5 for     I in range ( 0,len (info_id)): 6         data = Np.array (Info_data[i]) 7  8         angles = np.linspace (0, 2*np.pi, Data_len, endpoint= False) 9         data = Np.concatenate ((data, [data[0])) # closed         angles = np.concatenate ((angles, [angles[0]]) # closed 11 12< C10/>fig = Plt.figure ()         ax = Fig.add_subplot (111, polar=true) # Polar Parameters!!         Ax.plot (angles, data, ' bo-', linewidth=2) # Draw line         Ax.fill (angles, data, facecolor= ' R ', alpha=0.25) # fill 16         ax.set_thetagrids (Angles * 180/np.pi, labels, fontproperties= "Simhei")         Ax.set_title ("SKU:" + str (info_ Id[i]), va= ' bottom ', fontproperties= "Simhei")         Ax.set_rlim (3.8,5) # Set the range of the radar map         Ax.grid (True)         Plt.savefig ("c:\\users\\administrator\\desktop\\result\\ SKU:" + str (info_id[i]) + ". png", dpi=120)

What if the picture is too big? Use the library to change the size:

1         Import Image 2         # #更改图片大小 3         infile = "c:\\users\\administrator\\desktop\\result\\ SKU:" + str (info_id[i]) + ". png" 4         outfile = "c:\\users\\administrator\\desktop\\result1\\ SKU:" + str (info_id[i]) + ". png" 5         im = Image. Open (infile) 6         (x, y) = im.size 7         x_s =    # # # Set length 8         y_s = 100 # # Set width 9         out = Im.resize ((x_s, y_s), I Mage. AntiAlias)         out.save (outfile, ' png ', quality = 95)

Put the big picture and the small picture in the result and result1 two different folders, need to create these two folders in front:

1     if os.path.exists (R ' C:\\users\\administrator\\desktop\\result '):  # Create a folder on the desktop, folder for result 2         print (' The result folder already exists on the desktop and continues to run the program ... ') 3     else:4         print (' Result folder not on desktop, new folder result ') 5         os.mkdir (R ' c:\\users\\ Administrator\\desktop\\result ') 6         print (' folder established successfully, continue running program ') 7  8     if os.path.exists (R ' c:\\users\\ Administrator\\desktop\\result1 '):  # Set up a folder in C drive, folder for RESULT1 9         print (' Result1 folder already exists on desktop, continue to run program ... ') 10     else:11         Print (' Result1 folder is not on the desktop, new Folder Result1 ')         Os.mkdir (R ' c:\\users\\administrator\\desktop\\ Result1 ')         print (' folder established successfully, continue running program ')

Finally, insert the picture into Excel:

1         worksheet.insert_image (i + 1, len (info_first) + 1, ' c:\\users\\administrator\\desktop\\result1\\ ' + "SKU:" + STR ( Info_id[i] + '. png ')  # #写入图片2         time.sleep (1) # #防止写入太快电脑死机3         plt.close () #   Be sure to turn off the picture, Otherwise python will crash after opening 20 pictures 4 5     workbook.close () #最后关闭excel

The results are as follows:

Enclose the complete code:

  1 Import numpy as NP 2 import Matplotlib.pyplot as PLT 3 import xlsxwriter 4 from OPENPYXL import Load_workbook 5 im Port OS 6 import time 7 from PIL import Image 8 9 If __name__ = = ' __main__ ': Ten if os.path.exists (R ' c:\\users          \\Administrator\\Desktop\\result '): # Create a folder on the desktop, folder for result print (' Result folder already exists on desktop, continue to run program ... ') else:14 Print (' Result folder not on desktop, new folder result ') Os.mkdir (R ' C:\\users\\administrator\\desktop\\result ') p Rint (' folder established successfully, continue running program ') if Os.path.exists (R ' C:\\users\\administrator\\desktop\\result1 '): # Set up a folder in C Drive, folder for ResU         LT1 print (' Result1 folder already exists on desktop, continue to run program ... ') else:21 print (' Result1 folder not on desktop, new Folder Result1 ') 22 Os.mkdir (R ' c:\\users\\administrator\\desktop\\result1 ') print (' folder Setup succeeded, continue running program ') "WB = Load_workbook ( Filename=r ' C:\Users\Administrator\Desktop\ data indicator. xlsx ') # #读取路径 WS = Wb.get_sheet_by_name ("Sheet1") # #读取名字为Sheet1的sh Table info_id= [] Info_first = [] to Row_a in range (2, 32): # # Traverse Line 2nd to 32 Line id = Ws.cell (row=row_a, column= 1). Value # # Traverse lines 2nd through 32, column 1th Info_id.append (ID) for Col in range (2, 9): # #读取第1到9列 first = WS.C Ell (Row=1, column=col). Value Info_first.append (first) # #得到1到8列的标签 PNS print (info_id) T) Info_data = [] Row_num_btou in range (2, Len (info_id) + 2): # # Traverse Line 2nd to 32 lines row_empty = [ ] # #建立一个空数组作为临时储存地, each time a newline is emptied. For I in range (2, 9): # # Traverse Line 2nd through 32, 2nd to 9th column Data_excel = Ws.cell (row=                 Row_num_btou, column=i). value if Data_excel = = none:46 Pass else:48     Row_empty.append (data_excel) # #将单元格信息储存进去 info_data.append (row_empty) print (Info_data) 51 Print (len (info_data)) 52 53 # Set the name of each vertex of the radar labels = Np.array (info_first) 55 # Number of data Data_len = Len (i Nfo_first) 57 # Create a newExcel saves the results in workbook = Xlsxwriter.     Workbook (' c:\\users\\administrator\\desktop\\result.xlsx ') worksheet = Workbook.add_worksheet () # Create a sheet Object 60 # font format: "Workbook.add_format" (Border ': 1, ' align ': ' Center ', ' font_size ': One, ' font_name ', ' Microsoft Black '} # #字体居中, number 11th, Microsoft Black, give general Information 63 # Write down the first row of the first column of the label "Worksheet.write" (0, 0, ' sku ', font) # #设置图片的那一列宽度 Orksheet.set_column (0, Len (info_first) + 1, 11) # Set the width of the column len (info_first) + 1 to one-by-one, #写入标签-K-in-range (0 , 7): Worksheet.write (0, K + 1, info_first[k], font) 71 # Write the last column label Worksheet.write (0, Len (info_first ) + 1, ' Radar Chart ', font) 73 74 # Write additional parameters to Excel for J in range (0, Len (info_id)): Worksheet.write (j + 1, 0 , Info_id[j], font) # Write commodity sku Worksheet.set_row (J, $) # #设置行宽 for x in range (0, Len (info_first)): 7 9 Worksheet.write (j + 1, x + 1, info_data[j][x], font) # Other parameters for writing products, Len (info_id)): data = Np.array (Info_data[i]) angles = np.linspace (0, 2 * np.pi, Data_len, ENDP Oint=false) data = Np.concatenate ((data, [data[0])) # Closed angles = np.concatenate ((Angles, [angles[ 0]]) # Closed for the Plt.figure () () () in the AX = Fig.add_subplot (111, polar=true) # Polar Parameters!! Ax.plot (Angles, data, ' bo-', linewidth=2) # Draw line Ax.fill (angles, data, facecolor= ' R ', alpha=0.25) # Fill the Ax.set_thetagrids (angles * 180/np.pi, labels, fontproperties= "Simhei") on the Ax.set_title ("Item No.:" + S TR (Info_id[i]), va= ' bottom ', fontproperties= "Simhei") 94 Ax.set_rlim (3.8, 5) # Set the range of the radar chart. Ax.grid (True)         Plt.savefig ("c:\\users\\administrator\\desktop\\result\\ SKU:" + str (info_id[i]) + ". png", dpi=120) 97 # Plt.show () show 98 in Python # #更改图片大小100 infile = "c:\\users\\administrator\\desktop\\result\\ SKU:" + St R (Info_id[i]) + ". png" 101 Outfile = "c:\\users\\administrator\\desktop\\result1\\ SKU:" + str (info_id[i]) + ". png" 102 im = Image.open (infile) 103 (x, y) = im.size104 x_s = 80 # # Set Long y_s = 100 # # set wide 106 out = Im.resize ((x_s, y_s), Image.antialias) 107 Out.save (outfile, ' png ', quality=95) 108 109 worksheet.insert_image (i + 1, len (info_fir                                    ST) + 1,110 ' c:\\users\\administrator\\desktop\\result1\\ ' + "SKU:" + STR (111 Info_id[i] + '. png ') # #写入图片112 Time.sleep (1) # #防止写入太快电脑死机113 Plt.close () # must be off Drop the picture, or Python will crash after opening the image 20 Workbook.close () # finally close Excel

Python Batch Production radar chart