{"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            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-19T03:26:52.774136Z","iopub.execute_input":"2021-06-19T03:26:52.774526Z","iopub.status.idle":"2021-06-19T03:29:39.018451Z","shell.execute_reply.started":"2021-06-19T03:26:52.774473Z","shell.execute_reply":"2021-06-19T03:29:39.017483Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df2 = df[['title']].copy()\ndf2['title'].str.replace(\",\",\"\")\ndf2['title']=df['title'].str.lower()\ndf[\"title\"] = df['title'].str.replace('[^\\w\\s]','')\n\ndf","metadata":{"execution":{"iopub.status.busy":"2021-06-19T03:29:39.023849Z","iopub.execute_input":"2021-06-19T03:29:39.024182Z","iopub.status.idle":"2021-06-19T03:29:40.699602Z","shell.execute_reply.started":"2021-06-19T03:29:39.024146Z","shell.execute_reply":"2021-06-19T03:29:40.698656Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"arr= df2[\"title\"].unique()\nlen(arr)","metadata":{"execution":{"iopub.status.busy":"2021-06-19T03:29:40.703959Z","iopub.execute_input":"2021-06-19T03:29:40.704336Z","iopub.status.idle":"2021-06-19T03:29:40.778122Z","shell.execute_reply.started":"2021-06-19T03:29:40.704289Z","shell.execute_reply":"2021-06-19T03:29:40.777306Z"},"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-19T03:29:40.779343Z","iopub.execute_input":"2021-06-19T03:29:40.779666Z","iopub.status.idle":"2021-06-19T03:29:40.790170Z","shell.execute_reply.started":"2021-06-19T03:29:40.779638Z","shell.execute_reply":"2021-06-19T03:29:40.788890Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"len(df_tmp)","metadata":{"execution":{"iopub.status.busy":"2021-06-19T03:29:40.792191Z","iopub.execute_input":"2021-06-19T03:29:40.793011Z","iopub.status.idle":"2021-06-19T03:29:40.800052Z","shell.execute_reply.started":"2021-06-19T03:29:40.792973Z","shell.execute_reply":"2021-06-19T03:29:40.798983Z"},"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-19T03:29:40.801563Z","iopub.execute_input":"2021-06-19T03:29:40.802146Z","iopub.status.idle":"2021-06-19T03:29:41.494177Z","shell.execute_reply.started":"2021-06-19T03:29:40.802108Z","shell.execute_reply":"2021-06-19T03:29:41.493220Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df3=df[df['identifier'].str.match('noaa')]\n\ndf3=df2[df2['title'].str.contains('longitudinal')]\n\ndf3","metadata":{"execution":{"iopub.status.busy":"2021-06-19T03:29:41.496030Z","iopub.execute_input":"2021-06-19T03:29:41.496291Z","iopub.status.idle":"2021-06-19T03:29:41.607043Z","shell.execute_reply.started":"2021-06-19T03:29:41.496266Z","shell.execute_reply":"2021-06-19T03:29:41.606077Z"},"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":{}}]}