def produce_projects_csv_for_country(output_dir, country):
# first get all appeals for this country (could eliminate this duplicative call, but it's not expensive)
appeals = fts_queries.fetch_appeals_json_for_country_as_dataframe(country)
appeal_ids = appeals.index
# then get all projects corresponding to those appeals and concatenate into one big frame
list_of_projects = [fts_queries.fetch_projects_json_for_appeal_as_dataframe(appeal_id) for appeal_id in appeal_ids]
list_of_non_empty_projects = filter_out_empty_dataframes(list_of_projects)
projects_frame = pd.concat(list_of_non_empty_projects)
write_dataframe_to_csv(projects_frame, build_csv_path(output_dir, 'projects', country=country))
import argparse
import sys
parser = argparse.ArgumentParser(description='Produce a report on CERF and ERF funding by cluster for a given appeal')
parser.add_argument('appeal_id', type=int)
args = parser.parse_args()
appeal_id = args.appeal_id
# note relying on strings is fragile - could break if things get renamed in FTS
# we don't seem to have much in the way of alternatives, other than changing the FTS API
CERF_NAME = "Central Emergency Response Fund"
ERF_NAME = "Emergency Response Fund (OCHA)"
PLEDGE_NAME = "Pledge"
contributions = fts_queries.fetch_contributions_json_for_appeal_as_dataframe(appeal_id)
projects = fts_queries.fetch_projects_json_for_appeal_as_dataframe(appeal_id)
# join contributions with the associated project data
merged = pd.merge(contributions, projects, left_on='project_code', right_on='code', how='inner')
# exclude pledges
merged = merged[merged.status != PLEDGE_NAME]
# pivot, summing amount by cluster and donor
pivot = merged.pivot_table(values='amount', aggfunc='sum', rows=['cluster'], cols=['donor'])
# only keep CERF/ERF
pivot_slice = pivot[[CERF_NAME, ERF_NAME]]
# TODO figure out to how include clusters with 0 funding
