How to Plot a Graph From Excel Data in Python?

1. Experiment introduction
1.1 Experimental content
This course uses python to read data from excel and uses matplotlib to draw into a two-dimensional image. In this process, the image will be beautified through a series of operations, and finally an image that can be published can be obtained. This course is of great value to students who need to write experimental reports, dissertations, publish articles, and make PPT reports. The data and images in this course are derived from an SCI article of mine and are a real case.

Alibaba Cloud Simple Application Server:  Anti COVID-19 SME Enablement Program
$300 coupon package for all new SMEs and a $500 coupon for paying customers.

1.2 Experimental knowledge

• Use the xlrd expansion package to read excel data
• Use matplotlib to draw a two-dimensional image
• Beautify the image, add annotations, notes, display Latex style formulas, transparency at the coordinate points and other skills

1.3 Experimental environment

• python2.7
• Xfce terminal

1.4 Suitable for the crowd
The difficulty of this course is medium, suitable for users with basic Python, and it is of great value to students who need to write experimental reports, dissertations, publish articles, and do PPT reports.

1.5 Code acquisition
You can download the data and code to the environment of the experiment building through the following command, as a reference for comparison and learning.

$ wget http://labfile.oss.aliyuncs.com/courses/791/finally.py
$ wget http://labfile.oss.aliyuncs.com/courses/791/my_data.xlsx
$ wget http://labfile.oss.aliyuncs.com/courses/791/phase_detector.xlsx
$ wget http://labfile.oss.aliyuncs.com/courses/791/phase_detector2.xlsx

2. Development Preparation
Open Xfce terminal, download and install related dependencies.

$ sudo apt-get update
$ sudo apt-get install python-dev
$ sudo pip install numpy
$ sudo apt-get install python-matplotlib
$ sudo pip install xlrd
$ sudo apt-get install python-sip
$ sudo apt-get install libqt4-dev
$ sudo apt-get install python-qt4 python-qt4-dev pyqt4-dev-tools qt4-dev-tools

When asked whether to install, enter y and press Enter to continue the installation.

3. Experimental procedure
3.1 Draw a simple image and test whether the installation of the extension package is normal
After installing matplotlib, run a small program to test whether it is normal. Let's draw a very simple sine function.

import numpy as np
import matplotlib.pyplot as plt
 
 
x = np.linspace(0, 10, 500)
dashes = [10, 5, 100, 5]  # 10 points on, 5 off, 100 on, 5 off
 
fig, ax = plt.subplots()
line1, = ax.plot(x, np.sin(x), '--', linewidth=2,
                 label='Dashes set retroactively')
line1.set_dashes(dashes)
 
line2, = ax.plot(x, -1 * np.sin(x), dashes=[30, 5, 10, 5],
                 label='Dashes set proactively')
 
ax.legend(loc='lower right')
plt.show()

This program comes from an official routine and is only used to verify the installation package. This picture is too simple and not too beautiful.

3.2 Test xlrd extension package
As the name implies, xlrd is the extension package of the suffix name of the excel file. The read file of the xl file. This package can only read files, not write them. Another package is required for writing. But this package can actually read .xlsx files.

The process of reading data from excel is relatively simple. First, import open_workbook from the xlrd package, then open the excel file, and read out each row and column of data in each sheet. Obviously, this is a cyclic process.

from xlrd import open_workbook
x_data1=[]
y_data1=[]
wb = open_workbook('phase_detector.xlsx')
for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
        print 'the row is:',row
        values = []
        for col in range(s.ncols):
            values.append(s.cell(row,col).value)
        print values
        x_data1.append(values[0])
        y_data1.append(values[1])

If there is no problem with the installation package, this code should be able to print out the data content in the excel sheet. Explain this code: After opening an excel file, first loop through the sheets in the file, which is the outermost loop; in each sheet, perform the second loop, row loop; within each row, perform column loop This is the third layer of circulation. In the innermost column loop, the row and column values are taken out and copied to the newly created values list. Obviously, the source data has several columns, and the values list has a few elements. The excel file in our example has two columns, corresponding to "angle" and DC value. So after the end of the column cycle, we save the obtained data to the two lists x_data1 and y_data1.

3.3 Draw image V1.0
The function of the first version is very simple, read data from excel, and then draw into an image. The specific procedures are as follows:

#!/usr/bin/python
#-*- coding: utf-8 -*-
 
import matplotlib.pyplot as plt
import xlrd
from xlrd import open_workbook
 
x_data=[]
y_data=[]
x_volte=[]
temp=[]
wb = open_workbook('my_data.xlsx')
 
for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
        print 'the row is:',row
        values = []
        for col in range(s.ncols):
            values.append(s.cell(row,col).value)
        print values
        x_data.append(values[0])
        y_data.append(values[1])    
plt.plot(x_data, y_data, 'bo-',label=u"Phase curve",linewidth=1)
plt.title(u"TR14 phase detector")
plt.legend()
 
plt.xlabel(u"input-deg")
plt.ylabel(u"output-V")
 
 
plt.show()
print 'over!'

The procedure for reading data from excel has already been explained above. The function behind this code is the basic format of matplotlib drawing. The input format here is:

plt.plot (x-axis data, y-axis data, curve type, legend description, curve line width)

The name at the top of the picture is defined by this line of statements:

plt.title(u"TR14 phase detector")

Finally, use this statement to enable the display:

plt.legend()

3.4 Drawing an image V1.1
This figure only draws the data of one table, we have a total of three tables. But this one is ugly enough. Let's beautify it first. First of all, the problem with the coordinate axis: 0 on the horizontal axis corresponds to 8 on the vertical axis, which is obviously not good. Let's move the coordinate axis so that the 0 points coincide:

#!/usr/bin/python
#-*- coding: utf-8 -*-
 
import matplotlib.pyplot as plt
from pylab import *
import xlrd
from xlrd import open_workbook
 
x_data=[]
y_data=[]
x_volte=[]
temp=[]
wb = open_workbook('my_data.xlsx')
 
for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
        print 'the row is:',row
        values = []
        for col in range(s.ncols):
            values.append(s.cell(row,col).value)
        print values
        x_data.append(values[0])
        y_data.append(values[1])
 
plt.plot(x_data, y_data, 'bo-',label=u"Phase curve",linewidth=1)
 
 
plt.title(u"TR14 phase detector")
plt.legend()
 
ax = gca()
ax.spines['right'].set_color('none')
ax.spines['top'].set_color('none')
ax.xaxis.set_ticks_position('bottom')
ax.spines['bottom'].set_position(('data',0))
ax.yaxis.set_ticks_position('left')
ax.spines['left'].set_position(('data',0))
 
plt.xlabel(u"input-deg")
plt.ylabel(u"output-V")
 
plt.show()
print 'over!'

Ok, after moving the coordinate axis, the picture is a little bit more pleasing to the eye. We can also clearly see that the intersection point of the image and the horizontal axis is about 180 degrees.

Explain the code for moving the coordinate axis: we want to move the coordinate axis, we must first dismantle the old coordinates. How to disassemble it? The original image is an image with border scales on the top, bottom, left, and right sides. We first need to remove the right border. Use the statement:

ax.spines['right'].set_color('none')

Set the color of the right border to invisible, and the right border is removed. Similarly, the upper boundary is removed:

ax.spines['top'].set_color('none')

After disassembling, only the left and lower borders we care about are left. These two are the x-axis and y-axis. Then we move these two axes so that their zero points correspond:

ax.xaxis.set_ticks_position('bottom')
ax.spines['bottom'].set_position(('data',0))
ax.yaxis.set_ticks_position('left')
ax.spines['left'].set_position(('data',0))

In this way, the movement of the coordinate axis is completed.

3.5 Draw image V1.2
Can we add a mark to the zero crossing of the image? The display tells the viewer that where the zero-crossing point is, you don’t have to guess after reading the map, or you have to ask the person who made the report.

#!/usr/bin/python
#-*- coding: utf-8 -*-
 
import matplotlib.pyplot as plt
from pylab import *
 
import xlrd
 
from xlrd import open_workbook
 
x_data=[]
y_data=[]
x_volte=[]
temp=[]
wb = open_workbook('my_data.xlsx')
 
for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
        print 'the row is:',row
        values = []
        for col in range(s.ncols):
            values.append(s.cell(row,col).value)
        print values
        x_data.append(values[0])
        y_data.append(values[1])
 
plt.plot(x_data, y_data, 'bo-',label=u"Phase curve",linewidth=1)
 
plt.annotate('zero point', xy=(180,0), xytext=(60,3), arrowprops=dict(facecolor='black', shrink=0.05),)
 
plt.title(u"TR14 phase detector")
plt.legend()
 
ax = gca()
ax.spines['right'].set_color('none')
ax.spines['top'].set_color('none')
ax.xaxis.set_ticks_position('bottom')
ax.spines['bottom'].set_position(('data',0))
ax.yaxis.set_ticks_position('left')
ax.spines['left'].set_position(('data',0))
 
plt.xlabel(u"input-deg")
plt.ylabel(u"output-V")
 
plt.show()
print 'over!'

To add annotations, use this sentence:

plt.annotate (annotated text, annotated data points, annotated text coordinates, arrow shape)

Among them, the marked data points are the data we are interested in and need to be explained, and the marked text coordinates need to be adjusted according to the effect, which can not block the original curve, but also be eye-catching.