-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathadh_prep_south_africa.py
159 lines (131 loc) · 5 KB
/
adh_prep_south_africa.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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
# -*- coding: utf-8 -*-
"""
Created on Wed Aug 24 10:27:33 2022
@author: heiko
"""
import tabula
import pandas as pd
import numpy as np
from datetime import date, datetime, timedelta
import glob
import re
base_data_path ='./data/south_africa/'
files_list = glob.glob('%s*.pdf'% base_data_path)
for i in range(len(files_list)):
files_list[i] = files_list[i].replace("\\","/")
logs = pd.read_csv('%sdata_log.txt'% base_data_path,header=None)
logs.columns=['done']
logs = logs.done.to_list()
files = pd.DataFrame()
files['files'] = files_list
file = files[~files.files.isin(logs)]
data_path = file.files.to_list()[0].split('.pdf')[0]
f = open('%sdata_log.txt'% base_data_path,'w')
for i in range(len(files_list)):
f.write(files_list[i])
f.write('\n')
f.close()
#%%
#data_path ='./data/south_africa/P0141June2022_Tables'
tables = tabula.read_pdf("{}.pdf".format(data_path), pages="all")
df_1 = tables[0]
df_2 = tables[1]
df_SA = df_1.iloc[[3,17,37,44,47],[0,3]]
df_SA['Percentage change'] = df_SA['Percentage change'].astype(str)
df_SA['Percentage change'] = df_SA['Percentage change'].apply(lambda x:x.split(' ')[1])
df_SA = df_SA.reset_index(drop=True)
df_SA['Percentage change'] = df_SA['Percentage change'].str.replace(',','.')
df_SA['Percentage change'] = df_SA['Percentage change'].astype(float)
# labels
df_SA_labels = df_1.iloc[[3,17,18,19,37,38,39,44,45,47,48],[0]]
df_SA_labels.columns=['divisions']
template = [0,1,1,1,2,2,2,3,3,4,4]
df_SA_labels['template'] = template
df_SA_labels = df_SA_labels.groupby(['template'])['divisions'].apply(' '.join).reset_index()
df_SA_labels = pd.concat([df_SA_labels,df_SA],axis=1)
df_SA_labels = df_SA_labels.drop(columns=['template','Unnamed: 0'])
df_SA_2 = df_2.iloc[[3,12,15,22,27,34,37,40],[0,2]]
df_SA_2_labels = df_2.iloc[[3,4,5,12,15,22,27,28,34,37,38,40,41,42],[0]]
df_SA_2_labels.columns=['divisions']
template = [0,0,0,1,2,3,4,4,5,6,6,7,7,7]
df_SA_2['Percentage change'] = df_SA_2['Percentage change'].astype(str)
df_SA_2['Percentage change'] = df_SA_2['Percentage change'].apply(lambda x:x.split(' ')[1])
df_SA_2 = df_SA_2.reset_index(drop=True)
df_SA_2['Percentage change'] = df_SA_2['Percentage change'].str.replace(',','.')
df_SA_2['Percentage change'] = df_SA_2['Percentage change'].astype(float)
df_SA_2_labels['template'] = template
df_SA_2_labels = df_SA_2_labels.groupby(['template'])['divisions'].apply(' '.join).reset_index()
df_SA_2_labels = pd.concat([df_SA_2_labels,df_SA_2],axis=1)
df_SA_2_labels = df_SA_2_labels.drop(columns=['template','Unnamed: 0'])
df_SA = pd.concat([df_SA_labels,df_SA_2_labels])
df_SA.to_csv('{}.csv'.format(data_path),index=False)
#%%
def get_last_date_of_month(year, month):
"""Return the last date of the month.
Args:
year (int): Year, i.e. 2022
month (int): Month, i.e. 1 for January
Returns:
date (datetime): Last date of the current month
"""
if month == 12:
last_date = datetime(year, month, 31)
else:
last_date = datetime(year, month + 1, 1) + timedelta(days=-1)
return last_date.strftime("%Y-%m-%d")
months = dict({'Jan':1,
'Feb':2,
'Mar':3,
'Apr':4,
'May':5,
'Jun':6,
'Jul':7,
'Aug':8,
'Sep':9,
'Oct':10,
'Nov':11,
'Dec':12
})
country = 'South Africa'
month = [val for key, val in months.items() if key in data_path][0]
year = re.search(r'.*([1-3][0-9]{3})',data_path).group(1) # [1-3] = num between 1-3, [0-9]{3} = num 0-9 repeat 3 times
year = int(year)
last = get_last_date_of_month(year, month)
codes = pd.read_csv('./data/codeList.csv')
df_SA.columns = ['Indicator.Name',last]
#df_SA[last] = df_SA[last].astype(float)
#%%
data_path= './data/imf/'
df_template = pd.read_csv('{}combined_imf_template.csv'.format(data_path))
df_template = df_template[df_template['Country']==country]
df_template = df_template.iloc[:,[0,1,2,3,4,-2,-1]]
#%%
# all items
def mapp_values(df,template):
template = template.loc[:,['Indicator.Name','Indicator.Code']]
values = ['All',
'Food and non-',
'Tobacco',
'Clothing',
'Communication',
'Education',
'Housing',
'Household',
'Health',
'Miscellaneous',
'Recreation',
'Restaurants',
'Transport',
'Insurance']
for i in range(len(values)):
val = template[template['Indicator.Name'].str.contains(values[i],case=False)==True]
try:
df['Indicator.Name'][df['Indicator.Name'].str.contains(values[i],case=False)==True] = val['Indicator.Name'].values
except:
print('ERROR with: {}'.format(values[i]))
df = pd.merge(template,df,how='left',on = 'Indicator.Name')
df = df.round(2)
return df
df_1 = mapp_values(df_SA,df_template)
country = 'south_africa'
df_1.to_csv('./outputs/{}/{}_{}.csv'.format(country,country,last),index=False)