topic Re: Excel cell value as variable? in Python Questions

Excel cell value as variable?

Anonymous User — Sat, 11 Dec 2021 19:00:51 GMT

I'm trying to pull cell values from the files I'm grabbing in the code below so I can write them in the loop for a new column. The column will be called ADT and their value is in cell A333.

import os, sys, pandas as pd

path = r"C:\Users\anthonyv\Downloads\Metro\New folder"

files = os.listdir(path)

data = []  # a list to hold your data

for filename in files:
    f = filename.split(' MP ')
    address = f[0]
    mile = '.'.join(f[1].split(' ')[0].split('_')) # convert underscore to period
    df = pd.DataFrame({'MP': ["{}".format(mile)],'RdNm': ["{}".format(address)]})
    data.append([address, mile]) 

df = pd.DataFrame(data,columns=['Road','Milepost'],dtype=float)
writer = pd.ExcelWriter('MetroReportAGOL.xlsx')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

print ("Complete")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Excel cell value as variable?

DanPatterson_Retired — Tue, 23 Oct 2018 22:09:11 GMT

what does it do now?

Re: Excel cell value as variable?

Anonymous User — Tue, 23 Oct 2018 22:13:17 GMT

It's pulling values from the filename and adding it to an excel table. I'm now wanting to pull cell data from within each file and add it to the excel table it is creating.

Re: Excel cell value as variable?

DarrenWiens2 — Tue, 23 Oct 2018 22:30:23 GMT

You first need to read the file into a dataframe:

excel = pd.read_excel('path/to/xlsx')‍

Then, find the value:

excel.iloc[row_index][column_name]‍

Re: Excel cell value as variable?

Anonymous User — Tue, 23 Oct 2018 22:36:37 GMT

I made the change mentioned above and am receiving the error below.

Re: Excel cell value as variable?

DarrenWiens2 — Tue, 23 Oct 2018 22:45:08 GMT

You do a bunch of work to get things into the dataframe within the loop, but never use it. Rather, you append to 'data' (2 columns), and then attempt to make a new dataframe from that (specifying 3 columns).

I assume you should use:

data.append([adt, address, mile])‍

Re: Excel cell value as variable?

JoshuaBixby — Tue, 23 Oct 2018 22:55:36 GMT

As an FYI, the issues you are running into have nothing to do with ArcGIS, ArcPy, or any other Esri product. Doing some online searching, especially in places like StackExchange/Overflow, will likely provide suggestions to users who are having similar issues as you are here. I am not discouraging you from posting on GeoNet, I just want to point out there are many other avenues to troubleshoot your problem once it has nothing to do with Esri.

Re: Excel cell value as variable?

Anonymous User — Tue, 23 Oct 2018 23:02:33 GMT

Thanks for you help!

Re: Excel cell value as variable?

Anonymous User — Tue, 23 Oct 2018 23:07:15 GMT

The final table that's created from this script will be updating an ArcOnline feature service. Thanks