openbudget / budgetkey-data-pipelines Goto Github PK
View Code? Open in Web Editor NEWBudget Key data processing pipelines
Budget Key data processing pipelines
Service Providers scraper
Service Provider Registry is a list of suppliers which the government may contract with for purchasing 'services' (e.g. cleaning, security)
Data is received from scraping a website of the ministry of economy: http://www.economy.gov.il/Employment/WorkRights/LicensesandPermits/Licenses/Pages/NewContractorLicense.aspx
moital_service_providers
under budgetkey_data_pipelines/pipelines/entities
/var/datapackages/entities/moital_service_providers
)Ref:
How to write a pipeline:
https://github.com/frictionlessdata/datapackage-pipelines
Legacy scraper folder: https://www.dropbox.com/sh/xan213ilqrdh43p/AAC1tSoTiH1rnMOue7pBJyhHa?dl=0
Legacy scraper is in processors/scrape_moital_contractors.py
Example of using selenium can be found under pipelines/entities/special/scraper.py
Example of a generic scraper can be found under pipelines/supports/criteria/scraper.py
(relevant after #13 is merged)
geocode_entities: INFO :no update needed: 512206020
geocode_entities: INFO :no update needed: 512206046
geocode_entities: INFO :no update needed: 512206079
geocode_entities: INFO :no update needed: 512206301
geocode_entities: INFO :no update needed: 512206350
geocode_entities: INFO :no update needed: 512206384
geocode_entities: Traceback (most recent call last):
geocode_entities: File "/budgetkey_data_pipelines/pipelines/entities/geocode_entities.py", line 246, in
geocode_entities: spew(datapackage, GeoCodeEntities(parameters, datapackage, resources).filter_resources())
geocode_entities: File "/usr/local/lib/python3.6/site-packages/datapackage_pipelines/wrapper/wrapper.py", line 60, in spew
geocode_entities: for rec in res:
geocode_entities: File "/budgetkey_data_pipelines/pipelines/entities/geocode_entities.py", line 202, in filter_resource
geocode_entities: row = self.get_row(entity_id, entity_location)
geocode_entities: File "/budgetkey_data_pipelines/pipelines/entities/geocode_entities.py", line 223, in get_row
geocode_entities: provider, geojson = location_row.provider, location_row.geojson
geocode_entities: AttributeError: 'result' object has no attribute 'provider'
load_sql_table: ERROR :Output pipe disappeared!
We'd like to have these documents in a tabular data file: http://www.justice.gov.il/Units/Tmihot/Pages/TestServies.aspx
criteria
under budgetkey_data_pipelines/pipelines/supports
/var/datapackages/supports/criteria
)You can use this existing pipeline for inspiration: https://github.com/OpenBudget/budgetkey-data-pipelines/blob/master/budgetkey_data_pipelines/pipelines/procurement/spending/collect_report_uris.py
(although keep the processor local and not in the processors directory)
add_publisher_urls_resource
processor is doing some http requestsExemptionsPublisherScraper
) - which handles making of the http requests and returning the data.add_publisher_urls_resource
processor - in the context of the pipelines frameworkadd_publisher_urls_resource
should not contain any code which is used only for mocks or for testingadd_publisher_urls_resource
processorBased on the Active in Localities (i.e. city, settlement) and the Central Bureau of Statistics keys - add each organization a multivalue field of relevant areas.
Each city/settlement is related to a single area. Each area consist multiple cities/settlements
Later:
See this page:
www.calcalist.co.il/local/home/0,7340,L-3789,00.html
We want to scrape the data here, similar to what we're doing in here with the TheMarker page:
https://github.com/OpenBudget/budgetkey-data-pipelines/blob/master/budgetkey_data_pipelines/pipelines/people/appointments/media/pipeline-spec.yaml
(although scraping mechanism will probably be different)
Step 1: Scrape the data and store it individually, using the same schema as the TheMarker source
Step 2: Clean duplicates between the two sources.
Step 3: Make sure that each item in the result contains an indication (or link) to its sources
Now returning under supports
.
http://next.obudget.org/pipelines/#anchor-FAILED-entities-cooperatives-cooperatives
dump.to_sql: Traceback (most recent call last):
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1159, in _execute_context
dump.to_sql: context)
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 467, in do_executemany
dump.to_sql: cursor.executemany(statement, parameters)
dump.to_sql: psycopg2.IntegrityError: duplicate key value violates unique constraint "cooperatives_pkey"
dump.to_sql: DETAIL: Key (id)=(570000018) already exists.
dump.to_sql: The above exception was the direct cause of the following exception:
dump.to_sql: Traceback (most recent call last):
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/datapackage_pipelines/specs/../lib/dump/to_sql.py", line 134, in
dump.to_sql: SQLDumper()()
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/datapackage_pipelines/lib/dump/dumper_base.py", line 33, in __call__
dump.to_sql: self.stats)
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/datapackage_pipelines/wrapper/wrapper.py", line 60, in spew
dump.to_sql: for rec in res:
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/datapackage_pipelines/lib/dump/dumper_base.py", line 69, in hasher
dump.to_sql: for row in resource:
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/datapackage_pipelines/lib/dump/dumper_base.py", line 59, in row_counter
dump.to_sql: for row in resource:
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/jsontableschema_sql/writer.py", line 57, in write
dump.to_sql: for wr in self.__insert():
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/jsontableschema_sql/writer.py", line 75, in __insert
dump.to_sql: statement.execute(self.__buffer)
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/base.py", line 386, in execute
dump.to_sql: return e._execute_clauseelement(self, multiparams, params)
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
dump.to_sql: compiled_sql, distilled_params
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
dump.to_sql: context)
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
dump.to_sql: exc_info
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
dump.to_sql: reraise(type(exception), exception, tb=exc_tb, cause=cause)
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
dump.to_sql: raise value.with_traceback(tb)
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1159, in _execute_context
dump.to_sql: context)
dump.to_sql: File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 467, in do_executemany
dump.to_sql: cursor.executemany(statement, parameters)
dump.to_sql: sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) duplicate key value violates unique constraint "cooperatives_pkey"
dump.to_sql: DETAIL: Key (id)=(570000018) already exists.
dump.to_sql: [SQL: 'INSERT INTO cooperatives (id, name, registration_date, phone, primary_type_id, primary_type, secondary_type_id, secondary_type, legal_status_id, legal_status, last_status_date, type, municipality_id, municipality, inspector, address) VALUES (%(id)s, %(name)s, %(registration_date)s, %(phone)s, %(primary_type_id)s, %(primary_type)s, %(secondary_type_id)s, %(secondary_type)s, %(legal_status_id)s, %(legal_status)s, %(last_status_date)s, %(type)s, %(municipality_id)s, %(municipality)s, %(inspector)s, %(address)s)'] [parameters: ({'id': '570000018', 'name': 'נחלת ישראל רמה אגודה שתופית בע"מ (במחיקה)', 'registration_date': datetime.datetime(1921, 2, 6, 0, 0), 'phone': '', 'primary_type_id': '43', 'primary_type': 'שיכון', 'secondary_type_id': '61', 'secondary_type': 'שיכון', 'legal_status_id': '23', 'legal_status': 'הודעה שניה על מחיקה', 'last_status_date': datetime.datetime(1970, 1, 29, 12, 0), 'type': 'התאחדות האיכרים', 'municipality_id': None, 'municipality': '', 'inspector': 'צוק חיים', 'address': 'דופקר ++++'}, {'id': '570000026', 'name': 'הלואה וחסכון ירושלים אגודה שיתופית בע"מ', 'registration_date': datetime.datetime(1921, 3, 24, 10, 25), 'phone': '02-6234432', 'primary_type_id': '44', 'primary_type': 'אשראי וחסכון', 'secondary_type_id': '63', 'secondary_type': 'אשראי', 'legal_status_id': '10', 'legal_status': 'אגודה פעילה', 'last_status_date': datetime.datetime(1921, 3, 24, 8, 13, 46), 'type': '', 'municipality_id': '3000', 'municipality': 'ירושלים', 'inspector': 'בן-חמו יוסף', 'address': 'קרן היסוד 41 ירושלים 94188 ת.ד: 2575'}, {'id': '570000034', 'name': 'הלואה וחיסכון זכרון יעקב אגודה הדדית בע"מ', 'registration_date': datetime.datetime(1921, 10, 16, 0, 0), 'phone': '', 'primary_type_id': '44', 'primary_type': 'אשראי וחסכון', 'secondary_type_id': '63', 'secondary_type': 'אשראי', 'legal_status_id': '26', 'legal_status': 'אגודה מוזגה - מבוטלת', 'last_status_date': datetime.datetime(1977, 1, 29, 12, 0), 'type': '', 'municipality_id': '9300', 'municipality': 'זכרון יעקב', 'inspector': 'חליל יעקב', 'address': 'הנציב זכרון יעקב מיקוד ת.ד: 8'}, {'id': '570000042', 'name': 'קיבוץ איילת השחר', 'registration_date': datetime.datetime(1921, 12, 19, 12, 52), 'phone': '04-6932111', 'primary_type_id': '40', 'primary_type': 'חקלאות', 'secondary_type_id': '74', 'secondary_type': 'קיבוץ מתחדש', 'legal_status_id': '10', 'legal_status': 'אגודה פעילה', 'last_status_date': datetime.datetime(1921, 12, 19, 13, 27, 17), 'type': 'תנועה קבוצית מאוחדת תק"מ', 'municipality_id': '77', 'municipality': 'איילת השחר', 'inspector': 'מור טל', 'address': 'ד.נ. גליל עליון איילת השחר 12200'}, {'id': '570000059', 'name': 'אגודה שיתופית לעזרה הדדית ברחובות בע"מ', 'registration_date': datetime.datetime(1921, 12, 19, 0, 0), 'phone': '', 'primary_type_id': '44', 'primary_type': 'אשראי וחסכון', 'secondary_type_id': '63', 'secondary_type': 'אשראי', 'legal_status_id': '25', 'legal_status': 'אגודה בוטלה לאחר פירוק', 'last_status_date': datetime.datetime(1987, 5, 21, 12, 0), 'type': '', 'municipality_id': '8400', 'municipality': 'רחובות', 'inspector': 'יהודה משה', 'address': 'רחובות רחובות'}, {'id': '570000067', 'name': 'הכפר העברי - אגודה הדדית בע"מ', 'registration_date': datetime.datetime(1922, 2, 2, 0, 0), 'phone': '02-5700756', 'primary_type_id': '40', 'primary_type': 'חקלאות', 'secondary_type_id': '54', 'secondary_type': 'אגודה חקלאית כללית', 'legal_status_id': '10', 'legal_status': 'אגודה פעילה', 'last_status_date': datetime.datetime(1922, 2, 2, 12, 0), 'type': '', 'municipality_id': '3000', 'municipality': 'ירושלים', 'inspector': 'בן-חמו יוסף', 'address': 'נמצא אצל אבנאור שמואל, אביזהר, אחוזת בית הכ 8 כניסה: 247 ירושלים 96267'}, {'id': '570000075', 'name': 'החקלאית אג"ש לבטוח ולשרותים וטרינריים למקנה בישראל בעמ', 'registration_date': datetime.datetime(1922, 4, 11, 0, 0), 'phone': '04-6279600', 'primary_type_id': '40', 'primary_type': 'חקלאות', 'secondary_type_id': '57', 'secondary_type': 'ביטוח חקלאי', 'legal_status_id': '10', 'legal_status': 'אגודה פעילה', 'last_status_date': datetime.datetime(1922, 4, 11, 12, 0), 'type': '', 'municipality_id': '1167', 'municipality': 'קיסריה', 'inspector': 'שרעבי מזל', 'address': 'הברקת 20 קיסריה 38900 ת.ד: 3039'}, {'id': '570000083', 'name': 'קופת מלוה חקלאית לגמ"ח אגודה שיתופית פתח תקוה בע"מ', 'registration_date': datetime.datetime(1922, 6, 29, 0, 0), 'phone': '', 'primary_type_id': '45', 'primary_type': 'תגמולים פנסיה ועזה"ד', 'secondary_type_id': '63', 'secondary_type': 'אשראי', 'legal_status_id': '25', 'legal_status': 'אגודה בוטלה לאחר פירוק', 'last_status_date': datetime.datetime(1992, 12, 14, 12, 0), 'type': '', 'municipality_id': '7900', 'municipality': 'פתח תקוה', 'inspector': '', 'address': 'מונטיפיורי 14 פתח תקוה 49364'} ... displaying 10 of 1001 total bound parameter sets ... {'id': '570010009', 'name': 'הכורם הצעיר אגודה שיתופית חקלאית להספקת מים ברחובות בע"מ', 'registration_date': datetime.datetime(1950, 10, 23, 0, 0), 'phone': '08-9461817', 'primary_type_id': '40', 'primary_type': 'חקלאות', 'secondary_type_id': '56', 'secondary_type': 'אספקת מים', 'legal_status_id': '10', 'legal_status': 'אגודה פעילה', 'last_status_date': datetime.datetime(1950, 10, 23, 12, 0), 'type': '', 'municipality_id': '8400', 'municipality': 'רחובות', 'inspector': 'בר-נתן יונה', 'address': 'הרצל 143 רחובות 76266'}, {'id': '570010017', 'name': 'מעונות עובדי קופת חולים ב\' בגבעתיים אגודה שיתופית בע"מ', 'registration_date': datetime.datetime(1950, 10, 23, 0, 0), 'phone': '03-6250814', 'primary_type_id': '43', 'primary_type': 'שיכון', 'secondary_type_id': '61', 'secondary_type': 'שיכון', 'legal_status_id': '10', 'legal_status': 'אגודה פעילה', 'last_status_date': datetime.datetime(1950, 10, 23, 12, 0), 'type': '', 'municipality_id': '6300', 'municipality': 'גבעתיים', 'inspector': 'בר-נתן יונה', 'address': 'נמצא אצל בוריס בריק, מצולות ים 14 גבעתיים 53486'})]
(sink): /usr/local/lib/python3.6/site-packages/jsontableschema/model.py:48: UserWarning: Class models.SchemaModel is deprecated [v0.7-v1)
(sink): warnings.warn(message, UserWarning)
es:time-range
is used to filter / show sums of counts per time range
after PR #102 only supports
and tenders
will have this field, need to add it to all doc types
And if they're not - check why.
Generate sitemal xmls for all records in the website + links.
Make it scriptable (+note Google's limitations on size of XMLs)
Some relevant links:
I don't find an exemption and I afraid that it's not the only one that missing
https://www.mr.gov.il/ExemptionMessage/Pages/ExemptionMessage.aspx?pID=592899
select *
from exemption e
where publication_id = 592899 or publication_id = '592899'
In this file we have a list of all elected representatives in all municipalities
We'd like to write a pipeline to scrape this file and add it to the current 'people' pipelines as another role for each person.
The list of Ottoman Associations can be found here: https://foi.gov.il/he/node/1908
You need to write a pipeline that converts the Excel file into a simple tabular resource with 3 columns:
The pipeline should be named 'ottoman-associations' and be located under entities/
in PR #67 there were some DB schema changes which weren't applied
I think it causes the failure in the pipeline
Add a processor that does some geocoding for addresses of entities.
add_publisher_urls_resource: INFO :Processed 10362 rows
dump.to_path: INFO :Processed 10362 rows
add_publisher_urls_resource: INFO :Processed 94878 rows
dump.to_path: INFO :Processed 94878 rows
add_central_urls_resource: INFO :Processed 123 rows
dump.to_path: INFO :Processed 123 rows
TENDER_TYPE | COUNT |
---|---|
office | 10,377 |
exemptions | 94,898 |
central | 1 |
There are about 300 spending reports coming from the government. Each one of them with a slightly different format and there’s a pipeline that’s supposed to consolidate them into one (it’s everything in procurement/spending
).
But - it doesn’t always succeed and there are errors in the process:
there’s this report which is generated during the process: http://data.obudget.org/queries/1153/source
At the moment there are 59 problematic reports:
The task is to:
Also - record the decisions somehow (maybe incorporate it in the pipeline) so that in the future we can query only the reports which were not analysed.
We try to find a proper entity from the official registries when all we have is the name (e.g. in the supports dataset, exemptions and sometimes with contract spending).
Most of the time we succeed, but not always.
This task is to understand why we miss and improve the detection rate.
there is a failure from docker cloud but I can't debug it because all requests return 404
from PR #69 - https://cloud.docker.com/redirect?resource_uri=/api/audit/v1/action/b267b9a7-e2a8-4e83-81d3-4a2ac4938613/
Generalise the exemption scraper to scrape these two sections in the procurement department:
The data from these pipelines should end up in the same table (unless the schemas are too different so that it doesn't make sense).
look at this merge commit - OriHoch@28bb9d8
it was done when I pulled latest changes from OpenBudget master branch to my master branch
my master branch didn't have any local changes, so it should be a fast-forward without a merge commit
the merge commit is for these 2 commits:
however, there is a commit which has the same title and time which is on OpenBudget master branch:
2dcd32e
not sure exactly what's going on here
@akariv - is it possible you are editing history?
Exemption scraper
Exemptions are records for planned purchases of the government that are exempt from a tender process.
The website to scrape is quite slow and buggy, so we want to scrape frequently, and only get the most recent records (for example, scrape every few hours and get a day's worth of records).
(For initialisation there should also be a one-time-run pipeline that scrapes the entire website)
Data is written to a DB table (exemptions), with the update
mode - so that records with the same publication id will be updated and data is appended and not replaced.
Scraped data includes the basic list of fields for each exemption record, as well as the list of documents for the record (which should be saved as a JSON object).
There might be issues regarding scraping from non Israeli IPs - talk to me if you encounter such issues.
Ref:
Legacy scraper folder: https://www.dropbox.com/sh/xan213ilqrdh43p/AAC1tSoTiH1rnMOue7pBJyhHa?dl=0
Legacy scraper is in tenders/exemption_scraper.py
https://apps.moital.gov.il/CooperativeSocieties/
4/12/2015 | חוות דעת מקצועית
).signed
extension
Build a pipeline to scrape the the following data from guide star association item page:
Number of workers (integer)
Number of volunteers (integer)
(check this link for "No report" placeholder text example)
Salaeries (+names) - up to 5 positions (position (string), amount (integer/float)) :
maybe in README, link to this post explaining the terms budget / spending / procurement
There are a lot of data series in the Bank Of Israel website.
Develop a processor that can be configured to a list of such series and creates a dataset with the following columns: series-name, timestamp, value
Sample link:
http://www.boi.org.il/he/DataAndStatistics/Pages/SeriesSearchBySubject.aspx?Level=4&sId=16
And the data can be scraped from:
http://www.boi.org.il/he/DataAndStatistics/Pages/[email protected]&DateStart=01/01/1975&DateEnd=31/08/2017&Level=4&Sid=16
See here:
http://mof.gov.il/BudgetSite/statebudget/Pages/PoliticalAgreements.aspx
There are a few files detailing budget items (Takanot) related to political agreements and government decisions.
The task is to write a pipeline to scrape these files and store the result in a DB table.
#97 follow-up:
For each year get the Financial Report from GuideStar. Year with no available online report should be marked as null
/None
It has been failing for some time, probably something changed on the source.
The code itself is in the donations/
folder.
Current failure can be seen in http://next.obudget.org/pipelines
While you're at it, also add the rest of the political donation databases at the Mevaker Hamedina website.
We scrape a list of support criteria documents (מבחני תמיכה).
We want to analyze the title of these documents (which is usually in a very similar template) and extract properties such as:
Tasks:
supports/criteria
Sample of such titles:
endpoint | data source | comments |
---|---|---|
example | example | example |
This will allow in the future more correct behaviours, such as periodically re-scraping records that are still "in progress" (i.e data might still be changing) and not scraping records that have been concluded.
Monetary Change transaction grouping
Monetary changes are records of single changes to 3rd level budget items (a.ka. "6-digit" items).
During the year, the national budget is modified, and these changes represent individual modifications (Some of these changes are brought to the finance committee of the Knesset to be approved, some don't).
We download the data and store it in a DB (see here: http://data.obudget.org/queries/1061/source)
The pipeline for the downloading and processing the data is here: https://github.com/OpenBudget/budgetkey-data-pipelines/tree/master/budgetkey_data_pipelines/pipelines/budget/national/changes/original
All changes are part of a request - the request ID is uniquely identified by the (leading_item
, req_code
) fields. Those requests that are brought to the committee for approval, also have a non-zero committee_id
.
Some of these requests are part of a larger transaction - for example, when moving funds from one ministry to another, we'll see a request for moving funds from ministry A to the general reserve and another request for moving the same amount from the general reserve to ministry B.
The goal of this issue is to assign a 'transaction id' to all changes - the same transaction id should be assigned to all changes that belong to the same transaction.
You should create a pipeline and processors in budget/national/changes/processed/
Pipeline should be called 'transactions' and should generate a 3 column file:
leading_item
req_code
transaction_id
Ref:
Legacy processing code folder: https://www.dropbox.com/sh/xan213ilqrdh43p/AAC1tSoTiH1rnMOue7pBJyhHa?dl=0
Legacy algorithm is in tenders/extract_change_groups.py
FROM frictionlessdata/datapackage-pipelines:latest
RUN sudo pip install -e /
update data structure to include past data (archive) per organization. We need to have the archive of past data (in year resolution) of the followings:
We can integrate the archiving process in real time (what the specific entry is being updated) or once a month. It would be better to keep a whole snapshot of all relevant entries in a certain point of time, to avoid misunderstandings and adjustments in data interpretation.
@noamoss commented on Mon Feb 05 2018
We need to collect data form quarterly reports, to include each step (payment report0 in the table.
http://mof.gov.il/GCA/CompaniesInformation/Pages/default.aspx
http://mof.gov.il/GCA/CompaniesInformation/Pages/CompanyInfo.aspx?k=483
hasn't happened again, it might have been fixed, but worth to investigate and ensure it's indeed fixed
https://gist.github.com/OriHoch/76676e82f4b02053894557843468f2d2
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.