<jupyter_start><jupyter_text>ETL_EDA    This file records the process of acquiring raw data, traforming them, and loading them into a MongoDB. The data are store (almost) in their raw form.  1. Raw Data from LAPD     The [dataset](https://data.lacity.org/A-Safe-City/Arrest-Data-from-2010-to-Present/yru6-6re4) is a continuously updated file in csv format. It contains City of Los Angeles arrest data from 2010 to present. It can be retrieved simply by making requests without parameters. For more complicated API call, you may need to add query parameters.<jupyter_code>import logging
import pandas as pd
from sodapy import Socrata
import utils

# download data
CRIME_SOURCE = "data.lacity.org"
client0 = Socrata(CRIME_SOURCE, None)
results = client0.get("yru6-6re4",where="arst_date > '2019-12-10T00:00:00.000'",limit = 1000000) # wait to be confirmed
print(results[:2])
print('...')
print(results[-2:])
!pip install sodapy<jupyter_output>Collecting sodapy
  Downloading sodapy-2.1.0-py2.py3-none-any.whl (14 kB)
Requirement already satisfied: requests>=2.20.0 in /anaconda3/lib/python3.7/site-packages (from sodapy) (2.22.0)
Requirement already satisfied: idna<2.9,>=2.5 in /anaconda3/lib/python3.7/site-packages (from requests>=2.20.0->sodapy) (2.8)
Requirement already satisfied: certifi>=2017.4.17 in /anaconda3/lib/python3.7/site-packages (from requests>=2.20.0->sodapy) (2020.6.20)
Requirement already satisfied: chardet<3.1.0,>=3.0.2 in /anaconda3/lib/python3.7/site-packages (from requests>=2.20.0->sodapy) (3.0.4)
Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /anaconda3/lib/python3.7/site-packages (from requests>=2.20.0->sodapy) (1.25.8)
Installing collected packages: sodapy
Successfully installed sodapy-2.1.0<jupyter_text>2. Raw Data to Documents/Dicts      Datetime conversion is made and age column is changed to numeric data type. Now the data can be easily converted to a list of dicts which is what we want for the MongoDB.<jupyter_code>import logging
import pandas as pd
from sodapy import Socrata
import utils
CRIME_SOURCE = "data.lacity.org"
DOWNLOAD_PERIOD = 15         # second
logger = logging.Logger(__name__)
utils.setup_logger(logger, 'data.log')


def download_crime(url=CRIME_SOURCE, start_date = '2019-12-08T00:00:00.000'):
    """Returns records from `CRIME_SOURCE` that includes crime and arrestee information.
    """
    client = Socrata(url, None)
    results = client.get("yru6-6re4",where=f"arst_date >= '{start_date}'", limit = 10000) # wait to be confirmed
    return results
from datetime import datetime

df = pd.DataFrame.from_records(results)
df['arst_date'] = pd.to_datetime(df['arst_date'])
# df['hour'] = df['time'].apply(lambda x: datetime.strptime(x, '%H%M').time().hour)
#df['month'] = df['arst_date'].apply(lambda x: datetime.strptime(x, '%Y%m').time())
df['age'] = pd.to_numeric(df['age'])
df.head()
df['location_1']<jupyter_output><empty_output><jupyter_text>3. Upsert MongoDB         To avoid duplicate data entries between each run, the de-duplication mechanism makes sure that only a new record with a different report_id (rpt_id) will be inserted to the database.<jupyter_code>import pymongo
client = pymongo.MongoClient()
#import time
db = client.get_database("crime")
collection = db.get_collection("crime")
update_count = 0
count = 0
for record in df.to_dict('records'):
    #start = time.time()
    result = collection.replace_one(
        filter = {'rpt_id': record['rpt_id']},    # locate the document if exists
        replacement = record,                         # latest document
        upsert=True)
    if result.matched_count > 0:
        update_count += 1
print(f"rows={df.shape[0]}, update={update_count}, "
      f"insert={df.shape[0]-update_count}")<jupyter_output>rows=495, update=495, insert=0
{'fraction_non_alphanumeric': 0.12924453803632535, 'fraction_numerical': 0.048433798367991575, 'mean_word_length': 4.680119581464873, 'pattern_counts': {'":': 0, '<': 14, '<?xml version=': 0, '>': 27, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '626316', 'license': 'MIT', 'n_tokens_mistral': 1365, 'n_tokens_neox': 1294, 'n_words': 347, 'path': 'ETL_EDA.ipynb', 'repo_name': 'LACrimeMap/LACrimeMap', 'size': '3799'}