topic Re: How to aggregate data in an attribute table in Data Management Questions

How to aggregate data in an attribute table

StanleyMax — Sun, 07 Apr 2013 01:35:28 GMT

Hi everyone,

Among the fields in a shapefile's attribute table are two for state population in 2000 and state population in 2010. For these two, I need to create the percentage change for these and put that result into another field. I will do that with the following formula using the field calculator:

PopulationChange = (pop2010 - pop2000) / pop2000

OK. No problem there.

But here is what I don't know how to do:

Each state is also assigned to a region, and that assignment is in another field. For example, California is in the Pacific Region and Massachusetts is in the New England Region, and so on and so forth.

I need to aggregate the population data at this regional level. Thus, I will get the population change from 2000 to 2010 at this aggregated level.

I do not know how to do this aggregation using the field calculator.

Could someone please tell me how to do this.

Thank you very much for your help.

Stanley

Re: How to aggregate data in an attribute table

JoeBorgione — Sun, 07 Apr 2013 13:06:00 GMT

Take a look at creating a summary table: you can summarize on your region field and then create stats on any other field you may have.

Re: How to aggregate data in an attribute table

StanleyMax — Sun, 07 Apr 2013 20:53:53 GMT

Take a look at creating a summary table: you can summarize on your region field and then create stats on any other field you may have.

Hi Joe,

Thank you very much for your response.

I am still confused, however. So first, I summarize on the column entitled "region."

But then, I have these three questions:

> What statistic do I need? The average, the sum, or what, please?

> Into what column do I place this statistic?

> And here is what really perplexes me: How do I map this data? I guess that I don't understand the difference between an attribute table, to which a shapefile is attached, and a database file. This procedure of creating a summary table apparently creates a database file. But then how do I map the data that gets generated?

I am sorry to be so thick on this.

Thank you.

Stanley

Re: How to aggregate data in an attribute table

JoeBorgione — Mon, 08 Apr 2013 11:23:13 GMT

> What statistic do I need? The average, the sum, or what, please?

Didn't you say you are interested in the change in population by region? I would take the average of your 'PopulationChange' attribute mentioned in your original post.

> Into what column do I place this statistic?

The summary table will create it for you.

> And here is what really perplexes me: How do I map this data? I guess that I don't understand the difference between an attribute table, to which a shapefile is attached, and a database file. This procedure of creating a summary table apparently creates a database file. But then how do I map the data that gets generated?

You'll to join the summary table into the attribute table. This is the beauty of using a relational database. The attribute table and the summary table have a common field; 'REGION'. Thus they have a relationship. You will perform a Many to One join: Many records in your attribute table to One record in your Summary table. You can then map it via the Average field from your summary table

Surely they go over the cool stuff like this in your classes at Townson. Be sure to site this in your footnotes.... :cool:

Re: How to aggregate data in an attribute table

StanleyMax — Tue, 09 Apr 2013 01:28:46 GMT

Hi Joe,

Your reply was thorough, instructive, and extraordinarily helpful. It seems to have solved the problem, and I am now getting my map to draw just as I wanted.

I can assure you that I will give you full credit.

Thank you again. You are a great teacher.

Stanley

Re: How to aggregate data in an attribute table

JoeBorgione — Tue, 09 Apr 2013 12:57:34 GMT

Glad it worked out for you; summary tables and relates/joins are really powerful tool in many different applications.

All the best-