{"id":97,"date":"2021-07-14T08:40:52","date_gmt":"2021-07-14T13:40:52","guid":{"rendered":"https:\/\/sites.owu.edu\/geog-112\/?page_id=97"},"modified":"2022-07-11T16:46:43","modified_gmt":"2022-07-11T21:46:43","slug":"lab-3-data-processing-1","status":"publish","type":"page","link":"https:\/\/sites.owu.edu\/geog-112\/schedule-content\/lab-3-data-processing-1\/","title":{"rendered":"Lab 3: Data Processing 1"},"content":{"rendered":"<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p>Update 6\/11\/2022<\/p><\/blockquote>\n<p><strong>100 points<br \/>\n<\/strong>Assign: Wednesday, Sept. 21<br \/>\nDue: Monday, Sept. 26<\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p>Mappable Data<\/p><\/blockquote>\n<p>Mappable data is not truly mappable until you get it in a format your mapping software (in this case, ArcGIS Pro) is happy with. <em>You must do what software wants!<\/em><\/p>\n<p><strong>Thinking ahead is vital.<\/strong> We are going to create an animation of our time-series population data (population and population change from 1900-2020). That means the data we downloaded must be<\/p>\n<ul>\n<li>combined in <strong>one<\/strong> Excel file (which can be imported into ArcGIS Pro).<\/li>\n<li>have the data <strong>organized<\/strong>, in that file, in a manner that will allow us to animate the data.\n<ul>\n<li><strong>total<\/strong> population data (from the Census data), each decade 1900-2020<\/li>\n<li><strong>percent population change <\/strong>(derived data), 1900-1910, 1910-1920, etc.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>In the next lab (4), we&#8217;ll learn the basics of ArcGIS Pro and ArcGIS Online. In Lab 5 we&#8217;ll do even more data organization, in ArcGIS Pro, to allow us to animate our data. Lab 6 will focus on data classification and symbolization.<\/p>\n<p>Futzing around with data typically takes a lot of time in any mapping &amp; GIS Project. This one has a shitload.<\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p><strong>The Details<\/strong><\/p><\/blockquote>\n<p>The instructions below assume you are working on a Windows computer and using Excel. You can actually do this lab on a Mac but the commands are a bit different.<\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p>Data processing is exacting, picky, inflexible, and requires you to pay attention to a level of detail usually unknown to humankind. Focus and gird yourself!<\/p><\/blockquote>\n<p>Make sure you have all your census data and in the correct format. Secret details on where to find this data are <strong><a href=\"https:\/\/sites.owu.edu\/geog-112\/lab-1-mappable-data-secret-codicil\/\" target=\"_blank\" rel=\"noopener\">here<\/a>.<\/strong> My project includes:<\/p>\n<ul>\n<li>Two files for the 1900 &#8211; 1990 decennial census population data (files include all 50 states data)\n<ul>\n<li><strong>1900-90.txt<\/strong><\/li>\n<li><strong>cencounts.csv<\/strong><\/li>\n<\/ul>\n<\/li>\n<li>One file for the 2000 -2010 decennial census population data (file is specific to my state, Wisconsin)\n<ul>\n<li><strong>co-est00int-01-55.xls<\/strong><\/li>\n<\/ul>\n<\/li>\n<li>One file for the 2020 census population data (file is specific to my state, Wisconsin)\n<ul>\n<li>mine is\u00a0<strong>co-est2021-pop-55.xlsx<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><span style=\"color: #ff0000\"><strong>Please look up what the above three different file extensions<\/strong> (.txt, .csv, .xls) <strong>mean<\/strong><\/span> (in other words, what file types are they and what are their characteristics) and include them in your lab log entry for this lab.<\/p>\n<p>Open <b>Microsoft Excel<\/b>. Select <b>Open Other Workbooks<\/b> (lower left) then\u00a0<b>Browse<\/b> to your 1900-90 population data file (<strong>1900-90.txt<\/strong>). Select <b>All Files<\/b> to see your .txt file.<\/p>\n<p>Open your file. A <b>wizard<\/b> window should pop up. Wizrd! This is a tool for turning a simple text file into an Excel file. Neat. Store that away in your brain for the future.<\/p>\n<ul>\n<li>Original data type is <b>Fixed Width<\/b> (select if you need to, but you shouldn&#8217;t), then hit <b>next<\/b>.<\/li>\n<li>Set <strong>field widths<\/strong> (add column breaks): click and drag the location of the breaks (vertical lines). You can click and add breaks or click and drag off the &#8220;Data preview&#8221; to delete breaks. Shoot for the break right up against the right side of all the numbers in a column. Make sure you don&#8217;t lop off any numbers! That would be <i>bad. <\/i>Scroll up and down in the &#8220;Data preview&#8221; to make sure everything looks ok.\u00a0Hit <b>next<\/b>.<\/li>\n<li>Next window: column data format should be <b>general<\/b>&#8230;then hit <b>finish.<\/b><\/li>\n<\/ul>\n<p>Look at your data in Excel. <b>Holy haunch of <a href=\"https:\/\/en.wikipedia.org\/wiki\/Helmetshrike\" target=\"_blank\" rel=\"noopener\">Helmetshrike<\/a>!<\/b><\/p>\n<p>The data looks&#8230; well, like a <strong>bit of crap.<\/strong> What are the implications of free data from the WWW? Converting data is often a messy but exacting process. Halp us holy <a href=\"https:\/\/ifactory.com.au\/insights\/who-is-the-patron-saint-of-computers\/\" target=\"_blank\" rel=\"noopener\">St. Isidore of Seville<\/a>!<\/p>\n<ul>\n<li><strong>Save in Excel:<\/strong> File &gt;&gt; <strong>Save As<\/strong> &gt;&gt; Browse (to your Data folder). Save as Excel Workbook (.xlsx): <strong>1900-90.xlsx<\/strong><\/li>\n<li>why <strong>Save As<\/strong>?<\/li>\n<\/ul>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p>This data will take a bit of cleaning up!<\/p><\/blockquote>\n<p>Always be careful when cutting \/ pasting \/ adjusting. Turn your brain on please.<\/p>\n<ul>\n<li><strong>Remove<\/strong> the rows of <strong>jibberish<\/strong> above the FIPS and year row.<\/li>\n<li><strong>Remove<\/strong> the <strong>total<\/strong> US population row, and the <strong>total<\/strong> state population row (we won&#8217;t need this data for our project).<\/li>\n<li><strong>Remove<\/strong> the state abbreviation data<\/li>\n<li><strong>FIPS column:<\/strong> this is a unique identifier code for each geographic area (in this case, counties) in the US and World. For the US, the first two digits are the state, the last three digits the county. <span style=\"color: #ff0000\">Please look up information about FIPS codes and why they are important for GIS.<\/span> Comment on what you find in your lab blog.\n<ul>\n<li>Notice that <strong>numbers<\/strong> are <strong>justified<\/strong> <strong>right<\/strong> and <strong>text<\/strong> <strong>justified<\/strong> <strong>left<\/strong> in Excel. Guess what? When we go into ArcGIS Pro the software wants our FIPS codes to be text and not numbers! So we have to <strong>force<\/strong> the numbers to text.<\/li>\n<li>ArcGIS Pro assumes FIPS codes for states have <strong>five<\/strong> digits. If you have\u00a0<strong>four<\/strong> (Alabama through Connecticut) you will\u00a0have to add a zero before each of the FIPS codes. FYI, 01 is the same as 1. Problem is, if you add a zero before a number in Excel it will just disappear. Aghrgh.<\/li>\n<li><strong>Lettuce slay these two ducks with one sandwich:<\/strong> click on the first FIPS code (ending 001)\n<ul>\n<li>If you have Alabama through Connecticut, type a single quote and the number 0 then enter.<\/li>\n<li>All other states, type a single quote and enter.<\/li>\n<li>Notice that the FIPS code has 5 digits and is left justified and has a wee green triangle in the corner. That means it&#8217;s a number stored as text!\n<ul>\n<li>Do this for the rest of your FIPS codes<\/li>\n<li>There is a shortcut for doing all of these at once! See if you can figure it out.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li><strong>Add<\/strong> the word <strong>County<\/strong>\u00a0in the column above the county names<\/li>\n<li><strong>Replace<\/strong> any cell with &#8212; with an empty cell\n<ul>\n<li>what does &#8220;&#8212;&#8221; mean?<\/li>\n<\/ul>\n<\/li>\n<li>Delete the data for every other state but yours, and <strong>Save As<\/strong> a new file: mine is\u00a0<strong>wisconsin-1900-1990.xlsx<\/strong><\/li>\n<li><strong>Find<\/strong> all instances of the word <strong>County<\/strong> (with a single space before it) and <strong>replace<\/strong> all with nothing<\/li>\n<li><strong>Cut<\/strong> and <strong>paste<\/strong> the <strong>County<\/strong> column so it is just after <strong>FIPS<\/strong>\n<ul>\n<li>this follows the convention for county naming in ArcGIS Pro<\/li>\n<li><strong>Save<\/strong> the file<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>You should have a file that looks like the image below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-704\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/with1900-1990data.png\" alt=\"\" width=\"1030\" height=\"537\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/with1900-1990data.png 1030w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/with1900-1990data-300x156.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/with1900-1990data-1024x534.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/with1900-1990data-768x400.png 768w\" sizes=\"auto, (max-width: 1030px) 100vw, 1030px\" \/><\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p><strong>Update our file with the 2000, 2010, and 2020 data<\/strong><\/p><\/blockquote>\n<p><strong>Add<\/strong> three more columns: 2020, 2010, 2000:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-706\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/withaddedcolumns.png\" alt=\"\" width=\"1285\" height=\"542\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/withaddedcolumns.png 1285w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/withaddedcolumns-300x127.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/withaddedcolumns-1024x432.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/withaddedcolumns-768x324.png 768w\" sizes=\"auto, (max-width: 1285px) 100vw, 1285px\" \/><\/p>\n<p><strong>Open<\/strong> the file of 2000 and 2010 data, which should be specific to your state: mine, for Wisconsin, is <strong>co-est00int-01-55.xls<\/strong><\/p>\n<ul>\n<li><strong>Look at the data:<\/strong> you should see that it is formatted a bit differently, and does not include the FIPS numbers. But it does have the county names in alphanoodicle order.<\/li>\n<li>There are also more columns of data than we need.<strong> Only copy the columns we need<\/strong> and paste into the file we have been working on (mine is\u00a0<strong>wisconsin-1900-1990.xlsx)<\/strong><\/li>\n<li>Use the <strong>April 1, 2000 column.<\/strong> Copy only the County data (not the total population for your state)\n<ul>\n<li>I&#8217;m copying the column from Adams to Wood (click on Adams, shift-click on Wood)<\/li>\n<li>right mouse click and copy<\/li>\n<\/ul>\n<\/li>\n<li>Then go to your Excel file with the new empty column for Y2000 and click in the first cell\n<ul>\n<li>right mouse click, and under <strong>Paste Options match destination formatting<\/strong><\/li>\n<li>scroll down to make sure it looks right! Check that big and small population counties match the earlier data, and that there are the same number of rows of numbers<\/li>\n<\/ul>\n<\/li>\n<li><strong>Repeat<\/strong> for <strong>April 1, 2010<\/strong><\/li>\n<\/ul>\n<p><strong>Save As a new file:<\/strong> mine would be\u00a0<strong>wisconsin-1900-2020.xlsx\u00a0<\/strong><\/p>\n<ul>\n<li>Open\u00a0<b>your 2020 population file <\/b>(mine is <strong>co-est2021-pop-55.xlsx<\/strong>) in Excel<\/li>\n<li>check that the counties are in alphabetical order<\/li>\n<li>copy the <strong>April 1, 2020 Estimates Base<\/strong> column<\/li>\n<li>paste in the file with all your other data.\n<ul>\n<li><strong>Save<\/strong> your work<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p><strong>If there are commas in any of your population data, get rid of them.<\/strong><\/p>\n<ul>\n<li>select the columns with the commas and then right-mouse click and select <strong>Format Cells<\/strong><\/li>\n<li>under <strong>Category<\/strong> click on <strong>Number<\/strong><\/li>\n<li>make sure &#8220;Use 1000 Separator (,)&#8221; is <strong>not<\/strong> checked<\/li>\n<li>set <strong>Decimal Places<\/strong> to 0 (zero) and hit <strong>OK<\/strong><\/li>\n<li><strong>Save<\/strong> your file.<\/li>\n<\/ul>\n<p>Change the <strong>sheet name<\/strong> (tab at bottom) to <strong>1900-2020<\/strong> (right mouse click on it) reflecting the new range of data.<\/p>\n<p><strong>Delete the FIPS column:<\/strong> Typically FIPS is safer than words (like County names) but in this case, the county names work better.<\/p>\n<p><strong>My file for my state is below.<\/strong> Your file should look similar.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-720\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose.png\" alt=\"\" width=\"1198\" height=\"580\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose.png 1198w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose-300x145.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose-1024x496.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose-768x372.png 768w\" sizes=\"auto, (max-width: 1198px) 100vw, 1198px\" \/><\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p>Transposition<\/p><\/blockquote>\n<p>Turns out that having the years along the top and counties along the side of the Excel file is <strong>not<\/strong> correct for what ArcGIS Pro initially wants. Thus, we will <strong>transpose<\/strong> the rows and columns.<\/p>\n<p>In your file with all your data, <strong>click<\/strong> once on the upper left cell (County) and then <strong>shift-click<\/strong> on the farthest bottom and right cell (selecting all your data, but only your data).<\/p>\n<p><strong>Right-mouse click<\/strong>\u00a0once on the selected data and copy.<\/p>\n<p>Scroll down and <strong>click<\/strong> on an empty cell, about 5 cells down in the FIPS column.<\/p>\n<p><strong>Right-mouse click,<\/strong> then <strong>Paste Special<\/strong> and <strong>check<\/strong> the transpose checkbox and hit <strong>OK<\/strong><\/p>\n<p>Mine looks like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-721\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose2.png\" alt=\"\" width=\"1392\" height=\"543\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose2.png 1392w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose2-300x117.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose2-1024x399.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose2-768x300.png 768w\" sizes=\"auto, (max-width: 1392px) 100vw, 1392px\" \/><\/p>\n<p><strong>Delete<\/strong> the data above the transposed data.<\/p>\n<p><strong>Careful! Click once<\/strong> on the <strong>first row<\/strong> then <strong>shift click<\/strong> on the <strong>last empty row<\/strong> right above your transposed data. <strong>Right click<\/strong> on the selected data and <strong>delete<\/strong>.<\/p>\n<p>Check that you have all your County columns. This is mine (at least up to Door County)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-722\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose3.png\" alt=\"\" width=\"1392\" height=\"392\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose3.png 1392w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose3-300x84.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose3-1024x288.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose3-768x216.png 768w\" sizes=\"auto, (max-width: 1392px) 100vw, 1392px\" \/><\/p>\n<p><strong>Save as&#8230;<\/strong> and rename: mine is <strong>wisconsin-1900-2020_transpose.xlsx<\/strong><\/p>\n<p>Change <strong>County<\/strong> to <strong>Year <\/strong>(the\u00a0column header over the years) and <strong>Save<\/strong>.<\/p>\n<p>Thus this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-723\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose4.png\" alt=\"\" width=\"1548\" height=\"377\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose4.png 1548w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose4-300x73.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose4-1024x249.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose4-768x187.png 768w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/revisedpretranspose4-1536x374.png 1536w\" sizes=\"auto, (max-width: 1548px) 100vw, 1548px\" \/><\/p>\n<p>&nbsp;<\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p><strong>What a crap-load of work!<\/strong><\/p><\/blockquote>\n<p><strong style=\"font-size: 1rem\">This is all contingent on knowing what will allow us to some fancy animations in ArcGIS Pro.<\/strong><span style=\"font-size: 1rem\"> I&#8217;m telling you how to do it, but it took me a crap load of time and experimentation to get the data correct. In a different situation, you will have to experiment to figure out how to prepare your data so it works in ArcGIS Pro or some other GIS software. It&#8217;s no small feat!<\/span><\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p><strong>Tidy up your Data Folder<\/strong><\/p><\/blockquote>\n<ul>\n<li>In your Data folder, create a<strong> new folder<\/strong> called <strong>Original Data<\/strong><\/li>\n<li>Move <strong>everything<\/strong> <strong>but<\/strong> your two final .xlsx files (mine are\u00a0<strong>wisconsin-1900-2020_transpose_total.xlsx\u00a0<\/strong>and <strong>wisconsin-1900-2020_transpose_change.xlsx<\/strong>)\u00a0\u00a0into the original data folder<\/li>\n<\/ul>\n<p>Yeesh! <span style=\"color: #ff0000\">Jot down a few inspired paragraphs about the <b>joy of processing data for such mapping projects<\/b><\/span> in your digital lab blog.<\/p>\n<p><strong>Time to celebrate!<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-345 aligncenter\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2021\/09\/tgif.jpg\" alt=\"\" width=\"285\" height=\"439\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2021\/09\/tgif.jpg 285w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2021\/09\/tgif-195x300.jpg 195w\" sizes=\"auto, (max-width: 285px) 100vw, 285px\" \/><\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p>Completing the Lab<\/p><\/blockquote>\n<p>By end of class on the due date:<\/p>\n<p>Email me a copy of the Excel file with your combined Census population and % change data (1900-2020)<\/p>\n<p>Email me the link to your blog entry for Lab 3. This should include<\/p>\n<ul>\n<li>Definition of three different file extensions (.txt, .csv, .xls)<\/li>\n<li>Comments on FIPS codes<\/li>\n<li>Comments on processing data<\/li>\n<li>Any problems with this lab, and solutions to the problems<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p><strong>Extra Steps &#8211; Not Required Fall of 2022!<\/strong><\/p><\/blockquote>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p>Calculate Population Change in Excel<\/p><\/blockquote>\n<p>Population change data (from 1900-1910, 1910-1920) is better for visualizing using graduated symbol (or choropleth) maps. More on that later.<\/p>\n<p>It&#8217;s actually easier to calculate population change data in <strong>ArcGIS Pro.<\/strong> Alas, the way Pro likes data to be organized for animations makes such calculations impossible.<\/p>\n<p>We will instead use <strong>functions in Excel<\/strong>. A useful skill, this in essence allows you to build an equation in a cell or cells that calculates a new number. I can add up your scores for Geog 112, for example, and divide by the total number of points and multiply by 100 to get your % grade in the course.<\/p>\n<p>If you thought the last section was tedious, it gets worse. Again:<strong> brain on.<\/strong><\/p>\n<p>This is the step where you can <strong>easily create plausible bullshit data.<\/strong><\/p>\n<p>Excel functions are picky and easy to mess up. Thus, heads up.<\/p>\n<p>Add <strong>new column headers,<\/strong>\u00a0after your existing total population data, for your population change data: <strong>19001910<\/strong> then <strong>19101920<\/strong> then <strong>19201930<\/strong> and so on to <strong>20102020<\/strong><\/p>\n<p style=\"padding-left: 40px\">Notice that there is <strong>no dash or underline,<\/strong> just numbers: <strong>it has to be this way<\/strong> to work properly in ArcGIS Pro.<\/p>\n<p>Excel functions apply to a cell (or cells). The highlighting in Excel will look different than below, but in essence to calculate the % change from 1900-1910 use the function <strong>=((N2-O2)\/O2)*100<\/strong><\/p>\n<p style=\"padding-left: 40px\">make the value of the cell = to ((1910-1900)\/1900)*100<br \/>\nor, newest year minus the oldest year divided by the oldest year and times 100<\/p>\n<p style=\"padding-left: 40px\"><strong>N2<\/strong> is the total population in this county for 1910 (newest year)<br \/>\n<strong>O2<\/strong> is the total population cell in this county for 1900 (oldest year)<\/p>\n<p>What would the equation for <strong>1910-1920<\/strong> be?<\/p>\n<p style=\"padding-left: 40px\"><strong>=((M2-N2)\/N2)*100<br \/>\n<\/strong>and so on&#8230;<\/p>\n<p>The red cell is the <strong>population change<\/strong> in this county in Wisconsin (Adams) between 1900 and 1910. It dropped nearly 6 percent, thus the negative value. Population change can be either negative or positive.<\/p>\n<p>You can see the function in the function window above.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-712\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/percentchangeeq2-1.png\" alt=\"\" width=\"732\" height=\"327\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/percentchangeeq2-1.png 732w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/percentchangeeq2-1-300x134.png 300w\" sizes=\"auto, (max-width: 732px) 100vw, 732px\" \/><\/p>\n<p>I&#8217;ll demo this in class as it&#8217;s difficult to capture the highlights (which are set differently on different computers).<\/p>\n<p>Move slowly and methodically. Crazed clicking and selecting can easily screw up the functions and apply them to cells you don&#8217;t want them applied to. Stick to the following routine carefully.<\/p>\n<p>The basic routine is thus:<\/p>\n<ul>\n<li><strong>click once<\/strong> in the cell below your 19001910 column<\/li>\n<li>in the<strong> function window<\/strong> type <strong>=((N2-O2)\/O2)*100<\/strong><\/li>\n<li>this should highlight (with corresponding colors) the correct years (1900 and 1910)\n<ul>\n<li>if not, stop!<\/li>\n<\/ul>\n<\/li>\n<li><strong>Enter<\/strong><\/li>\n<li>the percent change for that county and that pair of years should appear.<\/li>\n<li>if not already selected, click once on your first calculation cell.<\/li>\n<li>carefully <strong>click on the tiny box<\/strong> in the lower right corner of the cell and <strong>drag<\/strong> straight down to the end of your data (not beyond) then release\n<ul>\n<li>this should apply the function to each of the subsequent counties<\/li>\n<\/ul>\n<\/li>\n<li><strong>review<\/strong> the numbers:\n<ul>\n<li>by hand (or using a calculator) check that the calculated data is correct for the first and last cell<\/li>\n<li>newest year minus the oldest year divided by the oldest year and times 100<\/li>\n<li>please do this for each first and last cell in the rest of the columns!\n<ul>\n<li><strong>Save<\/strong><\/li>\n<li>if good, continue with the next column<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>click once on the <strong>column header<\/strong> for <strong>19101920<\/strong><\/li>\n<li>click once on the first cell under 19101920<\/li>\n<li>copy the equation from <strong>here<\/strong> &#8211; <strong>not the function window!<\/strong>\n<ul>\n<li><strong>=((N2-O2)\/O2)*100<\/strong><\/li>\n<li>in <strong>caps<\/strong>, carefully change the function for 19101920: <strong>N to M and O to N<\/strong><\/li>\n<li><strong>check<\/strong> that the appropriate cells are <strong>highlighted<\/strong><\/li>\n<li><strong>Enter<\/strong><\/li>\n<li><strong>Save<\/strong><\/li>\n<li>then repeat the process of clicking and dragging down your column to calculate percent change for all the counties in your state between 1910 and 1920.<\/li>\n<li>check that the first and last cells in the column are correct with a quick hand calculation.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Repeat for each column.<\/p>\n<p>Save after each column is calculated.<\/p>\n<p>Slow and methodical.<\/p>\n<p>This is one of the most important steps to not make mistakes, moving quickly and not double checking your functions can lead to major screwups. This seems (is!) tedious, but it&#8217;s vital for accurate data.<\/p>\n<p>Part of my file with the percent change calculations:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-714\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/percentchgcalcsupdate.png\" alt=\"\" width=\"1481\" height=\"582\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/percentchgcalcsupdate.png 1481w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/percentchgcalcsupdate-300x118.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/percentchgcalcsupdate-1024x402.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/percentchgcalcsupdate-768x302.png 768w\" sizes=\"auto, (max-width: 1481px) 100vw, 1481px\" \/><\/p>\n<p>&nbsp;<\/p>\n<blockquote style=\"border: 0px solid #666;padding: 10px;background-color: #f2f2f2\"><p>Transposition (both total and change data)<\/p><\/blockquote>\n<p>Turns out that having the years along the top and counties along the side of the Excel file is <strong>not<\/strong> correct for what ArcGIS Pro initially wants. Thus, we will <strong>transpose<\/strong> the rows and columns.<\/p>\n<p>In your file with all your data, <strong>click<\/strong> once on the upper left cell (FIPS) and then <strong>shift-click<\/strong> on the farthest bottom and right cell (selecting all your data, but only your data).<\/p>\n<p><strong>Right-mouse click<\/strong>\u00a0once on the selected data and copy.<\/p>\n<p>Scroll down and <strong>click<\/strong> on an empty cell, about 5 cells down in the FIPS column.<\/p>\n<p><strong>Right-mouse click,<\/strong> then <strong>Paste Special<\/strong> and <strong>check<\/strong> the transpose checkbox and hit <strong>OK<\/strong><\/p>\n<p>Mine looks like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-699\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/transpose1update.png\" alt=\"\" width=\"1580\" height=\"590\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/transpose1update.png 1580w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/transpose1update-300x112.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/transpose1update-1024x382.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/transpose1update-768x287.png 768w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/transpose1update-1536x574.png 1536w\" sizes=\"auto, (max-width: 1580px) 100vw, 1580px\" \/><\/p>\n<p><strong>Delete<\/strong> the data above the transposed data.<\/p>\n<p><strong>Careful! Click once<\/strong> on the first row then shift click on the last empty row right above your transposed data. Right mouse click on the selected data and delete.<\/p>\n<p>Check that you have all your FIPS and County columns. This is mine:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-701\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_delete1.png\" alt=\"\" width=\"1565\" height=\"583\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_delete1.png 1565w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_delete1-300x112.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_delete1-1024x381.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_delete1-768x286.png 768w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_delete1-1536x572.png 1536w\" sizes=\"auto, (max-width: 1565px) 100vw, 1565px\" \/><\/p>\n<p><strong>Save as&#8230;<\/strong> and rename: mine is <strong>wisconsin-1900-2020_transpose.xlsx<\/strong><\/p>\n<p><strong>Click on the FIPS row and delete.<\/strong>\u00a0Typically you would use the FIPS codes to join this data to the existing map of your state, but the county name seems to work better in this case.<\/p>\n<p>Change <strong>County<\/strong> to <strong>Year <\/strong>(the\u00a0column header over the years) and <strong>Save<\/strong>.<\/p>\n<p>Thus this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-702\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_final.png\" alt=\"\" width=\"1560\" height=\"587\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_final.png 1560w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_final-300x113.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_final-1024x385.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_final-768x289.png 768w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/tramsposed_final-1536x578.png 1536w\" sizes=\"auto, (max-width: 1560px) 100vw, 1560px\" \/><\/p>\n<p>One more task: we need to make one file for the totals and one for the percent change data.<\/p>\n<p>File &gt;&gt; Save as&#8230; &gt;&gt; <strong>wisconsin-1900-2020_transpose_total.xlsx<\/strong><\/p>\n<p><strong>Delete<\/strong> the percent <strong>population change data<\/strong> and <strong>Save<\/strong>.<\/p>\n<p>Final population change data should look like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-717\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/totals.png\" alt=\"\" width=\"1572\" height=\"378\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/totals.png 1572w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/totals-300x72.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/totals-1024x246.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/totals-768x185.png 768w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/totals-1536x369.png 1536w\" sizes=\"auto, (max-width: 1572px) 100vw, 1572px\" \/><\/p>\n<p>Open <strong>wisconsin-1900-2020_transpose.xlsx<\/strong><\/p>\n<p>File &gt;&gt; Save as&#8230; &gt;&gt; <strong>wisconsin-1900-2020_transpose_change.xlsx<\/strong><\/p>\n<p><strong>Copy<\/strong> the rows with the <strong>population change data<\/strong><\/p>\n<p>Skip a few rows below, and <strong>Paste Special<\/strong>, then select <strong>Values<\/strong><\/p>\n<p><strong>Delete<\/strong> all rows above, <strong>except<\/strong> for the <strong>headers<\/strong>.<\/p>\n<p>Change the name of the sheet (at bottom) to <strong>1900-2000 Change<\/strong><\/p>\n<p><strong>Final population change data<\/strong> should look like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-718\" src=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/change.png\" alt=\"\" width=\"1573\" height=\"365\" srcset=\"https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/change.png 1573w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/change-300x70.png 300w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/change-1024x238.png 1024w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/change-768x178.png 768w, https:\/\/sites.owu.edu\/geog-112\/wp-content\/uploads\/sites\/186\/2022\/07\/change-1536x356.png 1536w\" sizes=\"auto, (max-width: 1573px) 100vw, 1573px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Update 6\/11\/2022 100 points Assign: Wednesday, Sept. 21 Due: Monday, Sept. 26 Mappable Data Mappable data is not truly mappable until you get it in a format your mapping software (in this case, ArcGIS Pro) is happy with. You must <span class=\"readmore\"><a href=\"https:\/\/sites.owu.edu\/geog-112\/schedule-content\/lab-3-data-processing-1\/\">Continue Reading<\/a><\/span><\/p>\n","protected":false},"author":138,"featured_media":0,"parent":52,"menu_order":4,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-97","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/pages\/97","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/users\/138"}],"replies":[{"embeddable":true,"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/comments?post=97"}],"version-history":[{"count":45,"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/pages\/97\/revisions"}],"predecessor-version":[{"id":724,"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/pages\/97\/revisions\/724"}],"up":[{"embeddable":true,"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/pages\/52"}],"wp:attachment":[{"href":"https:\/\/sites.owu.edu\/geog-112\/wp-json\/wp\/v2\/media?parent=97"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}