topic Re: pivot table help in Geoprocessing Questions

pivot table help

stevekadle — Sat, 20 Nov 2010 17:20:30 GMT

I am using student edition of ArcGIS 9.3 in arctoolx does not contain pivot tables function. I was wondering if someone could help construct a model builder with excel data using so that i can link it to shape file the pivot table. found out my university has it. but because of the up comming thanksgiving breaks the GIS Lab will be closed for maintance. any help one give me is appreciated....... All the stuff been reading and looking at has lead me to conlusion this probably my only way to preced further in joining to my shapefile

Re: pivot table help

RichardFairhurst — Sat, 20 Nov 2010 19:04:59 GMT

I am using student edition of ArcGIS 9.3 in arctoolx does not contain pivot tables function. I was wondering if someone could help construct a model builder with excel data using so that i can link it to shape file the pivot table. found out my university has it. but because of the up comming thanksgiving breaks the GIS Lab will be closed for maintance. any help one give me is appreciated....... All the stuff been reading and looking at has lead me to conlusion this probably my only way to preced further in joining to my shapefile

WIthout the ArcInfo licence I do not think that there is any way to use ModelBuillder to acheive the results of the Pivot tool with a lower license. Have you done the pivot in Excel already? Is it Excel 2003 and can you export to dbase format? Or are you using Excel 2007? Anyway, perform the pivot with Excel and export the data first to see if the results are what you want before trying to script this.

I do not think Modelbuilder can do interop with any Excel program interfaces to actually perform a Pivot, it can just use Excel files (in Excel 2003 format) with its own tools. Integrating with Excel itself might be possible with native Python, but I don't know how to do that. A Python script can acheive the results of the Pivot tool without interop with Excel, if you have the original non-pivoted data in ArcMap, but someone would need to breakdown how pivots work and recreate the bahavior. Probably your lab will be back online before anyone can provide code to do that. Anyway, please decribe your current configuration better so we can figure out your best option.

Use a file geodatabasee if you want to be ready for the future of GIS.

Re: pivot table help

stevekadle — Sat, 20 Nov 2010 21:00:00 GMT

I currently have the spread I posted on my earlier posts on file here in th forum..... I am using excel 2007. have a current group in my shapefile a blocks as linking area. Know as A1 A2........ etc.

another Idea trying to create percentage of blocks filled as another student in my class suggested. looking at the data. not sure could happen

my shape file attribute table contains Fid Shape ID, Block, Number
my excel file contains following fields... block, Number 2nd family name, name, age, gender, birth, death as field.

each polygon supposed to represent group of burials occupied in it

Re: pivot table help

RichardFairhurst — Sat, 20 Nov 2010 23:34:52 GMT

I tested your data with the Pivot tool in Excel and found that the behavior in Excel is different from the behavior of the ArcGIS Pivot Tool. In Excel 2007 you can only have numeric values fill in the summary data area defined by the column and row values, but with the ArcGIS tool you can have numeric or string values in the summary data area. So actually the ArcGIS Pivot Table tool is what you need.

I exported your data to a CVS file from Excel. I had to rename the field called 2nd_FamilyName to SecondFamilyName because ArcMap does not like fields that begin with numbers. I opened the CVS file in ArcMap and exported that to a File Geodatabase table. In the new table I added a new field called CONCAT where I concatenated the various fields (except for Block and Number) into a single string value. Then I ran the Pivot Table tool and used Block as the row value and Number as the column value and CONCAT as the data values. I did not preorganize the Number field values with a sort operation so that the correct column order was generated, but that should not be a problem for a label expression and you can drag the columns where you want in a tableview or in Excel. The result is attached as a Zipped Text file that you should be able to open in ArcMap. There are still a few blocks with two rows, because of there being two individuals in the same Number plot. The Excel Pivot can reveal which block groups have two individuals in the same plot number from your original data and you then could edit the table to collapse those records, either by adding more fields or concatenating the two individuals in the same Block and Number to one row and column.

With this data you should be able to create a stacked label. The ArcGIS Pivot Table tool can be used to output mutiple variations of the table depending on the summary you wanted. For example it could have just used the FamilyName fields for the data values. However, multiple fields need to be concatenated into a single field prior to running the tool. Anyway, let me know if these results are basically what you needed and if you want other summary value variations.

Re: pivot table help

stevekadle — Sun, 21 Nov 2010 07:15:05 GMT

thanks I appreciate the help very much with this help and for looking at for me.

Re: pivot table help

stevekadle — Sun, 21 Nov 2010 07:19:10 GMT

I will take look at the tonight