<jupyter_start><jupyter_text>When That Photo?Goal of this project is to train a ML network to provide an estimate of when a photo was taken, where the estimate is a range of years (e.g., 1890-1900)  Setup Access Via Big Query<jupyter_code>met = bq_helper.BigQueryHelper(active_project="bigquery-public-data", dataset_name="the_met")
bq_assistant = BigQueryHelper("bigquery-public-data", "the_met")
bq_assistant.list_tables()
pd.set_option('display.max_rows', 150)
bq_assistant.table_schema('objects')<jupyter_output><empty_output><jupyter_text>Exploratory Queries for Photos<jupyter_code>images_url_query = """SELECT 
a.object_id,
a.object_number,
a.title,
a.artist_display_name,
a.object_name,
a.object_date,
a.object_begin_date,
a.object_end_date,
a.medium,
a.dimensions,
a.classification,
b.cropImportanceFraction,
b.cropConfidence,
b.cropBB,
b.cropHintsAnnotation
FROM `bigquery-public-data.the_met.objects` a
JOIN (
  SELECT object_id, cropHintsAnnotation,
  cropHints.importanceFraction as cropImportanceFraction,
  cropHints.confidence as cropConfidence,
  cropHints.boundingPoly.vertices as cropBB
  FROM `bigquery-public-data.the_met.vision_api_data`, 
  UNNEST(cropHintsAnnotation.cropHints) cropHints
) b
ON a.object_id = b.object_id
WHERE a.department = "Photographs" AND
a.classification IN ("Transparencies", "Photographs", "Negatives") OR
a.classification LIKE "Photographs%"
        """
images_url_response = met.query_to_pandas_safe(images_url_query, max_gb_scanned=20)
images_url_response.head(100)
images_url_response.to_csv(r'/data/kaggle/met/photographs-02-08-19.csv', index=None, sep=',', mode='a')<jupyter_output><empty_output><jupyter_text>Photographs QueryExtract some basic info about the object from the `objects` table, and `JOIN` with the `images` table to get the relevant GCloud Storage pointer. Export all a subset of this data to a CSV to be used in a bash script for executing `gsutil cp` to download each image<jupyter_code>gcs_url_query = """
SELECT a.object_id, a.object_name, a.title, a.object_date, b.gcs_url
FROM `bigquery-public-data.the_met.objects` a
JOIN (
  SELECT object_id, gcs_url
  FROM `bigquery-public-data.the_met.images`
) b
ON a.object_id = b.object_id
WHERE (a.department = "Photographs" AND
a.classification IN ("Transparencies", "Photographs", "Negatives") OR
a.classification LIKE "Photographs%")
AND ends_with(lower(b.gcs_url), '/0.jpg')
    """
gcs_url_response = met.query_to_pandas_safe(gcs_url_query, max_gb_scanned=50)
gcs_url_response.head(10)
len(gcs_url_response)<jupyter_output><empty_output><jupyter_text>Grab the `object_id` and `gcs_url` columns to be used by the bash download script<jupyter_code>gcs_url_response.loc[:, ['object_id','gcs_url']].to_csv(r'/data/kaggle/met/images.csv', header=None, index=None, sep=',', mode='a')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1137809187279152, 'fraction_numerical': 0.009540636042402828, 'mean_word_length': 7.042613636363637, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '631396', 'license': 'MIT', 'n_tokens_mistral': 1079, 'n_tokens_neox': 1022, 'n_words': 217, 'path': 'notebooks/Met Photos Load v1.ipynb', 'repo_name': 'edave/when-that-photo', 'size': '2830'}