<jupyter_start><jupyter_text>Lecture 21 – Joining and Row Methods Data 94, Spring 2021<jupyter_code>from datascience import *
import numpy as np<jupyter_output><empty_output><jupyter_text>`join`ing<jupyter_code>phones = Table().with_columns(
    'Model', np.array(['iPhone 12', 'iPhone 12 Pro Max', 'Samsung Galaxy S21', 'OnePlus 8']),
    'Price', np.array([799, 1099, 799, 699]),
    'Screen Size', np.array([6.1, 6.7, 6.2, 6.6])
)

inventory = Table().with_columns(
    'Handset', np.array(['Samsung Galaxy S21', 'iPhone 12', 'iPhone 12', 'OnePlus 8', 'Pixel 5']),
    'Units', np.array([50, 40, 10, 100, 25]),
    'Store', np.array(['Berkeley', 'Berkeley', 'San Francisco', 'Oakland', 'Oakland'])
)
phones
inventory
phones.join('Model', inventory, 'Handset')
inventory.join('Handset', phones, 'Model')
store = phones.join('Model', inventory, 'Handset')
store
store.column('Price') * store.column('Units')
# Total value of all of the phones in my inventory (that I know the price of)
np.sum(store.column('Price') * store.column('Units'))
# Equivalent to the above
np.dot(store.column('Price'), store.column('Units'))<jupyter_output><empty_output><jupyter_text>Quick Check 1<jupyter_code>contacts = Table().with_columns(
    'Name', np.array(['Roxanne', 'Sandy', 'Stan', 'Tomas', 'Wilma']),
    'Email', np.array(['<EMAIL>', '<EMAIL>', '<EMAIL>', '<EMAIL>', '<EMAIL>']),
    'Area Code', np.array([510, 212, 734, 734, 212]),
)

codes = Table().with_columns(
    'Code', np.array([212, 310, 519, 734]),
    'Region', np.array(['New York City', 'Los Angeles', 'Ontario, Canada', 'Metro Detroit'])
)
contacts
codes
# contacts.join(___, ___, ___)<jupyter_output><empty_output><jupyter_text>Followup<jupyter_code>extra_codes = Table().with_columns(
    'Code', np.array([212, 212, 519, 734]),
    'Region', np.array(['New York City', 'Los Angeles', 'Ontario, Canada', 'Metro Detroit'])
)
contacts
extra_codes
contacts.join('Area Code', extra_codes, 'Code')<jupyter_output><empty_output><jupyter_text>Disclaimer<jupyter_code># No output – because there are no matches between
# the 'Name' column in contacts and the 'Code' column in codes
contacts.join('Name', codes, 'Code')<jupyter_output><empty_output><jupyter_text>Example: grades<jupyter_code>roster = Table.read_table('data/roster.csv')
grades = Table.read_table('data/grades.csv')
roster.show()
grades.show()
roster.num_rows
grades.num_rows
grades_merged = roster.join('SID', grades)
grades_merged
grades_merged.num_rows<jupyter_output><empty_output><jupyter_text>Let's see if there are any students in the roster who we don't have grades for:<jupyter_code>for sid in roster.column('SID'):
    if sid not in grades.column('SID'):
        display(roster.where('SID', sid))<jupyter_output><empty_output><jupyter_text>And vice versa:<jupyter_code>for sid in grades.column('SID'):
    if sid not in roster.column('SID'):
        display(grades.where('SID', sid))<jupyter_output><empty_output><jupyter_text>Whoever the above students are, they're not in our roster. (This happens often when students drop a class.)Now we can do some grade calculations:<jupyter_code>grades_merged
assignment_totals = {
    'Homework 1': 24,
    'Homework 2': 18,
    'Quiz 1': 100,
    'Quiz 2': 70,
    'Final': 90
}
# Divides each assignment column by its denominator in assignment_totals
for assignment in assignment_totals.keys():
    grades_merged = grades_merged.with_columns(
        assignment + ' Percentage', grades_merged.column(assignment) / assignment_totals[assignment]
    )
grades_merged<jupyter_output><empty_output><jupyter_text>Other tools  `.row`<jupyter_code>phones
phones.row(1)
type(phones.row(1))
phones.row(1).item(1)
list(phones.row(-1))<jupyter_output><empty_output><jupyter_text>`.with_rows`<jupyter_code>phones
phones.with_row(['iPhone 12 Mini', 699, 5.8])
phones.with_rows([['iPhone 12 Mini', 699, 5.8],
                  ['Moto RAZR', 459, 3.5]])<jupyter_output><empty_output><jupyter_text>Quick Check 2<jupyter_code>codes<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16292974588938713, 'fraction_numerical': 0.033632286995515695, 'mean_word_length': 6.144128113879003, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 54, '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': '637249', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1552, 'n_tokens_neox': 1413, 'n_words': 325, 'path': 'lecture/lec21/lec21.ipynb', 'repo_name': 'surajrampure/data-94-sp21', 'size': '4067'}