<jupyter_start><jupyter_text>Pandas Overview Pandas is a library built ontop of NumPy for ease of working with data, visualization, and loading data  Series Overview<jupyter_code>import numpy as np
import pandas as pd
labels = ['a', 'b', 'c']
myData = [10, 20, 30]
arr = np.array(myData)
d = {'a': 10, 'b': 20, 'c': 30}<jupyter_output><empty_output><jupyter_text>Creating the series<jupyter_code>pd.Series(data = myData)
# 0 1 and 2 are the indices and then the data
# We can specify what we want those indices to be in Pandas' Series
pd.Series(data = myData, index = labels)
# Note the indices have changed from 1:3 to a:c
pd.Series(myData, labels)
pd.Series(arr)
pd.Series(arr, labels)
# Using a dictionary to set an array organizes by key and values
print(d)
pd.Series(d)
# Series can hold (most) any type of data type as an object
pd.Series(labels)
# Note this data is strings
pd.Series(data = [sum, print, len])
# Note that it can even hold built in functions as data points
# This isn't very useful, but it's something to see it can do<jupyter_output><empty_output><jupyter_text>Key to using a series is using the indices<jupyter_code>ser1 = pd.Series(list(np.arange(1,5)), ['USA', 'Germany', 'USSR', 'Japan'])
ser1
ser2 = pd.Series([1, 2, 5, 4], ['USA', 'Germany', 'Italy', 'Japan'])
ser2
# Selecting the value at the index
# A string is used as the index as the data type for the labels is a string
ser1['USA']
ser3 = pd.Series(data = labels)
ser3
# An instance of using an integer
ser3[0]
# Series operations work off of indices
ser1 + ser2
# Note that when the label is not present in both series, we receive NAN
# Note that integers are converted to floats<jupyter_output><empty_output><jupyter_text>Dataframes Data frames are 'the true work horse of Pandas'<jupyter_code># Still have pandas and numpy imported as pd and np
from numpy.random import randn
np.random.seed(101) # Set to 101 based on lecture
# Recall tab can autocomplete/intellisense and shift + tab gives us the docstring
df = pd.DataFrame(data = randn(5,4), index = ['A', 'B', 'C', 'D', 'E'], columns = ['W', 'X', 'Y', 'Z'])
df<jupyter_output><empty_output><jupyter_text>Each column is a Pandas Series, but all share a common indiceA dataframe may best be described as a group of series sharing an index<jupyter_code># Return only a column
df['W']
print(type(df['W']))
print(type(df))
# Similar to SQL
df.W<jupyter_output><empty_output><jupyter_text>Bracket notation is prefered so as not to mess up the methods associated with the dataframe command<jupyter_code># Return multiple series/columns: input a list
df[['W','Z']]
# Create a new column
df['new'] = df['W'] + df['Y']
df
df.drop('new')
# Note the error refers to the axes = 0, let's change it to axis = 1 (column)
df.drop('new', axis = 1)
# Though this does not affect the initial dataframe, to do so,
# you must set inplace to True
df
df.drop('new', axis = 1, inplace=True)
df
# We can also drop rows
df.drop('E')
# Remember axis is default to 0, rows
# Find the dimension of the dataframe
df.shape
# Selecting Rows
df
# Selecting rows by  location: index 'label', not numerical index
print(df.loc['A'])
print(type(df.loc['A']))
# Note that the columns and rows are both series
# Selecting rows by index location: numerical index, not 'label'
df.iloc[2]
# Selecting subsets of rows and columns
df.loc['B', 'Y']
# Use the comma notation
df.loc[['A','B'], ['W','Y']]<jupyter_output><empty_output><jupyter_text>Conditional Selection with Pandas DataFrames<jupyter_code>np.random.seed(101) # Set to 101 based on lecture
df = pd.DataFrame(data = randn(5,4), index = ['A', 'B', 'C', 'D', 'E'], columns = ['W', 'X', 'Y', 'Z'])
df
# Returns boolean DataFrame
booldf = df > 0
df[booldf]
df[df > 0]
df
df['W'] > 0
df[df['W']>0]
# Null/NAN values only appear when you use the comparison operator on
# the entire DataFrame
# Doing the condition on a column, you will only return the relevant
# series<jupyter_output><empty_output><jupyter_text>Conditional Selection<jupyter_code>df > 0
booldf = df > 0
df[booldf]
df[df > 0]
# More common is to pass a row or column value for selection
df['W'] > 0
df[df['W'] > 0]
# Note how this does not return row 'C'
# For df['W'] > 0, row 'C' returned False
df[df['Z'] < 0]
# note that column 'Z' is less than zero only at row 'C'
# Note that this conditional selection is returning a DataFrame
resultdf = df[df['W']>0]
resultdf
resultdf['X']
# Notice now that in resultdf, there is no row 'C', so this returns
# a DataFrame without it
# Now, let's do the selection all in one step
df[df['W']>0]['X']
# Return selections of numerous columns instead of just one
df[df['W'] > 0][['Y','X']]
boolser = df['W'] >0
boolser
result = df[boolser]
result
myCols = ['Y','X']
result[myCols]
# Multiple condition Selection
# Using the AND / & Operator
df[(df['W']>0) and (df['Y']>1)]
# Error based on truth value, can't use Python's and operator as it
# can't take into account a series of boolean operators, only one at
# a time.
#
# USE & INSTEAD
print(df)
df[(df['W']>0) & (df['Y']>1)]
# Note that only row 'E' is true for this
# Using the OR / | operator
df[(df['W']>0) | (df['Y']>1)]
# Can't use Python's 'or' operator because of the same reason you
# can't use the 'and' operator<jupyter_output><empty_output><jupyter_text>Working with indices<jupyter_code># Reset the index from A:E with default values (0:...)
df.reset_index()
# Note that it adds the old index in as a column, but it also
# doesn't occur inplace
# See how there was no change? inplace was not set to True
df
newind = ' CA NY WY OR CO'.split()
newind
# Adding to a DataFrame
df['States'] = newind
df
# Make a column the index
df.set_index('States')
# Again, this is not inplace<jupyter_output><empty_output><jupyter_text>Multi-index and Index Hierarchy<jupyter_code># Index Levels
outside = ['G1','G1','G1','G2','G2','G2']
inside = [1,2,3,1,2,3]
hier_index = list(zip(outside,inside)) # combines pairs into tuples
hier_index = pd.MultiIndex.from_tuples(hier_index)
outside
inside
list(zip(outside,inside))
df = pd.DataFrame(randn(6,2),hier_index, ['A','B'])
df
# This has an index hierarchy
df.loc['G1']
df.loc['G1'].loc[1]
# Start from the outside, G1, and move inward, to G1 1 which returns
# the data in columns A and B from G1 1
# We can give these cells name
df.index.names
df.index.names = ['Groups', 'Num']
df
df.loc['G2'].loc[2]['B']
df.loc['G1'].loc[2]
# No change
np.transpose(df.loc['G1'].loc[2])
df.loc['G1'].loc[:]['B']
# Cross Sectioning - Can skip or go inside a multilevel index
df.loc['G1']
df.xs('G1')
# Say I want all values where the inside index is equal to one
df.xs(1,level = 'Num')
# Notice it pulled from both groups<jupyter_output><empty_output><jupyter_text>Missing Data<jupyter_code>d = {'A':[1, 2, np.nan], 'B':[5, np.nan, np.nan], 'C':[1, 2, 3]}
df = pd.DataFrame(d)
df
# Drop missing values
df.dropna()
# REMEMBER INPLACE!
df
df.dropna(axis=1)
# Thresh hold setting
df.dropna()
# drops everything
df.dropna(thresh = 2)
# Kept the second row because it only had 2 missing values
df.fillna(value = 'FILL VALUE')
df
df['A'].fillna(value = df['A'].mean())<jupyter_output><empty_output><jupyter_text>GroupBy Groupby allows you to group together rows based off of a column and perform aggregate functions on them<jupyter_code># Create dataframe
data = {'Company':['GOOG','GOOG','MSFT','MSFT','FB','FB'],
       'Person':['Sam','Charlie','Amy','Vanessa','Carl','Sarah'],
       'Sales':[200,120,340,124,243,350]}
df = pd.DataFrame(data)
df
# grouping by the company column and operating
df.groupby('Company')
# Displays where the object is stored in memory
byComp = df.groupby('Company')
byComp
byComp.mean()
byComp.std()
byComp.sum()
byComp.sum().loc['FB']
# Putting it together in one line
df.groupby('Company').sum().loc['FB']
df.groupby('Company').count()
df.groupby('Company').max()
# Sorts on strings alphabet when data is a string
df.groupby('Company').min()
df.groupby('Company').describe().transpose()
df.groupby('Company').describe().transpose()['FB']<jupyter_output><empty_output><jupyter_text>Merging, Joining, and concatenating<jupyter_code>df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3'],
                        'C': ['C0', 'C1', 'C2', 'C3'],
                        'D': ['D0', 'D1', 'D2', 'D3']},
                        index=[0, 1, 2, 3])
df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
                        'B': ['B4', 'B5', 'B6', 'B7'],
                        'C': ['C4', 'C5', 'C6', 'C7'],
                        'D': ['D4', 'D5', 'D6', 'D7']},
                         index=[4, 5, 6, 7])
df3 = pd.DataFrame({'A': ['A8', 'A9', 'A10', 'A11'],
                        'B': ['B8', 'B9', 'B10', 'B11'],
                        'C': ['C8', 'C9', 'C10', 'C11'],
                        'D': ['D8', 'D9', 'D10', 'D11']},
                        index=[8, 9, 10, 11])
df1
df2
df3
# Note the indice differences
pd.concat([df1,df2,df3])
# Dimensions should match up
pd.concat([df1, df2, df3], axis = 1)
left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3']})
   
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                          'C': ['C0', 'C1', 'C2', 'C3'],
                          'D': ['D0', 'D1', 'D2', 'D3']})
left
right<jupyter_output><empty_output><jupyter_text>Merging<jupyter_code>pd.merge(left,right, how = 'inner', on = 'key')<jupyter_output><empty_output><jupyter_text>More complex<jupyter_code>left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
                     'key2': ['K0', 'K1', 'K0', 'K1'],
                        'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3']})
    
right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
                               'key2': ['K0', 'K0', 'K0', 'K0'],
                                  'C': ['C0', 'C1', 'C2', 'C3'],
                                  'D': ['D0', 'D1', 'D2', 'D3']})
pd.merge(left, right, on = ['key1', 'key2'])
pd.merge(left, right, how = 'outer', on = ['key1', 'key2'])
pd.merge(right, left, how = 'outer', on = ['key1', 'key2'])
pd.merge(left, right, how = 'right', on = ['key1', 'key2'])
pd.merge(left, right, how = 'left', on = ['key1', 'key2'])<jupyter_output><empty_output><jupyter_text>Joining<jupyter_code>left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']},
                      index=['K0', 'K1', 'K2']) 

right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                    'D': ['D0', 'D2', 'D3']},
                      index=['K0', 'K2', 'K3'])
left.join(right)
left.join(right, how = 'outer')
left.join(right, how = 'right')<jupyter_output><empty_output><jupyter_text>Operations in Pandas<jupyter_code>df = pd.DataFrame({'col1':[1,2,3,4],'col2':[444,555,666,444],'col3':['abc','def','ghi','xyz']})
df.head()
# Returning unique values
df['col2'].unique()
# Count the number of unique values
print(len(df['col2'].unique()))
# Here is a pandas built-in method - nunique()
df['col2'].nunique()
# Table of the unique values and their frequency
df['col2'].value_counts()
df
df[df['col1']>2]
df[(df['col1'] > 2) & (df['col2'] == 444)]
df[df['col2'] == 444]<jupyter_output><empty_output><jupyter_text>Using functions on DataFrames - the .apply() method<jupyter_code>def times2(x):
    return x * 2
df['col1'].sum()
df['col1'].apply(times2)
df['col3'].apply(len)<jupyter_output><empty_output><jupyter_text>You can also use lambda experessions instead of defining an entirely new function<jupyter_code>df['col2'].apply(lambda x: x*2)
df.drop('col1', axis = 1)
df
# REMEMBER INPLACE
df.columns
df.index<jupyter_output><empty_output><jupyter_text>Sorting<jupyter_code>df
df.sort_values(by = 'col2', axis = 0)<jupyter_output><empty_output><jupyter_text>Finding null values<jupyter_code>df
df.isnull()
# all falses becuase there are no null type values
data = {'A':['foo','foo','foo','bar','bar','bar'],
     'B':['one','one','two','two','one','one'],
       'C':['x','y','x','y','x','y'],
       'D':[1,3,2,5,4,1]}
df = pd.DataFrame(data)
df<jupyter_output><empty_output><jupyter_text>Pivot Tables<jupyter_code>df.pivot_table(values = 'D', index = ['A', 'B'])
df.pivot_table(values = 'D', index = ['A', 'B'], columns = ['C'])<jupyter_output><empty_output><jupyter_text>Data Input and Output Pandas can take in files like:        * CSV    * Excel    * HTML    * SQL    You must install:        conda install sqlalchemy lxml html5lib BeautifulSoup4<jupyter_code>pd.read_csv('example.csv')
df = pd.read_csv('example.csv')
df
df.to_csv('MyOutput.csv', index = False)
pd.read_csv('MyOutput.csv')
# Reading and writing excel files
# Reading and writing with Macros can make things fail
#
# Pandas treats sheets in excel files as data from
pd.read_excel('Excel_Sample.xlsx', sheet_name = 'Sheet1')
df.to_excel('ExcelSample2.xlsx')<jupyter_output><empty_output><jupyter_text>HTML Input<jupyter_code>df = pd.read_html('http://www.fdic.gov/bank/individual/failed/banklist.html')
# find table 
df[0].head()<jupyter_output><empty_output><jupyter_text>Pandas may not be the best way to use SQL because of how many different flavors of SQL engine exist<jupyter_code>from sqlalchemy import create_engine
engine = create_engine('sqlite://', echo=False)
df.to_sql('myData', engine)
sqldf = pd.read_sql('myData', con = engine)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17652309752287434, 'fraction_numerical': 0.02722606561035483, 'mean_word_length': 4.276295133437991, 'pattern_counts': {'":': 0, '<': 102, '<?xml version=': 0, '>': 123, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '586501', 'license': 'Unlicense', 'n_tokens_mistral': 5085, 'n_tokens_neox': 4880, 'n_words': 1449, 'path': 'Python/PandasBasics.ipynb', 'repo_name': 'ChristopherDaigle/Tutorials', 'size': '13443'}