using arcpy to extract values and calculate means from tables

Question

Hello

I have been given two tables, one has data that i need to link to the attribute table of a layer, but has no direct link to the layer.

The other table contains fields that link the layer attribute table and data table together.

data table (county links to link table fields)

Link table links LAD16CD to county of the data table and NUTS318CD of the feature layer

Feature layer attribute table

I need to link the tables together then extract the values for each county of interest and calculate the mean participation value from the data table and then link the mean to the feature layer.

in essence I need to have the attribute table of the feature layer have a field that contains the mean participation data for each NUTS3 code

i have no idea what approach to take and could do with some advice.

current code that prunes the data looks like this

arcpy.env.workspace = r"C:\Users\danie\Desktop\Python_assessment\data"

arcpy.TableToTable_conversion(os.path.join(data,'LAD_to_LAU1_to_NUTS3_to_NUTS2_to_NUTS1_January_2018_Lookup_in_the_UK'), Outdir, 'LAU.dbf')
arcpy.TableToTable_conversion(os.path.join(data,'SportsStatisticsForPython'), Outdir, 'Sports.dbf')

#declare paths to dbf paths as variables 
LAUtoNUT = Outdir + 'LAU.dbf'
Sports = Outdir + 'Sports.dbf'

fieldNameList = ['LAD16CD','LAD16NM','NUTS218CD', 'NUTS218NM','NUTS118CD','NUTS118NM'] #non required fields
arcpy.DeleteField_management(LAUtoNUT, fieldNameList) #removing fields from table
arcpy.JoinField_management(LAUtoNUT, "LAU118CD",Sports , "county", "") #joining the data and link tables 
arcpy.management.CopyRows(LAUtoNUT, Outdir + 'processed_data.dbf', )

blake.terhune · Answer

Are you able to apply this solution you found to this discussion?

DanielFuller · Answer

Yes thats correct, effectively they are two levels of governance, so there are multiple LAD16CD values for each NUTS318CD value. I need to workout the average participation value for each NUTS3 code, as that is the level for my feature layer.

I think i need to extract unique values of the NUTS318CD field but i'm stuck on how i would go about calculating the average for each field if you understand my meaning

blake.terhune · Answer

There appears to be a potential issue with the relationships between the fields you mentioned in the three tables. One NUTS318CD value can have many LAD16CD (county) values. Because of this, you'll be putting multiple different participation values together for each NUTS3 code. Do you intend to sum or average the participation values? Alternatively, you would need to create multiple NUTS3 code features, each with a different participation value.