Using ArcMap 10 to join Excel data with a shapefile

hey this is Andrew long with journalism she is calm today I'm going to basically show you how to make a join I have some spatial data and I want to combine it with with an excel sheet this is great for taking data that you get from a source and then adding it to the locations of something in today's case I'm going to use airports so I have some passenger data that I've gotten and I want to add it to the to a shape file of airports and the shapefile is publicly available and the data you can actually create yourself if you can get into Excel and and create this so first thing I'm going to do now is so I'm in is arctan again this will work for earlier versions of Arc because it's basically set up the same over here I have where it has my layers this is going to be where my maps going to be made you'll see this big plus sign up here and that's how I add data so the first thing I want to do is kind of give myself some some you know spatial location that I'm looking at and since this is us dad I'm going to add a shape file of the States so I'm going to go to where I keep my my data my shape files and I'm going to add some States so I add the shapefile and I think this is Tiger data from the Census so there I have Alaska and States map basically just the shape file I've added it over here you'll see it's a layer it's got a weird funky census name I'm just going to name it States all right and it's kind of green I like to have these clear so I'm going to click on the little color box and I'm going to make no color and then hit okay so now I've kind of got my spatial background now I'm going to add a point layer so this is a polygon layer of States and what I want to do is add a point layer of actual airports and so I'm going to add that by getting my data and which is on my desktop and this is an airport shapefile so I click the the Add button and on any of this if you roll over the stuff it should tell you what it is but I'm adding a shapefile of airport locations so here we have Airport locations in the US and if we want to look at this data we can right click on this and see where says open attribute tables and we can basically look at what's attached to this data and right now see it's a point it's right here it says it's a point file it has low location ID so it has koat so it's a three-letter acronym for the airport it tells what it is there's some different features Airport this is a seaplane base it's the name of it some other stuff that I'm you know what state is in what what county these are things that are useful to attach the stuff so if I was doing with Arizona data I might just search for earlier I could pull out all the Arizona airports and get rid of the rest of the the the whole file so it's really kind of a way to look at to see what you have and this is also important because a join so if I'm going to take data from another source and join it to this shapefile I need to join it on a similar aspect because the computer wants to attribute so because the computer wants to join them together and so you're going to tell it what it's going to do now I've looked at my dad and I'm basically going to join on this location ID the three-letter ID of the airport because that's what my data is based on so I just took a look at the data and I can see what I'm going to join on and now I'm going to actually add the data that I've created in Excel and why don't we open that up for second in Excel so you can take a look at it so basically this is just a XLS file that that has some data in it that I'm going to actually join to this to the shapefile so here's what I've what I've gotten I have passenger data from 1970 1990 and 2009 and I'm going to join this data to that Airport data now you'll notice that it has an Origin column it has a name and it has passengers now I'm going to join with this origin because it's just going to be easier to match things so there's some couple things you need to keep in mind when you're joining data is that the computer is is looking at it and matching it based on you know exact stuff so spelling counts the way the data is organized counts now origin right here is going to be the easiest because it's the three-letter of the airport which is what I had in that location ID so we're going to kind of just add them together now couple things with joins there's also another data connection called relate what we're doing is a join it's a one-to-one there's there should be one list of passenger for each Airport now I do have different years but I'm only going to actually add one year at a time so you just have to understand that that joins are basically joining data exactly there's one point one spatial point that it's being attached to one data point which is what we're going to do here so now that I've looked at that I can actually add that to arc by using the add data and so when I hit that I'm going to go to here's my passenger XLS and I'm going to hit AB now I it shows me the different workbook pages so there's the 1970s there's a 1990s let's do 2009 so I'm going to add that data to to my Ark here I had already added one before so we have 2009 data right here now you'll notice a couple of things it's going to be in this list by source rather than the list by drawing order because we haven't done it's not drawn it's just data so if you're looking for it you're going to have to look in the look by source and you'll see that it's listed here here's the URL for where it's at and here's the data now we could look at this data like we can with with anything else so we'll just hit open and we hit open it's exactly like it was in Excel here's the origin here's the thing here's the passenger number and we can search and do things with that data but basically I just want to join that excel sheet to these this pitch shape file of airports that I have so here's my airport layer let's just name this airport alright so I'm going to right click on this or on the Mac double-click I'm using a MacBook Pro and so down here you'll see a thing called join and relate and I want to join the data to this shapefile so what I hit join it's going to ask me some questions and the first one is joined fields in this layer will be based on so basically wants to know what are the fields that you're going to and I'm going to do this join with and so since we've looked at that data we know that location ID is the three did the three letters that we're going to join with and I'm choosing the table since there's only one in there it's only giving me a choice of 1 is 2009 and I'm going to join it to the origin field because that had the three letters then it's going to give me an option to keep all records or to keep only matching records I'm going to keep only matching records now the thing you have to understand about joins is there then you can unjoin at any time so you're not really affecting your data at all you're basically just storing that connection at that time so at any further time you can remove the join and your data will be okay so it's not like you're actually rewriting that or anything but for this layer I just want to keep the records that are actually joined there should be about I think there were 60 some airports and you can also validate the join home again joins are time's joins our toughs because if you don't have the right things to join on it's not going to actually show you what you want or it's going to give you an error so I'm just going to hit okay and now you'll notice that most of the airports have disappeared and I only have about 60 I think there's 67 so airports in here these are the only the ones that had data attached to them the rest of them didn't have data and so they're they're not there now if we take a look at the attributes so if we open the attributes you'll see that it's only going to show you think it only shows you the ones that it's actually connected to so and we can still use the this info button up here to to look at our our data and it will tell you what it is if you want to look at it all right so now that I have the data attached to it which is useful but now I want to make a map and I want to show which airports have the most passengers in 1990 that's basically the map that I'm going to make so now I want to actually do something with that data that I've added so I'm going to right click or double-click on the airport layer and I'm going to go to properties and what we want to do is want to go to the symbology this is where how we make our maps and we're going to do quantiles quantities I'm sorry we're going to quantities and we're going to do proportional symbols so I want the circle to get bigger with more passengers and the value that I'm going to base it on was in our data and it's called passengers and I have a min value on a max value I'm going to kind of make this a little bit bigger so you can see somewhat increase the size of the minimum circle to 10 and I'm going to hit OK and when I hit apply you'll notice that the map the spots on the map get redrawn based on their data so these circles are proportional to the amount of passengers for each of those airports and so now I can basically start to see how my map looks and where the most passengers are and you know you can do some other things to this you can change the transparency under prop properties and display you know I can make the transparency something different so they kind of overlap each other one thing you also can that you should look at is that we didn't when we first made this if I open up the attribute table and I scroll down you'll actually see that and this is why they call it a join so if I look my original shape file only had up two counties it didn't have these last three data points these are the data points from the the excel file that I that I added in so I have origin description and passengers they are joined together and now I can export this map a file export I can make it an illustrator file if you need to send it to your news art or I can make it an image to put on the online there's a lot of other things I can do with this map once I made it now now that we've built this the other thing that I was alluding to is that we can also remove the join at any point we can just remove it and go back to where we were at so what I want to do is right-click on this and see where it says joined and relates I'm going to remove the join see it keeps track of it so there's 2009 I'm going to remove that join and then i'm back to my regular data I still have my airport shapefile with my airport locations and I'm you know with none of the data that I wanted to do so let's do this one more time I'm going to AB this time let's add the 1970s I'm going to add that data it's right here in my list by source I'm going to go back to my shapefile my airport shapefile I'm going to right click and I'm going to go to join and relate and I'm going to create a join I'm going to join the location ID now this time I'm a change it to 1970s and I'm going to join that to the origin because they're the same and I'm going to keep the matching records when I hit okay then I have just the airport's the top 67 airports in 1974 the United States now I am going to change the symbol symbolization so I'm going to go to properties symbology quantities proportional symbols I'm going to base the proportion on the gist the raw number of passengers I'm going to up my min value to 10 just to give it a little bit more size actually I'm going to change my color maybe blue I'm going to hit OK and I'm going to hit apply and you have a map of the most passengers in 1970 for specific airports so that's how you kind of do a join I know it's a quick now there are a lot of issues with most of the issues are with matching your your attribute that you're going to match on if you're going to have problems that's usually where it's at also there can be issues with the way you set up your Excel I'm going to put these files online so you can take a look at them and use them as a basis for practicing all right take care

Channel: JournalismGIS

Views: 208,475

Rating: 4.9245281 out of 5

Keywords: journalism, gis, arcgis, join

Id: mfyEXkkeLAg

Length: 14min 35sec (875 seconds)

Published: Wed Sep 29 2010