Excel’s pants

I want to make a 3D graph from my flight stats i.e. given 3 columns, says X, Y and Z from the ground facing video (x 2) and down-facing LiDAR, I want a 3D picture.  It turns out you can’t just plug in these 3 columns into a 3D surface chart, you need a mesh which Excel doesn’t support directly, so you have to purchase another app; the first one I stumbled across was called XYZ MESH which cost $90 for a single user.  So instead I’ve done a simple version with 85 lines of python code.

Python Excel Mesh

Python Excel Mesh

These are the results of another square flight this morning.  There’s lots more that can be done such as working out how XL chose these colours, and getting the points to join up, but this is the hard bit done.  Here’s the code.

import csv

min_x = 0.0
max_x = 0.0
min_y = 0.0
max_y = 0.0

with open("3DSquare.csv", 'rb') as fp_csv:
    fp_reader = csv.reader(fp_csv)
    row_num = 0
    data = []
    for row in fp_reader:
        if row_num == 0:
            column_num = 0
            for column in row:
                if column.strip() == "qdx_fuse":
                    x_index = column_num
                elif column.strip() == "qdy_fuse":
                    y_index = column_num
                elif column.strip() == "qdz_fuse":
                    z_index = column_num
                column_num += 1    
            print
            print x_index
            print y_index
            print z_index    
        else:
            x = float(row[x_index].strip())
            y = float(row[y_index].strip())
            z = float(row[z_index].strip())

            if x < min_x: min_x = x if x > max_x:
                max_x = x
            if y < min_y: min_y = y if y > max_y:
                max_y = y

            data.append((x,y,z))

        row_num += 1


x_increment = (max_x - min_x) / 250        
y_increment = (max_y - min_y) / 250        

print row_num        

map_dict = {}
x_dict = {}
y_dict = {}
for (x, y, z) in data:

    x_index = int(round(x / x_increment))
    y_index = int(round(y / y_increment))


    x_dict[x_index] = True
    y_dict[y_index] = True
    map_dict[(x_index,y_index)] = z

lines = []
line = ""
for x in sorted(x_dict.keys()):
    line += ", %f" % x   

lines.append(line + "\n")   

for y in sorted(y_dict.keys()):
    line = "%f" % y

    for x in sorted(x_dict.keys()):
        if (x, y) in map_dict:
            line += ", %f" % map_dict[(x,y)]
        else:
            line += ", "

    lines.append(line + "\n")

with open("mashed.csv", "wb") as mash:
    for line in lines:
        mash.write(line)                

The reason I want this will become clear tomorrow as part of my last post for a week while I’m at Disney.


Ah, that looks better…

Way cooler!

Way cooler!

Leave a Reply

Your email address will not be published. Required fields are marked *