Introduction to Data Science

Data Wrangling

Author

Joanna Bieri
DATA101

Important Information

Announcements

Please come to office hours to get help!

Day 5 Assignment

  1. Download Day5-HW.ipynb from the class website and move it into your Day5 folder in your repo.
  2. Open the file Day5-HW.ipynb and start doing the problems.
    • You can do these problems as you follow along with the lecture notes and video.
  3. Get as far as you can before class.
  4. Submit what you have so far Commit and Push to Git.
  5. Take the daily check in quiz on Canvas.
  6. Come to class with lots of questions!

—————————–

The Data Science Lifecycle

Today we are talking about Data Wrangling, which can take place during the Initial Exploration part of the Lifecycle just after you get your data. However, as you do your analysis, sometimes you will realize that the data needs some more tidying. This is normal.

    graph LR
    subgraph Data Science Initial Exploration
    A((Subject Matter Understanding <br> Define the Question))-->B((Data Gathering <br> Mining <br> Ethical Questions )) 
    B-->C((Data Cleaning <br> Wrangling))
    C-->D((Data Exploration <br> Visualization <br> Exploratory Data Analysis))
    end
    
    subgraph Data Science Modeling
    D-->E((Feature Engineering <br> Data Preparation))
    E-->F((Predictive Modeling))
    F-->G((Data Visualization <br> Communication <br> Sharing))
    end
    G-->A

    style C fill:#f9f,stroke:#333,stroke-width:4px
Figure 1: Data Science Lifecycle

What is Clean or Tidy Data?

Characteristics of Tidy Data:

  • Each variable forms a column
  • Each observation forms a row
  • Observational units form a table

Untidy Data:

  • Any other kind of mess.

This lab follows the Data Science in a Box lab “Wrangling and Tidying Data” by Mine Çetinkaya-Rundel and images were all created by the author. The content has been updated for our class and translated to Python by Joanna Bieri.

Some Examples of Data

Air force Data: Army Air Forces Statistical Digest, WW II

Data Image

Is this tidy data? NO!

  • Merged cells to represent date - we need to add a column for the year.
    • Might be visually appealing, but not good for analysis.
  • There are historical markers - we need to add a pre/post variable if we need this data.

Some Examples of Data

HIV Data: Gapminder, Estimated HIV prevalence among 15-49 year olds

Data Image

Is this tidy data? NO!

  • The orange cell - this is a title for the data set instead of a variable name.

Some Examples of Data

Economic Characteristics Data: US Census Fact Finder, General Economic Characteristics, ACS 2017

Data Image

Is this tidy data? NO!

  • This data is at the aggregate level. This data has already been synthesized and each cell means something different.
  • Each cell means something different.

————————–

Displaying Data vs. Summarizing Data

Load the Data

import numpy as np
import pandas as pd

import matplotlib.pyplot as plt
import plotly.express as px
from plotly.subplots import make_subplots
import plotly.io as pio
pio.renderers.default = 'notebook_connected'
# Download the raw data
file_location = 'https://joannabieri.com/introdatascience/data/starwars.csv'
DF_raw = pd.read_csv(file_location)
display(DF_raw)
name height mass hair_color skin_color eye_color birth_year sex gender homeworld species films vehicles starships
0 Luke Skywalker 172.0 77.0 blond fair blue 19.0 male masculine Tatooine Human A New Hope, The Empire Strikes Back, Return of... Snowspeeder, Imperial Speeder Bike X-wing, Imperial shuttle
1 C-3PO 167.0 75.0 NaN gold yellow 112.0 none masculine Tatooine Droid A New Hope, The Empire Strikes Back, Return of... NaN NaN
2 R2-D2 96.0 32.0 NaN white, blue red 33.0 none masculine Naboo Droid A New Hope, The Empire Strikes Back, Return of... NaN NaN
3 Darth Vader 202.0 136.0 none white yellow 41.9 male masculine Tatooine Human A New Hope, The Empire Strikes Back, Return of... NaN TIE Advanced x1
4 Leia Organa 150.0 49.0 brown light brown 19.0 female feminine Alderaan Human A New Hope, The Empire Strikes Back, Return of... Imperial Speeder Bike NaN
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
82 Finn NaN NaN black dark dark NaN male masculine NaN Human The Force Awakens NaN NaN
83 Rey NaN NaN brown light hazel NaN female feminine NaN Human The Force Awakens NaN NaN
84 Poe Dameron NaN NaN brown light brown NaN male masculine NaN Human The Force Awakens NaN X-wing
85 BB8 NaN NaN none none black NaN none masculine NaN Droid The Force Awakens NaN NaN
86 Captain Phasma NaN NaN none none unknown NaN female feminine NaN Human The Force Awakens NaN NaN

87 rows × 14 columns

This data should look familiar! This is the Star Wars data from a few days ago. What you see here is my raw data. The data as I first opened it. Now I am being a bit more careful, notice how I give my data the variable name

DF_raw

I want to make sure I don’t write over my raw data during my calculations!

Let’s reduce the number of variables

my_variables = ['name','height','mass']

DF_nhm = DF_raw[my_variables]

display(DF_nhm)
name height mass
0 Luke Skywalker 172.0 77.0
1 C-3PO 167.0 75.0
2 R2-D2 96.0 32.0
3 Darth Vader 202.0 136.0
4 Leia Organa 150.0 49.0
... ... ... ...
82 Finn NaN NaN
83 Rey NaN NaN
84 Poe Dameron NaN NaN
85 BB8 NaN NaN
86 Captain Phasma NaN NaN

87 rows × 3 columns

Displayed Data

If I was planning to do a lot of analysis just on these columns, I would typically create a new variable so I am sure not to overwrite or destroy my raw data. Here I gave the smaller bit of data the name:

DF_nhm

to remind me that this has the columns name, height, mass. You can name your variables anything you want, but it is nice to choose things that are clearly understood.

Even thought this is a subset of our raw data, here we are still Displaying Data and this data is Tidy. What if we did some analysis. In the cell below we will get a count of the different genders in the raw Star Wars Data.

display(DF_raw['gender'].value_counts())
gender
masculine    66
feminine     17
Name: count, dtype: int64

Summarized Data

This looks like a nice clean table! But is it Tidy? NO! In this case we are Summarizing Data. It might be nice to present this information in our analysis, but this data is aggregated. If we started with this data, we could not get back to the original data set.

Important Ideas - Data

  • To do an analysis you need to start with Raw Data and that data needs to be Tidy
  • Most of the time, our data will not be tidy, we will have to Clean and Wrangle it, keep track of all the things you do to clean the data.
  • Once we have our Tidy Raw Data it is important to not overwrite it. We must keep a record of what we do at every step.
  • Summarizing Data is an important part of data science. However, summary data is not enough to reproduce results. We must keep a record of what we do at every step.

Am I sounding like a broken record? We must keep a record of what we do at every step.

————————–

Data Wrangling

  • We start with raw data and save it in a data frame
  • The data frame stays on the left
  • We can apply operations (functions) on the left
  • The operations will not overwrite the data - so you can be experimental here and not lose your original data.

Load the raw data:

  • Data from two hotels: one resort and one city hotel
  • Observations: Each row represents a hotel booking
  • Goal for original data collection: Development of prediction models to classify a hotel booking’s likelihood to be canceled Antonia et al., 2019

Data Information: Tidy Tuesday

file_location = 'https://joannabieri.com/introdatascience/data/hotels.csv'
DF_raw_hotels = pd.read_csv(file_location)

The first thing I often want to do is just look at the data! There are lots of ways to do this:

Show the Data

The display() command shows the data frame too, and it works anywhere in a cell, not just on the last line. Later today we will learn how to sort and filter the data ourselves, which is a lot more useful than scrolling through 119,390 rows!

display(DF_raw_hotels)
hotel is_canceled lead_time arrival_date_year arrival_date_month arrival_date_week_number arrival_date_day_of_month stays_in_weekend_nights stays_in_week_nights adults ... deposit_type agent company days_in_waiting_list customer_type adr required_car_parking_spaces total_of_special_requests reservation_status reservation_status_date
0 Resort Hotel 0 342 2015 July 27 1 0 0 2 ... No Deposit NaN NaN 0 Transient 0.00 0 0 Check-Out 2015-07-01
1 Resort Hotel 0 737 2015 July 27 1 0 0 2 ... No Deposit NaN NaN 0 Transient 0.00 0 0 Check-Out 2015-07-01
2 Resort Hotel 0 7 2015 July 27 1 0 1 1 ... No Deposit NaN NaN 0 Transient 75.00 0 0 Check-Out 2015-07-02
3 Resort Hotel 0 13 2015 July 27 1 0 1 1 ... No Deposit 304.0 NaN 0 Transient 75.00 0 0 Check-Out 2015-07-02
4 Resort Hotel 0 14 2015 July 27 1 0 2 2 ... No Deposit 240.0 NaN 0 Transient 98.00 0 1 Check-Out 2015-07-03
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
119385 City Hotel 0 23 2017 August 35 30 2 5 2 ... No Deposit 394.0 NaN 0 Transient 96.14 0 0 Check-Out 2017-09-06
119386 City Hotel 0 102 2017 August 35 31 2 5 3 ... No Deposit 9.0 NaN 0 Transient 225.43 0 2 Check-Out 2017-09-07
119387 City Hotel 0 34 2017 August 35 31 2 5 2 ... No Deposit 9.0 NaN 0 Transient 157.71 0 4 Check-Out 2017-09-07
119388 City Hotel 0 109 2017 August 35 31 2 5 2 ... No Deposit 89.0 NaN 0 Transient 104.40 0 0 Check-Out 2017-09-07
119389 City Hotel 0 205 2017 August 35 29 2 7 2 ... No Deposit 9.0 NaN 0 Transient 151.20 0 2 Check-Out 2017-09-07

119390 rows × 32 columns

How many variables and observations?

In this data set we have 32 variables and 119,390 observations. That is enough data to be a bit overwhelming to just look at. Luckily Python can help!

What are the variable names (column labels?)

JupyterLab Notebook will just print the data frame if it is the last thing listed in the cell. Lets make a list of all the columns (also called keys)

columns_list = list(DF_raw_hotels.keys())
print(columns_list)
['hotel', 'is_canceled', 'lead_time', 'arrival_date_year', 'arrival_date_month', 'arrival_date_week_number', 'arrival_date_day_of_month', 'stays_in_weekend_nights', 'stays_in_week_nights', 'adults', 'children', 'babies', 'meal', 'country', 'market_segment', 'distribution_channel', 'is_repeated_guest', 'previous_cancellations', 'previous_bookings_not_canceled', 'reserved_room_type', 'assigned_room_type', 'booking_changes', 'deposit_type', 'agent', 'company', 'days_in_waiting_list', 'customer_type', 'adr', 'required_car_parking_spaces', 'total_of_special_requests', 'reservation_status', 'reservation_status_date']

How many variables do you have?

If I want to count the variables I need to count the columns. Remember I can find the length len() of the list above.

print(len(columns_list))
32

Focus on a specific column or columns (Slicing)

You just need to grab the column name (exactly spelled) and then tell the data frame to show only that column. You have seen code like this before:

DF[['column1','column2',...]]

You start with the variable name of the data frame, and then inside the square brackets you put the columns you are interested in.

For our data we will focus on the columns “hotel” and “lead_time”. Here are two ways to do this:

DF_raw_hotels[['lead_time']]
lead_time
0 342
1 737
2 7
3 13
4 14
... ...
119385 23
119386 102
119387 34
119388 109
119389 205

119390 rows × 1 columns

my_columns = ['lead_time']
DF_raw_hotels[my_columns]
lead_time
0 342
1 737
2 7
3 13
4 14
... ...
119385 23
119386 102
119387 34
119388 109
119389 205

119390 rows × 1 columns

In the first example I just listed the columns in the same line as the data frame. In the second example I stored the columns names in variable called my_columns and then gave that information to the data frame. I personally like the second way better unless I am in a hurry.

Q Can you figure out how you would focus on multiple columns?

Sorting the data

Ascending Order

Often you want numerical data sorted ascending or descending. To do that we can use the sort_values() command. Here is an example of how to sort by the column named “lead_time” in ascending order:

my_columns = ['hotel','lead_time']
DF_raw_hotels[my_columns].sort_values('lead_time')
hotel lead_time
6 Resort Hotel 0
76670 City Hotel 0
76671 City Hotel 0
76324 City Hotel 0
37343 Resort Hotel 0
... ... ...
65240 City Hotel 629
65237 City Hotel 629
65238 City Hotel 629
4182 Resort Hotel 709
1 Resort Hotel 737

119390 rows × 2 columns

Descending order

To descend the order of sorting we just add an optional flag. Can you spot the difference in the code?

DF_raw_hotels[my_columns].sort_values('lead_time', ascending=False)
hotel lead_time
1 Resort Hotel 737
4182 Resort Hotel 709
65241 City Hotel 629
65240 City Hotel 629
65242 City Hotel 629
... ... ...
17976 Resort Hotel 0
18001 Resort Hotel 0
17989 Resort Hotel 0
28495 Resort Hotel 0
28493 Resort Hotel 0

119390 rows × 2 columns

Here you start to see how we can combine command in Python. First we sub selected the columns we care about:

DF_raw_hotels[my_columns]

Then we added on the sorting

DF_raw_hotels[my_columns].sort_values('lead_time')

Each time you call a command like this it produces (returns) another data frame, but does not change the original data. So if you wanted to keep a copy of the sorted data to work with, you would have to save a copy(). Unless you are working with a HUGE data set and worried about running out of memory on your computer, you can just make copies and save them with new variable names (nicknames).

DF_leadtime_sorted = DF_raw_hotels[my_columns].sort_values('lead_time', ascending=False).copy()
display(DF_leadtime_sorted)
hotel lead_time
1 Resort Hotel 737
4182 Resort Hotel 709
65241 City Hotel 629
65240 City Hotel 629
65242 City Hotel 629
... ... ...
17976 Resort Hotel 0
18001 Resort Hotel 0
17989 Resort Hotel 0
28495 Resort Hotel 0
28493 Resort Hotel 0

119390 rows × 2 columns

Q Choose another set of variables (columns) that you can explore. Try sorting them ascending and descending.

Q What would happen if you tried to sort categorical data? Try sorting by the column “arrival_date_month”. Do you get an error? Does it sort it? If so how is it sorted?

DF_raw_hotels[[“hotel”,“arrival_date_month”]].sort_values(“arrival_date_month”)

Sub selecting specific categorical data

Maybe my analysis only concerns specific hotels. In other words I am investigating only resorts. I can easily get this information by creating a boolean mask.

A boolean mask is a fancy way to say a bunch of True and False data. Here is an example of checking whether the column named “hotel” has the categorical data “Resort Hotel” in each observation:

DF_leadtime_sorted['hotel']== 'Resort Hotel'
1         True
4182      True
65241    False
65240    False
65242    False
         ...  
17976     True
18001     True
17989     True
28495     True
28493     True
Name: hotel, Length: 119390, dtype: bool

Notice the double equals (==) this is telling Python to test the truth of the statement. What does this do

  • It goes to each observation (row) in our mini-data frame (DF_leadtime_sorted). It checks what value is in the “hotel” column and then asks “Is this equal to”Resort Hotel”?
  • For the first row it is True, second row True, third row False…
  • Go back and look at the data and convince yourself that the code is getting the right answer.

Let’s save this information in a variable

mask = DF_leadtime_sorted['hotel']== 'Resort Hotel'

Now we can give this mask to the data frame and it will only show us observations (rows) where “Resort Hotel” is true.

DF_leadtime_sorted[mask]
hotel lead_time
1 Resort Hotel 737
4182 Resort Hotel 709
8421 Resort Hotel 542
28423 Resort Hotel 542
8399 Resort Hotel 542
... ... ...
17976 Resort Hotel 0
18001 Resort Hotel 0
17989 Resort Hotel 0
28495 Resort Hotel 0
28493 Resort Hotel 0

40060 rows × 2 columns

Q Can you create a data frame that:

  1. Selects the columns “hotel”, “arrival_date_year”, and “arrival_date_month”
  2. Sorts by arrival_date_year ascending.
  3. Masks to only show the month of “August”

Summary of selecting, sorting, and masking.

What did we just do?

  1. We chose a subset of columns that we are interested in

     my_columns = ['hotel','lead_time']
 DF_raw_hotels[my_columns]

  2. Then we decided to sort this data descending

     DF_raw_hotels[my_columns].sort_values('lead_time', ascending=False)

  3. Then we made a copy of this data to work from and gave it a new name DF_leadtime_sorted

     DF_leadtime_sorted = DF_raw_hotels[my_columns].sort_values('lead_time', ascending=False).copy()

  4. Finally we decided we only care about resort hotels so we masked out all the other types of hotels

     mask = DF_leadtime_sorted['hotel']== 'Resort Hotel'
 DF_leadtime_sorted[mask]

You can do all of this analysis in one big line:

DF_raw_hotels[['hotel','lead_time']].sort_values('lead_time', ascending=False)[DF_raw_hotels['hotel']== 'Resort Hotel']
hotel lead_time
1 Resort Hotel 737
4182 Resort Hotel 709
8421 Resort Hotel 542
28423 Resort Hotel 542
8399 Resort Hotel 542
... ... ...
17976 Resort Hotel 0
18001 Resort Hotel 0
17989 Resort Hotel 0
28495 Resort Hotel 0
28493 Resort Hotel 0

40060 rows × 2 columns

But I actually think that code is more readable when you break it up a bit and add comments. Here is an example of how I would write my code:

# Here are my parameters: column names and variable info
my_columns = ['hotel','lead_time']
sort_column = 'lead_time'
hotel_type = 'Resort Hotel'

# Sub select my columns and then sort descending
DF_sorted = DF_raw_hotels[my_columns].sort_values(sort_column, ascending=False).copy()
# Do a mask so I am left with only Resort Hotels
mask = DF_sorted['hotel']== 'Resort Hotel'
DF_final = DF_sorted[mask].copy()
display(DF_final)
hotel lead_time
1 Resort Hotel 737
4182 Resort Hotel 709
8421 Resort Hotel 542
28423 Resort Hotel 542
8399 Resort Hotel 542
... ... ...
17976 Resort Hotel 0
18001 Resort Hotel 0
17989 Resort Hotel 0
28495 Resort Hotel 0
28493 Resort Hotel 0

40060 rows × 2 columns

Using Logical Operators

Above when we created a boolean mask we check if our data was equal to “Resort Hotel”, this secretly used a logical operator to test equality. We can actually test a lot of things using logical operators:

Basic Operators

Operator Definition
< less than
> greater than
<= less than or equal to
>= greater than or equal to
== exactly equal to
!= not equal to

Advanced Operators

Operator Definition
and check if two things are both true
or check if one of two things is true
in checks if something is in another thing
! not checks if something is false

Extra Advanced Ideas (optional to read)

  • In Python there are two operators for and:
    • and is a logical and operator, often represented by && in other programming languages
    • & is a bitwise and operator, often represented by & in other programming languages
  • In Python there are two operators for or:
    • or is the logical or operator, often represented by || in other programming languages
    • | is the bitwise or operator, often represented by | in other programming languages

Below are some examples. Try to figure out what will be printed before you run the cell!

3<10
True
10<3
False
3<10 and 2<10
True
'cat' == 'cat'
True
'CAT'=='cat'
False
'CAT'!='cat'
True
2<=2
True
2<=2 or 2<1
True
my_numbers = [3,6,18,42]
42 in my_numbers
True

You probably noticed that when dealing with words (strings) Python is 100% Literal, it knows the difference between upper and lower case, so ‘CAT’ and ‘cat’ are not the same word.

Q Create 5 more logical operator tests and show the results.

Logical Operators and Data - Example Analysis

Now that we know some logical operators we can create fancier boolean masks and get more interesting subsets of our data. Here is a mini analysis of our data using what we know so far. See if you can figure out what each command is doing. Add your comments explaining the code:

# Add your comments: 
DF_raw_hotels['country'].value_counts()
country
PRT    48590
GBR    12129
FRA    10415
ESP     8568
DEU     7287
       ...  
MRT        1
KIR        1
SDN        1
ATF        1
SLE        1
Name: count, Length: 177, dtype: int64

Wow there is a lot of data for PRT (Portugal)!

# Here I am defining the variables (columns) I care about
my_country = 'PRT'
my_columns = ['hotel',
              'arrival_date_year',
              'is_canceled', 
              'arrival_date_month',
              'country']
sort_column = 'arrival_date_year'

# Add your comments:
DF_mini_analysis = DF_raw_hotels[my_columns].sort_values(sort_column).copy()
display(DF_mini_analysis)
hotel arrival_date_year is_canceled arrival_date_month country
17 Resort Hotel 2015 0 July IRL
16 Resort Hotel 2015 0 July PRT
15 Resort Hotel 2015 0 July IRL
14 Resort Hotel 2015 0 July PRT
23167 Resort Hotel 2015 0 October GBR
... ... ... ... ... ...
119354 City Hotel 2017 0 August SWE
22041 Resort Hotel 2017 0 January PRT
22042 Resort Hotel 2017 0 January PRT
22043 Resort Hotel 2017 0 April PRT
119389 City Hotel 2017 0 August DEU

119390 rows × 5 columns

# What does this first mask do?
# Your comments:
mask_country = DF_mini_analysis['country']==my_country
DF_mini_analysis_masked = DF_mini_analysis[mask_country]
display(DF_mini_analysis_masked)
hotel arrival_date_year is_canceled arrival_date_month country
16 Resort Hotel 2015 0 July PRT
14 Resort Hotel 2015 0 July PRT
40061 City Hotel 2015 1 July PRT
40060 City Hotel 2015 0 July PRT
40077 City Hotel 2015 0 July PRT
... ... ... ... ... ...
119317 City Hotel 2017 0 August PRT
119357 City Hotel 2017 0 August PRT
22041 Resort Hotel 2017 0 January PRT
22042 Resort Hotel 2017 0 January PRT
22043 Resort Hotel 2017 0 April PRT

48590 rows × 5 columns

# What does this second mask do?
# Your comments:
mask_years = DF_mini_analysis_masked['arrival_date_year']>2015
DF_mini_analysis_masked = DF_mini_analysis_masked[mask_years] 
# Notice that I wrote over my data in the command above

display(DF_mini_analysis_masked)
hotel arrival_date_year is_canceled arrival_date_month country
78694 City Hotel 2016 0 January PRT
78695 City Hotel 2016 0 January PRT
78696 City Hotel 2016 0 January PRT
78697 City Hotel 2016 0 February PRT
78698 City Hotel 2016 0 February PRT
... ... ... ... ... ...
119317 City Hotel 2017 0 August PRT
119357 City Hotel 2017 0 August PRT
22041 Resort Hotel 2017 0 January PRT
22042 Resort Hotel 2017 0 January PRT
22043 Resort Hotel 2017 0 April PRT

35283 rows × 5 columns

fig = px.histogram(DF_mini_analysis_masked,
                  x='arrival_date_month',
                  color='hotel',
                  facet_col='is_canceled',
                  facet_col_wrap=1)

fig.update_layout(title='Number of Hotels Booked Each Month in Portugal',
                  title_x=0.5,
                  xaxis_title="Month",
                  autosize=False,
                  width=800,
                  height=500)
fig.show()

Q What exactly is this histogram showing and what is excluded based on the analysis we did above? Is there anything interesting about the graph? Any questions raised?

The graph above shows the number of hotels booked for each month over two years (2016 and 2017). It separates the data by whether the hotels were Resort Hotels or City Hotels. The top graph shows bookings that were canceled and the bottom shows bookings that were not canceled.

From this data I notice a few interesting things. First April, May, and June have the most canceled bookings and it seems like city hotels experience canceled bookings more than resorts. I wonder if the data could tell us more about the type of bookings that tend to be canceled during the Spring/Early summer months. It would also be interesting to compare these results to the results in other countries.

Q Now it’s your turn to do a mini analysis. Please do the following:

  1. Choose a subset of columns that you are interested in and look just at those column.
  2. Make at least one mask that will remove some of the data (eg. look only at one categorical value OR one or more numerical values.
  3. Sort the data using one of the columns.
  4. Make a histogram or bar graph of your final data set.
  5. Describe in words what you see in your graph, or if there is anything interesting there.

Excluding variables - removing one column (Extra)

There are a few ways to get rid of columns. The first would be to write down ALL the columns and leave out one. Let’s say we want to leave out information about the “agent” variable.

my_columns = ['hotel', 'is_canceled', 'lead_time', 'arrival_date_year', 
              'arrival_date_month', 'arrival_date_week_number', 
              'arrival_date_day_of_month', 'stays_in_weekend_nights', 
              'stays_in_week_nights', 'adults', 'children', 'babies', 
              'meal', 'country', 'market_segment', 'distribution_channel', 
              'is_repeated_guest', 'previous_cancellations', 
              'previous_bookings_not_canceled', 'reserved_room_type', 
              'assigned_room_type', 'booking_changes', 'deposit_type', 
              'company', 'days_in_waiting_list', 'customer_type', 
              'adr', 'required_car_parking_spaces', 'total_of_special_requests',
              'reservation_status', 'reservation_status_date']

DF_raw_hotels[my_columns]
hotel is_canceled lead_time arrival_date_year arrival_date_month arrival_date_week_number arrival_date_day_of_month stays_in_weekend_nights stays_in_week_nights adults ... booking_changes deposit_type company days_in_waiting_list customer_type adr required_car_parking_spaces total_of_special_requests reservation_status reservation_status_date
0 Resort Hotel 0 342 2015 July 27 1 0 0 2 ... 3 No Deposit NaN 0 Transient 0.00 0 0 Check-Out 2015-07-01
1 Resort Hotel 0 737 2015 July 27 1 0 0 2 ... 4 No Deposit NaN 0 Transient 0.00 0 0 Check-Out 2015-07-01
2 Resort Hotel 0 7 2015 July 27 1 0 1 1 ... 0 No Deposit NaN 0 Transient 75.00 0 0 Check-Out 2015-07-02
3 Resort Hotel 0 13 2015 July 27 1 0 1 1 ... 0 No Deposit NaN 0 Transient 75.00 0 0 Check-Out 2015-07-02
4 Resort Hotel 0 14 2015 July 27 1 0 2 2 ... 0 No Deposit NaN 0 Transient 98.00 0 1 Check-Out 2015-07-03
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
119385 City Hotel 0 23 2017 August 35 30 2 5 2 ... 0 No Deposit NaN 0 Transient 96.14 0 0 Check-Out 2017-09-06
119386 City Hotel 0 102 2017 August 35 31 2 5 3 ... 0 No Deposit NaN 0 Transient 225.43 0 2 Check-Out 2017-09-07
119387 City Hotel 0 34 2017 August 35 31 2 5 2 ... 0 No Deposit NaN 0 Transient 157.71 0 4 Check-Out 2017-09-07
119388 City Hotel 0 109 2017 August 35 31 2 5 2 ... 0 No Deposit NaN 0 Transient 104.40 0 0 Check-Out 2017-09-07
119389 City Hotel 0 205 2017 August 35 29 2 7 2 ... 0 No Deposit NaN 0 Transient 151.20 0 2 Check-Out 2017-09-07

119390 rows × 31 columns

TYPING IN ALL THOSE COLUMNS IS A PAIN!!!!!

Luckily Python can help! Here is how we can remove just one thing from a list

# First get all of them
my_columns = list(DF_raw_hotels.keys())
# Then remove the one you don't want
my_columns.remove('agent')
# Then get the new data frame
DF_raw_hotels[my_columns]
hotel is_canceled lead_time arrival_date_year arrival_date_month arrival_date_week_number arrival_date_day_of_month stays_in_weekend_nights stays_in_week_nights adults ... booking_changes deposit_type company days_in_waiting_list customer_type adr required_car_parking_spaces total_of_special_requests reservation_status reservation_status_date
0 Resort Hotel 0 342 2015 July 27 1 0 0 2 ... 3 No Deposit NaN 0 Transient 0.00 0 0 Check-Out 2015-07-01
1 Resort Hotel 0 737 2015 July 27 1 0 0 2 ... 4 No Deposit NaN 0 Transient 0.00 0 0 Check-Out 2015-07-01
2 Resort Hotel 0 7 2015 July 27 1 0 1 1 ... 0 No Deposit NaN 0 Transient 75.00 0 0 Check-Out 2015-07-02
3 Resort Hotel 0 13 2015 July 27 1 0 1 1 ... 0 No Deposit NaN 0 Transient 75.00 0 0 Check-Out 2015-07-02
4 Resort Hotel 0 14 2015 July 27 1 0 2 2 ... 0 No Deposit NaN 0 Transient 98.00 0 1 Check-Out 2015-07-03
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
119385 City Hotel 0 23 2017 August 35 30 2 5 2 ... 0 No Deposit NaN 0 Transient 96.14 0 0 Check-Out 2017-09-06
119386 City Hotel 0 102 2017 August 35 31 2 5 3 ... 0 No Deposit NaN 0 Transient 225.43 0 2 Check-Out 2017-09-07
119387 City Hotel 0 34 2017 August 35 31 2 5 2 ... 0 No Deposit NaN 0 Transient 157.71 0 4 Check-Out 2017-09-07
119388 City Hotel 0 109 2017 August 35 31 2 5 2 ... 0 No Deposit NaN 0 Transient 104.40 0 0 Check-Out 2017-09-07
119389 City Hotel 0 205 2017 August 35 29 2 7 2 ... 0 No Deposit NaN 0 Transient 151.20 0 2 Check-Out 2017-09-07

119390 rows × 31 columns

Fancy ways to get columns (EXTRA - ADVANCED)

There are fancy ways to get a list of columns that you might be interested in.

Starts with

You can use the command startswith() to check if a variable starts with a word or part of a word. Here is code to get only columns that start with the word “arrival”:

# Get the list of all the columns
columns_list = list(DF_raw_hotels.keys())
# Get true false values using the startswith() comand
column_mask = [column.startswith('arrival') for column in columns_list]
# Mask the column names to get just the ones you want and make a list
my_columns = list(DF_raw_hotels.keys()[column_mask])
print(my_columns)
['arrival_date_year', 'arrival_date_month', 'arrival_date_week_number', 'arrival_date_day_of_month']

Ends with

You can use the command endswith() to check if a variable ends with a word or part of a word. Here is code to get only columns that end with the word “type”:

# Get the list of all the columns
columns_list = list(DF_raw_hotels.keys())
# Get true false values using the startswith() comand
column_mask = [column.endswith('type') for column in columns_list]
# Mask the column names to get just the ones you want and make a list
my_columns = list(DF_raw_hotels.keys()[column_mask])
print(my_columns)
['reserved_room_type', 'assigned_room_type', 'deposit_type', 'customer_type']

IN

You can use the Python check is in to check if a variable contains a word or part of a word. Here is code to get only columns that contain the word “date”:

# Get the list of all the columns
columns_list = list(DF_raw_hotels.keys())
# Get true false values using the startswith() comand
column_mask = ["date" in column for column in columns_list]
# Mask the column names to get just the ones you want and make a list
my_columns = list(DF_raw_hotels.keys()[column_mask])
print(my_columns)
['arrival_date_year', 'arrival_date_month', 'arrival_date_week_number', 'arrival_date_day_of_month', 'reservation_status_date']