Excel cell value as variable?

180
8
Jump to solution
10-23-2018 02:33 PM
vonmoosa
MVP

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")
0 Kudos
1 Solution

Accepted Solutions
DarrenWiens2
MVP Honored Contributor

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

View solution in original post

8 Replies
DanPatterson_Retired
MVP Esteemed Contributor

what does it do now?

0 Kudos
vonmoosa
MVP

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.

0 Kudos
DarrenWiens2
MVP Honored Contributor

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]
vonmoosa
MVP

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

0 Kudos
DarrenWiens2
MVP Honored Contributor

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

View solution in original post

vonmoosa
MVP

Thanks for you help!

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

vonmoosa
MVP

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

0 Kudos