<jupyter_start><jupyter_text>Importing Data from Big Query and Pivoting Raison d'etre The objective of this notebook is to  pivot  the NOAA dataset's temperature and precipitation elements for ALL weather recordings of it's weather stations from the year 1900 to the present. Each of the steps required to do this will be extensively documented for didactic reasons. Installation pain To get started on this notebook you need to be able to authenticate your Big Query account with google.  For information on how to do this visit : https://cloud.google.com/iam/docs/quickstart-client-libraries  A quick summary of what you will find in the above hyperlink is as follow      A valid gmail account      In https://console.cloud.google.com/ create a new project      Using your project name         create a service account key. The end result of this process is the creation of a JSON file.     Create an enviromental variable called  GOOGLE_APPLICATION_CREDENTIALS  and link that variable to the path of your JSON file      Please visit the link above for the detailed information        A Big Query of NOAA's data To be able to pivot the relevant data we first need to fetch it. In order to fetch it we need to know where it is located it and how it is structured. Let us inspect the project name of  bigquery-public-data  specifically the schema  ghcn_d . The below script queries the NOAA schema and unveils all the tables corresponding to that schema.<jupyter_code>from google.cloud import bigquery
from google.cloud.bigquery.job import (
    LoadJobConfig, 
    WriteDisposition, 
    CreateDisposition
)

PROJECT='fjvr-testing'  # CHANGE THIS TO YOUR PROJECT NAME

bq = bigquery.Client(project=PROJECT)

# list tables in dataset
tables = bq.list_tables("bigquery-public-data.ghcn_d")

# Iterate over all tables in the schema and returns only those that have a year as the last four characters
for table in tables:
    try:
        if int(table.table_id[-4:]) >= 1900:
            print(table.table_id)
    except:
        pass<jupyter_output>ghcnd_1900
ghcnd_1901
ghcnd_1902
ghcnd_1903
ghcnd_1904
ghcnd_1905
ghcnd_1906
ghcnd_1907
ghcnd_1908
ghcnd_1909
ghcnd_1910
ghcnd_1911
ghcnd_1912
ghcnd_1913
ghcnd_1914
ghcnd_1915
ghcnd_1916
ghcnd_1917
ghcnd_1918
ghcnd_1919
ghcnd_1920
ghcnd_1921
ghcnd_1922
ghcnd_1923
ghcnd_1924
ghcnd_1925
ghcnd_1926
ghcnd_1927
ghcnd_1928
ghcnd_1929
ghcnd_1930
ghcnd_1931
ghcnd_1932
ghcnd_1933
ghcnd_1934
ghcnd_1935
ghcnd_1936
ghcnd_1937
ghcnd_1938
ghcnd_1939
ghcnd_1940
ghcnd_1941
ghcnd_1942
ghcnd_1943
ghcnd_1944
ghcnd_1945
ghcnd_1946
ghcnd_1947
ghcnd_1948
ghcnd_1949
ghcnd_1950
ghcnd_1951
ghcnd_1952
ghcnd_1953
ghcnd_1954
ghcnd_1955
ghcnd_1956
ghcnd_1957
ghcnd_1958
ghcnd_1959
ghcnd_1960
ghcnd_1961
ghcnd_1962
ghcnd_1963
ghcnd_1964
ghcnd_1965
ghcnd_1966
ghcnd_1967
ghcnd_1968
ghcnd_1969
ghcnd_1970
ghcnd_1971
ghcnd_1972
ghcnd_1973
ghcnd_1974
ghcnd_1975
ghcnd_1976
ghcnd_1977
ghcnd_1978
ghcnd_1979
ghcnd_1980
ghcnd_1981
ghcnd_1982
ghcnd_1983
ghcnd_1984
ghcnd_1985
ghcnd_1986
ghcnd_1987
ghcnd_1988
ghcnd_1989
ghcnd_1990[...]<jupyter_text>Pivot for 2019 and the DCA Airport weather station In the below query we retrieve the weather observations (for the weather elements of minimum temperature, maximum temperature and precipitation.) occuring in 2019 of the DCA Ronald Reagan weather station. To do the pivot a SELF-JOIN with the table containing the data is done. Once the query has been executed it is stored in a dataframe<jupyter_code>import pandas as pd

# Create a new dataframe with the required c
df = pd.DataFrame(columns=['date','id','name','precipitation_value','state','temp_max_value','temp_min_value'])

# Example query using only one year (2019) for one station (USW00013743)
query = """
SELECT DISTINCT
  base.id, 
  base.date,
  stations.name,
  stations.state,
  temp_min.value as temp_min_value,
  temp_max.value as temp_max_value,
  precipitation.value as precipitation_value

FROM {} base
INNER JOIN {} temp_min ON base.id = temp_min.id AND base.date = temp_min.date
INNER JOIN {} temp_max ON base.id = temp_max.id AND base.date = temp_max.date
INNER JOIN {} precipitation ON base.id = precipitation.id AND base.date = precipitation.date
INNER JOIN `bigquery-public-data`.ghcn_d.ghcnd_stations stations ON base.id = stations.id

WHERE temp_min.element = 'TMIN' AND temp_max.element = 'TMAX' AND precipitation.element = 'PRCP' AND stations.id = 'USW00013743'

ORDER BY base.id, base.date
"""

parameter = "`bigquery-public-data`.ghcn_d.ghcnd_2019"

query = query.format(parameter,parameter,parameter,parameter)

df_temp = bq.query(query).to_dataframe()
df.append(df_temp)<jupyter_output><empty_output><jupyter_text>Inspecting the circles and the proximity to a weather stationThe  cbc_cleaned_usa_statid.csv  contains a list of circle names (locations where an audobon bird count has occured) and the corresponding NOAA station that is closest to it. In the below script we find the unique stations AND unique dates that are of interest in our data extraction. To understand why the sets below will be produce look into  IN SQL operator .<jupyter_code>df_shell = pd.read_csv('cbc_cleaned_usa_statid.csv')

seperator = '\',\''

station_list = df_shell.loc[:, 'closest_station_id'].unique()

string_parameter_stations = "(\'" + seperator.join(station_list) + "\')"

string_parameter_stations

date_list = df_shell.loc[:,'count_date'].unique()

string_parameter_dates = "(\'" + seperator.join(date_list) + "\')"

string_parameter_dates<jupyter_output><empty_output><jupyter_text>Importing the data for ALL stations and between two yearsThe below loops between the initial year and the final year as per the variables. In each iteration of the loop the database is hit. The result of this query is stored in a dataframe which is later saved as a CSV with a default compression of GZIP<jupyter_code># Create the date frame that will be used to accumulate the results from each year
df = pd.DataFrame(columns=['date','id','name','precipitation_value','state','temp_max_value','temp_min_value'])

# The initial date and final date that the query will use to retrieve data
initial_year = 2018
final_year = 2019

# list tables in the big query GHCN dataset
tables = bq.list_tables("bigquery-public-data.ghcn_d")

# Iterates over all tables in the bigquery-public-data.ghcn_d datasets and only takes the once that have a year as the last 4 digits.
for table in tables:
    try:
        if int(table.table_id[-4:]) >= initial_year and int(table.table_id[-4:]) <= final_year:
            # Constructs a query to flatten data. Here we are doing a SELF-JOIN (see examples online if neccesary) to extract the attributes of interest
            query = """
            SELECT DISTINCT
              base.id, 
              base.date,
              stations.name,
              stations.state,
              temp_min.value as temp_min_value,
              temp_max.value as temp_max_value,
              precipitation.value as precipitation_value

            FROM {} base
            INNER JOIN {} temp_min ON base.id = temp_min.id AND base.date = temp_min.date
            INNER JOIN {} temp_max ON base.id = temp_max.id AND base.date = temp_max.date
            INNER JOIN {} precipitation ON base.id = precipitation.id AND base.date = precipitation.date
            INNER JOIN `bigquery-public-data`.ghcn_d.ghcnd_stations stations ON base.id = stations.id

            WHERE temp_min.element = 'TMIN' AND temp_max.element = 'TMAX' AND precipitation.element = 'PRCP' AND stations.id IN {} AND base.date IN {}

            ORDER BY base.id, base.date
            """
            # This parameter is determined baased on the current year we are iterating over
            parameter = "`bigquery-public-data`.ghcn_d." + table.table_id
            
            # The format function replaces instances of {} with the corresponding sequential input
            query = query.format(parameter,parameter,parameter,parameter,string_parameter_stations, string_parameter_dates)
            
            # Queries BigQuery public data set and creates a new dataframe object
            df_temp = bq.query(query).to_dataframe()
            
            # Appends the dataframe object to accumulated data frame
            df = df.append(df_temp)
    except:
        pass

# Saves csv to the root of where this notebook. 
# THIS FILE HAS BEEN GZIP!
# WHEN YOU WANT TO OPEN IT YOU NEED TO EXTRACT IT
df.to_csv(r'flatten_data_from_' + str(initial_year) + '_to_' + str(final_year) + ".csv", compression = "gzip")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0636607872912043, 'fraction_numerical': 0.04859245415255227, 'mean_word_length': 3.9663573085846866, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 18, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '443982', 'license': 'MIT', 'n_tokens_mistral': 3026, 'n_tokens_neox': 2677, 'n_words': 929, 'path': 'attic/flatten-data-ghcn_d.ipynb', 'repo_name': 'jellena/audubon-cbc', 'size': '8561'}