-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathiati_rhfp.py
143 lines (79 loc) · 5.27 KB
/
iati_rhfp.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
import os
import pandas as pd
from data_updates.utils import push_folder_to_github
CSV_FOLDER_PATH = "data_updates/Python/iati_csv"
# create csv folder if it does not exist
path = CSV_FOLDER_PATH
isdir = os.path.isdir(path)
if not isdir:
os.mkdir(CSV_FOLDER_PATH)
donors_selected = pd.read_csv("https://ddw.devinit.org/api/export/1447")
donors_selected.columns = ["reporting_org_ref","registry_id","org_type","org_name","abbreviation","usability_score","tracker_commit","tracker_spend","latest_txn_year","hq_country","CRS_codes","IATI_organisation_type","IATI_organisation_name","country"]
donors_selected = donors_selected[donors_selected['tracker_spend'] == "Yes"]
donors_selected = donors_selected[['reporting_org_ref','country']]
# Dataset 1
data = pd.read_csv("https://ddw.devinit.org/api/export/1231")
data = pd.DataFrame(data)
data.columns = ["Reporting Organisation Reference","Reporting Organisation Narrative","year","Sector Code - Calculated"
, "DAC3 Sector Code - Calculated","Reporting Organisation Type Code","Reporting Organisation Type Name - Calculated"
,"Recipient Code","Recipient Name","Flow Type Code - Calculated","aid_type_di_name","x_transaction_value_usd_m_Sum"]
data = data[data['Reporting Organisation Reference'].isin(donors_selected['reporting_org_ref'])]
data = data[(data['Sector Code - Calculated'] != 13010) & (data['Sector Code - Calculated'] != 13081)]
data = pd.merge(data, donors_selected, left_on='Reporting Organisation Reference',right_on='reporting_org_ref').drop('reporting_org_ref', axis=1)
data = data.groupby(["year","country","Sector Code - Calculated"
, "DAC3 Sector Code - Calculated","Reporting Organisation Type Code","Reporting Organisation Type Name - Calculated"
,"Recipient Code","Recipient Name","Flow Type Code - Calculated","aid_type_di_name"],dropna=False).sum().reset_index()
# Sector code mapping
data['purpose_name'] = ""
data.loc[data['Sector Code - Calculated']==13020,'purpose_name'] = "Reproductive health care"
data.loc[data['Sector Code - Calculated']==13030,'purpose_name'] = "Family planning"
# Only ODA
data = data[data['Flow Type Code - Calculated'] ==10]
# Put it in millions
data["x_transaction_value_usd_m_Sum"] = data["x_transaction_value_usd_m_Sum"]/1000000
# Rename away from country
data["Reporting Organisation Narrative"] = data["country"]
data = data[data['country'] != "Germany"]
data.to_csv(f'{CSV_FOLDER_PATH}/IATI-RHFP-data-v1.csv', encoding='utf-8', index=False)
# Dataset 2
data = pd.read_csv("https://ddw.devinit.org/api/export/1271")
data = data[data['Reporting Organsation Reference'].isin(donors_selected['reporting_org_ref'])]
data = pd.DataFrame(data)
data.columns = ["Code type","Year", "recipient_name","Reporting Organisation Narrative","Reporting Organisation Reference","Flow Type Code - Calculated","disbursed"]
data = data[(data['Code type'] != 13010) & (data['Code type'] != 13081)]
# Sector code mapping
data.loc[data['Code type']==13020,'Code type'] = "Reproductive health care"
data.loc[data['Code type']==13030,'Code type'] = "Family planning"
# Only ODA
data = data[data['Flow Type Code - Calculated'] ==10]
data = pd.merge(data, donors_selected, left_on='Reporting Organisation Reference',right_on='reporting_org_ref').drop('reporting_org_ref', axis=1)
# Summing both
total_data = data.groupby(["Year","recipient_name","country"]).agg({"disbursed":"sum"}).reset_index()
total_data['Code type'] = "Reproductive health care and family planning"
# Groupby country
data = data.groupby(["Year","recipient_name","country","Code type"]).agg({"disbursed":"sum"}).reset_index()
data = pd.concat([data, pd.DataFrame(data = total_data)],ignore_index=True)
# Put it in millions
data["disbursed"] = data["disbursed"]/1000000
data = data.pivot(index=['Code type','country','recipient_name'], columns='Year', values='disbursed').reset_index()
data["Reporting Organisation Narrative"] = data["country"]
data = data[data['country'] != "Germany"]
data.to_csv(f'{CSV_FOLDER_PATH}/IATI-RHFP-data-v2.csv', encoding='utf-8', index=False)
# Dataset 4
data = pd.read_csv("https://ddw.devinit.org/api/export/1309")
data = pd.DataFrame(data)
data.columns = ["Donor Name","Aid Type Di Name", "Year","Purpose Code","Reporting Organisation Reference", "Usd Disbursement Deflated Sum"]
data = data[data["Reporting Organisation Reference"].isin(donors_selected['reporting_org_ref'])]
data = pd.merge(data, donors_selected, left_on='Reporting Organisation Reference',right_on='reporting_org_ref').drop('reporting_org_ref', axis=1)
data = data[(data['Purpose Code'] != 13010) & (data['Purpose Code'] != 13081)]
# Sector code mapping
data.loc[data['Purpose Code']==13020,'Purpose Name'] = "Reproductive health care"
data.loc[data['Purpose Code']==13030,'Purpose Name'] = "Family planning"
# Put it in millions
data["Usd Disbursement Deflated Sum"] = data["Usd Disbursement Deflated Sum"]/1000000
data = data.groupby(["Year","Aid Type Di Name","country","Purpose Name","Purpose Code"]).agg({"Usd Disbursement Deflated Sum":"sum"}).reset_index()
data["Donor Name"] = data["country"]
data = data[data['country'] != "Germany"]
data.to_csv(f'{CSV_FOLDER_PATH}/iati_rhfp4.csv', encoding='utf-8', index=False)
# Sample call to the function below
push_folder_to_github('devinit/di-website-data', 'main', 'Python/iati_csv', '2022', 'Committing from API', '*.csv')