Read web content into a dataframe without writing to a file-CodePudding

I am trying to read data from the following link to a data frame without saving locally (this is important). I figured out a way (below), but is there an efficient way to do this?

from urllib.request import urlopen
import pandas as pd
from io import StringIO
from matplotlib.dates import DateFormatter
from datetime import datetime


uri = 'https://mesonet.agron.iastate.edu/cgi-bin/request/asos.py?station=AXA&data=all&year1=2022&month1=12&day1=1&year2=2022&month2=12&day2=1&tz=Etc/UTC&format=onlycomma&latlon=no&elev=no&missing=M&trace=T&direct=no&report_type=3&report_type=4'
data = urlopen(uri, timeout=300).read().decode("utf-8")

dateparse = lambda x: datetime.strptime(x.strip(), '%Y-%m-%d %H:%M')


str1 = data.split('\n')
dfList = []
for ii in range(1,len(str1)):
    if len(str1[ii])>0:
        df1 = pd.read_csv(StringIO(str1[ii]), parse_dates=[1], date_parser=dateparse, header=None) #Read each string into a dataframe
        if not df1.empty:
            df2 = df1.iloc[:,0:3] #Get the first five columns
            if df2.iloc[0,-1] != 'M': #Don't append the ones with missing data
                dfList.append(df2)
df = pd.concat(dfList, axis=0, ignore_index=True)
df.columns = ['Station','Date','Temp']

ax1 = df.plot(x=1,y=2)
ax1.get_figure().autofmt_xdate()

CodePudding user response：

Using requests, pandas and io:

from io import StringIO

import pandas as pd
import requests

url = (
    "https://mesonet.agron.iastate.edu/cgi-bin/request/asos.py?"
    "station=AXA&data=all&year1=2022&month1=12&day1=1&year2=2022&"
    "month2=12&day2=1&tz=Etc/UTC&format=onlycomma&latlon=no&"
    "elev=no&missing=M&trace=T&direct=no&report_type=3&report_type=4"
)

with requests.Session() as request:
    response = request.get(url, timeout=30)
if response.status_code != 200:
    print(response.raise_for_status())

df = pd.read_csv(StringIO(response.text), sep=",")
print(df)