# Calculate unknown value between the known values

766
7
07-16-2021 06:01 PM by
New Contributor

Good day, everybody.

Could you help me to develop a simple script?

I have two columns x and y (around 15 pairs).

I would like my script uses the known values to calculate the unknown values if I have only y values between the known values. For example, if y = 0.25, x = 2.5.

I think to provide .csv file with two columns (x and y) and another .csv with only one column y, so the script can calculate the known values applying the first .csv file.

I guess the output in python will be okay.

1 Solution

Accepted Solutions by MVP Regular Contributor
``````try:
from tkinter import filedialog
except ImportError:
import tkFileDialog as filedialog
import csv

def interpolate(xy, x):
"""Linear interpolation

xy: list of given data points as [(x, y)]
x: x-value of unknown data point

Returns the y-value as a linear interpolation between the given data points

"""
xy = sorted(xy, key=lambda p: p)  # sort list by x
x0, y0 = [p for p in xy if p <= x][-1] # get data point with next smaller x
x1, y1 = [p for p in xy if p >= x]  # get data point with next larger x
y = y0 + (y1 - y0) / (x1 - x0) * (x - x0)  # linear interpolation
return y

def interpolate_empty_values(xy):
"""Fills empty x or y values by linear interpolation using the known data points.

xy: list of data points as [(x, y)], x OR y can be null

Returns a list with interpolated values

"""
known_xy = [(x, y) for x, y in xy if not (x is None or y is None)]
known_yx = [(y, x) for x, y in known_xy]
filled_xy = []
for x, y in xy:
if not (x is None or y is None) or (x is None and y is None):
filled_xy.append((x, y))
elif x is None:
# Be very careful when interpolating x!
# Interpolating x for things like relief plots, time series,
# quadratic functions, etc. will give unintended results.
x_ = interpolate(known_yx, y)
filled_xy.append((x_, y))
elif y is None:
y_ = interpolate(known_xy, x)
filled_xy.append((x, y_))
return filled_xy

# generate [(x, y)]
csv_data = [line for line in csv.reader(csv_file)]
csv_file.close()
csv_data = csv_data[1:]
xy = []
for d in csv_data:
x = None if d in ['', ' '] else float(d)
y = None if d in ['', ' '] else float(d)
xy.append((x, y))
# run interpolate_empty_values
new_xy = interpolate_empty_values(xy)
# write the result into a new csv file
writer = csv.writer(new_csv_file)
for xy in new_xy:
writer.writerow(xy)
new_csv_file.close()``````

Paste that code into a script and run the script. You don't need arcpy or ArcGIS at all. In fact, expect errors or crashes when using the tkinter methods (filedialog.*) in the ArcGIS python window.

Have a great day!
Johannes
7 Replies by MVP Esteemed Contributor

A tad overkill to do this in Pro.

load the csv into excel and do a linear regression to get the equation of the line and scatterplot/line chart to plot the results if you are trying to fit one line.

If you are trying to do the inter-point values, then you just need the interpoint differences and get the simple interpolation value (y + dy/dx  etc)

... sort of retired... by
New Contributor

I need to do it in Python because it is my task. by MVP Esteemed Contributor

What have you got so far?

Or are you looking for python references to get you started?

... sort of retired... by
New Contributor

Well, it is better to say what I do not know how to start.

1) I do not know how to make the program understand the relationship between the known values to calculate the unknown value when the first value is known.

2) Certainly, I do not need any graphs, so I need only the numerical output.

I only indicated the path and that's all.

``````import arcpy, tkFileDialog

from arcpy import env
# Load the file with two known values
filePath = tkFileDialog.askdirectory(title='Select the file with the known values')
# Indicate workspace
env.workspace = filePath

print `````` by
New Contributor

If it is easier. It will be okay even if there is only one file. I mean the csv. can contain the known values and the values with only one known variable (for example y).

Thank you in advance for any ideas. by MVP Esteemed Contributor

As Dan suggests arcpy and or ArcGis Pro aren’t your best choices. Do a Google search on ‘python linear regression’. You’ll find several resources to get you started.

That should just about do it.... by MVP Regular Contributor
``````try:
from tkinter import filedialog
except ImportError:
import tkFileDialog as filedialog
import csv

def interpolate(xy, x):
"""Linear interpolation

xy: list of given data points as [(x, y)]
x: x-value of unknown data point

Returns the y-value as a linear interpolation between the given data points

"""
xy = sorted(xy, key=lambda p: p)  # sort list by x
x0, y0 = [p for p in xy if p <= x][-1] # get data point with next smaller x
x1, y1 = [p for p in xy if p >= x]  # get data point with next larger x
y = y0 + (y1 - y0) / (x1 - x0) * (x - x0)  # linear interpolation
return y

def interpolate_empty_values(xy):
"""Fills empty x or y values by linear interpolation using the known data points.

xy: list of data points as [(x, y)], x OR y can be null

Returns a list with interpolated values

"""
known_xy = [(x, y) for x, y in xy if not (x is None or y is None)]
known_yx = [(y, x) for x, y in known_xy]
filled_xy = []
for x, y in xy:
if not (x is None or y is None) or (x is None and y is None):
filled_xy.append((x, y))
elif x is None:
# Be very careful when interpolating x!
# Interpolating x for things like relief plots, time series,
# quadratic functions, etc. will give unintended results.
x_ = interpolate(known_yx, y)
filled_xy.append((x_, y))
elif y is None:
y_ = interpolate(known_xy, x)
filled_xy.append((x, y_))
return filled_xy

# generate [(x, y)]
csv_data = [line for line in csv.reader(csv_file)]
csv_file.close()
csv_data = csv_data[1:]
xy = []
for d in csv_data:
x = None if d in ['', ' '] else float(d)
y = None if d in ['', ' '] else float(d)
xy.append((x, y))
# run interpolate_empty_values
new_xy = interpolate_empty_values(xy)
# write the result into a new csv file
writer = csv.writer(new_csv_file) 