Plotting graph with mathplotlib with data from excel (XLRD)

Question

I'm getting an error ValueError: could not convert string to float. I realize my data contains empty values(' '). How do I remove them? I've tried filter and it didn't work.

book = xlrd.open_workbook('bioreactorfinal.xlsx')
sheet = book.sheets() [1]
data = [[sheet.cell_value(r,c) for c in range (sheet.ncols)] for r in range(sheet.nrows)]
x = sheet.col_values(3, start_rowx=1)
y = sheet.col_values(0, start_rowx=1)

plt.plot(x,y)
plt.xlabel('x')
plt.ylabel('y')
plt.title('test')

plt.show()

#print(data[:100])

[['Hours', 'VCD (Cells/mL)', 'Volume (mL)', 'Cells', 'Container Size'], [0.0, 300000.0, 16.666666666666668, 5000000.0, 'SF100'], [24.0, 600000.0, 16.666666666666668, 10000000.0, 'SF100'], [48.0, 1200000.0, 16.666666666666668, 20000000.0, 'SF100'], [72.0, 2400000.0, 16.666666666666668, 40000000.0, 'SF100'], [72.0, 300000.0, 133.33333333333334, 40000000.0, 'SF1000'], [96.0, 600000.0, 133.33333333333334, 80000000.0, 'SF1000'], [120.0, 1200000.0, 133.33333333333334, 160000000.0, 'SF1000'], [144.0, 2400000.0, 133.33333333333334, 320000000.0, 'SF1000'], [144.0, 300000.0, 1066.6666666666667, 320000000.0, 'BR5'], [168.0, 600000.0, 1066.6666666666667, 640000000.0, 'BR5'], [192.0, 1200000.0, 1066.6666666666667, 1280000000.0, 'BR5'], [216.0, 2400000.0, 1066.6666666666667, 2560000000.0, 'BR5'], [216.0, 300000.0, 8533.333333333334, 2560000000.0, 'BR40'], [240.0, 600000.0, 8533.333333333334, 5120000000.0, 'BR40'], [264.0, 1200000.0, 8533.333333333334, 10240000000.0, 'BR40'], [288.0, 2400000.0, 8533.333333333334, 20480000000.0, 'BR40'], [288.0, 300000.0, 68266.66666666667, 20480000000.0, 'BR200'], [312.0, 600000.0, 68266.66666666667, 40960000000.0, 'BR200'], [336.0, 1200000.0, 68266.66666666667, 81920000000.0, 'BR200'], [360.0, 2400000.0, 68266.66666666667, 163840000000.0, 'BR200'], [360.0, 300000.0, 546133.3333333334, 163840000000.0, 'BR2k'], [384.0, 600000.0, 546133.3333333334, 327680000000.0, 'BR2k'], [408.0, 1200000.0, 546133.3333333334, 655360000000.0, 'BR2k'], [432.0, 2400000.0, 546133.3333333334, 1310720000000.0, 'BR2k'], [432.0, 300000.0, 4369066.666666667, 1310720000000.0, 'BR20k'], [456.0, 600000.0, 4369066.666666667, 2621440000000.0, 'BR20k'], [480.0, 1200000.0, 4369066.666666667, 5242880000000.0, 'BR20k'], [504.0, 2400000.0, 4369066.666666667, 10485760000000.0, 'BR20k'], [528.0, 4800000.0, 4369066.666666667, 20971520000000.0, 'BR20k'], [552.0, 9600000.0, 4369066.666666667, 41943040000000.0, 'BR20k'], ['', 300000.0, 139810133.33333334, '', 'Not Enough Space'], ['', 600000.0, 139810133.33333334, '', 'Not Enough Space'], ['', 1200000.0, 139810133.33333334, '', 'Not Enough Space']]

the data is quite long. I can't send it through here. Any way you could help? There is a table of data and I just need the first and the 4th column data. There are empty cells at the end of both columns are there any way to remove them? — Soorieya Pillai
– Soorieya Pillai, Commented Dec 22, 2015 at 13:14
Just edit your question with a sample of your data. For example with print(data[:100]). I have no idea what you file looks like and how does xlrd read it, so it's hard to help. — Mel
– Mel, Commented Dec 22, 2015 at 13:16

Mike Müller · Accepted Answer · 2015-12-23 18:23:06Z

1

You can exclude the columns with missing data. This removes all rows where any entry in the first 4 columns is not float:

new_data = [row for row in data if all(isinstance(item, float) for item in row[:4])]

This selects the x and y values for plotting:

x = [entry[3] for entry in new_data]
y = [entry[0] for entry in new_data]

Now plot:

plt.plot(x,y)
plt.xlabel('x')
plt.ylabel('y')
plt.title('test')

plt.show()

edited Dec 23, 2015 at 18:23

answered Dec 23, 2015 at 18:17

Mike Müller

86k21 gold badges174 silver badges165 bronze badges

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

Comments

Oliver · Accepted Answer · 2015-12-24 00:20:44Z

0

After your x,y are defined you could filter out all non-float values, before giving to plt functions:

from numbers import Number
def isvalid(a, b):
    return isinstance(a, Number) and isinstance(b, Number)
xy = [xi,yi for xi,yi in zip(x,y) if isvalid(xi,yi)]
x,y = zip(*xy)

Any pair that has at least one non-member will be absent from the list given to plt.plot.

edited Dec 24, 2015 at 0:20

answered Dec 24, 2015 at 0:09

Oliver

30k12 gold badges81 silver badges114 bronze badges

Collectives™ on Stack Overflow

Plotting graph with mathplotlib with data from excel (XLRD)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related