使用 Excel 工作表中的数据在 python 中绘制图形

Using data from an excel sheet to graph in python

提问by Rima

So I have lot of data currently in excel spreadsheets. I need to graph this through python. I know how to read data from an excel file using xlrd and I know how to graph in python using matplotlib. Basically my data looks has columns of x coordinates, y coordinates, and positive and negative y errors. I need a way for for that data to be imported from the spreadsheet and become points and error bars on a graph. To be honest I'm very new at python and have no idea why my code isn't working.

所以我目前在excel电子表格中有很多数据。我需要通过python来绘制它。我知道如何使用 xlrd 从 excel 文件中读取数据，我知道如何使用 matplotlib 在 python 中绘图。基本上我的数据看起来有 x 坐标、y 坐标和正负 y 错误的列。我需要一种方法让这些数据从电子表格中导入并成为图表上的点和误差线。老实说，我对 python 很陌生，不知道为什么我的代码不起作用。

import xlrd
import numpy as np
import matplotlib.pyplot as plt
file_location = "C:/Users/Rima/Desktop/apjl731data.xlsx"
workbook = xlrd.open_workbook(file_location)
first_sheet = workbook.sheet_by_index(0)
for col in range(first_sheet.ncols):
    x = first_sheet.cell_value(0,col)
    y = first_sheet.cell_value(1,col)
    yerr = first_sheet.cell_value(2,col)
plt.errorbar(x,y,yerr,fmt='r^')
plt.show()

I haven't found how to do this online, only how to make the graphs in excel using python. I'm sure my code is probably missing a lot to make if work but I'm not really sure what. Also for the yerr, in order to get a different error value on the top and bottom of a data point I've been passing it as an array like yerr = np.array([]) with different values for the errors for each point. I have no idea how to import the data since my positive errors and negative errors are in different columns on the spreadsheet. If anyone know how to import the data please help since it would make my life easier since I wouldn't have to hand type 50 data point. Thank you!

我还没有在网上找到如何做到这一点，只有如何使用 python 在 excel 中制作图形。我确定我的代码可能缺少很多工作，但我不确定是什么。同样对于 yerr，为了在数据点的顶部和底部获得不同的错误值，我将其作为数组传递，例如 yerr = np.array([]) ，每个点的错误值都不同. 我不知道如何导入数据，因为我的正错误和负错误位于电子表格的不同列中。如果有人知道如何导入数据，请提供帮助，因为这会让我的生活更轻松，因为我不必手动输入 50 个数据点。谢谢！

Edit: An example of my data would be

编辑：我的数据的一个例子是

log(O/H)+12 positive error negative error virgo infall distance 8.56 0.05 0.05 4.61 8.59 0.03 0.03 - 8.54 0.04 0.06 2.97297 8.94 0.13 0.12 8.24493

log(O/H)+12 positive error negative error virgo infall distance 8.56 0.05 0.05 4.61 8.59 0.03 0.03 - 8.54 0.04 0.06 2.97297 8.94 0.13 0.12 8.24493

I do have gaps in my data which is mark with a -, I don't know if that would cause an error when trying to plot. So I probably need a way to skip those lines. Thanks again.

我的数据中确实有带有 - 标记的空白，我不知道在尝试绘图时是否会导致错误。所以我可能需要一种方法来跳过这些行。再次感谢。

Edit 2: I am still having an error so here is the traceback.

编辑 2：我仍然有一个错误，所以这里是回溯。

Thanks!

谢谢！

回答by jonnybazookatone

I have made a few assumptions. Assuming your data is like this:

我做了一些假设。假设你的数据是这样的：

x y yerr_positive yerr_negative
1 1 0.1 0.2
2 2 0.1 0.2
3 3 0.1 0.2
4 4 0.1 0.2

I have also modified how you load the data slightly so that you load each column into its own array, eg.:

我还稍微修改了加载数据的方式，以便将每一列加载到自己的数组中，例如：

x = [first_sheet.cell_value(i, 0) for i in range(first_sheet.ncols)]

you can have positive+negative errors for one value using errorbar by passing an array of the form:

通过传递以下形式的数组，您可以使用 errorbar 对一个值产生正+负错误：

yerr = [y_error_negative, y_error_positive]

where y_error_negativeand y_error_positive, are arrays that have the same length as y.

其中y_error_negative和y_error_positive, 是与 具有相同长度的数组y。

You should then have the following:

然后你应该有以下内容：

import xlrd
import numpy as np
import matplotlib.pyplot as plt
file_location = "C:/Users/Rima/Desktop/apjl731data.xlsx"
workbook = xlrd.open_workbook(file_location)
first_sheet = workbook.sheet_by_index(0)

x = [first_sheet.cell_value(i, 0) for i in range(first_sheet.ncols)]
y = [first_sheet.cell_value(i, 1) for i in range(first_sheet.ncols)]
yerr_pos = [first_sheet.cell_value(i, 2) for i in range(first_sheet.ncols)]
yerr_neg = [first_sheet.cell_value(i, 3) for i in range(first_sheet.ncols)]

yerr = [yerr_neg, yerr_pos]

plt.errorbar(x,y,yerr,fmt='r^')

plt.axis([0,5,0,5])
plt.show()

which gives this:

这给出了：

It's a bit more difficult to answer without more details.

如果没有更多细节，回答起来有点困难。

EDIT:

编辑：

If you have '-' in the data, there are plenty of ways to ignore it. So, a quick hack with the way I outlined above, you could re-check the x-values:

如果数据中有“-”，则有很多方法可以忽略它。因此，使用我上面概述的方式快速破解，您可以重新检查 x 值：

x y yerr_positive yerr_negative
1 1 0.1 0.2
- 2 0.1 0.2
3 3 0.1 0.2
4 4 0.1 0.2

You would then remove the '-' and replace with 0, for example,

然后，您将删除“-”并替换为 0，例如，

x = [float(i) if i != '-' else 0 for i in x]

Another way would be to loop over the values when loading them, and do an value if value.isdigit() else 0, without having two list comprehensions.

另一种方法是在加载它们时循环这些值，然后执行value if value.isdigit() else 0，而不需要两个列表推导式。

Or, you can ignore it completely like you said:

或者，您可以像您说的那样完全忽略它：

x = [float(i) for i in x if i!= '-']

It would be nicer not to waste your metallicity data if you could have some generic upper limit on the virgo infall distance. If you keep getting TypeErrors, give some more information.

如果您可以对处女座落入距离设置一些通用上限，那么最好不要浪费您的金属量数据。如果您不断收到 TypeErrors，请提供更多信息。