Skip to main content
 首页 » 编程设计

python之循环通过 URL 为 pandas 创建 csv 和数据框

2024年11月24日7qlqwjy

我想从 URL 结构中显示的满足特定日期要求的 URL 中提取数据,并将该信息放入 csvs 以在本地使用。

http://web.mta.info/developers/data/nyct/turnstile/turnstile_190629.txt 

URL 末尾的 6 位数字系列是年月日指示符:190629

我正在收集 2016 年至 2019 年 (16-19) 从 3 月到 6 月 (03-06) 的数据。如果 URL 存在,创建一个 csv 并将它们全部组合成一个 csv 以提供给 pandas 数据框。

这行得通,但是速度太慢了,而且我知道这不是最符合 Python 风格的方法。

import requests 
import pandas as pd 
import itertools 
 
date_list = [['16', '17', '18', '19'],['03', '04', '05', '06'],['01', '02', '03', '03', '04', '05', '06' 
       ,'07', '08', '09','10', '11', '12','13','14' ,'15', '16', 
       '17','18','19','20','21','22','23','24','25','26','27' 
       ,'28','29','30','31']] 
date_combo = [] 
 
# - create year - month - date combos 
# - link: https://stackoverflow.com/questions/798854/all-combinations-of-a-list-of-lists 
 
for sub_list in itertools.product(*date_list): 
    date_combo.append(sub_list) 
 
url_lead = 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_' 
url_list = [] 
 
# - this checks the url is valid and adds them to a list 
for year, month, day in date_combo: 
    concat_url = url_lead + year + month + day + '.txt' 
    response = requests.get(concat_url) 
    if response.status_code == 200: 
# ---- creates a list of active urls 
        url_list.append(concat_url) 
# ---- this creates individual csvs ---- change path for saving locally 
# ---- filename is date 
        df = pd.read_csv(concat_url, header = 0, sep = ',') 
        df.to_csv(r'/Users/.../GitHub/' + year + month + day + '.csv') 
 
# - this creates a master df for all urls 
dfs = [pd.read_csv(url,header = 0, sep = ',') for url in url_list] 
df = pd.concat(dfs, ignore_index = True) 
df.to_csv(r'/Users/.../GitHub/seasonal_mta_data_01.csv') 
 

我的代码按预期运行,但如果有任何清理代码的建议,我将不胜感激!

请您参考如下方法:

我能想到的不多。以下是一些我会采取不同做法的做法:

# more consie construction of date_combo   
date_list = [range(16,20), range(3,7),range(1,32)] 
date_combo = [sub_list for sub_list in itertools.product(*date_list)] 
 
url_lead = 'http://web.mta.info/developers/data/nyct/turnstile/turnstile_' 
url_list = [] 
dfs = [] 
 
# - this checks the url is valid and adds them to a list 
for year, month, day in date_combo: 
    # year, month, day are integers 
    # so we use f string here 
    concat_url = f'{url_lead}{year}{month:02}{day:02}.txt' 
 
    response = requests.get(concat_url) 
    if response.status_code == 200: 
        url_list.append(concat_url) 
 
        # append to dfs and save csv 
        dfs.append(pd.read_csv(concat_url, header = 0, sep = ',')) 
        dfs[-1].to_csv(f'/Users/.../GitHub/{year}{month:02}{day:02}.csv) 
 
# we don't need to request the txt files again: 
df = pd.concat(dfs, ignore_index = True) 
df.to_csv(r'/Users/.../GitHub/seasonal_mta_data_01.csv')