<jupyter_start><jupyter_text><jupyter_code>import numpy as np
import pandas as pd
import datetime<jupyter_output><empty_output><jupyter_text>DataFrames<jupyter_code>students = ['Alice', 'Bob', 'Emily', 'Charlie']
scores = [90, 80, 65, 50]
exam_results = pd.DataFrame(
    {
        "name": students,
        "scores": scores
    }
)
exam_results<jupyter_output><empty_output><jupyter_text>Searching (SQL SELECT)<jupyter_code>exam_results.loc[0, 'scores'] # location-> rowid, column_NAME(s)
exam_results.loc[:, 'scores']
exam_results.loc[2, :]
exam_results['scores']
exam_results[['name', 'scores']]
# on DataFrame->  filtering happens on COLUMN LEVEL<jupyter_output><empty_output><jupyter_text>Add Columns We can also easily add columns<jupyter_code>gender = ['female', 'male', 'female', 'male']
exam_results['gender'] = gender
# Creates a new dataframe with a new column called Gender

exam_results
exam_results['scores']<jupyter_output><empty_output><jupyter_text>Remove Columns<jupyter_code>exam_results.drop('name', axis=1)
# axis=1 means column wise
# axis=0 means row wise 
# 0 means index (flows through rows), 1 is for [column_names]<jupyter_output><empty_output><jupyter_text>Column names and Row Index<jupyter_code>exam_results.columns
#axis=1
exam_results.index
#axis=0<jupyter_output><empty_output><jupyter_text>Renaming Columns<jupyter_code>exam_results.rename({'name': 'first_name'}, axis=1)<jupyter_output><empty_output><jupyter_text>Dealing with missing values Dataframes have specific commands which help with the handling of missing data<jupyter_code>sparse = pd.DataFrame({'a': [1, 2, 3],
                       'b': [np.nan, 4, np.nan],
                       'c': [5, np.nan, np.nan]})
sparse
sparse.isna()
sparse.fillna(0) # fill those null values with 0
sparse.dropna(axis=1) # axis=1 means column level dropping
sparse = pd.DataFrame({'a': [1, 2, 3],
                       'b': [np.nan, 4, np.nan],
                       'c': [5, np.nan, np.nan]})
sparse.fillna(0)
sparse.dropna() # axis=0-> row wise!
# everything dropped-> each row contained at least 1 null value!<jupyter_output><empty_output><jupyter_text>Group by<jupyter_code>exam_results.groupby('gender').mean()
# SQL made a lot easier!
# Select mean(*) from exam_results group by gender 
mylist = [1,2,1,2]

exam_results.groupby(mylist).get_group(1)
exam_results.groupby(mylist).get_group(2)<jupyter_output><empty_output><jupyter_text>Thus, suppose we want to group people who obtained marks above or below 70, then we could do:<jupyter_code>above_70 = exam_results['scores'] > 70
above_70<jupyter_output><empty_output><jupyter_text>This will compute the mean scores of students who scored above or below 70<jupyter_code>exam_results.groupby(above_70).mean()<jupyter_output><empty_output><jupyter_text>Merge and Joining<jupyter_code>contacts = pd.DataFrame(
    {
        'name' : ['Alice', 'Charlie'],
        'tel'  : ['+44 1234 5678', '+44 3245 5564']
    }
)
exam_results.merge(contacts, left_on='name', right_on='name')   
# defaults to inner join
# inner join-> Set INTERSECTION
exam_results.merge(contacts, left_on='name', right_on='name', how='outer')
# Null values for Bob and Emily because on outer join, no data present!
# outer join-> Set Union<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1339040048914705, 'fraction_numerical': 0.022317334148578418, 'mean_word_length': 5.677551020408163, 'pattern_counts': {'":': 2, '<': 49, '<?xml version=': 0, '>': 56, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '342571', 'license': 'MIT', 'n_tokens_mistral': 1237, 'n_tokens_neox': 1166, 'n_words': 265, 'path': '03_Python_Pandas_DataFrames.ipynb', 'repo_name': 'annissatessffaaye/QA-Data-Engineering-Bootcamp-Azure-Python-SQL', 'size': '3271'}