Introduction
Welcome to my blog which will be used for the
2015 Fall Semester Quantitative Analysis course offered at the University of
Wisconsin-Eau Claire and taught by Dr. Ryan Weichelt of the Geography and
Anthropology Department. This blog will serve the purpose of portraying the
course work we will be covering in class. Then be able to be show in a
portfolio style blog to showcase my skills I have developed over my years at
the University.
For more information on myself or other blogs
I have put together show casing my geospatial capacity please visit my website
at http://drakebortolameolli.weebly.com/
The first assignment for this class was to
understand the functions we can do with Microsoft Excel and to gain a better
understanding on statistical analysis. For this assignment we will be working
with Wisconsin Organic Farm and Goat Farm data for individual counties. In
Microsoft Excel we will be working with several statistical functions
including, but not limited to, range, mean, median, mode and standard
deviation. Lastly, we will be introduced into ArcGIS, and learn how to show
similar data sets in different ways.
Part
1: Descriptive Statistics
The first step of this assignment was to work
with test score data from Eau Claire North High School and Eau Claire Memorial
High School. We were given a sample of 20 different junior students taking the
test. We wanted to find out the Range, Mean, Median, Mode and Standard
Deviation of the test scores at each of the previously mentioned schools. After
typing all of the data into Microsoft Excel and sorting the data from highest
score to lowest score we got a list of test scores, with the highest being 198
(out of 200) at Eau Claire Memorial High School, and the lowest of 107, also
from Eau Claire Memorial High School. Next I calculated the Median, Mean,
Range, Mode and Standard Deviation for the test score data.
Table 1: Test Scores for both Eau Claire North and
Memorial High Schools
|
Eau Claire North High School Test Scores
|
|
Eau Claire Memorial High School Test Scores
|
|
164.5
|
Median
|
159.5
|
|
160.92
|
Mean
|
158.54
|
|
83
|
Range
|
91
|
|
170
|
Mode
|
120
|
|
23.635
|
Standard Deviation
|
27.158
|
|
111
|
Min
|
107
|
|
194
|
Max
|
198
|
The question for this part of the assignment
was that students at Eau Claire North High School have low scores, and because
of that should the teachers teaching methods be brought into question, or
possibly even fired?
The data shown in Table 1 shows although Eau Claire Memorial High School has the
highest test score, it does not mean they are doing that much better then Eau
Claire North High School. Actually, the data shows Eau Claire North High School
is doing better. They have a higher median (middle) test score value, a higher
mean (average) test score value, a lower range, meaning their test scores are
more grouped up leading to a smaller standard deviation. The smaller standard
deviation range means 68% of the students scored in the range of 137-184.
In conclusion, the data for test scores shows
that the students at Eau Claire North High School are actually doing better
than those at Eau Claire Memorial High School.
Part
2: Using Microsoft Excel and ArcGIS
The second part of this assignment was to use
the provided data on organic farms in Wisconsin. The premise is that an organic
goat farming firm is looking into establishing an organic goat farm and they
want to use my statistical and spatial knowledge to determine a place or
multiple places where they can establish a new farm.
First we wanted to calculate the average, in
order to do this we must use the “=” in an open box and then type “AVG” then “(”
after that we can select which part we want to use for this average. For my
data I used C2 through C73 (Figure 1).We
would use this same techniques in order to calculate the average for goat farms
located in individual counties as well.
Continuing on to finding out more about our
data we would then calculate the median, mode, skewness, kurtosis and standard
deviation in similar fashion for both data sets.
Table 2: Statistical Analysis for both Goat and
Organic Farm data in Wisconsin counties
|
Organic Farms
|
|
Goat Farms
|
|
16.389
|
Average
|
33.597
|
|
9.5
|
Median
|
31
|
|
5
|
Mode
|
29 and 35
|
|
6.26
|
Skewness
|
0.864
|
|
46.547
|
Kurtosis
|
0.538
|
|
28.2
|
Standard Deviation
|
23.204
|
(numbers rounded to 0.001 for space saving, actual numbers will be used for the maps below)
After calculating all of the data found in Table 2 we then had to create new
columns of data to show the difference from state average to county total for
number of organic farms. This could be done by creating a column titled
DIFFAVG. There are several ways to go about doing this next step. The way I did
it, I created a new column in between the newly created DIFFAVG and the already
established Organic Farms column. Next I pasted the State Average into this
newly created column, but to simplify this step I put a negative sign before
the State Average (that way I could add the values together just by hitting the
Autosum button). After calculating the Difference between counties and state
average we had some fields with negatives, meaning they had less farms then the
state average and some categories with positive numbers meaning they had more than
the state average. (See Figure 2 for
visuals).
 |
| Figure 2: Shows our data in the top right before the addition of the new columns to the creation of the new data set "DIFFAVG" |
Next, I brought in my Excel file containing
the goat and organic farm data into ArcMap. This file had to be joined with my
counties data. This was a simple task since all that was need to be done was
join by county name. Now I have by goat and organic farm data joined with my
Wisconsin County data, and it is now ready to be turned into Choropleth maps.
In total we will be making 5 different maps,
using the same data excel spreadsheet. All maps will be shown after explaining
what all five maps are supposed to be. The first map is a 4 class (color)
choropleth map showing the number of Organic Farms per county. This map, along
with maps 1-4 are all using the natural breaks four class selection. The second
map is showing the individual county’s percentage of Organic Farms compared to
the total number of Organic Farms in the state of Wisconsin. The third map, is
a map showing the difference between county numbers of Organic Farms compared
to the state average of Organic Farms. The last of the natural break maps is
the map showing the individual county’s percentage of Goat Farms compared to
the total number of Goat farms in the entire state of Wisconsin. The last map
is a map showing the standard deviation of Goat Farms.
Map
1: Number of Organic Farms per County in Wisconsin
The first map shows
the number of Organic Farms per County in Wisconsin. Looking at the map we see
the northern portion of Wisconsin is red. This red color represents 0-11
Organic Farms. As we go from red to yellow to green to blue the number of goat
farms per county increases. The map shows that counties located in western Wisconsin
show to have more Organic Farms then the rest of the state. Vernon County
(Blue) has the most Organic Farms in the entire state at 229. There are 39
different counties in Wisconsin which fall into the lowest category (red 0-11)
of Organic Farms, these counties are found in northern Wisconsin as well as
central/eastern Wisconsin.
 |
| Map 1: Number of Organic Farms per County in the state of Wisconsin |
Map 2: Percentage of all Organic Farms for the State
for each County
The
second map created shows the Individual Counties Percentage of Organic Farms to
the State Total. This map is using the same data as map 1 did and also the same
color scheme. Red is equal to 0-1%, yellow 2%, green 3-5% and blue is 6-19%.
This map appears identical to the one in the Map 1 section because we are using
the same data but instead of just comparing the total per county, we are
dividing that number by how many are in the state. Much like the first map, we
see very few 0-1% of the Organic Farms in northern and eastern border counties.
Again the majority of the Organic Farms are located in western Wisconsin, with
Vernon having the highest percentage, 19%.
 |
| Map 2: Percentage of all Organic Farms for the state of Wisconsin |
Map 3: The Difference between the State Average and
County Total
This
map is showing us the difference between the State Average and the number of
Organic Farms which are found in each individual county. Using the same data
and color scheme, we see once again a very similar map to the previous two
maps. Once again it is because we are using the same data category, but instead
of dividing it by the state total we are subtracting the state average from the
total. The reason why there are both positive and negative numbers on this map
is because some counties have less than the state average (16.389) and others
have more than that average. The map continues to prove the point that there
are little to no Organic Farms located in northern and central/eastern
Wisconsin. While it backs up the previous two maps showing western Wisconsin
has the majority of the Organic Farms, and once again Vernon County has the
largest differential between state average and county total (212.61).
 |
| Map 3: County difference between the state average of Organic Farms and the actual number of Organic Farms per county |
Map 4: Percentage of Goat Farms for the state for each
County
This
map is showing us the percentage for each individual county for number of Goat
Farms when compared to the number of Goat Farms which are in the total for all
of Wisconsin. Using the same color scheme and four category break down of the
data, we see something very different. Just as before northern Wisconsin
continues to be close to 0% of the total per county. Western Wisconsin also
continues to have a large portion of Goat Farms. But the data can be deceiving.
The range from lowest county to highest county percentage on this map is 4%.
Suggesting Wisconsin’s Goat Farms are spread out all over Wisconsin. The data
shows that 27 counties have less than 1% of the States Goat Farms, and the highest
value again falls in Vernon, County, but it is only 4.4% of all Wisconsin Goat
Farms. Vernon County had over 19% of the Organic Farms.
 |
| Map 4: Percentage of Goat Farms for the state for each County |
Map 5: Standard Deviation of Goat Farms in Wisconsin
This
map is different from the rest because we are no longer using a count but
instead a standard deviation from the mean. A standard deviation map breaks up
the data so 68% of the data is within 1 standard deviation of the mean, 95% of
the data is within 2 standard deviations of the mean, and 99.7% of the data is
within 3 standard deviations of the mean. Since our data had a mean of just
over 33 and a standard deviation of just over 23, we will not see any of our data
points be much more than 1 standard deviation below the mean, and that is
because we cannot have negative Goat Farms. We should expect to see 68% of the
counties fall within 10-56 Goat Farms. Again we see northern Wisconsin lacking
the Goat Farms and Western Wisconsin is thriving with them.
 |
| Map 5: Standard Deviation of Goat Farms in Wisconsin Counties |
Discussion
Looking
at all of the maps I created for this project I did see a trend on where the
farms were. Western Wisconsin had the majority of the farms while northern Wisconsin
had very few. This is because of the breakdown of Wisconsin. Northern Wisconsin
is mostly dense forests, and it is pretty hard to have a farm in a forest.
While western Wisconsin is relatively open, making it much more suitable for
farm land. Going back to the question at the beginning of the assignment with
the Organic Goat Farming Firm, I would suggest they look in western Wisconsin.
Vernon County proved to be a very suitable place for both Organic and Goat
Farms, assuming there is still adequate land left there for more farms I would
suggest there, otherwise any of the surrounding counties seem to do quite well
too. Not knowing much about Organic Goat Farms, but going off of my data I would
say Wisconsin could be suitable for them. There are 2419 Goat Farms located in
Wisconsin, according to our data, there are 72 counties in Wisconsin and a
state average of 33.6 per county. Looking at the maps and seeing the most Goat Farms
per county is about 4.4% of the state total (Vernon County) I would have to say
yes, go with an Organic Goat Farm. The map I feel shows the best reasoning for
where to put a farm is the Standard Deviation Map (Map 5). If the numbers were
included (mean and SD) everyone would know exactly where the goat farms are and
how spread out across the state goat farms are. Just to be clear, these maps
should not be used to actually plan out where to create a goat farm because
they are lacking lots of data. The only data I used was how many farms are in
each county. Further studies would have to be done and much more data would
have to be gathered. Nowhere in the data does it say what year is this data
from, or if this is just one years’ worth of data, maybe it is a combination or
years, or since there is no source, how do we know no one made up all these
numbers. Other data to consider before trying to create a goat farm is how well
other goat farms in the area doing are, or is there available land to use for a
goat farm, or even quality land to use.
Conclusion
In
this assignment we were given the tasks of working with statistical analysis
tools as well as getting a basic understanding of creating maps in ArcMap.
Creating 5 maps to show similar data in different formats proved to be a
success, and now I know where a good place to build a goat farm could be.
Sources
