<jupyter_start><jupyter_text>When looking at our data- Always ask, what does each row/observation represent?  Handy Pandas Functions for Reading Data- `pd.read_csv("filename.csv")`- `pd.read_excel("filename.xlsx")`- `pd.read_json("filename.json")`- `pd.read_sql` uses a syntatically correct SQL query and a connection object- `pd.read_table` can read a HTML table of content - `pd.read_clipboard()` can read your clipboard into a dataframe  Let's Have Pandas and SQL Talk! Together at last!<jupyter_code>from env import host, user, password

# Connection string
url = f'mysql+pymysql://{user}:{password}@{host}/employees'
    
query = """
    SELECT * FROM employees limit 5
"""

pd.read_sql(query, url)<jupyter_output><empty_output><jupyter_text>Let's Talk Aggregation- Aggregation and grouping by allow us to put stuff together.- Can consider many things, one thing- Changes our dimension of observation- Literally changes what each row in a df means<jupyter_code>df.head()
# Remember double brackets mean a dataframe
# we get a dataframe here w/ reading, english, math, and we lose a level of detail
# We lose names and classrooms
# Aggregating changes what each row means
df[['english', 'reading', 'math']].agg(['mean', 'median', 'min', 'max', 'count'])
# Show the English average
df.english.mean()
# Show the median math grade
df.math.median()
df.head()
# Grouping By means we're grouping our original rows together by some column's value
# To Group By:
# Step 1 is we group by a column
# Step 2 is we need an aggregate function
# Take stock of how we're changing our observation/rows
# from a single student to a group of rows by classroom letter
# Average math score by classroom.
df.groupby("classroom").math.mean()
# Grouping by creates a "group by object"
df.groupby("classroom").math.agg(["mean", "median"])
# Think of the SQL that might correspond to this:
# select classroom, count 
# from students
# group by classroom
df.groupby("classroom").name.count()
df.groupby("classroom").describe()<jupyter_output><empty_output><jupyter_text>Takeaways:- Whatever column/value we "group by" becomes the new observation level- If the original rows were students, each observation is a student- when we groupby the classroom, each row/observation is a classroom<jupyter_code># Make a "passing_math" column
df["passing_math"] = df.math >= 70
df.head()
df.groupby(["classroom", "passing_math"]).reading.agg(["mean", "median", "min", "max"])
df.groupby(["classroom", "passing_math"]).name.count()
df.groupby(["classroom", "passing_math"]).math.agg(["count", "mean"])
# .transform in the way below allows us to calculate an aggregate and append that aggregate onto the original dataframe
df.assign(avg_math_score_by_classroom=df.groupby('classroom').math.transform('mean'))
# Aggregation example:
# Let's count the number of employees in each department from the employees_db

# Replicate the SQL here w/ pandas groupby and aggregation
# select dept_name, count(*)
# from departments
# join dept_emp using(dept_no)
# where to_date > now()
# group by dept_name;

sql = """
select *
from departments
join dept_emp using(dept_no)
where to_date > now()
"""

employees = pd.read_sql(sql, url)
employees.head()
employees.groupby("dept_name").emp_no.count()<jupyter_output><empty_output><jupyter_text>In the code example above, what does each row represent?- Always ask what each row/observation means, especially when aggregating<jupyter_code># Let's get minimum, mean, median, and maximum salary numbers by gender
sql = """
select *
from employees
join salaries using(emp_no)
where to_date > now();
"""

employees = pd.read_sql(sql, url)
employees.head()
employees.groupby("gender").salary.agg(["min", "mean", "median", "max", "count"])
# Do the same as above, but group by dept_name AND gender
sql = """
select *
from employees
join salaries using(emp_no)
join dept_emp using(emp_no)
join departments using(dept_no)
where dept_emp.to_date > now()
and salaries.to_date > now()
"""

employees = pd.read_sql(sql, url)
employees.head()
employees.groupby(["dept_name", "gender"]).salary.agg(["min", "mean", "median", "max", "count"])
# Crosstab and .pivot_table
pd.crosstab(df.passing_math, df.classroom)
pd.crosstab(employees.gender, employees.dept_name)
pd.crosstab(employees.gender, employees.dept_name, normalize=True)
pd.crosstab(employees.gender, employees.dept_name, margins=True)
len([1, 2, 3])
len
def nudge_grades(df):
    # cranks up the math score by 10%
    # cranks down the reading by 10%
    df.math = df.math + df.math * .10
    df.reading = df.reading - df.reading * .10
    return df
def bump_english(df):
    df.english = df.english + 5
    return df
# With nested function calls like this, the order of operations is from the inside to the outside
# which kinda feels like reading from right to left
bump_english(nudge_grades(df)).head()
# .pipe exists to take in function definitions and chain/read from left to right
# .pipes allows reading and order of application of the function from left to right.
df.pipe(nudge_grades).pipe(bump_english).head()
employees.head()
# .loc[starting_row:ending_row, starting_column:ending_column]
employees.loc[0:3, 'birth_date':'salary']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.119102416570771, 'fraction_numerical': 0.00364403528960491, 'mean_word_length': 5.478260869565218, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 23, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '653343', 'license': 'MIT', 'n_tokens_mistral': 1705, 'n_tokens_neox': 1627, 'n_words': 562, 'path': 'easley-data-science-libraries/advanced_dataframes_demo.ipynb', 'repo_name': 'o0amandagomez0o/numpy-pandas-visualization-exercises', 'size': '5214'}