I’m not sure how to get the data points out of Excel ibto Python:
- there are Excel to Python libraries, but I’ve never used them;
- or you can convert the spreadsheet to CSV and then use the
csv
module to import the data.
Let us know if you need more help with this step.
Once you have your data available to Python, put the x and y values in
two lists:
xs = [ ... ] # x values
ys = [ ... ] # y values
assert len(x) == len(y)
If the two sets of data aren’t the same length, you have to decide how to even them out. That might mean truncating the x values, or adding zeroes to the y values.
At this point we can work out an area under the curve, using numerical integration and an approximation to the hypothetical curve.
- Model the curve by a series of rectangles;
- Model the curve by a series of trapezoids;
- Simpson’s rule;
- or something more complicated.
There are three ways to do rectangular steps, but we can only use the left and right methods, not the midpoint method. There are two versions of Simpson’s rule, but both are a little complex so I’m going to skip them because I’m lazy.
This is a little easier than regular integration, because we don’t have a choice about the step size: we have to use the given points. So on that note, here are three methods for you to try:
from itertools import pairwise
xs = [1.0, 1.5, 2.0, 2.25, 2.5, 2.75, 3.75, 4.0, 4.25, 5.5]
ys = [3, 4, 7, 9, 6, 4, 5, 7, 9, 8]
assert len(xs) == len(ys)
def area_left(xs, ys):
area = 0
for (ax, ay), (bx, by) in pairwise(zip(xs, ys)):
h = bx - ax
area += h*ay
return area
def area_right(xs, ys):
area = 0
for (ax, ay), (bx, by) in pairwise(zip(xs, ys)):
h = bx - ax
area += h*by
return area
def area_trapezoid(xs, ys):
area = 0
for (ax, ay), (bx, by) in pairwise(zip(xs, ys)):
h = bx - ax
area += h*(ay + by)/2
return area
assert area_left(xs, ys) == 27.25
assert area_right(xs, ys) == 29.25
assert area_trapezoid(xs, ys) == 28.25
(Note: in general the trapezoidal method will be more accurate, unless the data points genuinely come from some sort of step function.)