Need to format the below

Question

I have data in this format:

Name                   I                       II                     III                   IV

Label                  Coordinate      Humans         Humans        Coordinate

Latitude             29.48                 60.79            29.98            32.62

Longitude          -91.24               -83.24           -76.24         -81.56

How can I roll this data so I can have it all on one line? It is currently horizontally but I need it vertically. I tried to use the transpose and cross tab but it got a little confusing.

Also, how then would I be able to determine if Humans are within a 5 mile radius from the object (i.e., coordinate)?

I have tried a spatial tool but need some assistance. Thanks!

AngelosPachis · Accepted Answer

Hi @amandacastro ,

Not sure what you mean by rolling the data into a single line (is that line a row or is it a column?) but this is my take on the problem.

I first used a transpose tool to bring the data in a longer format, where in column Field1 I would have my column headers, in column Name I had something that groups the different records and in column Value I have, well all my values.

Then with a crosstab tool, I converted that back to a table that would have  a more useful format than the initial table; so now each field contains info about a particular value (long, latitude, label) rather than each group of values (I, II, III ...)

Then I have plotted the points, separated humans from coordinates and assigned a trade area to the humans. That will create a circle around that initial humans centroid, the radius of which I set to be equal to 50 miles.

Finally, with the spatial match tool, I checked whether a coordinate falls within that circle of 50 miles; if it does, then it will fall out of the "M" output anchor

Hope that helps, let me know if it works for you.

Regards,

Angelos

atcodedog05 · Accepted Answer

Hi @amandacastro

I hope this what you are looking for

Data shape:

I didnt find any humans around 5 miles radius for the data given by you maybe you might have it in the overall data. In my scenario I have increased the radius to 500 miles then I was able to get a match.

Just change the radius in trade area tool for your actual scenario

Workflow explanation:

1. Took first row has headers

2. Transpose to get it Vertical

3. CrossTab to get Lon,Lat horizontally

4. Create point

5. Filter as Object and Humans

6. Create Trade Area

7. Match Human point and Trade Area.

Hope this helps 🙂