<jupyter_start><jupyter_text>Data Analysis Using Python: A Beginner’s Guide Featuring NYC Open Data Part 1: Reading and Writing Files in Python<NAME> Table of Contents=================   * [Getting Started](-Getting-Started:-Accessing-the-Building-Footprints-Dataset)       * [1. Search NYC Open Data in Google](1.-Search-NYC-Open-Data-in-Google)       * [2. Search "Building Footprints" in NYC Open Data search bar](-2.-Search-"Building-Footprints"-in-NYC-Open-Data-search-bar)       * [3. Select "Building Footprints" Dataset](3.-Select-"Building-Footprints"-Dataset)       * [4. The Building Footprints Dataset Page](4.-The-Building-Footprints-Dataset-Page)                 * [1. Reading In Data](-1.-Reading-In-Data)       * [1.1 Reading in data as csv in static form](-1.1-Reading-in-data-as-csv-in-static-form)       * [1.2 Reading in data as json in static form](-1.2-Reading-in-data-as-json-in-static-form)       * [1.3 Reading in shapefile data](-1.3-Reading-in-shapefile-data)       * [1.4 Unzipping and reading in data as csv in memory](-1.4-Unzipping-and-reading-in-data-as-csv-in-memory)       * [1.5 Unzipping and reading in data as csv to local folder](-1.4-Unzipping-and-reading-in-data-as-csv-to-local-folder)       * [1.6 Unzipping and reading in data as csv from local folder](-1.4-Unzipping-and-reading-in-data-as-csv-from-local-folder)       * [1.7 Reading in data from Socrata Open Data API (SODA)](-1.5-Reading-in-data-from-Socrata-Open-Data-API-%28SODA%29)                 * [2. Writing Out Data](-2.-Writing-Out-Data)       * [2.1 Writing to a CSV file](-2.1-Writing-to-a-CSV-file)       * [2.2 Writing to a Excel (xlsx) file](-2.2-Writing-to-a-Excel-%28xlsx%29-file)       * [2.3 Writing to a JSON file](-2.3-Writing-to-a-JSON-file)                 * [3. Reading In Data from Local Folder](-3.-Reading-In-Data-from-Local-Folder)       * [3.1 Reading in a CSV file](-3.1-Reading-in-a-CSV-file)       * [3.2 Reading in an Excel file](-3.2-Reading-in-an-Excel-file)       * [3.3 Reading in a JSON file](-3.3-Reading-in-a-JSON-file)                 * [4. Conclusion](-4.-Conclusion) **Goal:** In this notebook, we will review various ways to read (load) and write (save) data from NYC Open Data. Specifically, we will focus on reading our data into a pandas dataframe.**Main Library:** [pandas](https://pandas.pydata.org/) is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language.<jupyter_code># importing libraries
import pandas as pd
import numpy as np
import geopandas as gpd
from fiona.crs import from_epsg
import matplotlib.pyplot as plt
import os
import urllib
import json
import requests
from io import BytesIO
from sodapy import Socrata
import zipfile
from zipfile import ZipFile
from os.path import basename
from openpyxl import Workbook<jupyter_output><empty_output><jupyter_text>Printing verions of Python modules and packages with **watermark** - the IPython magic extension.Documention for installing watermark: https://github.com/rasbt/watermark<jupyter_code>%load_ext watermark

%watermark -v -p numpy,pandas,geopandas,matplotlib.pyplot,json,requests,sodapy<jupyter_output>The watermark extension is already loaded. To reload it, use:
  %reload_ext watermark
CPython 3.7.1
IPython 7.20.0

numpy 1.19.2
pandas 1.2.1
geopandas 0.8.1
matplotlib.pyplot 3.3.2
json 2.0.9
requests 2.25.1
sodapy 2.0.0<jupyter_text>Getting Started: Accessing the Building Footprints Dataset  1. Search NYC Open Data in Google ![building_footprints](images/1.nyc-open-data-google.png)  2. Search "Building Footprints" in NYC Open Data search bar ![building_footprints](images/2.building-footprints-opendata-search.png)  3. Select "Building Footprints" Dataset ![building_footprints](images/3.building-footprints-dataset-link.png)  4. The Building Footprints Dataset Page ![building_footprints](images/4.data-homepage.png) Dataset Link: https://data.cityofnewyork.us/Housing-Development/Building-Footprints/nqwf-w8ehDocumentation/Metadata: https://github.com/CityOfNewYork/nyc-geo-metadata/blob/master/Metadata/Metadata_BuildingFootprints.md **Building Footprints Dataset Identification**> **Here are a few things to note about the data:**>> - **Purpose:** This feature class is used by the NYC DOITT GIS group to maintain and distribute an accurate 'basemap' for NYC. The basemap provides the foundation upon virtually all other geospatial data with New York.> - **Description:** Building footprints represent the full perimeter outline of each building as viewed from directly above. Additional attribute information maintained for each feature includes: Building Identification Number (BIN); Borough, Block, and Lot information(BBL); ground elevation at building base; roof height above ground elevation; construction year, and feature type.> - **Source(s):** Annually captured aerial imagery, NYC Research of DOB records, or other image resources.> - **Publication Dates:** **Data**: 05/03/16> - **Last Update:** Weekly> - **Metadata:** 12/22/2016> - **Update Frequency:** Features are updated daily by DoITT staff and a public release is available weekly on NYC Open Data. Every four years a citywide review is made of the building footprints and features are updated photogrammetrically.> - **Available Formats:** File Geodatabase Feature Class as part of the Planimetrics geodatabase and individual shapefile on the [NYC Open Data Portal](https://data.cityofnewyork.us/Housing-Development/Building-Footprints/nqwf-w8eh)> - **Use Limitations:** Open Data policies and restrictions apply. See [Terms of Use](http://www.nyc.gov/html/data/terms.html)> - **Access Rights:** Public> - **Links:** https://data.cityofnewyork.us/Housing-Development/Building-Footprints/nqwf-w8eh> - **Tags:** Buildings, Building footprint, BIN, Structure**Source:** https://github.com/CityOfNewYork/nyc-geo-metadata/blob/master/Metadata/Metadata_BuildingFootprints.md  1. Reading In Data  1.1 Reading in data as csv in static form ![building_footprints_csv](images/building-footprints-csv.png)  NOTE: The buildings footprints `dataset identifier` changes weekly, and so does the data api path. Click on the API Docs page and verify the correct dataset identifier. If you're not working with the correct id, you will receive a `HTTP Error`. Screenshots below: **Click on API Docs**![building_footprints_csv](images/api_docs.png)**Grab the updated dataset identifier**![building_footprints_csv](images/api_docs_dataset_id.png) The `dataset identifier` is inserted into the api path below:  url = https://data.cityofnewyork.us/api/views/DATASET_IDENTIFIER/rows.csv?accessType=DOWNLOAD<jupyter_code># reading in data as a url from NYC Open Data
url = 'https://data.cityofnewyork.us/api/views/qb5r-6dgf/rows.csv?accessType=DOWNLOAD'

# saving data as a pandas dataframe named 'building_footprints_csv'
building_footprints_csv = pd.read_csv(url)
# previewing the first five rows 
building_footprints_csv.head()
# printing the dimentions (i.e. rows, columns) of the data
building_footprints_csv.shape
# more readable

rows, columns = building_footprints_csv.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))<jupyter_output>This dataset has 1,084,416 rows and 15 columns.<jupyter_text>**Sanity check**We use pandas `.head()` method to preview the first five rows of the dataframe.We use pandas `.shape` method to print the dimensions of the dataframe (i.e. number of rows, number of columns).We will use these two methods throughout the examples.  1.2 Reading in data as json in static form ![building_footprints_csv](images/building-footprints-json.png)<jupyter_code>url = 'https://data.cityofnewyork.us/api/views/qb5r-6dgf/rows.json?accessType=DOWNLOAD'

# loads a json object as a python object
with urllib.request.urlopen(url) as url:
    data = json.loads(url.read().decode())
# identifying type of python object
type(data)
data.keys()
type(data['data'])
len(data['data'])
# previewing the first row in our data section of our python object
data['data'][0]
data['meta'].keys()
keys = data['meta']['view'].keys()

for key in keys:
    print(key)
# locating our columns (i.e. field names) and saving as a new variable called 'cols'
cols = data['meta']['view']['columns']

# previewing first five
cols[:5]
for item in cols:
    print(item['fieldName'])
# saving our field names as a variable
fieldName = {x['fieldName']: x for x in cols}

# printing the field names in our data
for key in fieldName.keys():
    print(key)
# saving our field names in a list
columns = [*fieldName]
building_footprints_json = pd.DataFrame(data['data'], columns=columns)

# identifying columns not required for analysis
drop_columns = [':sid', 
                ':id', 
                ':position', 
                ':created_at', 
                ':created_meta', 
                ':updated_at', 
                ':updated_meta', 
                ':meta']

# dropping columns not required for analysis
building_footprints_json = building_footprints_json.drop(drop_columns, axis=1)
building_footprints_json.head()
# printing dimensions of data

rows, columns = building_footprints_json.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))<jupyter_output>This dataset has 1,084,416 rows and 15 columns.<jupyter_text>1.3 Reading in shapefile data ![building_footprints_csv](images/building-footprints-shp.png)<jupyter_code>url = 'https://data.cityofnewyork.us/api/geospatial/nqwf-w8eh?method=export&format=Shapefile'

# reading in data as a geodataframe
building_footprints_shp = gpd.read_file(url)

# printing the firt five rows
building_footprints_shp.head()
rows, columns = building_footprints_shp.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))
# plotting one building footprint
building_footprints_shp.head(1).plot()<jupyter_output><empty_output><jupyter_text>Another popular dataset is NYC's PLUTO dataset. We will use this one because it comes in a zip file.- Description: Extensive land use and geographic data at the tax lot level in comma–separated values (CSV) file format. The PLUTO files contain more than seventy fields derived from data maintained by city agencies.- Dataset Link: https://www1.nyc.gov/site/planning/data-maps/open-data/dwn-pluto-mappluto.page- Data Dictionary: https://www1.nyc.gov/assets/planning/download/pdf/data-maps/open-data/pluto_datadictionary.pdf?v=20v1 ![building_footprints_csv](images/pluto-csv.png)  1.4 Unzipping and reading in data as a csv in memory<jupyter_code>url = 'https://www1.nyc.gov/assets/planning/download/zip/data-maps/open-data/nyc_pluto_20v1_csv.zip'

# reading in our zipfile data in-memory
content = requests.get(url)
zf = ZipFile(BytesIO(content.content))

# printing files in our zipfile
for item in zf.namelist():
    print("File in zip: "+ item)
# read our csv data into a dataframe from our zipfile
pluto_data = pd.read_csv(zf.open('pluto_20v1.csv'), low_memory=False)

# previewing the first five rows of data
pluto_data.head()
rows, columns = pluto_data.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))<jupyter_output>This dataset has 859,172 rows and 99 columns.<jupyter_text>1.5 Unzipping and reading in data as csv to local folderWe will retrieve, unzip and read in data in our downloads folder.<jupyter_code>url = 'https://www1.nyc.gov/assets/planning/download/zip/data-maps/open-data/nyc_pluto_20v1_csv.zip'

# a path to our downloads folder 
downloads_path = '../../Downloads/'

# a path to our file from our downloads path
fullfilename = os.path.join(downloads_path, 'PLUTO.gz')

# retrieving data 
urllib.request.urlretrieve(url, fullfilename)
# a path to our file from our downloads folder
file_path = '../../Downloads/PLUTO.gz'

# open zipfile and saving items in our zipfolder
items = zipfile.ZipFile(file_path)

# available files in the container
print(items.namelist())
# opening zipfile using 'with' keyword in read mode
with zipfile.ZipFile(file_path, 'r') as file:
    file.extractall(downloads_path)
# read our data into a dataframe from our downloads path
pluto_data = pd.read_csv(downloads_path + 'pluto_20v1.csv', low_memory=False)

pluto_data.head()
rows, columns = pluto_data.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))<jupyter_output>This dataset has 859,172 rows and 99 columns.<jupyter_text>1.6 Unzipping and reading in data as csv from local folder **Manually zipping a csv file of the first 20 rows of the data in a zipped file.**<jupyter_code># saving first twenty rows of our data as a new csv
building_footprints_csv.head(20).to_csv('data/sample_buildings.csv', index=False)
file_path = 'data/sample-buidlings.zip'

# create a zipfile
with zipfile.ZipFile(file_path, 'w') as file:
        # write mode overrides all the existing files in the 'Zip.'
        # you have to create the file which you have to write to the 'Zip.'
        file.write('data/sample_buildings.csv', 
                   basename('data/sample_buildings.csv'))
# seeing if a file is a zipfile
print(zipfile.is_zipfile(file_path))
# list items in this file path
%ls data/
# save items in our zipfile
items = zipfile.ZipFile(file_path)

# available files in the container
print(items.namelist())<jupyter_output>['sample_buildings.csv']<jupyter_text>**Extracting the csv file of the data from the zipped file.**<jupyter_code>file_name = 'data/sample-buidlings.zip'

# opening zip using 'with' keyword in read mode
with zipfile.ZipFile(file_name, 'r') as file:
    # extracing all items in our zipfile
    file.extractall('data/unzipped-data')
# list files in this file path
%ls data/unzipped-data/
# read data as a dataframe
sample_buidlings = pd.read_csv('data/unzipped-data/sample_buildings.csv')

# previewing first five rows of data
sample_buidlings.head()
rows, columns = sample_buidlings.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))<jupyter_output>This dataset has 20 rows and 15 columns.<jupyter_text>1.7 Reading in data from Socrata Open Data API (SODA) **Note: If you haven't signed up for an app token, there is a 1,000 rows limit.** ![building_footprints_csv](images/building-footprints-soda-api.png)<jupyter_code># Enter the information from those sections here
socrata_domain = 'data.cityofnewyork.us' # nyc open data domain
socrata_dataset_identifier = 'qb5r-6dgf' # building footprints dataset identifier<jupyter_output><empty_output><jupyter_text>Source: https://github.com/xmunoz/sodapy/blob/master/examples/basic_queries.ipynb<jupyter_code># The main class that interacts with the SODA API. 
# Sample usage:
    # from sodapy import Socrata
    # client = Socrata("opendata.socrata.com", None)
    
client = Socrata(socrata_domain, None)

print("Domain: {domain:}\nSession: {session:}\nURI Prefix: {uri_prefix:}".format(**client.__dict__))<jupyter_output>WARNING:root:Requests made without an app_token will be subject to strict throttling limits.<jupyter_text>We are setting the **limit** at **1,000 rows** (i.e. the full data set).<jupyter_code># retrieving data as a dictionary 
results = client.get(socrata_dataset_identifier, limit=1000)

# creating a dataframe from our dictionary
building_footprints_soda_api = pd.DataFrame.from_dict(results)

# printing first five rows of data
building_footprints_soda_api.head()
rows, columns = building_footprints_soda_api.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))
# ending our API request
client.close()<jupyter_output><empty_output><jupyter_text>**Useful resources:**    - API Docs: https://dev.socrata.com/foundry/data.cityofnewyork.us/i62d-kjv8- Sign up for app token: https://data.cityofnewyork.us/profile/edit/developer_settings- Python client for the Socrata Open Data API: https://github.com/xmunoz/sodapy- Examples: https://github.com/xmunoz/sodapy/tree/master/examples  2. Writing Out Data  For simplicity, we're only exporting buildings built from 2010 to 2020<jupyter_code># saving only buildings built between 2010 and 2020 as a new dataframe
building_footprints_after_2010 = building_footprints_csv[building_footprints_csv['CNSTRCT_YR'].between(2010, 2020)]

# reset our index
building_footprints_after_2010 = building_footprints_after_2010.reset_index(drop=True)

# previewing first five rows of data
building_footprints_after_2010.head()
rows, columns = building_footprints_after_2010.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))
# sorting our construction year values and printing the unique values
building_footprints_after_2010.sort_values(by='CNSTRCT_YR').CNSTRCT_YR.unique()
# list items in data folder
%ls data/<jupyter_output>README.md             sample_buildings.csv
sample-buidlings.zip  [34munzipped-data[m[m/<jupyter_text>2.1 Writing to a CSV file<jupyter_code># writing files as a csv
building_footprints_after_2010.to_csv('data/building_after_2010.csv', index=False)

# listing items in data folder
%ls data/<jupyter_output>README.md                sample-buidlings.zip     [34munzipped-data[m[m/
building_after_2010.csv  sample_buildings.csv<jupyter_text>2.2 Writing to an Excel (xlsx) file<jupyter_code># writing files as an excel file
building_footprints_after_2010.to_excel('data/building_after_2010.xlsx', index=False)

# listing items in data folder
%ls data/<jupyter_output>README.md                 building_after_2010.xlsx  sample_buildings.csv
building_after_2010.csv   sample-buidlings.zip      [34munzipped-data[m[m/<jupyter_text>2.3 Writing to a JSON file<jupyter_code># writing files as json
building_footprints_after_2010.to_json('data/building_after_2010.json')

# listing items in data folder
%ls data/<jupyter_output>README.md                 building_after_2010.xlsx  [34munzipped-data[m[m/
building_after_2010.csv   sample-buidlings.zip
building_after_2010.json  sample_buildings.csv<jupyter_text>3. Reading In Data from Local Folder<jupyter_code># listing items in data folder
%ls data/<jupyter_output>README.md                 building_after_2010.xlsx  [34munzipped-data[m[m/
building_after_2010.csv   sample-buidlings.zip
building_after_2010.json  sample_buildings.csv<jupyter_text>3.1 Reading in a CSV file<jupyter_code># read data as a dataframe
building_footprints_after_2010 = pd.read_csv('data/building_after_2010.csv')

# previewing first five rows in data
building_footprints_after_2010.head()
rows, columns = building_footprints_after_2010.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))<jupyter_output>This dataset has 15,182 rows and 15 columns.<jupyter_text>3.2 Reading in an Excel file<jupyter_code># read data as a dataframe
building_footprints_after_2010 = pd.read_excel('data/building_after_2010.xlsx')

# previewing first five rows in data
building_footprints_after_2010.head()
rows, columns = building_footprints_after_2010.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))<jupyter_output>This dataset has 15,182 rows and 15 columns.<jupyter_text>3.3 Reading in a JSON file<jupyter_code># read data as a dataframe
building_footprints_after_2010 = pd.read_json('data/building_after_2010.json')

# previewing first five rows in data
building_footprints_after_2010.head()
rows, columns = building_footprints_after_2010.shape
print('This dataset has {:,} rows and {:,} columns.'.format(rows, columns))<jupyter_output>This dataset has 15,182 rows and 15 columns.
{'fraction_non_alphanumeric': 0.10911985211050632, 'fraction_numerical': 0.022337475608503646, 'mean_word_length': 4.9411226357535085, 'pattern_counts': {'":': 0, '<': 66, '<?xml version=': 0, '>': 80, 'https://': 20, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 17, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '295858', 'license': 'MIT', 'n_tokens_mistral': 6585, 'n_tokens_neox': 6110, 'n_words': 1799, 'path': '1-reading-writing-files/1_reading_writing_files.ipynb', 'repo_name': 'mebauer/data-analysis-using-python', 'size': '19478'}