Jupyter notebook available at gis.utah.gov/presentations¶
Why Use pandas?¶
Easy to Reference Data¶
pandas uses a series of labels for both rows and columns so that we can refer to spefic values in a table, like a spreadsheet's row number and column name, or a feature class' ObjectID and field name. These labels are a fundamental part of pandas.
No more trying to remember what the "ith element of the jth row" refers to (and heaven help you if you're in a nested cursor).
Less Overhead for Data Managment than arcpy¶
Calling geoprocessing tools in arcpy requires setting up input and output layers, either on disk or in memory. The code is constantly jumping back and forth from python to the underlying geoprocessing libraries.
pandas data structures are native python structures kept in memory and are easily modified. No more creating a new feature layer just to change field names.
In addition, most non-spatial table operations are highly optimzied to run against a collection of data at the same time rather than operating element-by-element.
Potentially More Readable Code¶
As a consequence of not having to deal with feature layer management and verbose geoprocessing tool calls, pandas code can be much shorter and more concise. Once you're familiar with pandas syntax and programming patterns, you can create brief, expressive statements that perform several operations all in one go.
And again, no more nested cursors. Seriously.
Easy Interoperabilty with Other Data Sources and Processing Libraries¶
As one of (if not the) main go-to python libraries for data science, the pandas ecosystem is vast.
You can easily pull in data from spreadsheets, databases, web-based sources like (well-formatted) json and xml, and cloud-based tables like Google BigQuery.
Once you've got your data, there's a vast body of tutorials, examples, and production code to build off of (or just shamelessly steal). Other libraries have been written to extend pandas or accept data from pandas data structures, like geopandas and the ArcGIS API for Python's spatially-enabled dataframes.
Laying Our Foundation¶
Get Our Imports Out of the Way¶
import numpy as np
import pandas as pd
import arcgis
from arcgis import GeoAccessor, GeoSeriesAccessor
from pathlib import Path
Scalars and Vectors¶
A scalar is just a single value.
#: Most python variables can be considered scalars
foo = 5
bar = 'Midway'
baz = True
A vector is a collection of scalars or other vectors
No, not that.
Or that.
#: python lists and tuples
spam = [1, 2, 3]
eggs = ('foo', 'bar', 'baz')
ham = [foo, 1.7, 'zen']
[spam, eggs, ham] #: a 2-dimensional vector
Series and DataFrames: pandas' Fundamental Data Structures¶
A series is a collection of scalars. Normally it should have the same data type, but it can be mixed.
#: Build a series from a python list
pd.Series(['a', 'b', 'c'])
#: Series have an index, which allows you to reference individual elements with arbitrary labels
pd.Series([1, 2, 3], index=['foo', 'bar', 'baz'])
A dataframe is a two-dimensional collection of vectors with labels for both rows and columns. Basically, a spreadsheet in code.
#: Build a dataframe from a dictionary, where each key is a column name and each value is a list of values for that column
pd.DataFrame({
'foo': [1, 2, 3],
'bar': ['a', 'b', 'c'],
'baz': [True, 1.7, 'zen']
})
Under the hood, a dataframe is stored in memory as a collection of vectors (numpy arrays), one for each column. Thus, a lot of pandas operations occur on a column-by-column basis, like adding two columns together and storing the result in a third:
df = pd.DataFrame({
'a': [1, 2, 3],
'b': [4, 5, 6]
})
df['c'] = df['a'] + df['b']
df
Vectorized Operations¶
The key to understanding pandas operations is to think in terms of operations that are applied to every element in a vector, rather than extracting each element and passing it to the operation one by one.
#: Standard python: we're responsible for looping through a vector and calling a function on each element:
for element in [1, 2, 3]:
print(np.sqrt(element))
#: Vectorized operation: pass a whole vector to a vectorized function:
np.sqrt([1, 2, 3])
#: pandas operation: a method on a series or dataframe
pd.Series([1, 2, 3]).transform(np.sqrt)
for loops in python are computationally expensive and require extra resources to set up the iteration. In addition, the function has to be called multiple times, requirin even more work behind the scenes for each call.
In contrast, vectorized operations are optimized to perform the same operation on multiple pieces of data. In addition to avoiding the overhead from iteration and multiple function calls, the processor has special logic and routines for parallelizing many operations. However, to use these it needs to know the operation and the data type ahead of time, which it generally can't with python for loops.
Think about sending a set of data to an operation, not operating on data one piece at a time.¶
Let's load a dataframe¶
counties_df = pd.DataFrame.spatial.from_featureclass(r'data/county_boundaries.gdb/Counties')
counties_df.set_index('FIPS_STR', inplace=True) #: Replace the default index with one of our columns
counties_df.head()
A dataframe has rows and columns, and each of these has a collection of labels informally called an index. The row labels are considered the main DataFrame index, while the column labels are just called columns.
counties_df.index
counties_df.columns
Each row and column in a dataframe can be extracted as an individual series.
#: Column- series name is column name, series index is the main dataframe index
counties_df.loc[:, 'NAME'].head()
#: Row- series name is the row index label, series index is the column set
counties_df.loc['49005', :]
Data Types¶
Every column has a data type, just like fields in a feature class.
Most data types come from the numpy library (which, at least currently, provides a lot of the backend data structures for python).
# .info() gives us an overview of the dataframe, including the column types
counties_df.info()
Strings are a special case. By default, pandas uses the object dtype for columns that contain text. However, it could also contain multiple types.
mixed_df = pd.DataFrame({
'ints': [1, 2, 3],
'mixed': ['zero', 1, 2.1]
})
mixed_df.info()
Handling Missing Data¶
#: Numeric Nones convert to np.nan, strings stay as None
none_df = pd.DataFrame({
'foo': [1, 2, None],
'bar': ['four', None, 'six']
})
print(none_df.info())
none_df
#: Convert to nullable types; note capitalized Int64
converted_df = none_df.convert_dtypes()
print(converted_df.info())
converted_df
df[]: selecting columns and rows¶
The [] operator on DataFrames is overloaded and will do different things depending on what you pass to it:
- string: returns all rows in the indicated column as a series
- list of strings: returns all rows the indicated columns as a single data frame.
- python-esque slices: select rows (either by label or by index)
- sequence of booleans: all rows whose index matches the sequence index of a true value. This is where magic happens, because we can put conditional statements as the boolean sequence. The condition is evaluated on each row in the given column, and the resulting true/false value is passed to the indexing operator
[]to select specific rows. The length of the sequence must match the number of rows in the dataframe.
#: 1: single string gives a single column as a series
counties_df['NAME'].head()
#: 2: list of strings returns multiple columns as a dataframe
counties_df[['NAME', 'POP_LASTCENSUS']].head()
#: 3: Slicing returns the rows, all-inclusive (as opposed to python's all-but-end behavior) ???
counties_df[3:5]
4: sequence of booleans¶
head_df = counties_df.head().copy()
head_df
head_df[[True, False, True, True, False]] #: Note the list is the same length as our dataframe index
Let's filter our dataframe down to counties with population greater than 100,000
pop_series = head_df['POP_LASTCENSUS'].copy()
pop_series
#: Performing a comparison on a series returns a new series with the result of each comparison
pop_gt_100k = pop_series > 100000
pop_gt_100k
#: Pass our new boolean series as a boolean indexer
head_df[pop_gt_100k]
#: All the previous steps, just in one line of code
head_df[head_df['POP_LASTCENSUS'] > 100000]
#: Use .isin() to filter based on membership in a sequence
head_df[head_df['COLOR4'].isin([2, 3])]
head_df['COLOR4'].isin([2, 3])
.loc and .iloc: Selecting by Label/Index¶
.loc: Label-based¶
#: Single value: index label
counties_df.loc['49051']
#: Two values: index label, column label
counties_df.loc['49051', 'NAME']
#: Two values with everything slice: column as series
counties_df.loc[:, 'NAME'].head()
#: everything slice and list of labels: columns as DataFrame
counties_df.loc[:, ['NAME', 'POP_LASTCENSUS']].head()
#: everything slice and list of labels reversed: rows as DataFrame
counties_df.loc[['49001', '49003'], :]
.iloc: Position-based¶
#: Get the first row:
counties_df.iloc[0]
#: Use slicing to get the first column for the first five rows:
counties_df.iloc[:5, 0]
#: investigate the last row
counties_df.iloc[-1]
Common Problem: Chained Indexing and SettingWithCopyWarning¶
#: Create a copy so we don't mess with our original
test_df = counties_df.copy()
#: "get the rows that have a Central state plane and set the foo column to 3"
test_df[test_df['STATEPLANE'] == 'Central']['foo'] = 3 #: The [] calls are chained- do the first, then do the second
test_df.head()
#: Fix one: use .loc[] to perform the row and column indexing in one call
test_df.loc[test_df['STATEPLANE'] == 'Central', 'foo'] = 3
test_df.head()
#: Fix two: create an explicit copy to break up the chain
central_df = test_df[test_df['STATEPLANE'] == 'Central'].copy()
central_df['foo'] = 3
central_df.head()
Working With Columns¶
Pandas makes working with columns really easy. Whether renaming, re-ordering, or re-calculating, it's usually just a single line of code.
Let's take our counties dataset and calculate the population density, creating a new dataframe with just the relevant columns.
#: Create a copy to avoid altering the original
density_df = counties_df.copy()
#: Create a new column by assigning the results of a calculation against another column
# density_df['sq_km'] = density_df['SHAPE_Area'] / 1000000 #: shapely
density_df['sq_km'] = density_df['SHAPE'].apply(lambda x: x.area / 1000000) #: arcpy
density_df['density'] = density_df['POP_LASTCENSUS'] / density_df['sq_km']
density_df.head()
#: Change dtype of FIPS column
density_df['FIPS'] = density_df['FIPS'].astype(int)
density_df.head()
#: Rename columns with .rename() and a dictionary
density_df.rename(columns={'density': 'population_per_sq_km', 'POP_LASTCENSUS': 'pop_2020'}, inplace=True)
density_df.head()
#: Subset down to just the desired columns; reindex doesn't have inplace option
density_df = density_df.reindex(columns=['population_per_sq_km', 'NAME', 'ENTITYYR', 'STATEPLANE'])
density_df.head()
#: Another way to delete individual columns
del density_df['STATEPLANE']
density_df.head()
#: Or .drop, which returns a new dataframe
density_df.drop('NAME', axis='columns').head()
#: Update ENTITYYR
density_df['ENTITYYR'] = 2020
density_df.head()
density_df.info()
Working with strings¶
#: Use .str to access string methods of a series
density_df['NAME'] = density_df['NAME'].str.title() + ' County'
density_df.head()
#: Chain multiple .str calls together to get the first result from a split operation
density_df['NAME'].str.split().str[0].head()
Example: Cleaning Up Addresses for Geocoding¶
We have a CSV of school names and addresses that we want to geocode using the UGRC API Client.
The address field contains the entire address as a single string, but the API Client requires separate street and city/zip fields.
Some addresses use a newline character \n in between the street address and the city/state/zip address, while others just use a comma.
We need to pull out the street address from both types, and then grab the zip code as well.
#: Read a csv in, reanme the columns
in_df = pd.read_csv('data/schools.csv').rename(columns={'School name ': 'school', 'School address ': 'address'})
in_df.head()
#: First, work on addresses that use newlines
newline_df = in_df[in_df['address'].str.contains(r'\n')].copy()
print(len(newline_df))
#: Split on newline, then split on "(" to remove alternative street names, then strip whitespace
newline_df['street_addr'] = newline_df['address'].str.split(r'\n').str[0].str.split(r'(').str[0].str.strip()
newline_df.head()
#: Now operate on all the addresses that don't have a newline
#: The ~ is panda's negating operator (similar to !). We wrap the whole expression to be negated in ().
comma_df = in_df[~(in_df['address'].str.contains(r'\n'))].copy()
print(len(comma_df))
#: Just split on comma, taking the first piece
comma_df['street_addr'] = comma_df['address'].str.split(',').str[0]
comma_df.head()
#: Combine them back together with pd.concat (more on this later)
recombined_df = pd.concat([newline_df, comma_df])
#: The zip code is always the text after the last space
recombined_df['zip'] = recombined_df['address'].str.split(' ').str[-1]
recombined_df.sort_index().head()
#: Write to csv
recombined_df.to_csv('data\combined.csv')
Working with Rows: .apply()¶
The Wrong Way to Get Row Values¶
#: Get the values of each row as a named tuple- "fastest" iteration if you absolutely have to iterate
#: Find the FIPS value of all counties with population over 200,000
for row in counties_df.itertuples():
if row.POP_LASTCENSUS > 200000:
print(row.FIPS)
Iterating over the rows of a dataframe is like using using a a set of pliers to drive in a nail. It can be done, but it's slow and everyone will tell you to use a hammer instead.
Instead, change your thought process. Think about how your output could be expressed as a function of other columns within the dataframe. Using pandas' built-in vectorized functions is much faster and ultimately more readable.
#: use filtering and lists
list(counties_df[counties_df['POP_LASTCENSUS'] > 200000]['FIPS'])
Use .apply Instead¶
The .apply() method can be used to perform an aribtrary operation against data in a DataFrame. This is a shift in thinking: instead of extracting the data from the dataframe to pass to another function, you pass the function to the dataframe. This is much faster than iterating over the rows to get individual elements.
.apply sends a series of data to the specified function and combines the resulting data. If called directly on a series, it just sends that data. If called on a DataFrame, it either sends each column as the series of values in each row or each row as the series of values in each column.
The function passed via .apply can either aggregate the data (create a new output that is a function of the inputs) or transform the data (create a new element for eact input element).
#: Get some numeric data to work on
county_pop_df = counties_df[['POP_LASTCENSUS', 'POP_CURRESTIMATE']]
county_pop_df.head()
Aggrevating Aggregation¶
An agregation function takes a set of data and computes a value for each set. The output will have one dimension less than the input. Applying to a dataframe will result in a series, like taking the average of values:
county_pop_df.apply(np.mean) #: default is axis='rows', which applies the function to every row in a column
county_pop_df.apply(np.mean, axis='columns').head() #: change to pass columns (applied along the columns)
Note the differences with axis='columns' in aggregating functions. This parameter controls the contents of the series that is passed to the function.
The default (axis='rows') sends a series containing all the row values in a column to the function, repeating for however many columns there are. Thus, the function is applied along the rows.
Using axis='columns' instead sends a series containing all the columns to the function, repeating for however many rows are in the dataframe. Thus, the function is applied along the columns.
Transcontinental Transformations¶
A transformation function returns an output for every input and thus has the same dimensions as the input, such as taking the square root of all the values in the dataframe:
county_pop_df.apply(np.sqrt).head()
Using lambda Functions for Arbitrary Operations¶
lambda functions are small, one-line functions that don't use the normal def function_name(args): syntax.
They are useful for creating simple bits of code you can use with .apply without having to declare a normal function elsewhere in your code.
lambdas are callable objects meant to be passed to another function imediately after creation, instead of the normal behavior of assigning them a name for later reference.
#: Calculate the square kilometers of a geometry
def get_sq_km(geometry):
return geometry.area / 1000000
counties_df['SHAPE'].apply(get_sq_km).head()
#: Access the `.area` property of each geometry in the SHAPE column by applying a lambda function instead
counties_df['SHAPE'].apply(lambda x: x.area / 1000000).head()
lambda syntax¶
lambda functions are defined with the statement lambda var_name: <operations on var_name>.
var_name is a name you choose to refer to the input; x is used by convention but you can choose another name that is more applicable to your problem.
The body of the statement, everything after :, is what you want to do with the input
Rather than explicitely using a return statement, it implicitely returns whatever the operation creates.
#: Create a custom aggregation function for each row that references the column names
county_pop_df.apply(lambda row: (row['POP_LASTCENSUS'] + row['POP_CURRESTIMATE'])/2, axis='columns').head()
Groupby: Aggregation and Summarization by Category¶
.groupby splits a dataframe by the values of a column, applies an operation on that each chunk's sub-frame, and then combines the results into a data structure based on the type of operation performed.
#: Split by the different state plane projections, compute the mean of the population column, and recombine into a series
counties_df.groupby('STATEPLANE')['POP_LASTCENSUS'].mean()
Each groupby chunk is its own DataFrame, and any operation that can be done on a DataFrame can be done to the chunk. .groupby() returns a groupby object that handles the iteration over the DataFrames, and it also gives you access to the individual groups' DataFrames
#: Get a groupby object and list the groups
grouped = counties_df.groupby('STATEPLANE')
grouped.groups
#: Access an individual group's dataframe
grouped.get_group('South').head()
.groupby and .apply¶
Because .groupby creates dataframes and iterates an operation on each one, we can use .apply to perform any arbitrary function on each dataframe.
The function passed by .apply operates on the rows or columns of each chunk sub-frame just like it would when you use .apply on a normal dataframe, and the results from each group are combined back together.
If you use a tranformation function that returns a value for each input value, .apply thus returns a dataframe. The groubpy combine step then concats all the dataframes together into a new dataframe with the same index as the original.
This can be useful if you want to compare a value to the group's average, or apply a different correction value to each group.
#: calculate the percent contribution of each county's population to the group's total
plane_pop_df = counties_df[['STATEPLANE', 'POP_LASTCENSUS', 'POP_CURRESTIMATE']]
plane_pop_df.groupby('STATEPLANE').apply(lambda x: x/x.sum()).head()
If you use an aggregation function that returns a series for each group, the combine step concats these series into a new dataframe.
This can be useful for running the same operation on multiple columns in each group, like a descriptive statistic.
#: Get the average for each column by group. The apply acts across two series for each group dataframe and returns
#: a series for each, and then these are added as columns of our new dataframe
plane_pop_df.groupby('STATEPLANE')[['POP_LASTCENSUS', 'POP_CURRESTIMATE']].apply(np.mean)
Finally, if you use an aggregation function that returns a single value for each group, they are combined into a series.
A commone use case is to get the total value for each group, like summing populations.
plane_pop_df.groupby('STATEPLANE')['POP_LASTCENSUS'].sum()
While these different recombinations may seem a little trivial, it's important to understand them when you pass more complicated functions.
groupby Example: Broadband Data¶
The FCC has released new broadband availability data based on individual Broadband Servicable Locations (BSLs). While the BSL locations themselves are protected by license, we can download the available service info from broadbandmap.gov and analyze it.
The data are available for download by technology type, and there can be multiple records per location id within any technology types—one per provider that serves that location.
We'll take a folder of the downloaded CSVs, load and combine them into a single dataframe, classify the speeds into the FCC's three service levels (served, underserved, and unserved), and use .groupby to apply a classification function to determine which locations are served based on a subset of technologies.
#: Build a list of CSVs within a directory using Path's .glob() method
csv_dir = Path('data/fcc/')
csvs = list(csv_dir.glob('*.csv'))
#: Build a list of dataframes by reading in each one and adding a column with the technology name from the filename
dataframes = []
for csv in csvs:
tech = csv.name.split('_')[2]
tech_df = pd.read_csv(csv)
tech_df['technology_name'] = tech
dataframes.append(tech_df)
#: Combine all dataframes into a single dataframe
all_df = pd.concat(dataframes)
all_df['technology_name'].value_counts()
#: np.select uses a list of boolean arrays or series to determine which choice should be returned
#: at each appropriate index.
conditions = [
(all_df['max_advertised_download_speed'] >= 100) & (all_df['max_advertised_upload_speed'] >= 20), # 1
((all_df['max_advertised_download_speed'] >= 100) & ((all_df['max_advertised_upload_speed'] < 20) & (all_df['max_advertised_upload_speed'] >=3)))
| ((all_df['max_advertised_upload_speed'] >= 3) & ((all_df['max_advertised_download_speed'] < 100) & (all_df['max_advertised_download_speed'] >= 20))),
(all_df['max_advertised_download_speed'] < 25) | (all_df['max_advertised_upload_speed'] < 3),
]
choices = ['above 100/20', 'between 100/20 and 25/3', 'under 25/3']
all_df['classification'] = np.select(conditions, choices, default='n/a')
all_df.groupby('location_id').get_group(1010272409)
def get_location_id_status(location_df):
if (location_df['classification'] == 'above 100/20').any():
return 'served'
if (location_df['classification'] == 'between 100/20 and 25/3').any():
return 'underserved'
if (location_df['classification'] == 'under 25/3').any():
return 'unserved'
#: Subset to the desired techs
reliable_techs = ['Cable', 'Copper', 'Fiber-to-the-Premises', 'Licensed-Fixed-Wireless']
reliable_techs_df = all_df[all_df['technology_name'].isin(reliable_techs)]
#: Groupby individual locations (location_id) and apply our classification function
reliable_service_df = reliable_techs_df.groupby('location_id').apply(get_location_id_status)
reliable_service_df.head()
Joing Datasets: concat and merge¶
pd.concat: Adding rows or columns¶
Mainly useful when an operation creates another dataframe with the same column labels (ie, adding rows with the same schema) or the same index labels (ie, creating new columns for existing data).
#: Create a new dataframe of bike routes
bike_routes_df = pd.DataFrame({
'name': ['Main Street Trail', 'Benches', 'Beltway'],
'type': ['sidewalk', 'paved', 'paved']
})
bike_routes_df
#: Add another row
new_trail_df = pd.DataFrame({
'name': ['Provo Express'],
'type': ['paved']
})
combined_df = pd.concat([bike_routes_df, new_trail_df])
combined_df
#: Add a pair of new columns, which are added according to the index
new_columns_df = pd.DataFrame({
'status': ['open', 'open', 'open', 'planned'],
'condition': ['good', 'poor', 'failed', None]
})
print(combined_df.index)
print(new_columns_df.index)
new_combined_df = pd.concat([combined_df, new_columns_df], axis='columns') #: Note axis=1 to append columns instead of rows
combined_df.reset_index(inplace=True)
print(combined_df.index)
print(new_columns_df.index)
new_combined_df = pd.concat([combined_df, new_columns_df], axis='columns')
new_combined_df
Merge: Joining Two Disparate Datasets¶
.merge allows you to do SQL-style joins on two different dataframes based on a common key.
This provides much more flexibility than pd.concat on the columns used for the keys and allows you to specify the join type (inner, outer, etc).
#: first, let's drop the index column from the previous .reset_index() call
new_combined_df.drop(columns=['index'], inplace=True)
new_combined_df
#: Build our new dataframe of surface types and descriptions
surface_description_df = pd.DataFrame({
'surface_type': ['sidewalk', 'paved', 'gravel'],
'surface_description': ['A shared-use path usually consisting of concrete four to eight feet wide', 'An asphalt-paved shared-use path at least 10 feet wide', 'A gravel-based natural-surface path'],
})
surface_description_df
#: Inner merge: only rows whose key is in both dataframes
new_combined_df.merge(surface_description_df, left_on='type', right_on='surface_type', how='inner')
#: Outer: Keep all rows, no matter if the key is missing in one
new_combined_df.merge(surface_description_df, left_on='type', right_on='surface_type', how='outer', indicator=True)
Spatial Joins: Like a Table, but Spatial!¶
Question: How many people are there per supermarket in each county?
#: Load in the data
places_df = pd.DataFrame.spatial.from_featureclass(r'data/open_source_places.gdb/OpenSourcePlaces')
new_counties_df = pd.DataFrame.spatial.from_featureclass(r'data/county_boundaries.gdb/Counties')
#: Gives us the frequency of all the unique values in a series
places_df['category'].value_counts()
#: Returns all the unique values in a series and then sorts them
sorted(places_df['category'].unique())
#: Filter down to just supermarkets, make a copy
supermarkets_df = places_df[places_df['category'] == 'supermarket'].copy()
supermarkets_df.info()
supermarkets_df.head()
#: Try the join
supermarkets_df.spatial.join(new_counties_df)
print(supermarkets_df.spatial.sr)
print(new_counties_df.spatial.sr)
#: Reproject the supermarkets to Web Mercator
supermarkets_df.spatial.project(3857)
supermarkets_df.spatial.join(new_counties_df, how='inner', op='within')
#: Reset the index and call the spatial join again using method chaining
supermarkets_df.reset_index().spatial.join(new_counties_df, how='inner', op='within').head()
#: Now let's save the join and only get the name and population columns from the counties
new_supermarkets_df = supermarkets_df.reset_index(drop=True).spatial.join(new_counties_df[['NAME', 'POP_LASTCENSUS', 'SHAPE']], how='inner', op='within')
new_supermarkets_df.head()
Now lets use our join results to get the number of supermarkets per county and the total county population
#: Use groupby to get total count of rows in each group, 'category' is arbitrary column
new_supermarkets_df.groupby('NAME')['category'].count().head()
#: And just get the first population value in each group (they're all the same per group)
new_supermarkets_df.groupby('NAME')['POP_LASTCENSUS'].first().head()
#: Concat our groupby outputs into a new DataFrame
answer_df = pd.concat([new_supermarkets_df.groupby('NAME')['category'].count(), new_supermarkets_df.groupby('NAME')['POP_LASTCENSUS'].first()], axis=1)
answer_df.head()
#: Calculate our metric and clean up the column names
answer_df['people_per_supermarket'] = answer_df['POP_LASTCENSUS'] / answer_df['category']
answer_df.rename(columns={'category': 'supermarkets', 'POP_LASTCENSUS': 'pop_last_census'}, inplace=True)
answer_df.head()
#: Use chaining and line continuation to do it in one statement
new_answer_df = (pd.concat([new_supermarkets_df.groupby('NAME')['category'].count(), new_supermarkets_df.groupby('NAME')['POP_LASTCENSUS'].first()], axis=1)
.assign(people_per_supermarket= lambda x: x['POP_LASTCENSUS'] / x['category'])
.rename(columns={'category': 'supermarkets', 'POP_LASTCENSUS': 'pop_last_census'}))
new_answer_df.head()
#: Now let's join our new data back to the geometries using just name and shape columns
merged_df = counties_df[['NAME', 'SHAPE']].merge(answer_df, left_on='NAME', right_on='NAME')
merged_df.sort_values(by='people_per_supermarket').head()
merged_df.spatial.plot()
Let's Get Spatial, the Esri Version¶
We've already used some aspects of Esri's spatially-enabled dataframes, which are an extension to normal pandas dataframe namespace provided by the ArcGIS API for Python.
from arcgis.features import GeoAccessor, GeoSeriesAccessor
These imports add the .spatial attribute to pd.DataFrame, which provides access to a bunch of spatial methods and attributes. We've already used pd.DataFrame.spatial.from_featureclass(), .project(), .join(), and .sr.
The documentation for all the methods and attributes exposed through .spatial can be found in the GeoAccessor class of the arcgis.features module in the ArcGIS API for Python docs
Because the ArcGIS API for Python does not require ArcGIS Pro/Enterprise, it can use two different geometry engines for spatial data types and operations.
If arcpy is available in your python environment via ArcGIS Pro/Enterprise, it uses arcpy's underlying geometry operations (just without all the feature layer nonsense).
If arcpy is not available, it uses the shapely open-source library for geometry operations. The geometry objects will look a little different, and you won't be able to write to File GDBs (though you can still read from them).
Creating Spatially-Enabled DataFrames¶
#: From a Feature Class
pd.DataFrame.spatial.from_featureclass(r'data/county_boundaries.gdb/Counties').head()
#: From a hosted feature layer
feature_layer = arcgis.features.FeatureLayer('https://services1.arcgis.com/99lidPhWCzftIe9K/arcgis/rest/services/UtahCountyBoundaries/FeatureServer/0')
pd.DataFrame.spatial.from_layer(feature_layer).head()
#: From a dataframe containing lat/longs
input_df = pd.DataFrame({
'ugic': ['Midway', 'Vernal'],
'latitude': [40.52528, 40.45389],
'longitude': [-111.48883, -109.52327]
})
pd.DataFrame.spatial.from_xy(input_df, x_column='longitude', y_column='latitude').head()
Exporting Spatially-Enabled DataFrames¶
SEDFs can be exported to several different forms using the df.spatial.to_* methods.
to_featurest/to_feature_collection:arcgis.features.FeatureSetor.FeatureCollectionobjects.to_featureclass: Write to a feature class within a GDB or shapefile (depending on file extension and whether arcpy is present)to_featurelayer: Create or overwrite a hosted feature layer in aarcgis.gis.GIS(AGOL or Portal organization).
SEDF Tips and Tricks¶
Sometimes, we perform a .spatial operation only to get a weird error that seems to be related to geometries. We can use .spatial.validate() to make sure the Spatially-Enabled DataFrame is, well, spatially-enabled.
#: Use .validate to check if all the spatial bits are working
counties_df.spatial.validate()
What if this returns False? There are a couple common fixes.
#: First, make sure it's point to the right geometry column.
#: Don't try to keep multiple geometry columns in one DataFrame.
counties_df.spatial.set_geometry('SHAPE')
counties_df.spatial.name #: The name of the geometry column
#: If using shapely, projecting often misnames the .sr property, so set it manually
counties_df.spatial.sr = {'wkid': 3857}
Resources¶
