如何将汤转换为数据框

Question

我是美丽的汤的新手。我正在努力从源刮擦一些Excel文件。 url到source： 原始数据来源： https://droughtmonitor.unl.unl.unl.edu/dmdata/dmdata/gisdata/gisdata.aspx/ <

我的主要目标是从该URL刮擦数据，并将其转换为数据框架，包括原始数据源URL中的所有文件，以及是否可以自动下载一些新文件并添加到源中。

from bs4 import BeautifulSoup
import requests
import json
import pandas as pd

url2 = 'https://droughtmonitor.unl.edu/DmData/GISData.aspx/?mode=table&aoi=county&date=2022-06-21'
r2 = requests.get(url2)
soup = BeautifulSoup(r2.text,'html.parser')
raw_data = [data.text for data in soup]

以上代码为我提供了一个输出： -

["MapDate,FIPS,County,State,Nothing,D0,D1,D2,D3,D4,ValidStart,ValidEnd\r\n20220621,01001,Autauga County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01003,Baldwin County,AL,81.22,18.78,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01005,Barbour County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01007,Bibb County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01009,Blount County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01011,Bullock County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01013,Butler County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01015,Calhoun County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01017,Chambers County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01019,Cherokee County,AL,69.27,30.73,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n20220621,01021,Chilton County,AL,100.00,0.00,0.00,0.00,0.00,0.00,2022-06-21,2022-06-27\r\n

我想拥有Inital 12值：mapdate，fips，county，state，nothene，d0，d0，d1，d2，d3，d4，valle vallestart，vallystart，valsect 成为我的列名，并与与之相同的值休息。

此外，原始数据源URL具有2000年至2022年的值。我需要以相同格式和单个CSV中的所有数据。

另外，我需要以一种方式将代码自动提取到源中添加的任何新数据。

有人可以指导我吗？

Answer 1

它发送文件 csv ，因此您不需要

您可以使用 io 与 pandas.read_csv（）

import requests
import pandas as pd
import io

url = 'https://droughtmonitor.unl.edu/DmData/GISData.aspx/?mode=table&aoi=county&date=2022-06-21'

response = requests.get(url)

fh = io.StringIO(response.text)  # create file in memory
df = pd.read_csv(fh)

print(df)

或您可以使用Module CSV

import requests
import csv
import io

url = 'https://droughtmonitor.unl.edu/DmData/GISData.aspx/?mode=table&aoi=county&date=2022-06-21'

response = requests.get(url)

fh = io.StringIO(response.text)  # create file in memory
data = list(csv.reader(fh))

print(data)

---

编辑：

您甚至可以直接使用 url pandas 代码>

import pandas as pd

url = 'https://droughtmonitor.unl.edu/DmData/GISData.aspx/?mode=table&aoi=county&date=2022-06-21'

df = pd.read_csv(url)

print(df)

---

编辑：

现在，您只需要使用日期列表，然后运行 -loop的才能读取所有CSV并保持列表。稍后，您可以使用 pandas.concat（）将此列表转换为单个 dataframe 。

pandas doc： Merge

“示例：

import pandas as pd

# --- before loop ---

all_dates = ["2022-06-21", "2022-06-14", "2022-06-07"]
all_dfs = []

# url without `2022-06-21` at the end
url = 'https://droughtmonitor.unl.edu/DmData/GISData.aspx/?mode=table&aoi=county&date='

# --- loop ---

for date in all_dates:
    print('date:', date)
    df = pd.read_csv( url + date )
    all_dfs.append( df )

# --- after loop --- 

full_df = pd.concat(all_dfs)
print(full_df)

要获取日期列表，您可以从网页上刮擦它们，但可能需要 selenium 而不是而不是<代码> BeautifulSoup 因为页面使用JavaScript在页面上添加日期。

或者，您应该使用 devtools （TAB：网络，filter： xhr ）查看JavaScript使用的URL来获取日期并使用请求获取它们。

import requests

# without header `Content-Type` it sends `HTML` instead of `JSON`
headers = {
#    'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64; rv:98.0) Gecko/20100101 Firefox/98.0',
#    'X-Requested-With': 'XMLHttpRequest',
#    'Referer': 'https://droughtmonitor.unl.edu/DmData/GISData.aspx/',
    'Content-Type': 'application/json; charset=utf-8',
}

url = 'https://droughtmonitor.unl.edu/DmData/GISData.aspx/ReturnDMWeeks'

response = requests.get(url, headers=headers)
#print(response.text)

data = response.json()

all_dates = data['d']
all_dates = [f"{d[:4]}-{d[4:6]}-{d[6:]}" for d in all_dates]

print(all_dates)

结果

['2022-06-21', '2022-06-14', '2022-06-07', ..., '2000-01-18', '2000-01-11', '2000-01-04']