topic Re: Large dataset field population in Data Management Questions

Large dataset field population

SarahPierce — Wed, 15 Apr 2020 18:37:55 GMT

Please help! I need to populate a field in a statewide feature class (over 25 million records) based on four fields within the feature class. I'd like to be able to have it populate it using ModelBuilder or something since there are over 8,000 combinations of the 4 fields but I'm no coder. Here's what I'm essentially looking for:

If field1=a and field2=b and field3=c and field4=d, populate field5=e

Here's an example of the combinations from excel:

field1	field2	field3	field4	field5
Well drained	A	Bare Land	Forest	36
Well drained	A	Bare Land	Recreation	39

Re: Large dataset field population

JoeBorgione — Wed, 15 Apr 2020 20:21:06 GMT

Do you have a table of all the 8,000 + combinations?

Re: Large dataset field population

SarahPierce — Thu, 16 Apr 2020 11:45:50 GMT

I do!

Re: Large dataset field population

JoeBorgione — Thu, 16 Apr 2020 14:02:19 GMT

There's got to be a reasonable solution for it; the problem is the 25 million updates. My first thought is to go with an update cursor with a dictionary. However plowing through 25 million records is going to take some time.

What say you:

Dan Patterson‌

Joshua Bixby‌

Randy Burton‌

Re: Large dataset field population

JoshuaBixby — Thu, 16 Apr 2020 14:56:32 GMT

If you have a table with all 8,000 combinations, I suggest making new composite keys in each table that combine the 4 fields together as a text string. Then, join the two tables based on the composite key, and then run an update on the one table based on the joined values from the other table.

Another approach would be to load the 8,000 combination table into a nested Python dictionary and use a cursor to lookup values and update the field. I think my first suggestion will be easier for you to implement.

Re: Large dataset field population

DanPatterson_Retired — Thu, 16 Apr 2020 15:18:37 GMT

I would suggest examining the combinations first.

What are the combinations being used for... e.g. is a 'decision' being made based on a combination?

Are 8000 combinations really necessary?

How different are the combinations?

It would be a real waste of time and effort to have to aggregate at a later stage when you realize that some of the combinations weren't really necessary because of their similarity and influence on the 'end goal'

Re: Large dataset field population

DavidPike — Thu, 16 Apr 2020 17:46:56 GMT

could you just turn those field strings into coded values of 0 to 999 for example, then concatenate them

well drained = 002
A = 104

Bare Land = 065

Forest = 908

field 5 = 002104065908

well drained = 002
A = 104

Bare Land = 065

Recreation = 002

field 5 = 002104065002