<jupyter_start><jupyter_text>Reading a file from the web with pandas  Overview:- **Teaching:** 10 min- **Exercises:** 5 min**Questions*** How can I read data from the web into pandas?**Objectives*** Use pandas to download and read in a CSV file for the web. Increasingly data is being made openly available on the internet.  All research data that supports published work should where possible be published and the UK government has adopted an open data policy to increase transparency e.g centrally via https://data.gov.uk/ and the Office of National Statistics: https://www.ons.gov.uk/.For this lesson we will focus on data related to COVID-19/Cornoavirus which are available at https://coronavirus.data.gov.uk/details/download. We have used this to generate a link to the [latest daily and cumulated recorded cases and deaths](https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&metric=cumCasesBySpecimenDate&metric=cumDeaths28DaysByDeathDate&metric=newCasesBySpecimenDate&metric=newDeaths28DaysByDeathDate&format=csv) in csv format.  You can explore the page to extract different data and generate your own link.  We will read this directly into pandas so that whenever we run our analysis we will always be working with the latest data. First we will assign the url to a variable:<jupyter_code>data_url='https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&metric=cumCasesBySpecimenDate&metric=cumDeaths28DaysByDeathDate&metric=newCasesBySpecimenDate&metric=newDeaths28DaysByDeathDate&format=csv'<jupyter_output><empty_output><jupyter_text>Because this link will generate a csv file, we can use the pandas function `read_csv()` just as before, to read the file and convert it to a `DataFrame`.<jupyter_code>import pandas as pd

covid_data=pd.read_csv(data_url)
covid_data<jupyter_output><empty_output><jupyter_text>We can see that by default it's done a good job of parsing the file, as the open data is designed to be as usable as possible.  In selecting a subset of this data it would be useful to treat the date as a `datetime` value rather than a string and possibly use this as the index of the dataset.  First let's first check what `datatype ` have been assigned to each of the columns:<jupyter_code>dtypes = covid_data.dtypes
print(dtypes)
print(type(dtypes))<jupyter_output>date                           object
areaType                       object
areaCode                       object
areaName                       object
cumCasesBySpecimenDate        float64
cumDeaths28DaysByDeathDate    float64
newCasesBySpecimenDate        float64
newDeaths28DaysByDeathDate    float64
dtype: object
<class 'pandas.core.series.Series'><jupyter_text>This tells is that the date is of type of `object` which is how pandas treats strings.  We can use an inbuilt pandas function to convert this one column to a `datetime` format and reassign it to the date `column`.  In order to reduce the amount of data printed to the screen we can also use `dataframe_name.head()` to print just the first few lines of a dataframe.<jupyter_code>covid_data['date'] = pd.to_datetime(covid_data['date'])
covid_data.head()<jupyter_output><empty_output><jupyter_text>We don't see any difference in the output but can verify that the datatype has been converted by checking the types again:<jupyter_code>dtypes = covid_data.dtypes
print(dtypes)
print(type(dtypes))<jupyter_output>date                          datetime64[ns]
areaType                              object
areaCode                              object
areaName                              object
cumCasesBySpecimenDate               float64
cumDeaths28DaysByDeathDate           float64
newCasesBySpecimenDate               float64
newDeaths28DaysByDeathDate           float64
dtype: object
<class 'pandas.core.series.Series'><jupyter_text>Let's verify that we can select a range of dates.  We need to import the `datetime` library explicitly and assign start and end dates, for which we will choose 1st October 2020 and 1st November respectively.<jupyter_code>import datetime
start_date=datetime.datetime(year=2020,month=10,day=1)
end_date=datetime.datetime(year=2020,month=10,day=31)<jupyter_output><empty_output><jupyter_text>We can now use these to select a subset of the original dataframe for the month of October.  To do this we need to identify all data where the date is greater than or equal to the start date AND less than the end date.  As we saw in the first episode of the lesson we can do this by generating a boolean mask. First to select data after the start date<jupyter_code>covid_data['date'] >= start_date<jupyter_output><empty_output><jupyter_text>second before the end date:<jupyter_code>covid_data['date'] <= end_date<jupyter_output><empty_output><jupyter_text>But we need both of these to be true so we combine them with the boolean and operator:<jupyter_code>(covid_data['date'] >= start_date) & (covid_data['date'] <= end_date)<jupyter_output><empty_output><jupyter_text>While it is useful to know about this way of combining masks there is also a useful helper function in pandas `between` that can handle the logic for us:<jupyter_code>covid_data['date'].between(start_date,end_date)<jupyter_output><empty_output><jupyter_text>Now we can use this mask to select the data that we want to plot or analyse further:<jupyter_code>covid_oct = covid_data[covid_data['date'].between(start_date,end_date)]
covid_oct.head()<jupyter_output><empty_output><jupyter_text>Let's now plot the number of daily cases against the date:<jupyter_code>import matplotlib.pyplot as plt

covid_oct.plot(x='date', y='newCasesBySpecimenDate', label='Total UK')
plt.xlabel('Date')
plt.ylabel('Daily Cases')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07, 'fraction_numerical': 0.01087719298245614, 'mean_word_length': 4.823289070480082, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 51, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '52882', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 1736, 'n_tokens_neox': 1627, 'n_words': 606, 'path': 'nbplain/04_pandas_web.ipynb', 'repo_name': 'james-grant1/data-plotting-pandas', 'size': '5700'}