Python Project 2.2: DataFrames

For this second project in module 2, I want you to code this code block below and run it in Python

x = {'Name': ['Bill','Carol','Jared','Rozmeen','Jeff','Juan','Lori'],
     'Age': [25,33,42,32,24,48,33],
     'JobTitle': ['Tech','Analyst','Manager', 'Consultant', 'DBA','Engineer', 'Analyst' ],
     'YearsService': [2,8,12,3,1,23,6]
      }

Now, turn the dictionary above into a DataFrame

Next sort DataFrame by YearsService in ascending order

Copy and paste this code below into Python

Loc = ['London', 'NYC', 'Los Angeles', 'New Orleans', 'Paris','Miami', 'NYC']

Create a new column in the DataFrame called Location, using the list provided above

Next filter the DataFrame to only show the Analysts in the list

Now finally filter the DataFrame down to the 3 columns below.

Python Project 2.1 – Create a Module

In this project, I want you to create your own module. The module needs to have 3 methods (functions):

  • Method called Intro > Accepts 1 argument > Name > Prints “My name is <the inputted name>”
  • Method called Job > 2 args > Name, Job > Prints “Hi, I am <name>, and I am a <job>”
  • Method called Pets >2 args > Number, Pets > Prints “I have <number> <pets>”

Open a new python script, import your module and test the three methods.

Python: Accessing a MySQL Database

Here is a code block to create a database if you want to play along

create database sandbox;

use sandbox;
CREATE TABLE employee_id (
        emp_nm varchar(30) not null,
        emp_id varchar(8),
        b_emp_id varchar(8),
        PRIMARY KEY(emp_nm) );   

While loading data from Excel files and CVS files is pretty straightforward in Python, getting database from a SQL database is really a skill you should learn. SQL databases can store much more data than an Excel file and are used pretty much everywhere now.

This lesson will focus on MySQL, I have another lesson for SQL Server as well. The syntax does vary a little and if you are working with Oracle or Teradata, your syntax may change a bit as well. Luckily the Internet has plenty of resources to point you in the right direction.

Now, for MySQL, lets get started. First you are going to want to install mysql.connector (if you have the Anaconda distribution that I recommend, it comes with this pre-installed.

If you don’t have it installed, you can get it using pip or conda – I have a video showing you how to install a module here: video link — skip ahead to the 7 minute mark where I explain how to install modules

Once install, import it into your notebook

Now lets make a connection to our server: syntax should be self explanatory – localhost simply means its installed on my computer — otherwise you would provide a network path to the server there.

Let’s start by looking around- First I want a name off all the databases in my instance

Now in this example we will be working with the Sandbox database — I provided code at the top of the lesson you can paste and run in your MySQL instance to create a Sandbox database

Now lets add a new element to our connection string – database

And query the table names in the Sandbox database

Using the .execute() method, we can pass SQL commands to the MySQL instance

Below I am creating a table -> passing data to the table -> and committing the data

Without the commit() command, the data will not be saved into the table.

To add multiple rows to the table, I can use the executemany() method

Now let’s query our table. Note the .fetchall() method — this brings in all the rows from the query. I can iterate through list by running a simple for loop

I can also use the command .column_names after my cursor to return the column names of the last table I queried

Finally, we can use Pandas to put this database table into a dataframe we can work with

Python: Working with Rows, Pandas DataFrames

Here is a quick lesson on working with rows in DataFrames

Let’s start by building a DateFrame

Lets start by deleting (or dropping) a row:


Let’s try dropping a couple of rows:

[-1] allows us to drop the last row:

Side note:

Here is another method from pandas you should know. This is the shape method. See it returns (rows, columns)

We can filter rows out using conditional logic

The index method returns the row indexes

We can use the .index as well as conditional logic to add a new column to our dataframe

Using append(), we can add a dictionary as a new row to the DataFrame

Finally, using .iloc we can iterate through the values of a row

Last Lesson: Pandas Renaming Columns

Next Lesson: Python: Working with Rows in DataFrames

Return to: Python for Data Science Course

Follow this link for more Python content: Python

Python: Closures

This is a more advanced programming topic, and honestly not one I make much use of myself. However, I got a request to make a lesson on Closures and Decorators. I am going to make it into two lessons to try to make it a bit clearer.

Now to understand closures and decorators, the first thing we need to discuss is nested functions. In programming, nested functions means wrapping a function inside of another function. Here is a very simple example of a nested function:

def outerFunction(x):
    innerFunction():
        print(x)
    innerFunction()

Now if I just call this with outerFunction(‘Hello World’), the argument x gets passed into the innerFunction which is in turn called by the outerFunction. The result is shown below:

Now why would anyone do this? Outside of just being able to show you that you can? I don’t know. There are reasons for nested functions that involve hiding variables from the code outside of the functions. That is a bit beyond the scope of this lesson. I will try to tackle it when I create my Object Oriented Programming lessons.

For now, let us get on to Closures. Let’s look at the code below:

It is very close to the first example, except notice 2 little changes. First, instead of just calling the innerFunction at the end of the function, we are returning it. Notice there is no () when we return innerFunction

Also notice we are passing the outerFunction to a variable and when we call the var we add () to the end: var()

What we have done now is created an official Closure, let me show you why that matters below.

First, notice I delete the outerFunction. When I try calling it returns an error saying outerFunction is not defined (meaning it doesn’t exist).

But, look what happens when I called var(). It is still there. The closure commits the value to memory when you create it. That is not something that would happen from a normal function or even nesting function.

SO WHY THE HELL SHOULD I CARE????

So why this is cool, and why you might use this in the future, is imagine a function that does processer heavy multi-step calculations. If you just keep calling the function as usual, each time you call it, it has to run the whole chain of calculations again. However, once you’ve called it as closure, you don’t have to run the calculations again, you just get the value.

So, how about a more concrete example

Here I created a closure that uses two functions which each take 1 argument, the arguments are then multiplied together.

Notice, I created a variable called double where I sent the outer function mult_by() a value of 2.

When I call double() I can pass it a value for (y)

I can create as many of these instances as I want. Look below, created one for Triple, and notice double is still functional

I’ll continue this in the Decorators lesson.

Just the main thing to keep in mind about closures, is that they improve program performance by no requiring the function to be run each time.

If you are still a little confused after reading this, don’t feel bad. It took me a few tries to finally understand closures myself.

Free Courses

Database Development and Design

Introduction to Database Development and Design using MS Access

Python for Data Science

  • Fundamentals (Available now)
  • Data Handling / Management (coming soon)
  • Statistics and Visualizations (coming soon)
  • Machine Learning (coming soon)

R for Data Science (coming soon)

Excel for Data Science (coming soon)

SQL for Data Science (coming soon)

Python for Data Science

Welcome to Python for Data Science, my free course that will take you from complete beginner to being able to build a machine learning model. The course will consist of 4 modules, the first one: Fundamentals is a available now. Each lesson contains a write up as well as a video. There is over an hour of videos for the Fundamentals Module.

Each module will also also contain some projects: some are simple challenges while the later projects will require you to build a ML model and test it.

The code for each lesson and project solutions are available to download below each module.

If you are working from your phone/tablet or work computer where you can’t install Python, I have a browser based Python console you can work with here:

Python testing shell


Fundamentals

  1. Python: Install Python and Hello World
  2. Python: Arithmetic Operations and Variables
  3. Python: Print Variables and User Input
  4. Python: Printing with .format()
  5. Python: Lists and Dictionaries
  6. Python: Working with Lists
  7. Python: Working with Dictionaries
    1. Project 1: Lists and Dictionaries
  8. Python: Tuples and Sets
  9. Python Conditional Logic
  10. Python Loops
  11. Python Functions
    1. Project 2: Loops and Conditional Logic
  12. Python: Enumerate() and Sort
  13. Python: Error handling
  14. Python lambda, map(), reduce(), filter()
  15. Python zip and unpack
  16. Python list comprehensions
  17. Python: Generators
  18. Python: Regular Expressions
  19. Python: **Kwargs and *Args
    1. Project 3: Cash Register
  20. Python: Closures (Bonus)
  21. Python: Decorators (Bonus)

Download Fundamental lesson Notebooks and Project solutions:

Data Handling / Management

  1. Create, Import, and Use Modules
  2. Numpy
  3. Numpy Part II
  4. Pandas (Series)
  5. Pandas (DataFrame)
  6. Pandas: Working with DataFrames
  7. Pandas: Renaming a Column
  8. Pandas: Working with Rows in DataFrames
  9. Working with CSV Files
  10. Working with Excel and CSV files using Pandas
  11. Pivot tables with Pandas
  12. An Interesting Problem with Pandas
  13. Connect to a MySQL database
    1. Connect to a SQL Server Database

coming soon

Statistics and Visualizations

coming soon

Machine Learning

coming soon

Python Project 1: Lists and Dictionaries

For your first project in the course, I am giving you the code below. You will notice I am placing 2 dictionaries in a list:

d = {'Name': 'Ben', 'Age': 35}
e = {'Name': 'Christine', 'Age': 30}
a = []
a.append(d)
a.append(e)
print(a)

Your challenge, should you choose to accept it, will be to run this code on your machine or in the test python browser (click on the blue arrow below) 

Try your Python code in the free console

I then want you to

1) Print out the second dictionary from the list

2) Print out the name from the first dictionary

3) Print out both ages

Note: I have not shown how to work with dictionaries inside a list. So consider this a stretch project. Don’t be afraid to use Google to help find an answer.


Project Answer Notebook Download Available at Course Page below:

Back to Python Course: Course

Solution Video

Python: Working with Dictionaries

A dictionary is an advanced data structure in Python. It holds data in a Key – Value combination.

Basic Syntax for a dictionary:

dictionary = { <key> : <value>,
               <key> : <value>,
               <key> : <value> }

I think the key/value relationship can be explained thinking about a car. A car has a few key values that describe it. Think: make, model, color, year

If I want to make a dictionary to describe a car, I could build it like this:

car = { 'Make': 'Ford',
        'Model': 'Mustang',
        'Color': 'Candy Apple Red',
        'Year': 1965 }

You can also use the dict() function to create a dictionary

car = dict([ ('Make', 'Ford'), 
             ('Model', 'Mustang'),
             ('Color', 'Candy Apple Red'),
             ('Year', 1965)])

Now, how do we access the values in a dictionary. I know in lists you can just refer to the index, but in dictionaries, it doesn’t work that way.

Try your code in our online Python console: 

  

Instead, we refer to dictionaries by their key:

You can add a key/value pair:

You can change a dictionary value:

To remove values from a dictionary, use the del() function

Try your code in our online Python console: 

Previous Lesson: Working with lists

Next Lesson: Tuples and Sets

Back to Python Course: Course

Python: Installing Python, Hello World and learning to use Jupyter Notebooks

I want to start with the obvious. there are many different version of Python, and many platforms you can use to work with it, from command line/terminal, IDEs like Spyder of IDLE, or notebooks like Jupyter. For this course on Python, I have chosen to start with Jupyter Notebooks, because I feel this is a great tool for learning Python.

Installing Python

First things first. You need to get Python on your machine. In this series of lessons, I am going to use the Anaconda Distribution. I am choosing this distribution for one simple reason: it is built for data science, most of the tools you will need for data analysis and machine learning come pre-loaded with Anaconda.

To install this distribution, go to the Anaconda website and install Python: Anaconda

Jupyter Notebook

Once installed, you will find Jupyter Notebook in your program list.

Jupyter will open in your default browser and should look something like this

Click on the New button and then Python 3

You will now have a new notebook that looks like this

Lets start with some very basic code. We will do the traditional Hello World exercise

The code is a follows

print("Hello World")

Print() with the text you wish to print in quotes inside the parenthesis

After you type it into a notebook command box, click the arrow icon to run your code (or hit Shift+Enter)

Your results will appear at the bottom.

Click here for lesson 2: Arithmetic Operation, Comparators, and Variables

Back to Python Course: Course