Calculate unknown value between the known values

766
7
Jump to solution
07-16-2021 06:01 PM
Mick
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.

2021-07-17_10-48-32.png

 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.

 

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
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[0])  # sort list by x
    x0, y0 = [p for p in xy if p[0] <= x][-1] # get data point with next smaller x
    x1, y1 = [p for p in xy if p[0] >= x][0]  # 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



# load your csv file
csv_file = filedialog.askopenfile(title="Select csv file")
# generate [(x, y)]
csv_data = [line for line in csv.reader(csv_file)]
csv_file.close()
header = csv_data[0]
csv_data = csv_data[1:]
xy = []
for d in csv_data:
    x = None if d[0] in ['', ' '] else float(d[0])
    y = None if d[1] in ['', ' '] else float(d[1])
    xy.append((x, y))
# run interpolate_empty_values
new_xy = interpolate_empty_values(xy)
# write the result into a new csv file
new_csv_file = filedialog.asksaveasfile(title="Save csv file")
writer = csv.writer(new_csv_file)
writer.writerow(header)
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

View solution in original post

0 Kudos
7 Replies
DanPatterson
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...
0 Kudos
Mick
by
New Contributor

I need to do it in Python because it is my task.

0 Kudos
DanPatterson
MVP Esteemed Contributor

What have you got so far?

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


... sort of retired...
0 Kudos
Mick
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 

 

0 Kudos
Mick
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.

0 Kudos
JoeBorgione
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....
0 Kudos
JohannesLindner
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[0])  # sort list by x
    x0, y0 = [p for p in xy if p[0] <= x][-1] # get data point with next smaller x
    x1, y1 = [p for p in xy if p[0] >= x][0]  # 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



# load your csv file
csv_file = filedialog.askopenfile(title="Select csv file")
# generate [(x, y)]
csv_data = [line for line in csv.reader(csv_file)]
csv_file.close()
header = csv_data[0]
csv_data = csv_data[1:]
xy = []
for d in csv_data:
    x = None if d[0] in ['', ' '] else float(d[0])
    y = None if d[1] in ['', ' '] else float(d[1])
    xy.append((x, y))
# run interpolate_empty_values
new_xy = interpolate_empty_values(xy)
# write the result into a new csv file
new_csv_file = filedialog.asksaveasfile(title="Save csv file")
writer = csv.writer(new_csv_file)
writer.writerow(header)
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
0 Kudos