Search code examples
attributesfieldarcgistemporal

ArcGIS Pro table field calculation: SUM of field where multiple attributes have same value

I'm working on an ArcGIS project where the table contains temporal data, with relevant fields that contain:

  • Zipcode
  • Year
  • Sales_Annual
  • Sales_Cumulative

The goal is to calculate the fourth by summing all of the attributes taxable sales for each zip code across all the years there is data for. E.g. If there are 10 attribute each for a different year for the same zip code, I want to sum all 10 of those sales cells in the Sales_Cumulative for all of 10 of those attributes.

I tried to see if I could use the field calculator function, but was not familiar enough to work out how to calculate for what I needed.

My other option is summing them outside of ArcGIS Pro in Excel.

Solution

  • You could use the tools Summary Statistics, Add Join, Calculate Field, and Remove Join.

    1. Use Summary Statistics to calculate the cumulative annual sale
    1. Join your table and the result of Summary Statistics using the field Zipcode

    1. Use Calculate Field to set Sales_Cumulative to !Sales_Statistics.SUM_Sales_Annual!

    2. Remove the join by using Remove Join

    Here is a screenshot of the model:

    Alternatively, you could use Python and a Spatially enabled DataFrame:

    import pandas as pd

from arcgis.features import GeoAccessor, GeoSeriesAccessor

table = r"Default.gdb\Sales"

# load table
sdf = pd.DataFrame.spatial.from_table(filename=table, skip_nulls=False)

# calulate the total sales by zip code 
total_sales_by_zipcode = sdf.groupby(["Zipcode"])["Sales_Annual"].sum()

sdf["Sales_Cumulative"] = sdf["Zipcode"].apply(lambda x: total_sales_by_zipcode[x])

sdf.spatial.to_table(location=table, overwrite=True)

    Notes:

    • This overwrites your table.
    • When using sdf.spatial.to_table, all field names are suddenly lower case.
    • Use skip_nulls when reading the table, otherwise no data is read due to a "bug". See here.