import pandas as pd  # importing it as pd is a convention you'll see everywhere
# this renders figures inside the notebook
%matplotlib inline
import matplotlib
import numpy as np

# this makes the figures a bit nicer
import seaborn as sns
sns.set_context('notebook')

grades = pd.DataFrame(
    {
        'subject': ['Calculus 1', 'Digital design 1', 
                    'Physics 1i', 'System modeling', 'Basics of Programming 1', 'System theory',
                   'Introduction to the Theory of Computing 1', 'Introduction to the Theory of Computing 2'],
        'grade': [3, 4, 3, 2, 5, 1, 4, 1],
        'teacher': ['A. Smith', 'J. Doe', 'J. Smith', 'M. Jackson', 'T. Swift', 'A. Grande', 'J. Bieber', 'C. Cox'],
        'semester': [1, 1, 1, 2, 1, 3, 1, 2],
        'completion_date': [
            '2017-12-18',
            '2018-01-05',
            '2018-01-21',
            '2018-06-11',
            '2018-06-01',
            '2018-12-17',
            '2017-01-01',
            '2018-01-01',
        ]
    }
)
grades['completion_date'] = pd.to_datetime(grades['completion_date'])
grades

"""
Explanation: Pandas introduction
This notebook is a supplementary material for the Business intelligence laboratory (VIAUMB00) course at BME AUT. Course page in Hungarian and in English.
The goal of this notebook is to provide a short introduction to the Pandas data manipulation library.
The main problem sheet is available through Github Classroom.
You can find further information about Pandas:

A 10min introduction
Brandon Rhodes' tutorial from PyCon 2015
Kevin Markham's tutorial from PyCon 2018

What is pandas?
Pandas is a data analysis and manipulation library popular among data scientists and machine learning practitioners.
Its name is derived from panel data. Pandas is built to handle tabular data with mixed data types.
Pandas is integrated with Python's main numerical computation library, NumPy and with the main visualization library, matplotlib.
End of explanation
"""


grades.shape

"""
Explanation: .shape is a tuple of the number of rows and the number of columns:
End of explanation
"""


grades.head()

"""
Explanation: .head() returns the first 5 rows of a DataFrame, .tail() returns the last ones. These are very useful for manual data inspection. You should always check what the contents of your dataframes.
End of explanation
"""


grades.tail(2)

"""
Explanation: Printing other only the last two elements:
End of explanation
"""


id(grades), id(grades.tail(2))

"""
Explanation: Each of these operations return a new dataframe. We can confirm this via their object identity:
End of explanation
"""


grades.tail(2).copy()

"""
Explanation: But these objects are not copied unless we explicitly ask for a copy:
End of explanation
"""


grades['teacher']

"""
Explanation: Selecting rows, columns and cells
The first boldfaced column of the table is the index column. It's possible to use multiple columns as index (Multiindex).
Selecting columns
End of explanation
"""


grades.teacher

"""
Explanation: The name of the column is also exposed as an attribute as long as it adheres to the naming limitations of attributes (no spaces, starts with a letter, doesn't crash with a method name):
End of explanation
"""


type(grades.teacher)

grades.teacher.shape

"""
Explanation: The type of a column is pd.Series, which is the type for a vector:
End of explanation
"""


grades[['grade', 'teacher']]

"""
Explanation: We can select multiple columns with a list of column names instead of a column name. Note the double square brackets.
End of explanation
"""


print(type(grades[['grade']]))
grades[['grade']]

"""
Explanation: The return type of the operator [] depends on the type of the index. If it's a string, it returns a Series if it's a list, it returns a DataFrame:
End of explanation
"""


grades = grades.set_index('subject')
grades

"""
Explanation: Selecting rows
Rows can be selected

by their index or
by their integer location.

To demonstrate this, we will use the subject name as the index. Note that it's now in bold:
End of explanation
"""


grades.loc['Physics 1i']

"""
Explanation: Selecting by index
Note that you need to use [] not ():
End of explanation
"""


type(grades.loc['Physics 1i'])

"""
Explanation: The type of one row is a Series since it's a vector:
End of explanation
"""


grades.iloc[2]

"""
Explanation: Selecting by integer location
End of explanation
"""


grades.iloc[1:3]

"""
Explanation: We can use ranges here as well. Note that the range is upper-bound exclusive, in other words, .iloc[i:j] will not include element j:
End of explanation
"""


grades.iloc[:, [0, 2]]

grades.iloc[:, 1:-1]

grades.iloc[1:5, 1:2]

"""
Explanation: Selecting columns with iloc
End of explanation
"""


grades.loc['Physics 1i', 'grade']

"""
Explanation: Selecting a cell
There are multiple ways of selecting a single cell, this is perhaps the easiest one:
End of explanation
"""


grades['grade'] + 1

grades[['grade', 'semester']] + 1

"""
Explanation: Vectorized operations
Arithmetic operators are overloaded for DataFrames and Series allowing vectorized operations
End of explanation
"""


grades['semester'] == 1

"""
Explanation: Comparisions are overloaded as well:
End of explanation
"""


grades.index == 'System theory'

"""
Explanation: The index can be manipulated in a similar way but the return value is different:
End of explanation
"""


old_index = grades.index.copy()
grades.index = grades.index.str.upper()
grades

"""
Explanation: It is generally used to override the index:
End of explanation
"""


grades.index = old_index
grades

"""
Explanation: Changing it back:
End of explanation
"""


grades['teacher'].str

grades['teacher'].str.contains('Smith')

"""
Explanation: Vectorized string operations
String columns have a .str namespace with many string operations:
End of explanation
"""


grades['teacher'].str[:5]

"""
Explanation: It also provides access to the character array:
End of explanation
"""


def get_last_name(name):
    return name.split(" ")[1]


grades['teacher'].apply(get_last_name)

"""
Explanation: apply
.apply allows running arbitrary functions on each element of a Series (or a DataFrame):
End of explanation
"""


grades['teacher'].apply(lambda name: name.split(" ")[1])

"""
Explanation: The same with a lambda function:
End of explanation
"""


def format_grade_and_completion(row):
    grade = row['grade']
    completed = row['completion_date'].strftime("%b %Y")
    return f"grade: {grade}, completed: {completed}"
    
grades.apply(format_grade_and_completion, axis=1)

"""
Explanation: apply on rows
apply also works on full dataframes. The parameter is a row (axis=1) or a column in this case.
End of explanation
"""


grades['completion_date'].dt

grades['completion_date'].dt.day_name()

grades['completion_date'].dt.year

"""
Explanation: Vectorized date manipulation
Date columns can be manipulated through the dt namespace:
End of explanation
"""


grades.semester == 1

"""
Explanation: Filtering
Comparisons return a Series of True/False values
End of explanation
"""


grades[grades.semester == 1]

"""
Explanation: which can be used for filtering rows:
End of explanation
"""


grades[(grades.semester == 1) & (grades.teacher.str.contains('Smith'))]

"""
Explanation: We can also use multiple conditions. Note the parentheses:
End of explanation
"""


credits = pd.DataFrame(
    {
        'subject': ['Calculus 1', 'Physics 1i', 'Physics 2i'],
        'credit': [7, 5, 5]
    }
)
credits

"""
Explanation: Handling multiple dataframes, merge
Let's define a second Dataframe with the credit values of some classes:
End of explanation
"""


d = grades.merge(credits, left_index=True, right_on='subject', how='outer')
d

"""
Explanation: What are the credit values of the classes we have in the grades table?
End of explanation
"""


grades.merge(credits, left_index=True, right_on='subject', how='inner')

"""
Explanation: Merge
Merge has two operands, a left and a right DataFrame.
Parameters:
1. left_index: merge on the index of the left Dataframe
2. right_on: merge on one or more columns of the right Dataframe. This column is credits in this example.
3. how: inner/outer. Exclude/include all rows even if the key of the merge is unmatched.
We can merge on two types of columns, index and non-index. left_index=True and right_index=True means that we merge on the index. left_on and right_on means that we merge on a column.
End of explanation
"""


d = d.dropna()
d

"""
Explanation: We can discard rows with NaN values with dropna. Be careful. It discards all rows with any NaN.
This has the same effect as an inner join:
End of explanation
"""


print(type(grades.max()))
grades.max()

"""
Explanation: Finding min/max rows
max and min return the highest and lowest values for each column. The return value is a Series with the column names as indices and the maximum/minimum values as the Series values:
End of explanation
"""


# grades.idxmax()  # we get an error because of the string and the date column
grades[['grade', 'semester']].idxmax()

"""
Explanation: The location of the maximum/minimum is often more interesting. idxmax and idxmin return where the maximum is:
End of explanation
"""


grades.loc[grades[['grade', 'semester']].idxmax()]

"""
Explanation: The return value(s) of idxmax can directly be used with loc:
End of explanation
"""


grades.grade.idxmax()

"""
Explanation: idxmax works similarly for Series but the return value is a single scalar, the index of the maximum:
End of explanation
"""


g = credits.groupby('credit')

g.groups

"""
Explanation: groupby
Groupby allows grouping the rows of the Dataframe along any column(s):
End of explanation
"""


grades.groupby(['grade', 'semester']).groups

"""
Explanation: Or on multiple columns:
End of explanation
"""


grades.groupby(grades['semester'] % 2).groups

"""
Explanation: Or even on conditions:
End of explanation
"""


grades.groupby('semester').mean()

grades.groupby('semester').std()

"""
Explanation: We can perform operations on the groups:
End of explanation
"""


grades.groupby('semester').size()

"""
Explanation: size returns the number of elements in each group:
End of explanation
"""


grades.groupby(['grade', 'semester']).size().index

grades.groupby(['grade', 'semester']).size()

"""
Explanation: stack and unstack
Grouping on multiple columns and then aggregating results in a multiindex:
End of explanation
"""


grades.groupby(['grade', 'semester']).size().unstack()

"""
Explanation: unstack moves up the innermost index level to columns:
End of explanation
"""


credits

credits.stack()

"""
Explanation: stack does the opposite:
End of explanation
"""


grades.sort_index()

"""
Explanation: Sorting
We can sort Dataframes by their index:
End of explanation
"""


grades.sort_values(['grade', 'semester'])

"""
Explanation: Or by one or more columns:
End of explanation
"""


grades.sort_index(ascending=False)

"""
Explanation: In ascending order:
End of explanation
"""


grades['semester'].value_counts()

"""
Explanation: Miscellaneous operations
value_counts
value_counts returns the frequency of values in a column:
End of explanation
"""


grades.groupby(['semester', 'grade']).size()

"""
Explanation: It can't be used on multiple columns but groupby+size does the same:
End of explanation
"""


grades['semester'].hist()

"""
Explanation: We can also plot the histogram of the values with:
Histogram
End of explanation
"""


grades.plot(y='grade')

"""
Explanation: Visualization
Pandas is integrated with matplotlib, the main plotting module of Python.
End of explanation
"""


grades.plot(y='grade', kind='bar')

"""
Explanation: Or as a bar chart:
End of explanation
"""


grades.plot(x='semester', y='grade', kind='scatter')

"""
Explanation: We can also specify both axes:
End of explanation
"""


grades.groupby('semester').mean().plot(kind='bar')

"""
Explanation: Combining groupby and visualization.
Plotting the grade averages by semester:
End of explanation
"""


grades.groupby('semester').size().plot(kind='pie', title="Classes per semester", ylabel="Classes")

"""
Explanation: Or the number of classes per semester:
End of explanation
"""


