Import agate, the very neat data program


In [1]:
import agate

Import in the natural amenities file forcing some categories to be text to ensure that leading zeros are lost


In [2]:
text = agate.Text()
tester = agate.TypeTester(force={
    'FIPS': text,
    'CombinedFIPS': text,
})

natural = agate.Table.from_csv('naturalamenities.csv', column_types=tester)

Let's see what columns are in the table


In [3]:
print(natural)


|-------------------------+---------------|
|  column_names           | column_types  |
|-------------------------+---------------|
|  FIPS                   | Text          |
|  CombinedFIPS           | Text          |
|  STATE                  | Text          |
|  County name            | Text          |
|  CensusDivision         | Number        |
|  RuralUrbanCode         | Number        |
|  UrbanInfluenceCode     | Number        |
|  MeanJanuaryTemp        | Number        |
|  MeanJanuarySun         | Number        |
|  MeanJulyTemp           | Number        |
|  MeanJulyHumidity       | Number        |
|  TopographyCode         | Number        |
|  PercentWaterArea       | Number        |
|  NaturalLogPercentWater | Number        |
|  JanTempZScore          | Number        |
|  JanSunZScore           | Number        |
|  JulyTempZScore         | Number        |
|  JulyHumidityZScore     | Number        |
|  TopoZScore             | Number        |
|  WaterAreaZScore        | Number        |
|  NaturalAmenityScale    | Number        |
|  NaturualAmenityRank    | Number        |
|-------------------------+---------------|

Order the counties by the natural amenity scale and only select the county, state and scale amount columns


In [6]:
bottom = natural.order_by('NaturalAmenityScale').select([
        'County name', 'STATE', 'NaturalAmenityScale'
    ])

Let's see the 50 lowest scoring counties. You'll undoubtedly notice that South Dakota isn't featured once.


In [8]:
bottom.print_table(50)


|---------------+-------+----------------------|
|  County name  | STATE | NaturalAmenityScale  |
|---------------+-------+----------------------|
|  RED LAKE     | MN    |               -6.40  |
|  WILKIN       | MN    |               -6.10  |
|  TIPTON       | IN    |               -5.40  |
|  NORMAN       | MN    |               -5.37  |
|  MOWER        | MN    |               -5.18  |
|  PEMBINA      | ND    |               -5.18  |
|  TRAILL       | ND    |               -5.12  |
|  DODGE        | MN    |               -5.08  |
|  GRAND FORKS  | ND    |               -5.01  |
|  PENNINGTON   | MN    |               -4.97  |
|  KITTSON      | MN    |               -4.90  |
|  GRUNDY       | IA    |               -4.86  |
|  CASS         | ND    |               -4.84  |
|  BENTON       | IN    |               -4.71  |
|  CHAMPAIGN    | IL    |               -4.55  |
|  LYON         | IA    |               -4.49  |
|  KOSSUTH      | IA    |               -4.47  |
|  WINNESHIEK   | IA    |               -4.44  |
|  CLINTON      | IN    |               -4.37  |
|  CLARK        | WI    |               -4.34  |
|  ROSEAU       | MN    |               -4.30  |
|  ROCK         | MN    |               -4.25  |
|  OBRIEN       | IA    |               -4.23  |
|  PIATT        | IL    |               -4.21  |
|  RANSOM       | ND    |               -4.21  |
|  KOOCHICHING  | MN    |               -4.20  |
|  FRANKLIN     | IA    |               -4.18  |
|  CLAY         | MN    |               -4.17  |
|  HOWARD       | IA    |               -4.16  |
|  PIPESTONE    | MN    |               -4.13  |
|  MITCHELL     | IA    |               -4.10  |
|  FAYETTE      | IA    |               -4.09  |
|  HANCOCK      | IA    |               -4.06  |
|  HUMBOLDT     | IA    |               -4.05  |
|  MARSHALL     | MN    |               -4.05  |
|  YORK         | NE    |               -4.05  |
|  FLOYD        | IA    |               -4.03  |
|  FORD         | IL    |               -4.02  |
|  WAYNE        | NE    |               -4.02  |
|  IROQUOIS     | IL    |               -4.00  |
|  FILLMORE     | NE    |               -4.00  |
|  TOWNER       | ND    |               -3.99  |
|  JAY          | IN    |               -3.98  |
|  SIMPSON      | KY    |               -3.98  |
|  YELLOW MEDIC | MN    |               -3.98  |
|  RUSH         | IN    |               -3.97  |
|  OLMSTED      | MN    |               -3.96  |
|  LIVINGSTON   | IL    |               -3.95  |
|  KEOKUK       | IA    |               -3.95  |
|  WALSH        | ND    |               -3.95  |
|  ...          | ...   |                 ...  |
|---------------+-------+----------------------|

Let's print out an overall csv with only the FIPS and amenity scale so I can create a QGIS map.


In [24]:
naturals = natural.select([
        'FIPS', 'NaturalAmenityScale'
    ])
naturals.to_csv('natural-map.csv')