Excel cell value as variable?

[Anonymous User]

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")
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[DanPatterson_Retired]

what does it do now?

[Anonymous User]

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.

[DarrenWiens2]

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]‍

[Anonymous User]

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

[DarrenWiens2 · Accepted solution]

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])‍

[Anonymous User]

Thanks for you help!

[JoshuaBixby]

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.

[Anonymous User]

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