Using data from an excel sheet for a graph in python
So I have a lot of data currently in Excel spreadsheets. I need to draw 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 like columns of x, y coordinates and positive and negative y errors. I need this data to be imported from a spreadsheet and become points and error bars in the graph. To be honest, I am very new to python and have no idea why my code is not working.
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, just how to make graphs in excel using python. I'm sure my code is probably not that much to work with, but I'm not really sure what. Also for yerr to get a different error value at the top and bottom of the data point, I passed it as an array like yerr = np.array ([]) with different error values for each point. I don't know how to import the data as my positive errors and negative errors are in different columns in the spreadsheet. If anyone knows how to import data please help as it would make my life easier since I would not have to transfer a data point of type 50. Thanks!
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
I have spaces in my data that is a label with - I don't know if it would cause this error when trying to build. So I probably need a way to skip these lines. Thanks again.
Edit 2: I still have the error, so there is a traceback here. 
Thank!
source to share
I've made several assumptions. Assuming your data looks 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 also changed how you load data a little so that you load each column into its own array, like so:
x = [first_sheet.cell_value(i, 0) for i in range(first_sheet.ncols)]
you can have positive + negative errors for a single value with an error by passing an array of the form:
yerr = [y_error_negative, y_error_positive]
where y_error_negative
and y_error_positive
, are arrays of equal length y
.
Then you should 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 the following:
It's a little harder to answer without any details.
EDIT:
If you have a "-" in your data, there are many ways to ignore it. So, a quick hack with the above method, you can re-check the x values:
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
Then you remove the "-" and replace with 0, for example
x = [float(i) if i != '-' else 0 for i in x]
Another way would be to iterate over the values as they are loaded and done value if value.isdigit() else 0
without having two list concepts.
Or you can ignore it completely, as you said:
x = [float(i) for i in x if i!= '-']
It would be better not to waste your metallicity data if you could have an overall total limit at depressive distance. If you keep getting TypeErrors please give more information.
source to share