1

I have data in one field and I want to transfer it to a specific field in another table based on specific keys Such as ID, road_type and veg_type. My original table looks like this

ID  Road_Type    veg_type      DENSITY
1   1            A             5.26
2   2            B             2.12
3   2            A             .564
3   1            C             1.23
4   3            B             .5
5   2            B             1.5

The Transfer table will have each possible combination of Road_Type and veg_type as a field and I want the density value to be placed under the field based on the ID, Road and veg keys.

ID  R1_VA  R2_VA  R3_VA  R1_VB  R2_VB  R3_VB R1_VC R2_VC R3_VC
1   5.26   0      0      0      0      0     0     0     0
2   0      0      0      0      2.12   0     0     0     0
3   0      .564   0      0      0      0     1.23  0     0
4   0      0      0      0      0      .5    0     0     0
5   0      0      0      0      1.5    0     0     0     0 

My code just gives me zeros

Here's what it looks like:

for fc in arcpy.ListFeatureClasses('*M'):

table1 = fc + 'table'
flds = ['ID', 'Road_Type', 'veg_type', 'DENSITY']

search_fc = {f[0:2]:f[3] for f in arcpy.da.SearchCursor(table1, flds)}

uc_flds = ['ID', 'R1_VA', 'R2_VA', 'R3_VA', 'R1_VB', 'R2_VB', 'R3_VB', 
         'R1_VC', 'R2_VC', 'R3_VC']

with arcpy.da.UpdateCursor(fc, uc_flds) as Cursor:
    for row in Cursor:
        ID = row[0]

        if search_fc.get(row[0]) == ID and search_fc[1] == '1' and search_fc[2] == 'A':
            row[1] = search_fc[row[0]]

        elif search_fc.get(row[0]) == ID and search_fc[1] == '2' and search_fc[2] == 'A':
            row[2] = search_fc[row[0]]

        #continue with elif statments for each field

        Cursor.updateRow(row)

        print str(row) +str(fc)

Is there anyway to fix it or is there a more efficient solution?

  • Which ArcMap version do you have? – BERA Jun 27 at 19:01
  • @BERA I have 10.5 – harry.p Jun 27 at 19:06
2

By converting it to string I was able to get it to recognize the ID. Theres probably a much better way but this is what I got.

for fc in arcpy.ListFeatureClasses('*M'):

table1 = fc + 'table'
flds = ['ID', 'Road_Type', 'veg_type', 'DENSITY']

searchDict = {str(f[0])+','+str(f[1])+','+str(f[2]):(f[3:]) for f in 
arcpy.da.SearchCursor(table1, flds)}

uc_flds = ['ID', 'R1_VA', 'R2_VA', 'R3_VA', 'R1_VB', 'R2_VB', 'R3_VB', 'R1_VC', 'R2_VC', 'R3_VC']


print str(table1)
with arcpy.da.UpdateCursor(fc, uc_flds) as Cursor:
    for row in Cursor:
        ID1 = row[0]+','+ '1' +','+ 'A'
        ID2 = row[0]+','+ '2' +','+ 'B'
        ID3 = row[0]+','+ '3' +','+ 'C'
        ID4 = row[0]+','+ '1' +','+ 'B'
        ID5 = row[0]+','+ '2' +','+ 'C'
        ID6 = row[0]+','+ '3' +','+ 'A'
        ID7 = row[0]+','+ '1' +','+ 'C'
        ID8 = row[0]+','+ '2' +','+ 'A'
        ID9 = row[0]+','+ '3' +','+ 'B'

        if ID1 in searchDict:
            row[1] = searchDict[ID1][0]
        else: row[1] =0

        if ID2 in searchDict:
            row[2] = searchDict[ID2][0]
        else: row[2] =0

        if ID3 in searchDict:
            row[3] = searchDict[ID3][0]
        else: row[3] =0

        if ID4 in searchDict:
            row[4] = searchDict[ID4][0]
        else: row[4] =0

        if ID5 in searchDict:
            row[5] = searchDict[ID5][0]
        else: row[5] =0

        if ID6 in searchDict:
            row[6] = searchDict[ID6][0]
        else: row[6] =0

        if ID7 in searchDict:
            row[7] = searchDict[ID7][0]
        else: row[7] =0

        if ID8 in searchDict:
            row[8] = searchDict[ID8][0]
        else: row[8] =0

        if ID9 in searchDict:
            row[9] = searchDict[ID9][0]
        else: row[9] =0

        Cursor.updateRow(row)

        print str(row) +str(fc)
2

You can also use pandas module which is included in 10.5:

pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.

import pandas as pd
import arcpy

fc = r'C:\data.gdb\table' #Change
fields = ['ID', 'Road_Type', 'veg_type', 'DENSITY']

df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc,fields), columns=fields) #Create dataframe using da.SearchCursor
df2 = pd.pivot_table(data=df, values='DENSITY', index='ID', columns=['Road_Type','veg_type'], fill_value=0) #Pivot
df2 = df2.stack().unstack().fillna(0) #Add all possible columns even though value is missing, for example 'R1_B'
df2.columns = ['R'+'_V'.join(map(str, col)) for col in df2.columns] #Multilevel to one level and add prefix R and V

df2 is now:

    R1_VA  R1_VB  R1_VC   R2_VA  R2_VB  R2_VC  R3_VA  R3_VB  R3_VC
ID                                                       
1   5.26   0.0  0.00  0.000  0.00   0.0   0.0   0.0   0.0
2   0.00   0.0  0.00  0.000  2.12   0.0   0.0   0.0   0.0
3   0.00   0.0  1.23  0.564  0.00   0.0   0.0   0.0   0.0
4   0.00   0.0  0.00  0.000  0.00   0.0   0.0   0.5   0.0
5   0.00   0.0  0.00  0.000  1.50   0.0   0.0   0.0   0.0

Then you can do df2.to_csv(r'C:\output\data.csv') and Table To Table to convert to the format you want

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.