<jupyter_start><jupyter_text>Introduction Since I've been working on a lot of Kaggle competitions, I use Pandas quite a bit. As you may know, Pandas (in addition to Numpy) is the go-to Python library for all your data science needs. It helps with dealing with input data in CSV formats and with transforming your data into a form where it can be inputted into ML models. However, getting comfortable with the ideas of dataframes, slicing, etc was very tough for me in the beginning. Hopefully, this short tutorial can show you a lot of different commands that will help you gain the most insights into your dataset.<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>Loading in Data The first step in any ML problem is identifying what format your data is in, and then loading it into whatever framework you're using. For Kaggle compeitions, a lot of data can be found in CSV files, so that's the example we're going to use.  Since I'm a huge sports fan, we're going to be looking at a sports dataset that shows the results from NCAA basketball games from 1985 to 2016. This dataset is in a CSV file, and the function we're going to use to read in the file is called **pd.read_csv()**. This function returns a **dataframe** variable. The dataframe is the golden jewel data structure for Pandas. It is defined as "a two-dimensional size-mutable, potentially heterogeneous tabular data structure with labeled axes (rows and columns)". Just think of it as a table for now.<jupyter_code>df = pd.read_csv('RegularSeasonCompactResults.csv')<jupyter_output><empty_output><jupyter_text>The Basics Now that we have our dataframe in our variable df, let's look at what it contains. We can use the function **head()** to see the first couple rows of the dataframe (or the function **tail()** to see the last few rows).<jupyter_code>df.head()
df.tail()<jupyter_output><empty_output><jupyter_text>We can see the dimensions of the dataframe using the the **shape** attribute<jupyter_code>df.shape<jupyter_output><empty_output><jupyter_text>We can also extract all the column names as a list, by using the **columns** attribute and can extract the rows with the **index** attribute<jupyter_code>df.columns.tolist()<jupyter_output><empty_output><jupyter_text>In order to get a better idea of the type of data that we are dealing with, we can call the **describe()** function to see statistics like mean, min, etc about each column of the dataset.<jupyter_code>df.describe()<jupyter_output><empty_output><jupyter_text>Okay, so now let's looking at information that we want to extract from the dataframe. Let's say I wanted to know the max value of a certain column. The function **max()** will show you the maximum values of all columns<jupyter_code>df.max()<jupyter_output><empty_output><jupyter_text>Then, if you'd like to specifically get the max value for a particular column, you pass in the name of the column using the bracket indexing operator<jupyter_code>df['Wscore'].max()<jupyter_output><empty_output><jupyter_text>If you'd like to find the mean of the Losing teams' score.<jupyter_code>df['Lscore'].mean()<jupyter_output><empty_output><jupyter_text>But what if that's not enough? Let's say we want to actually see the game(row) where this max score happened. We can call the **argmax()** function to identify the row index<jupyter_code>df['Wscore'].argmax()<jupyter_output><empty_output><jupyter_text>One of the most useful functions that you can call on certain columns in a dataframe is the **value_counts()** function. It shows how many times each item appears in the column. This particular command shows the number of games in each season<jupyter_code>df['Season'].value_counts()<jupyter_output><empty_output><jupyter_text>Acessing Values Then, in order to get attributes about the game, we need to use the **iloc[]** function. Iloc is definitely one of the more important functions. The main idea is that you want to use it whenever you have the integer index of a certain row that you want to access. As per Pandas documentation, iloc is an "integer-location based indexing for selection by position."<jupyter_code>df.iloc[[df['Wscore'].argmax()]]<jupyter_output><empty_output><jupyter_text>Let's take this a step further. Let's say you want to know the game with the highest scoring winning team (this is what we just calculated), but you then want to know how many points the losing team scored.<jupyter_code>df.iloc[[df['Wscore'].argmax()]]['Lscore']<jupyter_output><empty_output><jupyter_text>When you see data displayed in the above format, you're dealing with a Pandas **Series** object, not a dataframe object.<jupyter_code>type(df.iloc[[df['Wscore'].argmax()]]['Lscore'])
type(df.iloc[[df['Wscore'].argmax()]])<jupyter_output><empty_output><jupyter_text>The following is a summary of the 3 data structures in Pandas (Haven't ever really used Panels yet)![](DataStructures.png) When you want to access values in a Series, you'll want to just treat the Series like a Python dictionary, so you'd access the value according to its key (which is normally an integer index)<jupyter_code>df.iloc[[df['Wscore'].argmax()]]['Lscore'][24970]<jupyter_output><empty_output><jupyter_text>The other really important function in Pandas is the **loc** function. Contrary to iloc, which is an integer based indexing, loc is a "Purely label-location based indexer for selection by label". Since all the games are ordered from 0 to 145288, iloc and loc are going to be pretty interchangable in this type of dataset<jupyter_code>df.iloc[:3]
df.loc[:3]<jupyter_output><empty_output><jupyter_text>Notice the slight difference in that iloc is exclusive of the second number, while loc is inclusive.  Below is an example of how you can use loc to acheive the same task as we did previously with iloc<jupyter_code>df.loc[df['Wscore'].argmax(), 'Lscore']<jupyter_output><empty_output><jupyter_text>A faster version uses the **at()** function. At() is really useful wheneever you know the row label and the column label of the particular value that you want to get.<jupyter_code>df.at[df['Wscore'].argmax(), 'Lscore']<jupyter_output><empty_output><jupyter_text>If you'd like to see more discussion on how loc and iloc are different, check out this great Stack Overflow post: http://stackoverflow.com/questions/31593201/pandas-iloc-vs-ix-vs-loc-explanation. Just remember that **iloc looks at position** and **loc looks at labels**. Loc becomes very important when your row labels aren't integers.   Sorting Let's say that we want to sort the dataframe in increasing order for the scores of the losing team<jupyter_code>df.sort_values('Lscore').head()
df.groupby('Lscore')<jupyter_output><empty_output><jupyter_text>Filtering Rows Conditionally Now, let's say we want to find all of the rows that satisy a particular condition. For example, I want to find all of the games where the winning team scored more than 150 points. The idea behind this command is you want to access the column 'Wscore' of the dataframe df (df['Wscore']), find which entries are above 150 (df['Wscore'] > 150), and then returns only those specific rows in a dataframe format (df[df['Wscore'] > 150]).<jupyter_code>df[df['Wscore'] > 150]<jupyter_output><empty_output><jupyter_text>This also works if you have multiple conditions. Let's say we want to find out when the winning team scores more than 150 points and when the losing team scores below 100.<jupyter_code>df[(df['Wscore'] > 150) & (df['Lscore'] < 100)]<jupyter_output><empty_output><jupyter_text>Grouping Another important function in Pandas is **groupby()**. This is a function that allows you to group entries by certain attributes (e.g Grouping entries by Wteam number) and then perform operations on them. The following function groups all the entries (games) with the same Wteam number and finds the mean for each group.<jupyter_code>df.groupby('Wteam')['Wscore'].mean().head()<jupyter_output><empty_output><jupyter_text>This next command groups all the games with the same Wteam number and finds where how many times that specific team won at home, on the road, or at a neutral site<jupyter_code>df.groupby('Wteam')['Wloc'].value_counts().head(9)<jupyter_output><empty_output><jupyter_text>Each dataframe has a **values** attribute which is useful because it basically displays your dataframe in a numpy array style format<jupyter_code>df.values<jupyter_output><empty_output><jupyter_text>Now, you can simply just access elements like you would in an array.<jupyter_code>df.values[0][0]<jupyter_output><empty_output><jupyter_text>Dataframe Iteration In order to iterate through dataframes, we can use the **iterrows()** function. Below is an example of what the first two rows look like. Each row in iterrows is a Series object<jupyter_code>for index, row in df.iterrows():
    print row
    if index == 1:
        break<jupyter_output>Season    1985
Daynum      20
Wteam     1228
Wscore      81
Lteam     1328
Lscore      64
Wloc         N
Numot        0
Name: 0, dtype: object
Season    1985
Daynum      25
Wteam     1106
Wscore      77
Lteam     1354
Lscore      70
Wloc         H
Numot        0
Name: 1, dtype: object<jupyter_text>Extracting Rows and Columns The bracket indexing operator is one way to extract certain columns from a dataframe.<jupyter_code>df[['Wscore', 'Lscore']].head()<jupyter_output><empty_output><jupyter_text>Notice that you can acheive the same result by using the loc function. Loc is a veryyyy versatile function that can help you in a lot of accessing and extracting tasks.<jupyter_code>df.loc[:, ['Wscore', 'Lscore']].head()<jupyter_output><empty_output><jupyter_text>Note the difference is the return types when you use brackets and when you use double brackets.<jupyter_code>type(df['Wscore'])
type(df[['Wscore']])<jupyter_output><empty_output><jupyter_text>You've seen before that you can access columns through df['col name']. You can access rows by using slicing operations.<jupyter_code>df[0:3]<jupyter_output><empty_output><jupyter_text>Here's an equivalent using iloc<jupyter_code>df.iloc[0:3,:]<jupyter_output><empty_output><jupyter_text>Data Cleaning One of the big jobs of doing well in Kaggle competitions is that of data cleaning. A lot of times, the CSV file you're given (especially like in the Titanic dataset), you'll have a lot of missing values in the dataset, which you have to identify. The following **isnull** function will figure out if there are any missing values in the dataframe, and will then sum up the total for each column. In this case, we have a pretty clean dataset.<jupyter_code>df.isnull().sum()<jupyter_output><empty_output><jupyter_text>If you do end up having missing values in your datasets, be sure to get familiar with these two functions. * **dropna()** - This function allows you to drop all(or some) of the rows that have missing values. * **fillna()** - This function allows you replace the rows that have missing values with the value that you pass in.  Visualizing Data An interesting way of displaying Dataframes is through matplotlib.<jupyter_code>import matplotlib.pyplot as plt
%matplotlib inline
ax = df['Wscore'].plot.hist(bins=20)
ax.set_xlabel('Points for Winning Team')<jupyter_output><empty_output><jupyter_text>Creating Kaggle Submission CSVs This isn't directly Pandas related, but I assume that most people who use Pandas probably do a lot of Kaggle competitions as well. As you probably know, Kaggle competitions require you to create a CSV of your predictions. Here's some starter code that can help you create that csv file<jupyter_code>import numpy as np
import csv

results = [[0,10],[1,15],[2,20]]
results = pd.np.array(results)
print results
firstRow = [['id', 'pred']]
with open("result.csv", "wb") as f:
    writer = csv.writer(f)
    writer.writerows(firstRow)
    writer.writerows(results)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08396496547077649, 'fraction_numerical': 0.009853461344113188, 'mean_word_length': 5.682611142374789, 'pattern_counts': {'":': 0, '<': 137, '<?xml version=': 0, '>': 140, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '34', 'dir': 'jupyter-structured-clean-dedup', 'id': '329748', 'license': 'MIT', 'n_tokens_mistral': 3564, 'n_tokens_neox': 3398, 'n_words': 1501, 'path': 'docs/Pandas Tutorial(1).ipynb', 'repo_name': 'hk-23/linear_regression', 'size': '11874'}