<jupyter_start><jupyter_text>MEDC0106: Bioinformatics in Applied Biomedical Science    --------------------------------------------------------------- 06 - Introduction to Pandas*Written by:* <NAME>**This notebook provides a general introduction to Pandas.**Do not be afraid to make changes to the code cells to explore how things work! What is Pandas?**[Pandas](https://pandas.pydata.org/)** is a Python package for data analysis, providing functions for analysing, cleaning and manipulating data. Pandas is probably one of the most important tools for data scientists and is the backbone of most data science projects using Python.Pandas is built on top of NumPy, hence the Numpy structure is used a lot in the Pandas interface. Data manipulation often prefaces further analysis using other Python packages such as statistical analysis using [SciPy](https://www.scipy.org/), visualisation using tools such as [Matplotlib](https://matplotlib.org/) and machine learning using [scikit-learn](https://scikit-learn.org/stable/). These tools and others make up the Python scientific stack and are essential to learn for a career in informatics or data-science. To be effective in pandas it is essential to have a good grasp of the core concepts in Python (these concepts are outlined in the first session) along with some familiarity with NumPy. If you get lost with some concepts it might be a good idea to take a look through the previous material across the sessions.In this notebook we will learn the basics of Pandas. Pandas is a huge package and is deserving of an entire lecture series itself, so here we will learn tyhe fundamentals from which you will be able to build upon if you want to learn more.----- Contents1. [The Basics](The-Basics)2. [Creating DataFrames](Creating-DataFrames)3. [Reading Data](Reading-Data)4. [Essential Operations](Essential-Operations)5. [Slicing and Selecting](Slicing-and-Selecting)5. [Arithmetic](Arithmetic)6. [Applying Functions](Applying-Functions)7. [Time-Series](Time-Series)8. [Plotting](Plotting)----- Extra Resources:- [Pandas Getting Started Guide](https://pandas.pydata.org/pandas-docs/stable/getting_started/index.html)- [RealPython-01](https://realpython.com/pandas-python-explore-dataset/)- [RealPython-02](https://realpython.com/pandas-dataframe/)----- References:- [Pandas Documentation](https://pandas.pydata.org/docs/)- [Learn Data Science](https://www.learndatasci.com/tutorials/python-pandas-tutorial-complete-introduction-for-beginners/)----- The BasicsImporting Pandas is no different to any other package/module. Pandas users often use the `pd` alias to keep code clean:<jupyter_code>import pandas as pd

s = pd.Series([1.0, 2.0, 3.0, 5.0])
s<jupyter_output><empty_output><jupyter_text>Core ComponentsPandas has two core components, the `Series` and the `DataFrame`.The `Series` can be imagined as a single column in a data table, whereas the `DataFrame` can be imagined as a full data table made up of multiple `Series`. Both types have a similar interface allowing a user to perform similar operations. DataFrames are similar to spreadsheets that you may have interacted with in software such as Excel. DataFrames are often faster, easier to use and more powerful than spreadsheets.    [Image source](https://www.datasciencemadesimple.com/wp-content/uploads/2020/05/create-series-in-python-pandas-0.png?ezimgfmt=rs%3Adevice%2Frscb1-1)----- Creating DataFramesThere are numerous ways to create a DataFrame using the Pandas package. In most cases it is likely that you will want to read in data from a paticular file, however DataFrames can also be constructed from scratch from lists, tuples, NumPy arrays or Pandas series. Probably the most simple way however is from a simple Python dictionary `dict`. Suppose we wanted to construct a table like the one below:| PatientID | Gender | Age | Outcome  ||-----------|--------|-----|----------|| 556785    | M      | 19  | Negative || 998764    | F      | 38  | Positive || 477822    | M      | 54  | Positive || 678329    | M      | 22  | Negative || 675859    | F      | 41  | Negative |We can construct this using a Python dictionary where the key corresponds to the column name and the list the data present in the rows. For this we can use the default constructor `pd.DataFrame()`. Notice how there is also an unnamed column containing the numbers 0-4, this is the **index** of each row. In fact you may also specify a custom index when contructing a dataframe; (`pd.DataFrame(data, index=['Tom', 'Joanne', 'Joe', 'Xander', 'Selena'])`) In this case the index is the names of the patients.<jupyter_code># This is or dictionary containing the raw data
data = {
    'PatientID': [556785, 998764, 477822, 678329, 675859],
    'Gender': ['M', 'F', 'M', 'M', 'F'],
    'Age': [19, 38, 54, 22, 41],
    'Outcome': ['Negative', 'Poisitive', 'Positive', 'Negative', 'Negative']
}

# We can now construct a DataFrame like so:
df = pd.DataFrame(data)
df  # show the data<jupyter_output><empty_output><jupyter_text>Often you will be working with very large tables of data making it impractical to view the whole table. Pandas provides a method `.head()` to display the first few n items or `.tail()` for the last few:<jupyter_code># Display the first three rows
df.head(n=3)
# Display the last two rows
df.tail(n=2)<jupyter_output><empty_output><jupyter_text>Accessing an individual column is easy using the same syntax as a Python dictionary `dict`:<jupyter_code>gender_column = df['Gender']
gender_column<jupyter_output><empty_output><jupyter_text>If the column label is a string you may also use **dot-syntax** to access the column:<jupyter_code>age_column = df.Age
age_column<jupyter_output><empty_output><jupyter_text>Reading DataReading and writing data from/to files in multiple formats is an essential part of the data analysis pipeline. Pandas can read data from file including; CSV, JSON, Excel, SQL and [many more](https://pandas.pydata.org/pandas-docs/stable/reference/io.html).In the folder `data` we have provided a dataset downloaded from the [UK government](https://coronavirus.data.gov.uk/details/cases?areaType=overview&areaName=United%20Kingdom) detailing the number of reported positive COVID-19 test results in the United Kingdom by date reported (up to Oct-31-21). The file is in the CSV format and can be read using Pandas with the function `.read_csv()`:<jupyter_code>cv_data_path = './data/data_2021-Oct-31.csv'  # This is the path to our data

cv_data = pd.read_csv(cv_data_path)
cv_data.head(n=10)<jupyter_output><empty_output><jupyter_text>We could also easily write this DataFrame to a new CSV file using the method `df.to_csv()`:```pythoncv_data.to_csv('./data/coronavirus_testing_results.csv')```Give it a go. Maybe also saving to a different [format](https://pandas.pydata.org/pandas-docs/stable/reference/io.html)!  Essential OperationsNow that we have loaded some data into a `DataFrame` we can perform operations for performing analysis. Typically once you have loaded some data you should view your data to make sure that it looks correct and to get an idea of what values you will be dealing with. Since we have already coovered visualising the data using `.head()`/`.tail()`, the next function you should probbaly run is `.info()` which provide essential details about your dataset including the number of rows/columns  the number of none-null values (None), what type of data is in each column and how much memory the data is taking up:<jupyter_code>cv_data.info()<jupyter_output><empty_output><jupyter_text>Notice that we have 6 columns of which four are of type `object` (this could be something like a string) and two that are `int64` (integers) (these types correspond the the types used in NumPy). The info also tells us that we have 2466 non-null values and no null-values in this case. Knowing the datatype of ourt columns is very important as it will determine what operations we can perform on each column (we wouldnt want to calculate the mean of a column containing strings). Just like NumPy you can also use `.shape` to see the number of (rows/columns):<jupyter_code>cv_data.shape<jupyter_output><empty_output><jupyter_text>Removing duplicate dataOften input data is noisy and needs cleaning up before we do any further analysis. It is often the case that data contains duplicated rows which is not great when we are trying to do statitical analysis. Luckily Pandas has utilities for dealing with this problem easily. The data we have read does not contain any duplicated rows so we will arbritrarily create some by duplicating the data and adding it to itself:<jupyter_code>duplicated = cv_data.append(cv_data)  # here we have copied the data and added it to itself
duplicated.shape<jupyter_output><empty_output><jupyter_text>Notice that we have to assign the result of the `append` to a new variable. Here we have copied the data so we wont do anything to the original DataFrame. We can now easily drop the duplicates using the `.drop_duplicates()` [method](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop_duplicates.html). It is always a good idea to look at the [documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.drop_duplicates.html) to see what other arguments these functions accept.<jupyter_code>duplicated = duplicated.drop_duplicates()
duplicated.shape<jupyter_output><empty_output><jupyter_text>Notice that the shape is now the same as the original data. Also notice that again we assigned the result to a new variable (with the same name). This technique can get quite annoying so Pandas often offers an argument `inplace` which if we set to `True` allows pandas to perform the operation modifying the original data rather than a copy.```pythonduplicated.drop_duplicates(inplace=True)   no need to assign to a new variable```  Removing Null values (None)Data before cleaning commonly has missing values that you will need to deal with before further analysis. Missing values are represented by `None` or `np.nan`. There is usually two options to dealing with missing values:1. Remove all rows with missing data2. Imputing the missing valuesIn this tutorial we will stick to the first case.Again as our data is nice and clean it contains no null values so for this example we will inject a new column containing some null values, let's do this first:<jupyter_code>import numpy as np

n_rows = cv_data.shape[0]
# p is for weighting the choice here it is more likely to choose 1 than None
null_containing_data = np.random.choice([None, 1], n_rows, p=[0.2, 0.8])
null_containing_data<jupyter_output><empty_output><jupyter_text>Now add a row to the data containing our constructed data:<jupyter_code>cv_data['RandomData'] = null_containing_data  # make a colum called 'RandomData'
cv_data.head(10)<jupyter_output><empty_output><jupyter_text>We can also see now that we have null values:<jupyter_code>cv_data.info()<jupyter_output><empty_output><jupyter_text>We can also check the number of null values in each column using `.isnull()`. This returns a dataframe with boolean columns where `True` indicated a null value. We can then use `.sum()` to count the number of `True` values in each column:<jupyter_code>cv_data.isnull().sum()<jupyter_output><empty_output><jupyter_text>When performing data analysis you often have to make the choice to remove missing values or impute them in some way. Removing data is only really recommended if the number of missing data points is relatively small. To remove null values you can simply use the [method](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.dropna.html) `.dropna()`. This operation will remove any row with at least 1 null value, returning a new DataFrame unless you specified inplace. Instead of dropping rows we could instead drop columns with null values by changing the axis of operation. Columns are represented by `axis=1` (The axes are defined in the same way as NumPy!):<jupyter_code># First lets remove rows with null values
remove_rows = cv_data.dropna()
remove_rows.head(10)
remove_rows.shape<jupyter_output><empty_output><jupyter_text>Now lets change the axis and remove the colum we injected:<jupyter_code>cv_data.dropna(axis=1, inplace=True)  # We can do it inplace since we do not care about this column
cv_data.head(10)<jupyter_output><empty_output><jupyter_text>Understanding DataNow that your data is clean(er) than when we started, it is time to do some basic stats to understand the data that we have in each column. This may help inform us how to continue with our analysis and maybe how to plot the data. Pandas provides us with an easy way to get a quick summary of the distribution of our continuous variables `.describe()`:<jupyter_code>cv_data.describe()<jupyter_output><empty_output><jupyter_text>We can also do the same for categorical columns but we will have to do it seperately:<jupyter_code>cv_data.areaName.describe()<jupyter_output><empty_output><jupyter_text>This shows us that in this dataset there are four unique area names with 'England' being the most frequent with a frequency of 640. We can also check the unique values using the `.unique()` method:<jupyter_code>cv_data.areaName.unique()<jupyter_output><empty_output><jupyter_text>We can see that the dataset contains data for:- England- Northern Ireland- Scotland- WalesBut how many times are these values recorded? We can use the method `.value_counts()` to find out:<jupyter_code>cv_data.areaName.value_counts()<jupyter_output><empty_output><jupyter_text>Slicing and SelectingIn the previous section we saw how to produce summaries of the entire data which is useful however, sometimes we will want to perform analysis on certain subsets of data. We have already seen how to extract a column of data using square brackets and dot-syntax `df['col'] / df.col` and now we will dive deeper into the Pandas selction language. When selecting parts of a DataFrame we may be returning either a `DataFrame` or a `Series`, it is important to know which so that yopu use the correct syntax. Selecting by Column(s)Using the square-bracket syntax we mentioned previously will return a Pandas `Series`<jupyter_code>type(cv_data['areaName'])<jupyter_output><empty_output><jupyter_text>If you wish to access it as a dataframe you can supply the column name as a list:<jupyter_code>type(cv_data[['areaName']])<jupyter_output><empty_output><jupyter_text>Adding another column to our selection is a simple as adding another column name to the list. Obviosuly inh this case our code will return a `DataFrame`:<jupyter_code>selection = cv_data[['areaName', 'areaCode']]
selection.head(5)<jupyter_output><empty_output><jupyter_text>Selecting by rowsSelecting rows is a little trickier with two methods:- `.loc`:  locate by name- `.iloc`: locate by numerical indexConsidering that our data has a numerical index it makes sense for us to use `.iloc`. If our data has an index using strings `.loc` would be the correct solution if we want to select using the string. Of course `.iloc` will also work returning the data at the numerical position instead of the name.Both methods are similar to indexing lists or NumPy arrays:<jupyter_code>cv_data.loc[222]  # Return the row with index 222<jupyter_output><empty_output><jupyter_text>Since Pandas is backed by NumPy we can also use slices to select a range of data:<jupyter_code>cv_data.loc[222:226]<jupyter_output><empty_output><jupyter_text>Conditional SelectionsSelecting data by index can be useful, but if we do not know what dat the indexes correspond too this can be limiting. Perhaps we are only interested in the data from Wales, we can use conditional selections to make informed selections.Pandas like numpy can be indexed using a boolean array/Series/DataFrame generated using a conditional expression:<jupyter_code>ind = cv_data['areaName'] == 'Wales'  # A boolean Series
ind.tail(5)<jupyter_output><empty_output><jupyter_text>Using this boolean Series we can index the DataFrame!<jupyter_code>wales_data = cv_data[ind]
wales_data.head(5)<jupyter_output><empty_output><jupyter_text>We can simplify this quite nicely into a one line expression:<jupyter_code>wales_data = cv_data[cv_data['areaName'] == 'Wales']
wales_data.head(5)<jupyter_output><empty_output><jupyter_text>Of course we can apply this to numerical columns also:<jupyter_code># Select rows where reported positives is less than 100
cv_data[cv_data['newCasesByPublishDate'] < 100].head(5)<jupyter_output><empty_output><jupyter_text>Chaining conditional expressions allows us to create powerful selections. For this we can use the logical operators `|` and `&`. Remeber to put seperate conditions in brackets!<jupyter_code># Count dates in england with reported positive results > 10,000
cv_data[(cv_data['areaName'] == 'England') & (cv_data['newCasesByPublishDate'] > 10000)].shape<jupyter_output><empty_output><jupyter_text>ArithmeticBasic arithmentic operations can be applied in the same way as NumPy arrays, so we will quickly brush over it:<jupyter_code>cv_data.newCasesByPublishDate / 100   # divide a column by 100 return a Series<jupyter_output><empty_output><jupyter_text>You may also perform arithmetic between columns:<jupyter_code>cv_data.newCasesByPublishDate + cv_data.cumCasesByPublishDate<jupyter_output><empty_output><jupyter_text>You can insert a new column with the result:<jupyter_code>cv_data['Rubbish'] = cv_data.newCasesByPublishDate * 0.3 / cv_data.cumCasesByPublishDate
cv_data.head(5)<jupyter_output><empty_output><jupyter_text>Pandas also provides some handy utility functions:<jupyter_code>print(cv_data.newCasesByPublishDate.mean())
print(cv_data.newCasesByPublishDate.std())<jupyter_output><empty_output><jupyter_text>Applying FunctionsWhile it is possible to iterate over a DataFrame/Series like a NumPy array, it is slow in Python so instead we can use the `.apply()` function to apply a function to each element in a column or across columns. We can also save this result to a new column. Let's create an arbritary function that we can apply to the data we have:<jupyter_code>def categorize_cases(x):
    if x >= 10000:
        return 'High'
    elif x <= 200:
        return 'Low'
    else:
        return 'Medium'<jupyter_output><empty_output><jupyter_text>The above function categorises a case count into arbritarty categories: 'High', 'Medium' and 'Low'. Now we can apply this to the column 'newCasesByPublishDate':<jupyter_code>cv_data['Category'] = cv_data['newCasesByPublishDate'].apply(categorize_cases)
cv_data.head(10)<jupyter_output><empty_output><jupyter_text>Users often will use anonymous functions instead of defining an explicit function like above:<jupyter_code>cv_data['newCategory'] = cv_data['newCasesByPublishDate'].apply(lambda x: 'Red' if x >= 20000 else 'Amber')
cv_data.head(10)<jupyter_output><empty_output><jupyter_text>Time-SeriesSome of you may have notices that one of the columns contains dates as a string (object). This isnt paticularly useful to us in this form. Pandas however has a datetime type which we can use to make some more intelligent selections based on time spans. First we need to tell pandas that our column is a datetime column:<jupyter_code>cv_data['date'] = pd.to_datetime(cv_data['date'])
cv_data.info()<jupyter_output><empty_output><jupyter_text>Now we have the date in this form we can make selections within time ranges using the `.between()` method:<jupyter_code># Lets select data between the 20th and the 30th October 2021 and restrict it to England
selection = cv_data[(cv_data.date.between('2021-10-20','2021-10-30')) & (cv_data.areaName == 'England')]
selection.head(10)<jupyter_output><empty_output><jupyter_text>Working with time-series data is even more powerful if we use the time as our index. Lets first only consider 'Scotland' in our analysis<jupyter_code>scotland_data = pd.DataFrame(cv_data[cv_data.areaName == 'Scotland'])  # also copy into a new DataFrame<jupyter_output><empty_output><jupyter_text>Now we can set the index of the 'scotland_data' DataFrame as the index:<jupyter_code>scotland_data.set_index('date', inplace=True)
scotland_data.head(5)<jupyter_output><empty_output><jupyter_text>You may have noticed that the data is in time-decending order, often we we will want to reverse this ordering. Now that the index is the date we can sort it easilt using the `.sort_index()` method:<jupyter_code>scotland_data.sort_index(inplace=True)
scotland_data.head(5)<jupyter_output><empty_output><jupyter_text>Also we can simply use slicing to select a data range with `.loc`!<jupyter_code>scotland_data.loc['2021-10-20':'2021-10-30']<jupyter_output><empty_output><jupyter_text>We can resample time-series data into different intervals and get a mean value for that interval. Below we resmaple the data into 10-day intervals and calculate the mean of 'newCasesByPublishDate':<jupyter_code>scotland_data.resample(rule='10d')['newCasesByPublishDate'].mean()<jupyter_output><empty_output><jupyter_text>Instead of mean you could use other functions such as `min()`, `max()`, `sum()` etc. Indeed you can also calculate a rolling statistic using `.rolling()` and a window size. Here we will calculate a rolling average using a ten day window:<jupyter_code>scotland_data['rollingAvgTenDay'] = scotland_data.rolling(10)['newCasesByPublishDate'].mean()
scotland_data.head(20)<jupyter_output><empty_output><jupyter_text>PlottingPandas allows the visualisation of data in DataFrames/Series interfacing with the plotting package [matplotlib](https://matplotlib.org/). Displaying the plots will first require that import matplotlib:<jupyter_code># We also add this 'Jupyter magic' to display plots in the notebook.
%matplotlib inline

import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Now creating a plot with pandas is as simple as calling `.plot()` on some selected data!<jupyter_code>scotland_data.newCasesByPublishDate.plot();  # We also add the semicolon when plotting in Jupyter<jupyter_output><empty_output><jupyter_text>We could have also achieved the same result using the syntax:```pythonscotland_data.newCasesByPublishDate.plot.line()```These plotting functions also have many [arguments](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.plot.html) which you can specify to tune the look of your plots. Thes arguments are passed to the underlying matplotlib methods. We can also specify other types of plot. For example we could visualise the data as a box plot, do you notice anything unusual?:<jupyter_code># Select a time window (1-month)
window = scotland_data['2021-09-30':'2021-10-30']

window.newCasesByPublishDate.plot.box();<jupyter_output><empty_output><jupyter_text>How about we plot the raw data along with the 10 day rolling average:<jupyter_code>scotland_data.newCasesByPublishDate.plot(figsize=(12, 8))  # also specify the size!
scotland_data.rollingAvgTenDay.plot()
plt.legend();  # We can also add a legend using matplotlib!<jupyter_output><empty_output><jupyter_text>We can also save figures using `.savefig()`, check the data directory!<jupyter_code>figure = scotland_data.newCasesByPublishDate.plot(figsize=(12, 8)).get_figure()
figure.savefig('./data/Scotland_2021-Oct-31.png');<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08649462365591398, 'fraction_numerical': 0.013247311827956989, 'mean_word_length': 5.64694110920526, 'pattern_counts': {'":': 0, '<': 204, '<?xml version=': 0, '>': 206, 'https://': 18, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '50', 'dir': 'jupyter-structured-clean-dedup', 'id': '667674', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 7113, 'n_tokens_neox': 6699, 'n_words': 2697, 'path': 'workshop/session_2/06_introduction_to_pandas.ipynb', 'repo_name': 'MEDC0106/PythonWorkshop', 'size': '23256'}