How to read CSV efficiently in Python?

user_16265960 0
Viewed 77

We are asked to grasp the data of COVID-19 in Hong Kong in 2020 from https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_daily_reports.

As you can see, there are 345 CSV files meeting the demands. However, we only need one row in every CSV file. It's very time-costing to read one CSV using pandas and locate the row "region" is "hong kong" and append to another data frame.

Any better solutions to this task?

#hrefs here is the list containing all the csv URL
df1 = pd.read_csv(hrefs[0])
df1 = df1[df1["Province/State"] == "Hong Kong"]
myL = []
for i in range(1,len(hrefs)):
    df2 = pd.read_csv(hrefs[i])
    if "Province/State" in df2.columns:
        #df2 = df2[df2["Province/State"] == "Hong Kong"]
        df2 = df2.loc[df2["Province/State"] == "Hong Kong"]
    else:
        #df2 = df2[df2["Province_State"] == "Hong Kong"]
        df2 = df2.loc[df2["Province_State"] == "Hong Kong"]
        df2.rename(columns={"Country_Region":"Country/Region","Province_State":"Province/State","Last_Update":"Last Update"}, inplace=True)
        df2 = df2[["Province/State","Country/Region","Last Update","Confirmed","Deaths","Recovered"]] 
    myL.append(df2)

df1.append(myL)
pandas dataframe
1 Answers

pip install httpx convtools first.

The first one for async requests, the second one for data processing (it generates code under the hood).

I hope this pandemic will leave us soon, hope the following is helpful.

import asyncio
import logging
from datetime import date, timedelta

import httpx

from convtools import conversion as c
from convtools.contrib.tables import Table


logging.basicConfig(level=logging.DEBUG)


async def fetch_urls(urls, max_connections):
    """Fetches urls asynchronously"""
    # TODO: implement local caching here
    results = []
    async with httpx.AsyncClient(
        limits=httpx.Limits(max_connections=max_connections),
        timeout=10,
    ) as client:
        for url in urls:
            results.append(client.get(url))

        for f in asyncio.as_completed(results):
            try:
                response = await f
            except Exception as e:
                logging.exception(e)
                continue
            yield response


async def filter_and_chain_csvs(responses, province, output_file):
    """Reads csv content from responses, renames columns, filters by province
    and puts together the resulting report"""
    table = None
    number = 0
    rename_columns = {
        "Province_State": "Province/State",
        "Country_Region": "Country/Region",
        "Case_Fatality_Ratio": "Case-Fatality_Ratio",
        "Lat": "Latitude",
        "Long_": "Longitude",
        "Incident_Rate": "Incidence_Rate",
        "Last_Update": "Last Update",
    }
    async for response in responses:
        table_ = Table.from_rows(
            list(
                Table.from_csv(response.iter_lines(), header=True)
                .rename(rename_columns)
                .update(source_url=str(response.url))
                .filter(c.col("Province/State") == province)
                .into_iter_rows(include_header=True)
            ),
            header=True,
        )

        if table is None:
            table = table_
        else:
            table = table.chain(table_)
        number += 1

    if table:
        table.into_csv(output_file)


async def fetch_info(
    start_date, end_date, province, output_file, max_connections=5
):
    urls = [
        (start_date + timedelta(days=i)).strftime(
            "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/%m-%d-%Y.csv"
        )
        for i in range((end_date - start_date).days + 1)
    ]
    streams = fetch_urls(urls, max_connections)
    await filter_and_chain_csvs(streams, province, output_file)


asyncio.run(
    fetch_info(
        date(2020, 1, 22),
        date(2021, 1, 15),
        "Hong Kong",
        "results.csv",
        max_connections=3,
    )
)
user_53877380
Related