Preparing to put tables together

Python for Spreadsheet Users

Chris Cardillo

Data Scientist

Table merging

Table Merges.png

Python for Spreadsheet Users

Table merging

Table Merges Name Highlighted.png

Python for Spreadsheet Users

Merging: The VLOOKUP of Data Science

Fruit VLookup.png

Python for Spreadsheet Users

Merging: The VLOOKUP of Data Science

Fruit Vlookup Key on the Left.png

Python for Spreadsheet Users

Merging: The VLOOKUP of Data Science

Fruit Vlookup Match on the Right.png

Python for Spreadsheet Users

Merging in Python

  • Python is case-sensitive!
    • 'Apple' != 'apple'
Python for Spreadsheet Users

Merging in Python

  • Python is case-sensitive!
    • 'Apple' != 'apple'
  • Python is exact with whitespace!
    • 'Apple' != ' Apple '
Python for Spreadsheet Users

Merging in Python

  • Python is case-sensitive!
    • 'Apple' != 'apple'
  • Python is exact!
    • 'Apple' != ' Apple '
  • Python joins whole tables!
    • AB + ACD = ABCD, even if we only want ABC
    • So we have to drop column D.
Python for Spreadsheet Users

Handling case-sensitivity

fruit_Price - Uppercase Name

fruit price upper.png

fruit_Color - title case name

fruit color console.png

Python for Spreadsheet Users

.str.title() method

fruit_price['name'].str.title()
Python for Spreadsheet Users

.str.title() method

fruit_price['name'] = fruit_price['name'].str.title()

print(fruit_price)

fruit prices console.png

Python for Spreadsheet Users

.str.upper() method

fruit_price['name'] = fruit_price['name'].str.upper()

print(fruit_price)

fruit price upper.png

Python for Spreadsheet Users

.str.lower() method

fruit_price['name'] = fruit_price['name'].str.lower()

print(fruit_price)

fruit price lower.png

Python for Spreadsheet Users

.str.title() method

fruit_price['name'] = fruit_price['name'].str.title()

print(fruit_price)

fruit prices console.png

Python for Spreadsheet Users

Handling whitespace with .str.strip()

fruit_price['name'] = fruit_price['name'].str.strip()

print(fruit_price)

fruit prices console.png

Python for Spreadsheet Users

Selecting and dropping columns

fruit with unneeded column.png

Python for Spreadsheet Users

Selecting columns

Select Columns
fruit_price[['name', 'price_usd']]

fruit prices console.png

Python for Spreadsheet Users

Selecting columns

Select Columns
fruit_price = fruit_price[['name', 'price_usd']]

fruit prices console.png

Python for Spreadsheet Users

Dropping Columns with .drop()

Drop Columns
fruit_price.drop('unneeded', axis=1)

fruit prices console.png

Python for Spreadsheet Users

Dropping Columns

Drop Columns
fruit_price = fruit_price.drop('unneeded', axis=1)

fruit prices console.png

Python for Spreadsheet Users

Your turn!

Python for Spreadsheet Users

Preparing Video For Download...