<jupyter_start><jupyter_text>Notebook for Pandas workshop<jupyter_code># IPython / Jupyter notebook preliminaries
# what are cells ? 
# how to create a new cell 
# how to run a cell<jupyter_output><empty_output><jupyter_text>An important note on sources - ** Pandas from the Ground Up ** by <NAME>- ** SQL to Pandas and back ** by <NAME>- ** 10 minutes to Pandas ** by Pandas   Things we will cover today- **Create and Query** a database- **Sorting, Counting and Plotting** columns from the database- Fast Lookups with **Indexing**- Using **Functions** with Map- Grouping records with **GroupBy**  Preliminary : Let's add some code to make our tables look colorful- You don't need to remember this<jupyter_code>from IPython.core.display import HTML
css = open('style/style-table.css').read() + open('style/style-notebook.css').read()
HTML('<style>{}</style>'.format(css))<jupyter_output><empty_output><jupyter_text>Let's get started and tell IPython to get us Pandas   Getting Data- Simplest : from your **clipboard**- Also, from a **URL**- Most often, from a ** CSV / tab-delimited ** file Sample data Chromosome size_Mbpchr1 250chr2 243chr3 198 chr4 191chr5 180  For this workshop :- We have data in the data/movies.csv file<jupyter_code># start using TAB complete ASAP !<jupyter_output><empty_output><jupyter_text>Quick sanity checks for your data - **head()** to look at 5 rows- **shape** to find number of rows and cols- **describe()** to get summary statistics   Congratulations ! You've already built your first database. - It has 215 Thousand rows and 2 columns Any questions ?  Let's try to take out some specific rows or columns of this database<jupyter_code># Suppose I only want the YEAR 'column' in which these movies were released<jupyter_output><empty_output><jupyter_text>A column is called a SERIES in Pandas<jupyter_code># what type is it ?

# compare this with the type of movies 

# lets try to play with just a small part of the table for now<jupyter_output><empty_output><jupyter_text>But how often do we really want some entries from a particular set of rows ?  What we really want is : Given a movie name, find it in the database So let's do something called "Boolean Filtering"<jupyter_code># ten_movies

# check which movies released after 2000 

# call this as 'condition'<jupyter_output><empty_output><jupyter_text>- This condition is True only when the movie year is > 2000- It has returned a Pandas Series of True/Falses Can we use this Series of True / False for anything ?<jupyter_code># access the movie that satisfies condition_2k<jupyter_output><empty_output><jupyter_text>We can embed this Boolean series inside a query and use it to search for movies<jupyter_code># now look at the whole command without using the condition intermediate

# try it with title<jupyter_output><empty_output><jupyter_text>Read the "[" symbol as "WHERE" So the query is : "SELECT FROM ten_movies WHERE ten_movies.title is Consp Th"  Let's look for more movies in the BIG database<jupyter_code># how about some new movie ? lets look for The Jungle Book

# upcoming movies in the year 2017<jupyter_output><empty_output><jupyter_text>Filtering using & and |- we can make more compicated conditionals than just the year being a certain value<jupyter_code># Some of us grew up in the 90s, so lets look for 90s movies<jupyter_output><empty_output><jupyter_text>SortingUse the *sort_values(by="")* method<jupyter_code># sort these movies by year<jupyter_output><empty_output><jupyter_text>Does the movie title have what we want ?  Pandas comes with str methods support<jupyter_code># Use string methods !
# Look just for a movie called "<NAME>"

# btw, Tab complete works after .str 
# Use "startswith()" to look for any such movies 

# other string methods
# try "contains"<jupyter_output><empty_output><jupyter_text>How many movies in a particular year?<jupyter_code># lets count all movies by year and then check head()
# apply value_counts() to a Series<jupyter_output><empty_output><jupyter_text>Is this sorted by year ?  No it's not !<jupyter_code># lets sort by year<jupyter_output><empty_output><jupyter_text>Let's start plotting<jupyter_code># first get matplotlib inline<jupyter_output><empty_output><jupyter_text>Congratulations ! You have your first plot from Pandas data!  Take home messages so far - **Create and Query** using Boolean Filtering and **[]**- **Sorting, Counting and Plotting** using **sort_values(by=" "), col.value_counts(), plot()**  Next : Applying functions to a column / SERIES<jupyter_code># look at ten_movies

# define a small function called "return_label" 
%%time 

# Apply this function to every row by iterating<jupyter_output>CPU times: user 4 µs, sys: 2 µs, total: 6 µs
Wall time: 11 µs<jupyter_text>Iteration can be SLOW and PAINFUL use Vector operations instead<jupyter_code>%%time 
# Pandas gives you a keyword called "map" 
# This lets you avoid looping

# Pandas also has "apply"<jupyter_output>CPU times: user 3 µs, sys: 1 µs, total: 4 µs
Wall time: 5.96 µs<jupyter_text>Takeaway : YOU DONT NEED FOR LOOPS No need to say   for row in Pandas dataframe :     for column in row :      do this  IPython SPECIALS are cool. They give you superpowers Starts with "%%"<jupyter_code>%%time
# time to look for 'Iron Man'<jupyter_output>CPU times: user 3 µs, sys: 8 µs, total: 11 µs
Wall time: 25 µs<jupyter_text>Lets use the %%time special to learn about INDEXING  Indexing makes lookups fast!<jupyter_code># Before indexing
# lets look at the table

# Now lets run the command to do indexing

# After indexing
# lets look at the table now<jupyter_output><empty_output><jupyter_text>Look how different it is now : There are no numbers on the side  Looking up using .loc<jupyter_code>%%time 
# time after indexing<jupyter_output>CPU times: user 3 µs, sys: 0 ns, total: 3 µs
Wall time: 7.15 µs<jupyter_text>It's already a bit faster, right ? 11 ms to 6 ms  Sorted Indices are the best !<jupyter_code># how to sort indices

%%time 
# how fast is this ?<jupyter_output>CPU times: user 3 µs, sys: 1e+03 ns, total: 4 µs
Wall time: 6.91 µs<jupyter_text>Look at the time on that one ! We went from 11ms -> 6ms -> 400 Micro sec (10x improvement in speed!)<jupyter_code># Undo the indexing<jupyter_output><empty_output><jupyter_text>Take home messages so far - **Create and Query** using Boolean Filtering and **[]**- **Sorting, Counting and Plotting** using **sort_values(by=" "), col.value_counts(), plot()**- Applying **Functions** using col.**map**(func)- Fast Lookups with **Indexing** using **set_index().sort_index()** Bonus material - **str.** methods- Jupyter specials **%%time**  The SPLIT - APPLY - COMBINE philosophy of Hadley Wickham   Groupby ()<jupyter_code># retrieve all movies with title containing"Sherlock"<jupyter_output><empty_output><jupyter_text>Suppose out of all these movies, you want to collect all movies called "<NAME>" together ?<jupyter_code># Let's try a small example to group sherlocks by title 
# what type is this ?

# let's look at counts<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09044441289223343, 'fraction_numerical': 0.012210705665199488, 'mean_word_length': 5.3746606334841625, 'pattern_counts': {'":': 0, '<': 98, '<?xml version=': 0, '>': 101, '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': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '147533', 'license': 'MIT', 'n_tokens_mistral': 2345, 'n_tokens_neox': 2197, 'n_words': 895, 'path': 'pandas_workshop.ipynb', 'repo_name': 'md5sam/pandas_workshop_genomix_2016', 'size': '7069'}