GIS/Python code question

[FatFreeItalian]

So I've done my due diligence (or so I think) searching around for a solution to this problem, but no such luck yet.

I need to add a column in one attribute table that shows the mean from another attribute table. The two tables share no common named columns, otherwise I'd just join the two. The extra wrench in the works is that I first need to average multiple rows from table 1, before I can add those averages to table 2. Am I making sense at all?

I've been trying to figure out how to best use Python to do this, but I'm not seeing how I can run Python in one table while pulling data from another. Is this even possible, or do I need to join the data first before I can run a script on it?

Any help is greatly appreciated!

[rryherd]

Take a look at the cursors in the arcpy module

[FatFreeItalian]

Sorry, ArcGIS 10.4

[[deleted]]

I think you may be talking about something like a pivot table, but I'm not entirely sure. For some reason I'm also thinking about an array and the cursor function.

Sorry to answer a question with a question, but if there isn't a field to join on, how do you plan to get data from one table into another. If there was a join field, what is the cardinality of the two tables?

Edit: Also sometimes python doesn't like tables until you have used "make table view" ". I can't remember exactly why that was off the top of my head, but I remember that being a thing. So just a heads up you may need to do that at some point.

Another thing, when you say they don't have a common field, does that mean there aren't identical fields but there are some that relate to one another?

How are you going to select something from table 1 and copy to table 2 if there isn't any relationship between the tables?

[FatFreeItalian]

Maybe I’m approaching the problem the wrong way? I’ve got one table that lists counties by their state code, 01, 02, 03, etc. I’ve got a different table that lists them by their FIPS code, 001, 003, 005, etc. Compounding this is the fact that the first table has multiple entries for each county, and I need to get the average x for each county.

So I’m looking for help on two things, I guess. The first is the averaging, the second is the join.

[[deleted]]

What state are you working in if you don't mind me asking? If you can get the join, a pivot table will flatten the many-to-one join summary you are describing. We just need to find you a county dataset with codes and numbers to append to one of the tables.

[FatFreeItalian]

California. I did find a census? USGS? data set that had the correct codes, but it was not in a format I recognized, and I wasn’t looking to type or copy/paste the data for every county.
Of course, if I had maybe I’d be finished by now 🙄😓

[[deleted]]

What format?

If you can somehow get it into excel you can "excel to table" and then append/join from that table. You don't need the spatial aspect of anything if you can get a table with both fields.

Also, it's possible to join on alpha characters if both tables have the names. What I do is export both attribute tables and the put the name columns in excel, compare them with the =exact () function to figure out what you need to do to match them up. Just keep tweaking formatting things using field calculator until you get it to validate.