# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# %config InlineBackend.figure_format = 'retina'
# %reload_ext autoreload
# %autoreload 2
# %matplotlib inline

import pandas as pd
import bq_helper
from bq_helper import BigQueryHelper

# # 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

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')

# ## Exploratory Queries for Photos

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')

# ## Photographs Query
# Extract 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

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)

# Grab the `object_id` and `gcs_url` columns to be used by the bash download script

gcs_url_response.loc[:, ['object_id','gcs_url']].to_csv(r'/data/kaggle/met/images.csv', header=None, index=None, sep=',', mode='a')

# The bash download script handles the fact that trying to pipe a list of URLs to download into `gsutil cp` ignores the directory structure when downloading to the destination and places everything in the same folder (i.e., if any of the files have the same filename, they will overwrite each other).
#
# ## `gsutil` Download Script
#
# ```
# # #!/bin/bash
# while IFS=, read -r col1 col2
# do
#     gsutil -m cp -r $col2 /data/kaggle/met/images/$col1
# done < /data/kaggle/met/images.csv
# ```



StarcoderdataJupyterScriptsDedupFiltered