<gh_stars>1-10
import pandas as pd
import os
import urllib.request
import sys
utils_path = os.path.join(os.path.abspath(os.getenv('PROCESSING_DIR')),'utils')
if utils_path not in sys.path:
    sys.path.append(utils_path)
import util_files
import util_cloud
import util_carto
from zipfile import ZipFile
import logging
import datetime


# Set up logging
# Get the top-level logger object
logger = logging.getLogger()
for handler in logger.handlers: logger.removeHandler(handler)
logger.setLevel(logging.INFO)
# make it print to the console.
console = logging.StreamHandler()
logger.addHandler(console)
logging.basicConfig(format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

# name of table on Carto where you want to upload data
# this should be a table name that is not currently in use
dataset_name = 'com_028_rw1_effect_of_ag_prices_on_commodity_prices'

logger.info('Executing script for dataset: ' + dataset_name)
# create a new sub-directory within your specified dir called 'data'
# within this directory, create files to store raw and processed data
data_dir = util_files.prep_dirs(dataset_name)

'''
Download data and save to your data directory
'''
logger.info('Downloading raw data')
# insert the url used to download the data from the source website
url = 'http://www.ag-incentives.org/sites/default/files/data/AgIncentivesNRP.zip'

# download the data from the source
raw_data_file = os.path.join(data_dir, 'AgIncentivesNRP.zip')
urllib.request.urlretrieve(url, raw_data_file)

# unzip source data
raw_data_file_unzipped = raw_data_file.split('.')[0]
zip_ref = ZipFile(raw_data_file, 'r')
zip_ref.extractall(raw_data_file_unzipped)
zip_ref.close()

'''
Process data
'''
# read the data into a pandas dataframe
df = pd.read_csv(os.path.join(raw_data_file_unzipped, 'AgIncentivesNRP.csv'))

# convert the column names to lowercase to match the column name requirements of Carto 
df.columns = [x.lower() for x in df.columns]

# convert the years in the 'year' column to datetime objects and store them in a new column 'datetime'
df['datetime'] = [datetime.datetime(x, 1, 1) for x in df.year]

# extract subset to only retain 'COUNTRY_TOTAL' (aggregate of all products at country level) in the 'category' column
df = df.loc[df['category'] == 'COUNTRY_TOTAL']

# remove column 'notes' since it only contains indexes instead of actual data
# remove column 'productcode' since it contains the same information as the column 'productname'
# remove column 'source' since it contains the same information as the column 'sourceversion'
df = df.drop(columns = ['notes','productcode','source'])

# save dataset to csv
processed_data_file = os.path.join(data_dir, dataset_name+'_edit.csv')
df.to_csv(processed_data_file, index=False)

'''
Upload processed data to Carto
'''
logger.info('Uploading processed data to Carto.')
util_carto.upload_to_carto(processed_data_file, 'LINK')

'''
Upload original data and processed data to Amazon S3 storage
'''
# initialize AWS variables
aws_bucket = 'wri-public-data'
s3_prefix = 'resourcewatch/'

logger.info('Uploading original data to S3.')
# Upload raw data file to S3

# Copy the raw data into a zipped file to upload to S3
raw_data_dir = os.path.join(data_dir, dataset_name+'.zip')
with ZipFile(raw_data_dir,'w') as zip:
    zip.write(raw_data_file, os.path.basename(raw_data_file))
# Upload raw data file to S3
uploaded = util_cloud.aws_upload(raw_data_dir, aws_bucket, s3_prefix+os.path.basename(raw_data_dir))

logger.info('Uploading processed data to S3.')
# Copy the processed data into a zipped file to upload to S3
processed_data_dir = os.path.join(data_dir, dataset_name+'_edit.zip')
with ZipFile(processed_data_dir,'w') as zip:
    zip.write(processed_data_file, os.path.basename(processed_data_file))
# Upload processed data file to S3
uploaded = util_cloud.aws_upload(processed_data_dir, aws_bucket, s3_prefix+os.path.basename(processed_data_dir))

import pandas as pd
import os
import urllib.request
import sys
utils_path = os.path.join(os.path.abspath(os.getenv('PROCESSING_DIR')),'utils')
if utils_path not in sys.path:
    sys.path.append(utils_path)
import util_files
import util_cloud
import util_carto
from zipfile import ZipFile
import logging
import datetime


# Set up logging
# Get the top-level logger object
logger = logging.getLogger()
for handler in logger.handlers: logger.removeHandler(handler)
logger.setLevel(logging.INFO)
# make it print to the console.
console = logging.StreamHandler()
logger.addHandler(console)
logging.basicConfig(format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

# name of table on Carto where you want to upload data
# this should be a table name that is not currently in use
dataset_name = 'com_028_rw1_effect_of_ag_prices_on_commodity_prices'

logger.info('Executing script for dataset: ' + dataset_name)
# create a new sub-directory within your specified dir called 'data'
# within this directory, create files to store raw and processed data
data_dir = util_files.prep_dirs(dataset_name)

'''
Download data and save to your data directory
'''
logger.info('Downloading raw data')
# insert the url used to download the data from the source website
url = 'http://www.ag-incentives.org/sites/default/files/data/AgIncentivesNRP.zip'

# download the data from the source
raw_data_file = os.path.join(data_dir, 'AgIncentivesNRP.zip')
urllib.request.urlretrieve(url, raw_data_file)

# unzip source data
raw_data_file_unzipped = raw_data_file.split('.')[0]
zip_ref = ZipFile(raw_data_file, 'r')
zip_ref.extractall(raw_data_file_unzipped)
zip_ref.close()

'''
Process data
'''
# read the data into a pandas dataframe
df = pd.read_csv(os.path.join(raw_data_file_unzipped, 'AgIncentivesNRP.csv'))

# convert the column names to lowercase to match the column name requirements of Carto 
df.columns = [x.lower() for x in df.columns]

# convert the years in the 'year' column to datetime objects and store them in a new column 'datetime'
df['datetime'] = [datetime.datetime(x, 1, 1) for x in df.year]

# extract subset to only retain 'COUNTRY_TOTAL' (aggregate of all products at country level) in the 'category' column
df = df.loc[df['category'] == 'COUNTRY_TOTAL']

# remove column 'notes' since it only contains indexes instead of actual data
# remove column 'productcode' since it contains the same information as the column 'productname'
# remove column 'source' since it contains the same information as the column 'sourceversion'
df = df.drop(columns = ['notes','productcode','source'])

# save dataset to csv
processed_data_file = os.path.join(data_dir, dataset_name+'_edit.csv')
df.to_csv(processed_data_file, index=False)

'''
Upload processed data to Carto
'''
logger.info('Uploading processed data to Carto.')
util_carto.upload_to_carto(processed_data_file, 'LINK')

'''
Upload original data and processed data to Amazon S3 storage
'''
# initialize AWS variables
aws_bucket = 'wri-public-data'
s3_prefix = 'resourcewatch/'

logger.info('Uploading original data to S3.')
# Upload raw data file to S3

# Copy the raw data into a zipped file to upload to S3
raw_data_dir = os.path.join(data_dir, dataset_name+'.zip')
with ZipFile(raw_data_dir,'w') as zip:
    zip.write(raw_data_file, os.path.basename(raw_data_file))
# Upload raw data file to S3
uploaded = util_cloud.aws_upload(raw_data_dir, aws_bucket, s3_prefix+os.path.basename(raw_data_dir))

logger.info('Uploading processed data to S3.')
# Copy the processed data into a zipped file to upload to S3
processed_data_dir = os.path.join(data_dir, dataset_name+'_edit.zip')
with ZipFile(processed_data_dir,'w') as zip:
    zip.write(processed_data_file, os.path.basename(processed_data_file))
# Upload processed data file to S3
uploaded = util_cloud.aws_upload(processed_data_dir, aws_bucket, s3_prefix+os.path.basename(processed_data_dir))
# Set up logging # Get the top-level logger object # make it print to the console. # name of table on Carto where you want to upload data # this should be a table name that is not currently in use # create a new sub-directory within your specified dir called 'data' # within this directory, create files to store raw and processed data Download data and save to your data directory # insert the url used to download the data from the source website # download the data from the source # unzip source data Process data # read the data into a pandas dataframe # convert the column names to lowercase to match the column name requirements of Carto # convert the years in the 'year' column to datetime objects and store them in a new column 'datetime' # extract subset to only retain 'COUNTRY_TOTAL' (aggregate of all products at country level) in the 'category' column # remove column 'notes' since it only contains indexes instead of actual data # remove column 'productcode' since it contains the same information as the column 'productname' # remove column 'source' since it contains the same information as the column 'sourceversion' # save dataset to csv Upload processed data to Carto Upload original data and processed data to Amazon S3 storage # initialize AWS variables # Upload raw data file to S3 # Copy the raw data into a zipped file to upload to S3 # Upload raw data file to S3 # Copy the processed data into a zipped file to upload to S3 # Upload processed data file to S3