topic Re: Create variables from Excel in Python Questions

Create variables from Excel

DavidFotheringham1 — Wed, 20 Aug 2014 09:12:35 GMT

Hello. I have an excel table which contains a list of wind turbine IDs and their associated sound power levels at various octave bands.

The table roughly looks like this...

ID	63Hz	125Hz	250Hz	500Hz	1000Hz	2000Hz	4000Hz	8000Hz
T19	79.4	89.4	98.1	100.2	100.5	97.9	96.0	90.2

Previous I have manually input the variables for each turbine as an array in the following format

T19 = [79.4, 89.4, 98.1, 100.2, 100.5, 97.9, 96.0, 90.2]

Now I am looking of a way to automate many lines from an excel table to automatically create a variable like I have done manually. I have successfully been able to print the data in the correct format but obviously this isn't setting it as a variable.

Does anyone have any ideas how I could achieve this?

Many thanks for your help

David

Re: Create variables from Excel

RiyasDeen — Wed, 20 Aug 2014 10:20:47 GMT

Hi David,

Below code reads through a Excel datasheet and creates a dictionary. You can reference the values for your key column from the dictionary as key.

import arcpy
import arcgisscripting


gp = arcgisscripting.create()
def Message(sMsg):
    print sMsg
    gp.AddMessage(sMsg)


# Define the feature class  
table = "C:\Users\deenr\Desktop\Data.xlsx\Sheet1$"
keyField = "ID"
keyIdx = -1


fields = arcpy.ListFields(table)


# Get Key column Id
fIdx = 0
for field in fields:
  if field.name == keyField:
  keyIdx = fIdx
  break
  fIdx += 1


dictionary = {}


with arcpy.da.SearchCursor(table, ("*")) as cursor:
  for row in cursor:
  idx = 0
  for cell in row:
  if idx == fIdx:
  if dictionary.has_key(str(cell)) == False:
  dictionary[str(cell)] = []
  else:
  if dictionary.has_key(str(row[fIdx])) == True:
  dictionary[str(row[fIdx])].append(cell)

  idx += 1

# Print whole dictionary
Message(str(dictionary.items()))


for key in dictionary.keys():
  # Print Values
  Message(str(dictionary[key]))

Re: Create variables from Excel

DavidFotheringham1 — Wed, 20 Aug 2014 10:42:18 GMT

Thank you for your quick answer Riyas.

I had to tweak a few of the lines for the for loops (lines 23-26 and 33-41), hopefully I did this correctly. I do get an output from the code. One row from excel comes out as

('T2', []), ('97.9', []), ('97.1', []), ('95.8', []), ('95.9', []), ('95.1', []), ('74.9', [])

Is this what you would expect?

Re: Create variables from Excel

RiyasDeen — Wed, 20 Aug 2014 10:52:24 GMT

Hi David,

Not sure what change you made to the code. Below is the data used and output for the code I posted.

Line 46 is printing the whole dictionary out, including key and value.

Line 51 is printing the values for each key from the dictionary.

Re: Create variables from Excel

DavidFotheringham1 — Wed, 20 Aug 2014 11:02:26 GMT

import arcpy
import arcgisscripting




gp = arcgisscripting.create()
def Message(sMsg):
    print sMsg
    gp.AddMessage(sMsg)




# Define the feature class  
table = r"Z:\test1.xls\Sheet1$"
keyField = "ID"
keyIdx = -1




fields = arcpy.ListFields(table)




# Get Key column Id
fIdx = 0
for field in fields:
    if field.name == keyField:
        keyIdx = fIdx
    break
    fIdx += 1




dictionary = {}




with arcpy.da.SearchCursor(table, ("*")) as cursor:
  for row in cursor:
    idx = 0
    for cell in row:
        if idx == fIdx:
            if dictionary.has_key(str(cell)) == False:
                dictionary[str(cell)] = []
        else:
            if dictionary.has_key(str(row[fIdx])) == True:
                dictionary[str(row[fIdx])].append(cell)
    idx += 1


# Print whole dictionary
Message(str(dictionary.items()))




for key in dictionary.keys():
  # Print Values
  Message(str(dictionary[key]))

this is the updated code (not sure why the extra empty lines have added, that isn't shown on my screen!), again apologies if I've mess with how it works because we seem to have differing outputs. If it helps at all I'm using Python in PythonWin rather than directly through ArcGIS. Thanks for your help

Re: Create variables from Excel

MatthiasAbele — Wed, 20 Aug 2014 11:46:19 GMT

Dear David,

I am not sure for what purpose you would like to create the variable? But if you would like to create this kind of array you can use the Excel formula "concatenate" to create the array from your column values.

If you then like to transfer the Excel data to ArcGIS you can use the GISconnector for Excel. If you afterwards change your data again, in ArcGIS or in Excel, you can synchronize your data again using the GISconnector.

...everythin without Python

Best,

Matthias

Re: Create variables from Excel

RiyasDeen — Wed, 20 Aug 2014 11:46:29 GMT

Hi David,

There seems to be some line intend issue when i posted the script in the forum.

I have uploaded the script in this location.Variable_from_excel.py

Re: Create variables from Excel

DavidFotheringham1 — Wed, 20 Aug 2014 11:54:14 GMT

Perfect! Thanks so much Riyas!

Re: Create variables from Excel

DavidFotheringham1 — Wed, 20 Aug 2014 11:55:11 GMT

Thanks for your answer Matthias. That would appear to work well but we are hoping to keep everything in one self-contain python script. I'll keep it in mind for other work though