Python Read Website Table Data into Dataframe

Question

I came to know this source to import data. I tried but not successful in importing the data

https://public.opendatasoft.com/explore/embed/dataset/us-zip-code-latitude-and-longitude/table/

my code:

from urllib.request import urlopen, Request
### Import USA ZIP codes,counties, latitudes
usurl = 'https://public.opendatasoft.com//explore//embed//dataset//us-zip-code-latitude-and-longitude//table//'
query_url = Request(usurl)
url_response = urlopen(query_url)
read_response = url_response.read()

print (read_response)
b'\n<!DOCTYPE html>\n<html lang="en">\n    <head>\n    \n    \n\n    \n    
    <title>Opendatasoft</title>\n        <link rel="stylesheet"
 type="text/css" href="/static/vendor/font-awesome-4.7.0/css/font-awesome.min.css">\n        <link rel="styleshe....

Presently I see no data but a string text.

Table on the data:

Have you tried df = pd.read_html('url here')? It'll return a dataframe for each table on the site, each dataframe stored in a list, so you just select the list element you want, and that's your dataframe. — BLimitless
– BLimitless, Commented Jun 3, 2021 at 20:25
JS is creating the table and rendering javascript in a request does not work. workaround can be url='https://public.opendatasoft.com/explore/dataset/us-zip-code-latitude-and-longitude/download/?format=csv&timezone=America/New_York&lang=en&use_labels_for_header=true&csv_separator=%3B' df=pd.read_csv(url,sep=";") — simpleApp
– simpleApp, Commented Jun 3, 2021 at 21:50
@BLimitless Got following error ValueError: No tables found — Mainland
– Mainland, Commented Jun 3, 2021 at 23:32

simpleApp · Accepted Answer · 2021-06-03 23:49:01Z

2

JS is creating the table and rendering of javascript in a request does not work. a workaround can be:

url='https://public.opendatasoft.com/explore/dataset/us-zip-code-latitude-and-longitude/download/?format=csv&timezone=America/New_York&lang=en&use_labels_for_header=true&csv_separator=%3B' 
df=pd.read_csv(url,sep=";")

answered Jun 3, 2021 at 23:49

simpleApp

3,1782 gold badges15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BLimitless Over a year ago

this works and is magical! Upvote. But how did you know to remove 'table/' from the end of the url and add '/download/?format=csv&timezone=America/New_York&lang=en&use_labels_for_header=true&csv_separator=%3B'. When I try it without that addition I get a ParserError: Error tokenizing data. Thank you.

simpleApp Over a year ago

hi there, whenever there is a platform for the dataset, they will have a way to download the dataset. otherwise, it defeats the purpose of ML training. So I explored the site and found they have "Export" section which supports csv,json and many more. I picked their csv file download URL and then feed here. I hope it helped. cheers!

Collectives™ on Stack Overflow

Python Read Website Table Data into Dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related