<jupyter_start><jupyter_text>First, we import pandas and set two options we learnt about last week.<jupyter_code>import pandas as pd
pd.options.mode.chained_assignment = None
%matplotlib inline<jupyter_output><empty_output><jupyter_text>2. Pandas advanced  Documentation Check out the ```read_csv()``` documentation: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html There are many other functions to read different file formats as Pandas dataframes:- ```read_excel()``` https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html- ```read_sql()``` https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql.html- ```read_sas()``` https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sas.htmland many more. They are all working in a similar way. Just check out the Pandas documentation:https://pandas.pydata.org/pandas-docs/stable/reference/io.html  Bad lines Sometimes files you want to read are formatted in a bad way. This may prevent you from reading it (Pandas will show you errors). You may skip these lines by passing a ```error_bad_lines=False``` like that:  ```read_csv(filepath, sep=",", error_bad_lines=False)```  Separators  Not all files are separated by ```,```. Oftetimes tabs, semicolons or even whitespaces are used. Sometimes its crazy stuff like ```:::```.Just pass the separator as a string to the Pandas ```read_csv(filepath, sep=",")``` function. Here are some important special symbols you may want to use:- tab: ```"\t"```- newline: ```"\n"```So if you want to read a file that is tab delimited, pass ```read_csv(filepath, sep="\t")```.  Importing large files If you read data from your hard drive to a Pandas dataframe, your computer's memory (RAM) needs to be large enough to hold all of that data. Sometimes you may want to work with data files that are larger than your memory. There are several ways to deal with that issue.First, we can use the ```nrows=``` parameter to read only a certain number of lines from the top of the file we are loading.<jupyter_code>pd.read_csv("../data/times_university_data.csv", sep=",", nrows=2)<jupyter_output><empty_output><jupyter_text>We may also load only certain columns by passing a list with their names.<jupyter_code>pd.read_csv("../data/times_university_data.csv", sep=",", nrows=2, usecols=["world_rank", "university_name"])<jupyter_output><empty_output><jupyter_text>Lastly there is a way to read files in small chunks by passing the ```chunksize=``` parameter and the number of lines each chunk should contain, for example ```read_csv(filepath, sep=",", chunksize=1000)```. However this will not directly return a single dataframe, but an object ```pandas.io.parsers.TextFileReader``` that is ready to read you file chunk by chunk.<jupyter_code>chunks = pd.read_csv("../data/times_university_data.csv", sep=",", chunksize=1000)
chunks<jupyter_output><empty_output><jupyter_text>We can either call ```.get_chunk()``` on this reader to retrieve chunk after chunk...<jupyter_code>chunks.get_chunk()<jupyter_output><empty_output><jupyter_text>... or, we use a for loop to read the file chunk by chunk.<jupyter_code>for chunk in chunks:
    print(chunk[["world_rank", "university_name"]].head(1))
    print("############")
for index, chunk in enumerate(chunks):
    one_obs = chunk.sample(1)
    if index == 0:
        collecter = one_obs
    else:
        collecter = pd.concat([collecter, one_obs])<jupyter_output><empty_output><jupyter_text>Format columns Last week we had this issue that some columns in our university ranking data were not properly formatted. The columns "international", "income", "total_score", and "num_students" should be numbers (float or integer), but they are objects (text).<jupyter_code>df = pd.read_csv("../data/times_university_data.csv", sep=",")
print(df.dtypes)
df.head(2)<jupyter_output>world_rank                 object
university_name            object
country                    object
teaching                  float64
international              object
research                  float64
citations                 float64
income                     object
total_score                object
num_students               object
student_staff_ratio       float64
international_students     object
female_male_ratio          object
year                        int64
dtype: object<jupyter_text>Because of that we cannot do basic mathematical operations on these columns because these cannot handle the text values that cannot be casted to float. Python will show us an error like this:```TypeError: unsupported operand type(s) for /: 'str' and 'float'``` It will work on a column with float datatype.<jupyter_code>df["teaching"] / 10<jupyter_output><empty_output><jupyter_text>But not on one with datatype "object".<jupyter_code>df["international"] / 10.0<jupyter_output><empty_output><jupyter_text>Python will raise the same "TypeError" when we do something like this.<jupyter_code>"ten" / 2.0<jupyter_output><empty_output><jupyter_text>Let's use Pandas ```to_numeric()``` function to transfer the column to numeric. This time the error message is more helpful, because it tells us that it cannot parse string ```-``` at postion (line) 16.<jupyter_code>pd.to_numeric(df["international"])<jupyter_output><empty_output><jupyter_text>Let's have a look at that line with position 16 by using the ```.loc``` method on our dataframe and passing the location we are looking for as an index in squared brackets ```[16]```.<jupyter_code>df.loc[16]<jupyter_output><empty_output><jupyter_text>Maybe there are more ```-``` in the "international" column (Python stops when the first error occurs which was at position 16 and will only show this first error).<jupyter_code>df[df["international"] == "-"]
df.head(2)<jupyter_output><empty_output><jupyter_text>Now we have several options to get rid of these ```-``` which the creators of the dataset have used instead of inserting proper "NaN" (nulls) for missing values.First, we may use Pandas ```to_numeric()``` function and pass the ```errors="coerce"``` parameter to tell it to return "NaN" values for data that cannot be transfered to numeric.<jupyter_code>pd.to_numeric(df["international"], errors="coerce")<jupyter_output><empty_output><jupyter_text>Let's overwrite the original column with a numeric copy. After this step, the datatype of the "international" column should have changed to float.<jupyter_code>df["international"] = pd.to_numeric(df["international"], errors="coerce")
df.dtypes<jupyter_output><empty_output><jupyter_text>Now mathematical operations are possible.<jupyter_code>df["international"] / 10.0<jupyter_output><empty_output><jupyter_text>Use of the ```apply()``` method The ```apply()``` method can be used to apply any kind of function while iterating through the observations in a dataframe.Let's say we want to replace the ```-``` values in the "income" column with a random number based on the distribution of the "income" values in our dataset.Let's first have a look at the income distribution of our dataset. For that we (again) use ```pd.to_numeric(errors="coerce")``` to transfer the values in the "income" column to numeric while replacing values that cannot be transferred to NaNs.<jupyter_code>income_distribution = pd.to_numeric(df["income"], errors="coerce")
income_distribution.plot(kind="hist")<jupyter_output><empty_output><jupyter_text>The mean is 48.97 and the standard deviation is 21.7 as we can see using the ```describe()``` method we learned about last week.<jupyter_code>income_distribution.describe()<jupyter_output><empty_output><jupyter_text>We can use these parameters to define a normal distribution just for demonstration purposes. For that we use the numpy Python package which includes a lot of scientific computing stuff.  Let's import numpy and use the ```random.normal()``` function to generate 100 random numbers using a normal distribution defined by the mean and standard deviation from the observed income distribution of our dataset.<jupyter_code>import numpy as np

random_values = np.random.normal(income_distribution.mean(), income_distribution.std(), size=100)
random_values<jupyter_output><empty_output><jupyter_text>We can also plot these numbers by transferring them to a Pandas Series.<jupyter_code>pd.Series(random_values).plot(kind="hist", bins=25)<jupyter_output><empty_output><jupyter_text>To draw a single observation from the distribution, we pass ```size=1``` to the function and use indexing ```[0]``` to get the first element from the returned array (remember, Python starts counting indexes at 0 and not at 1).<jupyter_code>np.random.normal(income_distribution.mean(), income_distribution.std(), size=1)[0]
np.random.normal(income_distribution.mean(), income_distribution.std())<jupyter_output><empty_output><jupyter_text>Okay, now let us use the ```apply()``` function on the "income" column. The ```lambda x:``` tells Python to create a temporary variable x for each observation in the "income" column. Now we can draw a single number from our normal distribution and relace x if x equals ```-```, otherwise we just return x (the temporary variable which holds the original value of the current observation from "income").<jupyter_code>mean = income_distribution.mean()
std = income_distribution.std()

print(df.loc[4])
df["income"] = df["income"].apply(lambda x: np.random.normal(mean, std, size=1)[0] if x == "-" else x)
print("----------------------------------------")
print(df.loc[4])<jupyter_output>world_rank                                       5
university_name               Princeton University
country                   United States of America
teaching                                      90.9
international                                 70.3
research                                      95.4
citations                                     99.9
income                                   41.353039
total_score                                   94.2
num_students                                 7,929
student_staff_ratio                            8.4
international_students                         27%
female_male_ratio                          45 : 55
year                                          2011
Name: 4, dtype: object
----------------------------------------
world_rank                                       5
university_name               Princeton University
country                   United States of America
teaching                                      90.9
international     [...]<jupyter_text>After this operation, "income" will still be an object column.<jupyter_code>df.dtypes<jupyter_output><empty_output><jupyter_text>But now all values contained in "income" should be transferable to numeric without any problems.<jupyter_code>df["income"] = pd.to_numeric(df["income"])
df.dtypes
df["country"].replace({"United States of America": "USA", "United Kingdom": "UK", "Germany": "Deutschland"})<jupyter_output><empty_output><jupyter_text>replace() Another option is to use the ```replace()``` method, if you know exactly what values you want to replace with another specific value.<jupyter_code>df["country_renamed"] = df["country"].replace({"United States of America": "USA", "United Kingdom": "UK", "Germany": "Deutschland"})
df.head(2)<jupyter_output><empty_output><jupyter_text>3. Seaborn We will now use the Python package Seaborn, which is based on the very popular matplotlib libary. Seaborn provides a high-level interface for drawing attractive and informative statistical graphics. Check out the seaborn gallery for some awesome examples:<jupyter_code>from IPython.display import IFrame

display(IFrame("https://seaborn.pydata.org/examples/index.html", width=800, height=400))<jupyter_output><empty_output><jupyter_text>First install seaborn using pip.```pip install seaborn``` Import seaborn<jupyter_code>import seaborn as sns<jupyter_output><empty_output><jupyter_text>It is very easy to use seaborn functions to plot something because we can just pass our dataframe:<jupyter_code>sns.scatterplot(x="income", y="student_staff_ratio", data=df)<jupyter_output><empty_output><jupyter_text>There is also another way to create seaborn (and matplot) plots that give us more control over the indiviual elements of the plot. First we create an empty figure with one or more subplots. For that we need the matplot libary which runs "under the hood" of seaborn.<jupyter_code>import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Now we can create emtpy plots.<jupyter_code>plt.subplots(1, figsize=(5, 5))<jupyter_output><empty_output><jupyter_text>One row with two subplots.<jupyter_code>plt.subplots(1, 2, figsize=(10, 5))<jupyter_output><empty_output><jupyter_text>Two rows with two sublots.<jupyter_code>plt.subplots(2, 2, figsize=(5, 5))<jupyter_output><empty_output><jupyter_text>The ```subplot()``` function returns two objects (one "figure" object and a number of "axis" objects). Let's assign these to two variables.<jupyter_code>fig, axes = plt.subplots(1, 2, figsize=(10, 5))<jupyter_output><empty_output><jupyter_text>We can now fill these empty subplots with seaborn plots by passing the axes object togehter with an index (remember Python starts counting with 0, so when we want to reference the first axis, we pass ```[0]```).<jupyter_code>sns.scatterplot(x="income", y="student_staff_ratio", data=df, ax=axes[0])
fig<jupyter_output><empty_output><jupyter_text>Let's fill the second subplot with a histogram of the "year" column. The seaborn ```distplot()``` takes as input the column, we want to plot.<jupyter_code>sns.distplot(df["year"], ax=axes[1])
fig<jupyter_output><empty_output><jupyter_text>Let's get rid of that kernel density estimate and tell the function to use a number of bins that correspond to the number of years present in our dataset. Check out the documentation for more options: https://seaborn.pydata.org/generated/seaborn.distplot.htmlseaborn.distplot<jupyter_code>df["year"].nunique()
df["year"].unique()<jupyter_output><empty_output><jupyter_text>When you overwrite a subplot, make sure to ```clear()``` the axis and the contained plot you previously put there (otherwise the new content will overlap the old content).<jupyter_code>axes[1].clear()
sns.distplot(df["year"], ax=axes[1], kde=False, bins=df["year"].nunique())
fig<jupyter_output><empty_output><jupyter_text>We can also control the elements of the subplots.<jupyter_code>axes[0].set_xlabel("Income of university")
axes[0].set_ylabel("Student to staff ratio")
axes[0].set_xlim(0,100)
axes[0].set_ylim(0,100)
axes[0].set_title("Scatterplot")
fig
axes[1].set_xlabel("Year")
axes[1].set_ylabel("Frequency")
axes[1].set_ylim(0,1000)
axes[1].set_title("Year distribution")
fig<jupyter_output><empty_output><jupyter_text>Usually you want to do put all the plot related code into a single cell. But first let's select and prepare some data.<jupyter_code># get ivy league universities
ivy = df[df["university_name"].isin(["Harvard University", "Stanford University", "Massachusetts Institute of Technology"])]
# world rank to numeric
ivy["world_rank"] = pd.to_numeric(ivy["world_rank"])
# change uni names
ivy["university_name"] = ivy["university_name"].replace({"Harvard University": "Harvard", "Stanford University": "Stanford", "Massachusetts Institute of Technology": "MIT"})
# clean and cast to numeric international_student and num_students
ivy["international_students"] = pd.to_numeric(ivy["international_students"].apply(lambda x: x.replace("%", "")))
ivy["num_students"] = pd.to_numeric(ivy["num_students"].apply(lambda x: x.replace(",", "")))<jupyter_output><empty_output><jupyter_text>First we set some general styling options.Also check out the seaborn color palettes: https://seaborn.pydata.org/tutorial/color_palettes.html<jupyter_code>sns.set(font_scale=1.2)
sns.set_style("ticks")
fig, axes = plt.subplots(2,2, figsize=(10,10))

# Ranking plot axis [0,0] first row, first column
ranking = sns.lineplot(x="year", y="world_rank", hue="university_name", style="university_name", data=ivy, markers=True, palette="Paired", ax=axes[0,0])

ranking.set_ylim(0,10)
ranking.set_yticks([1,2,3,4,5,6,7,8,9,10])
ranking.set_ylabel("Ranking")
ranking.invert_yaxis()
ranking.set_xlabel("Year")
ranking.legend(loc=4).texts[0].set_text("University")

# Income plot axis [0,1] first row, second column
income = sns.barplot(x="year", y="income", hue="university_name", data=ivy, palette="Paired", ax=axes[0,1])
income.set_xlabel("Year")
income.set_ylabel("Income")
income.legend(loc=4).set_title("University")

# Citations plot axis [1,0] second row, first column
citations = sns.stripplot(x="university_name", y="citations", hue="year", palette="Paired", data=ivy, size=10, ax=axes[1,0])
citations.set_ylabel("Citations")
citations.set_xlabel("")
citations.set_ylim(95.0,100)
citations.legend(loc=4).set_title("Year")
citations.grid(linestyle="dotted")

# Remove last subplot [1,1] second row, second column
fig.delaxes(axes[1,1])

# Final adjustments
fig.tight_layout() #tidy up the figure 
plt.savefig("../misc/my_nice_plot.png", dpi=150) #save the figure<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11931420573827851, 'fraction_numerical': 0.012946116165150455, 'mean_word_length': 4.969021928297947, 'pattern_counts': {'":': 9, '<': 159, '<?xml version=': 0, '>': 159, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '40', 'dir': 'jupyter-structured-clean-dedup', 'id': '148590', 'license': 'MIT', 'n_tokens_mistral': 5421, 'n_tokens_neox': 5096, 'n_words': 1618, 'path': 'notebooks/Day_02.ipynb', 'repo_name': 'datawizard1337/WebDataScience', 'size': '17148'}