<jupyter_start><jupyter_text>Pandas [Documentation](https://pandas.pydata.org/docs/)Installation using pip package installer and terminal:`pip install pandas`<jupyter_code>import numpy as np
from numpy.random import randn
import pandas as pd
from sqlalchemy import create_engine

# Set the seed for random
np.random.seed(101)<jupyter_output><empty_output><jupyter_text>Series<jupyter_code>labels = ['a', 'b', 'c']
ls = [10,20,30]
arr = np.array([10,20,30])
dic = {'a': 10, 'b': 20, 'c':100}
# Create a series using various objects
pd.Series(ls, labels)
 pd.Series(dic)
pd.Series(labels)
pd.Series([sum, print, len])
ser1 = pd.Series([1,2,3,4], index=['Canada','UAE', 'France', 'Japan'])
ser1
ser2 = pd.Series([1,2,3,4], index=['Canada','USA', 'Germany', 'Japan'])
ser2
# Select the data of a label
ser1['Canada']
ser2['USA']
# Add to series
ser1 + ser2<jupyter_output><empty_output><jupyter_text>DataFrames<jupyter_code># Create a dataframe
df = pd.DataFrame(randn(5,4), ['A','B','C','D', 'E'], ['W','X','Y','Z'])
df
# Select a column
df['W']
# A dataframe is a collection of series that share the same index
type(df['W'])
# Ask for multiple columns
df[['X','W']]
# Create a new column
df['V'] = "True"
df
# Remove column
df.drop('V', axis=1)
# Drop doesn't happen inplace
df
# Set inplace=True to change it in place
df.drop('E', inplace=True)
df
df.shape
# Select a row using label based index
df.loc['C']
# Select a row using numerical based index
df.iloc[2]
# Select a subset of rows and columns
df.loc['B', 'Y']
df.loc[['A', 'B'], ['W', 'Y']]<jupyter_output><empty_output><jupyter_text>Conditional Selection in DataFrames<jupyter_code>df = pd.DataFrame(randn(5,4), ['A','B','C','D', 'E'], ['W','X','Y','Z'])
df
# Conditional selction on the entire dataframe, will return dataframe with null values
booldf = df > 0
df[booldf]
df[df > 0]
# Conditional selection using columns, returns series values and no nulls
df[df['W'] > 0]
df[df['W']>0][['X', 'Y']]
# Multiple conditions
df[(df['W']>0) & (df['Y']>1)]
# Reseting the indices, not inplace
df.reset_index()
new_index = 'CA NY WY OR CO'.split()
df['States'] = new_index
df
# Set a column to be the new index, not inplace
df.set_index('States')
# Create a multi index dataframe
outside = ['G1', 'G1', 'G1','G2', 'G2', 'G2']
inside = [1,2,3,1,2,3]
higher_index = list(zip(outside, inside))
higher_index = pd.MultiIndex.from_tuples(higher_index)
df = pd.DataFrame(randn(6,2), higher_index, ['A', 'B'])
df
# select a row using index hierarchy 
df.loc['G1'].loc[1]
# Select a column using index hierarchy
df.loc['G2'].loc[2]['B']
# Naming the inside and outside indices
df.index.names = ['Groups', 'Num']
df
# Cross section of rows and columns
df.xs(1, level='Num')<jupyter_output><empty_output><jupyter_text>Missing Data<jupyter_code>dic = {'A':[1,2,np.nan] ,'B':[5, np.nan, np.nan] ,'C':[1,2,3]}
df = pd.DataFrame(dic)
df
# Drop rows with null or missing values
df.dropna()
# Drop columns with null or missing values
df.dropna(axis=1)
# Modify the threshhold for dropping null values
df.dropna(thresh=2)
# Fill in the missing values with a specific value
df.fillna(value="value")
# Fill in the missing values with the mean of the values
df['A'].fillna(value=df['A'].mean())<jupyter_output><empty_output><jupyter_text>GroupBy<jupyter_code>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
byCompany = df.groupby('Company')
byCompany.mean()
byCompany.sum()
byCompany.std()
byCompany.sum().loc['FB']
df.groupby('Company').sum().loc['FB']
df.groupby('Company').count()
df.groupby('Company').max()
df.groupby('Company').min()
df.groupby('Company').describe()
df.groupby('Company').describe().transpose()<jupyter_output><empty_output><jupyter_text>Concatenating, Merging, and Joining<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])
df1
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])
df2
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])
df3<jupyter_output><empty_output><jupyter_text>ConcatenationConcatenation glues the dataframes together. Note that dimensions should match along the axis that are being concatenated.<jupyter_code># Concatenate along the rows
pd.concat([df1, df2, df3])
pd.concat([df1, df2, df3], axis=1)<jupyter_output><empty_output><jupyter_text>Merging<jupyter_code>left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'A': ['A0', 'A1', 'A2', 'A3'],
                     'B': ['B0', 'B1', 'B2', 'B3']})
left
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
                     'C': ['C0', 'C1', 'C2', 'C3'],
                     'D': ['D0', 'D1', 'D2', 'D3']})
right
pd.merge(left, right, how='inner', on='key')
left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K1'],
                     'key2': ['K0', 'K1', 'K0', 'K1'],
                     'A': ['A1', 'A2', 'A3', 'A4'],
                     'B': ['B1', 'B2', 'B3', 'B4']})
left
right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K3'],
                      'key2': ['K0', 'K0', 'K0', 'K0'],
                     'C': ['C0', 'C1', 'C2', 'C3'],
                     'D': ['D0', 'D1', 'D2', 'D3']})
right
# Merging using multiple keys
pd.merge(left, right, on=['key1', 'key2'])
pd.merge(left, right, 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 Similar to merge but the keys are indices<jupyter_code>left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                      'B': ['B0', 'B1', 'B2']},
                    index=['K0', 'K1', 'K2'])
left
right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                     'D': ['D0', 'D2', 'D3']},
                    index=['K0', 'K2', 'K3'])
right
left.join(right)
left.join(right, how='outer')<jupyter_output><empty_output><jupyter_text>Operations<jupyter_code>df = pd.DataFrame({'col1': [1,2,3,4],
                  'col2': [444,555,666,444],
                  'col3': ['abc', 'def', 'ghi', 'xyz']})
df.head()
# Finding unique values in a dataframe
len(df['col2'].unique())
df['col2'].nunique()
# Count how many times a value shows up
df['col2'].value_counts()
# Applying a custom function
def times3(x):
    return x*2
df['col1'].apply(times3)
df['col1'].apply(lambda x: x*2)
df['col3'].apply(len)
# Columns
df.columns
# Indices
df.index
# Sort by column
df.sort_values('col2')
# Pivot table
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
df.pivot_table(values='D', index=['A','B'], columns=['C'])<jupyter_output><empty_output><jupyter_text>Data Input/Output- CSV- Excel- HTML- SQL<jupyter_code># I/O CSV file
pd.read_csv('../sample-files/sample.csv')
df = pd.read_csv('../sample-files/sample.csv')
df.to_csv('../sample-files/output.csv',index=False)
pd.read_csv('../sample-files/output.csv')
# I/O for Excel files requires openpyxl library
# Install using pip: pip install openpyxl
pd.read_excel('../sample-files/excel-sample.xlsx', sheet_name='Sheet1')
df.to_excel('../sample-files/excel-output.xlsx', sheet_name='new-sheet')
# I/O for HTML files requires html5lib, lxml, and BeautifulSoup4
# Installation using pip: pip install html5lib
# pip install lxml
# pip install BeautifulSoup4
# Then restart Jupyter notebook
data = pd.read_html('https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/')
type(data)
data[0]
data[0].head()
# I/O for SQL files requires sqlalchemy library
# Installation using pip: pip install sqlalchemy
# Create a simple SQL engine in memory
engine = create_engine('sqlite:///:memory:')
# Write a dataframe to the engine
df.to_sql('sample_table', engine)
sqldf = pd.read_sql('sample_table', con=engine)
sqldf<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.2167037601030807, 'fraction_numerical': 0.03596111046034907, 'mean_word_length': 4.149577804583836, 'pattern_counts': {'":': 0, '<': 49, '<?xml version=': 0, '>': 55, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 2}, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '597235', 'license': 'MIT', 'n_tokens_mistral': 3421, 'n_tokens_neox': 3290, 'n_words': 792, 'path': 'notebooks/pandas.ipynb', 'repo_name': 'rmanaem/pydatasci', 'size': '8537'}