Using data from an excel sheet to graph in python

Question

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.

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!

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 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. enter image description here

Thanks!

En example of how your excel file (or a dummy version) of it would be good, so that we can see how the date is organized. — Marcin
– Marcin, Commented Jul 8, 2015 at 23:59

jonnybazookatone · Accepted Answer · 2015-07-10 16:16:37Z

5

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:

yerr = [y_error_negative, y_error_positive]

where y_error_negative and y_error_positive, are arrays that have the same length as 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: enter image description here

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 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,

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.

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.

edited Jul 10, 2015 at 16:16

answered Jul 9, 2015 at 0:09

jonnybazookatone

2,28817 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rima Over a year ago

Hello, Thanks that really helps. My data looks basically like you said except sometimes there are gaps like

Rima Over a year ago

x y pos yerr neg yerr 1 2 3 4 3 2 - 5 0 9 4 6 and I think maybe that might be causing the error I keep receiving. TypeError: unsupported operand type(s) for -: 'unicode' and 'unicode'

jonnybazookatone Over a year ago

Updated the answer, you'll need to provide more things such as the traceback if you still have problems.

Rima Over a year ago

Hello, thanks for the response. I provided the traceback above since I kept getting the error

jonnybazookatone Over a year ago

Looks like it's not making floats out of the values loaded. Interesting that I had no problems. Try the edited version (ie., try casting the values to floats). This is why you get the traceback, as u'1' - u'1' does not do anything for strings/unicode.

|

Collectives™ on Stack Overflow

Using data from an excel sheet to graph in python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related