{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import urllib.request, json \nimport pandas as pd\n#from pandas.io.json import json_normalize\n############## https://labs.data.gov/dashboard/offices/qa\napi_list = [\"http://www.usda.gov/data.json\",\n            \"http://www.commerce.gov/data.json\",\n            #\"https://www.defense.gov/data.json\",\n            \"http://www.ed.gov/data.json\",\n            \"http://www.energy.gov/data.json\",\n            \"http://www.hhs.gov/data.json\",\n            \"http://www.dhs.gov/data.json\",\n            \"http://www.hud.gov/data.json\",\n            \"http://www.usdoj.gov/data.json\",\n            \"http://www.dol.gov/data.json\",\n            #\"http://www.state.gov/data.json\",\n            \"http://www.ustreas.gov/data.json\",\n            \"http://www.dot.gov/data.json\",\n            \"http://www.va.gov/data.json\"\n            \"http://www.gsa.gov/data.json\",\n            \"http://www.nsf.gov/data.json\",\n            #\"http://www.nrc.gov/data.json\",\n            \"http://www.opm.gov/data.json\",\n            \"http://www.sba.gov/data.json\",\n            \"http://www.ssa.gov/data.json\",\n            \"http://www.usaid.gov/data.json\",\n            \"http://www.usccr.gov/data.json\",\n            \"http://www.consumerfinance.gov/data.json\",\n            \"http://www.cpsc.gov/data.json\",\n            \"http://www.eeoc.gov/data.json\",\n            \"http://www.fdic.gov/data.json\",\n            #\"http://www.fhfa.gov/data.json\",\n            \"http://www.frtib.gov/data.json\",\n            \"http://www.ftc.gov/data.json\",\n            #\"http://www.mspb.gov/data.json\",\n            \"https://www.doi.gov/sites/doi.gov/files/data.json\",\n            \"http://www.udall.gov/data.json\",\n            \"http://www.ncua.gov/data.json\",\n            #\"http://www.arts.gov/data.json\",\n            \"http://www.neh.gov/data.json\",\n            \"http://www.ntsb.gov/data.json\",\n            \"http://www.osc.gov/data.json\",\n            \"http://www.pbgc.gov/data.json\",\n            \"http://www.presidio.gov/data.json\",\n            \"https://www.pdsdc.org/data.json\",\n            \"http://www.sec.gov/data.json\",\n            \"http://www.usitc.gov/data.json\", \n           ]\n#api_list = [\"http://www.commerce.gov/data.json\"]\ni=0\nfor site in api_list:\n    print (site)\n    with urllib.request.urlopen(site) as url:\n        try:\n            data = json.loads(url.read().decode('utf8'))\n        except json.decoder.JSONDecodeError:\n            print('failure')\n            pass\n        \n        #print (data)\n        if i==0:\n            df = pd.DataFrame(pd.json_normalize(data, 'dataset'))\n        else:\n            temp = pd.DataFrame(pd.json_normalize(data, 'dataset'))\n            df = df.append(temp, ignore_index=True)\n    i=i+1\n#df=df[df['@type'].str.contains('dcat:Dataset')]\ndf","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-06-20T08:42:29.526558Z","iopub.execute_input":"2021-06-20T08:42:29.526892Z","iopub.status.idle":"2021-06-20T08:44:11.578296Z","shell.execute_reply.started":"2021-06-20T08:42:29.526861Z","shell.execute_reply":"2021-06-20T08:44:11.577466Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df","metadata":{"execution":{"iopub.status.busy":"2021-06-20T08:49:07.971395Z","iopub.execute_input":"2021-06-20T08:49:07.971834Z","iopub.status.idle":"2021-06-20T08:49:08.266998Z","shell.execute_reply.started":"2021-06-20T08:49:07.971783Z","shell.execute_reply":"2021-06-20T08:49:08.265985Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df2 = df[['title']].copy()\ndf2['title'].str.replace(\",\",\"\")\ndf2['lower_title'] = df['title'].str.lower()\ndf[\"title\"] = df['title'].str.replace('[^\\w\\s]','').str.replace(\",\",\"\")\n\ndf","metadata":{"execution":{"iopub.status.busy":"2021-06-20T08:49:42.864531Z","iopub.execute_input":"2021-06-20T08:49:42.864885Z","iopub.status.idle":"2021-06-20T08:49:44.294577Z","shell.execute_reply.started":"2021-06-20T08:49:42.864853Z","shell.execute_reply":"2021-06-20T08:49:44.293542Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"arr = df[\"title\"].unique()\nlen(arr)","metadata":{"execution":{"iopub.status.busy":"2021-06-20T08:49:53.852564Z","iopub.execute_input":"2021-06-20T08:49:53.852949Z","iopub.status.idle":"2021-06-20T08:49:53.912876Z","shell.execute_reply.started":"2021-06-20T08:49:53.852916Z","shell.execute_reply":"2021-06-20T08:49:53.911897Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df","metadata":{"execution":{"iopub.status.busy":"2021-06-20T08:49:56.542404Z","iopub.execute_input":"2021-06-20T08:49:56.542742Z","iopub.status.idle":"2021-06-20T08:49:56.703735Z","shell.execute_reply.started":"2021-06-20T08:49:56.542695Z","shell.execute_reply":"2021-06-20T08:49:56.702626Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_tmp = pd.DataFrame(arr, columns =[\"title\"])\ndf_tmp.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-20T08:50:02.098908Z","iopub.execute_input":"2021-06-20T08:50:02.099246Z","iopub.status.idle":"2021-06-20T08:50:02.107558Z","shell.execute_reply.started":"2021-06-20T08:50:02.099217Z","shell.execute_reply":"2021-06-20T08:50:02.106775Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(df_tmp)","metadata":{"execution":{"iopub.status.busy":"2021-06-20T08:50:09.244329Z","iopub.execute_input":"2021-06-20T08:50:09.244656Z","iopub.status.idle":"2021-06-20T08:50:09.249691Z","shell.execute_reply.started":"2021-06-20T08:50:09.244629Z","shell.execute_reply":"2021-06-20T08:50:09.248859Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_tmp.to_csv('govt_much_bigger_datasets.csv', index = False)","metadata":{"execution":{"iopub.status.busy":"2021-06-20T08:51:29.364738Z","iopub.execute_input":"2021-06-20T08:51:29.365087Z","iopub.status.idle":"2021-06-20T08:51:29.788753Z","shell.execute_reply.started":"2021-06-20T08:51:29.365055Z","shell.execute_reply":"2021-06-20T08:51:29.787906Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df3=df[df['identifier'].str.match('noaa')]\n\ndf_tmp[df_tmp['title'].str.lower().str.contains('noaa')]","metadata":{"execution":{"iopub.status.busy":"2021-06-20T07:55:31.187455Z","iopub.execute_input":"2021-06-20T07:55:31.187783Z","iopub.status.idle":"2021-06-20T07:55:31.318104Z","shell.execute_reply.started":"2021-06-20T07:55:31.187753Z","shell.execute_reply":"2021-06-20T07:55:31.317123Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Thank you https://www.kaggle.com/mlconsult/100000-govt-datasets-api-json-to-df","metadata":{}},{"cell_type":"markdown","source":"Thank you @Ken Miller for sharing ","metadata":{}}]}