Introduction to Importing Data, Using Tables and Creating Graphs

The Jupyter Notebook

If you run into errors, check the common errors Google doc first.

First of all, note that this page is divided into what are called “cells”. For example, the following cell is a “code cell” where you will write your code. You’ll see a In [ ]: next to each cell for code, which is a counter for the cells you have run. You can navigate cells by clicking on them or by using the up and down arrows. Cells will be highlighted as you navigate them.

# this is a code cell

Executing cells

Try running the following cell and see what it prints out:

print("Hello world!")

Hello world!

10 + 10

20

(10 + 10) / 5

4.0

Now run this cell to import some code we’ll use today, nothing will print out, don’t worry!

import numpy as np
import matplotlib.pyplot as plt
from datascience import *
%matplotlib inline 
plt.style.use("fivethirtyeight")

Importing

In data analytics, there is almost always a file holding your data that already exists. There are thousands of databases online that contain information on topics from all domains. In general, to import data from a file, we write:

Table.read_table("file_name")

Most often, these file names end in .csv to show the data format. .csv format is popular for spreadsheets and can be imported/exported from programs such as Microsoft Excel, OpenOffice Calc, or Google spreadsheets.

An example is shown below using U.S. Census data.

!pwd

/Users/chrispyles/GitHub/modules-textbook/content/psych

Table.read_table("data/nc-est2015-agesex-res.csv")

That’s a lot of information. As you can see from the labels on top, this table shows Biological Sex (0=total, 1=male, 2=female), Age, 2010 Census Information, and predictions for U.S. population for the next five years.

Using Tables

We can make criteria to cut down tables. Accessing only the rows, columns, or values specfic to our purpose makes information easier understood. Analysis and conclusions can be made when data is more digestible.

We need to access the census table above and name it for further use. We assign the table to a variable so that we can reference it later!

census_data = Table.read_table("data/nc-est2015-agesex-res.csv")
census_data

This notebook can calculate how large this table is with two functions: num_rows and num_columns. The general form for these functions are table.num_rows and table.num_columns.

Let’s use these on the table above.

census_data.num_rows

306

census_data.num_columns

10

That’s a 306 x 10 table! We can first start to cut down this table using only some columns. Let’s only include biological sex, age and the estimated base for 2010 census data.

There are two methods to make a table with select columns included. We could either use the ‘select’ function or the ‘drop’ function.

• select can create a new table with only the columns indicated in the parameters
• drop can create a new table with columns NOT indicated in the parameters

Here’s an example of two equal codes: (keep in mind that we assign each new table to a new variable, to make organization easier).

select_census_data = census_data.select("SEX", "AGE", "ESTIMATESBASE2010")
select_census_data

drop_census_data = census_data.drop("CENSUS2010POP","POPESTIMATE2010","POPESTIMATE2011","POPESTIMATE2012","POPESTIMATE2013","POPESTIMATE2014","POPESTIMATE2015")
drop_census_data

As you can see underneath the table, there are still 296 rows omitted! Our next step is to only include non-gendered data AKA data where SEX=0, neither male or female specific.

To do this, we need to use a new function where. The general form of this function is:

table_name.where(column_name, predicate)

To cut our table down to only include sex=0, we may use the predicate are.equal_to(). Note that we are assigning the new table to a new variable. We are referencing the table stored in an older variable (select_census_data), and modifying it. That modification is what is stored in the new variable.

new_census_data = select_census_data.where("SEX", are.equal_to(0))
new_census_data

There are still 92 rows omitted! Let’s take every 10th entry to cut this table down a little more.

To do this we need to use the take function. The take function creates a new table with rows from the original table whose indices(row number) are given. In Python, indices start at 0!

Here’s taking every 10th entry. Inside of the take parentheses is a Python list of numbers from 0 to 90, increasing by 10s. This indicates exactly which rows we want to keep (every 10th row).

census_10_year = new_census_data.take([0,10,20,30,40,50,60,70,80,90])
census_10_year

Now that sex is all the same, we can drop that column.

final_census_table = census_10_year.drop("SEX")
final_census_table

Tables Essentials!

For your reference, here’s a table of useful Table functions:

Visualizations

Now that we have a manageable table we can start making visualizations! Due to the numerical nature of the census table above, let’s first try a scatter plot.

To create a scatter plot, we need to use the scatter() function. The general form is:

table.scatter("column for x axis", "column for y axis")

An example is shown below:

final_census_table.scatter("AGE", "ESTIMATESBASE2010") 

With this data, we can also make a line plot. To do this, we need to use the plot() function. This works a lot like scatter() where the general form is:

table.plot("x column", "y column")

final_census_table.plot("AGE", "ESTIMATESBASE2010") 

Though a bar may be better. Bar graphs follow the same formula as scatter plots and line graphs above, with the general form:

table.bar("x axis", "y axis")

final_census_table.bar("AGE", "ESTIMATESBASE2010") 

You can also use the functino barh() instead of bar() in order to flip the bar graph horizontally. Sometimes, this makes for a cleaner visualization.

Merging Tables

We are going to cover one more topic briefly that you will need to use in your project. We are going to look into how to merge two tables that have common information. This technique will be very valuable when the time comes for you to do your own analysis with your own data sets.

We are going to read in a table with information about psychologists. We will call this new table psych1.

psych1 = Table.read_table("example-data/psych1.csv")
psych1

Adding rows

You may have another table that has the exact same columns and you just want to add the rows to what you already have. Let’s read in another short table with a couple more psychologists:

psych2 = Table.read_table("example-data/psych2.csv")
psych2

Great! We see that this second table has the same columns as the first one. Merging these two tables would allow us to consolidate our information. We are going to use the “append” method to append the second table onto the first!

psych_merged = Table.copy(psych1)  # copying over the new_psych table to a new variable for the merged table
psych_merged.append(psych2)
psych_merged

As you can see, we have succesfully merged these two tables together! Now, let us try merging an additional column onto the original new_psych table.

Adding columns

Let’s pretend that we suddenly have access to the favorite foods of each psychologist. Wow! We definitely want to include that information in our table. This means that we need to merge in a new column to the initial table. Let us print the table first, and then we’ll get the other column.

psych_merged

Now we are going to create to read in our new information.

psych_foods = Table.read_table('example-data/favorite_food.csv')
psych_foods

Luckily, we are going to assume that each row is in the right order in the column. We are going to use a similar process as before to merge this column! The method we now use is append_column. Otherwise, the format stays the same!

psych_merged_with_food = Table.copy(psych_merged)  # copying over the new_psych table to a new variable 
psych_merged_with_food.append_column("Favorite Food", psych_foods['Favorite Food'])
psych_merged_with_food

As you can see, we’ve successfully merged a column to our table too!

Joining on columns

Suppose now that you have more information on the these psycologists (another column), but the rows aren’t in the right order so you can’t just append_column. Luckily, you have their names.

psych_birthplaces = Table.read_table("example-data/birthplaces.csv")
psych_birthplaces

Awesome! We love more data, but how can I merge this back into our existing information? We can’t just add a column because the order is different.

Solution: We can use the join method and tell it which column the two tables have in common, and it will match the data to the correct row:

final_table = psych_merged_with_food.join("Psychologist", psych_birthplaces)
final_table

That’s super cool!

SUMMARY

You’ve learned a lot in this module! Let’s look back on the key parts.

• To import data from a .csv/.txt file, we write Table.read_table("file_name").

• To create our own table, we write Table( ).with_columns("Column Name", array_name, . . .) .

• To count number of rows, we use table_name.num_rows.

• To count number of columns, we use table_name.num_columns.

• To create a new table with only the columns indicated in the parameters, we use table_name.select("COLUMN NAME", ...).

• To create a new table without the columns indicated in the parameters, we use table_name.drop("COLUMN NAME", ...).

• To create a table with only certain values, we can use table_name.where(column_name, predicate).

• To create a new table with indicated rows from the original table, we use table_name.take([index 1, index 2, . . . ]). Remember in Python indices start at 0!

• To create a scatter plot, we use table.scatter(column for x axis, column for y axis).

• To create a line plot, we use table.plot(x column, y column).

• To make a bar graphs, we can use either table.bar(x column, y column) or table.barh(x column, y column).

• To make a histogram, we use table.hist(x axis, bins(optional), unit(optional)).

• To merge tables, we use either append or append_column.

• To merge two tables with a common column name we use the join method.

With just some simple code, we were able to do an incredible amount of data analysis! Play around with the examples until you feel comfortable with the content of this notebook. We will be using notebooks to analyze your own data sets in the future! Please ask if you have questions!

If you need help, please consult the Data Peers!