# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

"""
Explanation: <a href="https://colab.research.google.com/github/google/applied-machine-learning-intensive/blob/master/content/02_data/02_intermediate_pandas/colab.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>
Copyright 2020 Google LLC.
End of explanation
"""


import pandas as pd

airport_df = pd.DataFrame.from_records((
  ('Atlanta', 498044, 2),
  ('Austin', 964254, 2),
  ('Kansas City',  491918, 8),
  ('New York City', 8398748, 3),
  ('Portland', 653115, 1),
  ('San Francisco', 883305, 3),
  ('Seattle', 744955, 2),
), columns=("City Name", "Population", "Airports"))

airport_df

"""
Explanation: Intermediate Pandas
Pandas is a powerful Python library for working with data. For this lab, you should already know what a DataFrame and Series are and how to do some simple analysis of the data contained in those structures.
In this lab we'll look at some more advanced capabilities of Pandas, such as filtering, grouping, merging, and sorting.
DataFrame Information
DataFrame objects are rich containers that allow us to explore and modify data. In this lab we will learn powerful techniques for working with the data contained in DataFrame objects.
To begin, let's create a DataFrame containing information about populations and airports in a few select cities.
End of explanation
"""


airport_df.shape

"""
Explanation: If you aren't familiar with the from_records() method, it is a way to create a DataFrame from data formatted in a tabular manner. In this case we have a tuple-of-tuples where each inner-tuple is a row of data for a city.
Shape
One interesting fact about a DataFrame is its shape. What is shape?
Shape is the number of rows and columns contained in the dataframe.
Let's find the shape of the airport_df:
End of explanation
"""


url = "https://download.mlcc.google.com/mledu-datasets/california_housing_train.csv"

# Download the housing data

# Print the shape of the data

"""
Explanation: The DataFrame has a shape of (7, 3).
This means that the DataFrame has seven rows and three columns.
If you are familiar with NumPy, you probably are also familiar with shape. NumPy arrays can have n-dimensional shapes while DataFrame objects tend to stick to two dimensions: rows and columns.
Exercise 1: Finding Shape
Download the California housing data referenced below into a DataFrame, and print out the shape of the data.
Student Solution
End of explanation
"""


airport_df.columns

"""
Explanation: Columns
Speaking of columns, it's possible to ask a DataFrame what columns it contains using the columns attribute:
End of explanation
"""


for c in airport_df.columns:
  print(c)

"""
Explanation: Notice that the columns are contained in an Index object. An Index wraps the list of columns. For basic usage, like loops, you can just use the Index directly:
End of explanation
"""


type(airport_df.columns.values)

"""
Explanation: If you do need the columns in a lower level format, you can use .values to get a NumPy array:
End of explanation
"""


type(airport_df.columns.values.tolist())

"""
Explanation: If you need a basic Python list, you can then call .tolist() to get the core Python list of column names:
End of explanation
"""


import pandas as pd

url = "https://download.mlcc.google.com/mledu-datasets/california_housing_train.csv"

df = pd.read_csv(url)

# Your Code Goes Here

"""
Explanation: Exercise 2: Pretty Print Columns
The columns in the California housing dataset are not necessarily easy on the eyes. Columns like housing_median_age would be easier to read if they were presented as Housing Median Age.
In the code block below, download the California housing dataset. Then find the names of the columns in the dataset and convert them from "snake case" to regular English.  For instance housing_median_age becomes Housing Median Age and total_rooms becomes Total Rooms. Print the human-readable names one per line. You can find Python string methods that might be helpful here.
Write your code in a manner that it could handle any column name in "snake case": Underscores should be replaced by spaces. The first letter of each word should be capitalized.
Be sure to get the column names from the DataFrame.
Student Solution
End of explanation
"""


import pandas as pd

airport_df = pd.DataFrame.from_records((
  ('Atlanta', 498044, 2),
  (None, 964254, 2),
  ('Kansas City',  491918, 8), 
  ('New York City', None, 3),
  ('Portland', 653115, 1),
  ('San Francisco', 883305, None),
  ('Seattle', 744955, 2),
), columns=("City Name", "Population", "Airports"))

airport_df

"""
Explanation: Missing Values
It is common to find datasets with missing data. When this happens it's good to know that the data is missing so you can determine how to handle the situation.
Let's recreate our city data but set some values to None:
End of explanation
"""


airport_df.isna()

"""
Explanation: You can see that the population of New York and the number of airports in San Francisco are now represented by NaN values. This stands for 'Not a Number', which means that the value is an unknown numeric value. You'll also see that where 'Austin' once was, we now have a None value. This means that we are missing a non-numeric value.
If we want to ask the DataFrame what values are present or missing, we can use the isna() method:
End of explanation
"""


airport_df[airport_df['Population'].isna() | airport_df['Airports'].isna()]

"""
Explanation: Here we get True values where a data point is missing and False values where we have data.
Using this, we can do powerful things like select all columns with populations or airports that have missing data:
End of explanation
"""


airport_df[airport_df['Airports'] > 2]

"""
Explanation: Now that we know that we are missing the population of New York and the number of airports in San Francisco, we can look up that data and manually fix it.
Sometimes the fixes aren't so easy. The data might be impossible to find, or there might be so many missing values that you can't individually fix them all.
In these cases you have two options: completely remove the offending rows or columns or patch the data in some way. Throughout this course we will work with many datasets that have missing or obviously invalid values, and we will discuss mitigation strategies.
Filtering
Filtering is an important concept in data analysis and processing. When you think of filtering in the real world, you likely think of an object that blocks undesired things while allowing desired things to pass through.
Imagine a coffee filter. It stops the coffee grounds from getting into the coffee pot, but it allows the water bound to coffee's chemical compounds to pass through into your perfect brew.
Filtering a DataFrame is similar. A DataFrame contains rows of data. Some of these rows might be important to you, and some you might want to discard. Filtering allows you select only the data that you care about and put that data in a new DataFrame.
In the example below, we filter our airport_df to select only cities that have more than two airports. In return we get a DataFrame that contains only information about cities that have more than two airports.
End of explanation
"""


airport_df['Airports'] > 2

"""
Explanation: Let's deconstruct this statement. At its core we have:
python
airport_df['Airports'] &gt; 2
This expression compares every 'Airports' value in the airport_df DataFrame and returns True if there are more than two airports, False otherwise.
End of explanation
"""


has_many_airports = airport_df['Airports'] > 2

airport_df[has_many_airports]

"""
Explanation: This data is returned as a Pandas Series. The series is then used as a boolean index for the airport_df the DataFrame.
Boolean index is just a term used to refer to a Series (or other list-like structure) of boolean values used in the index operator, [], for the DataFrame. Ideally the boolean index length should be equal to the number of rows in the DataFrame being indexed. DataFrame rows that map to True values in the index are retained, while rows that map to False values are filtered out.
End of explanation
"""


print(True and False)
print(True or False)
print(not True)

"""
Explanation: If you are familiar with Boolean logic and Python, you probably know that you can create compound expressions using the or and and keywords. You can also use the keyword not to reverse an expression.
End of explanation
"""


has_many_airports = airport_df['Airports'] > 2
has_many_airports

"""
Explanation: You can do similar things in Pandas with boolean indices. However, and, or, and not don't work as expected. Instead you need to use the &amp;, |, and ! operators.

and changes to &amp;
or changes to |
not changes to !

For normal numbers in Python, these are actually the 'bitwise logical operators'. When working on Pandas objects, these operators don't perform bitwise calculations but instead perform Boolean logic.
Let's see this in action with an example. Imagine we want to find all cities with more than two airports and less than a million inhabitants. First, let's find the rows with more than two airports:
End of explanation
"""


small_cities = airport_df['Population'] < 1000000
small_cities

"""
Explanation: Now we can find the rows that represent a city with less than a million residents:
End of explanation
"""


small_but_flighty = has_many_airports & small_cities
small_but_flighty

"""
Explanation: We can then combine has_many_airports with small_cities to find small cities with a large number of airports.
To do this we first need to use the &amp; operator to combine the two Boolean tables:
End of explanation
"""


airport_df[small_but_flighty]

"""
Explanation: We can use this boolean index to select the rows from the original DataFrame that contain data about cities with fewer than one million residents and more than two airports.
End of explanation
"""


airport_df[(airport_df['Airports'] > 2) & (airport_df['Population'] < 1000000)]

"""
Explanation: In this example we broke the filter down into many steps. It could actually be performed in one expression as shown below.
End of explanation
"""


population_df = airport_df[['City Name', 'Population']]

population_df

"""
Explanation: Notice the need for parenthesis around each Boolean expression. This is because &amp; has a higher precedence than &gt; and &lt;.
The term 'filtering' is typically used when talking about rows of data. However, it is possible to filter out columns of a dataset. To filter columns simply list the columns that you do want to keep in a list and pass it to the DataFrame selector:
End of explanation
"""


population_df = airport_df[
  [col for col in airport_df.columns if col is not 'Airports']]

population_df

"""
Explanation: If a dataset has many columns, it might be easier to exclude a column using a list expansion instead of explicitly listing many columns:
End of explanation
"""


population_df = airport_df[
  [col for col in airport_df.columns if col not in {'Airports', 'Population'}]]

population_df

"""
Explanation: This works for multiple columns also:
End of explanation
"""


url = "https://download.mlcc.google.com/mledu-datasets/california_housing_train.csv"
cali_df = pd.read_csv(url)

# Your Code Goes here

"""
Explanation: Exercise 3: SoCal
Using the California housing DataFrame from the previous unit, make a new DataFrame that only contains data from the southern part of California. What is 'southern'? For the purpose of this exercise, let's say that southern California includes everything below 36 degrees latitude. 
Create a new DataFrame called socal_df containing only data points below the 36 latitude. Then print out the shape of that DataFrame.
Student Solution
End of explanation
"""


import pandas as pd

body_measurement_df = pd.DataFrame.from_records((
  (2, 83.82, 8.4),
  (4, 99.31, 16.97),
  (3, 96.52, 14.41),
  (6, 114.3, 20.14),
  (4, 101.6, 16.91),
  (2, 86.36, 12.64),
  (3, 92.71, 14.23),
  (2, 85.09, 11.11),
  (2, 85.85, 14.18),
  (5, 106.68, 20.01),
  (4, 99.06, 13.17),
  (5, 109.22, 15.36),
  (4, 100.84, 14.78),
  (6, 115.06, 20.06),
  (2, 84.07, 10.02),
  (7, 121.67, 28.4),
  (3, 94.49, 14.05),
  (6, 116.59, 17.55),
  (7, 121.92, 22.96),
), columns=("Age (yrs)", "Height (cm)", "Weight (kg)"))

body_measurement_df

"""
Explanation: Grouping Data
We can also aggregate DataFrame objects by grouping rows of data together.
For our examples we will create a DataFrame containing the ages, heights, and weights of a sample of children:
End of explanation
"""


body_measurement_df.groupby('Age (yrs)').mean()

"""
Explanation: As you can see, we have a fairly low-level dump of data. It is unsorted and is generally difficult to gain any insight from. We could group the data by age and find metrics such as the count, max, min, mean, median, and more. This information might be more easy to analyze.
In order to do this grouping, we use the groupby method on the DataFrame.
For instance, if we wanted to know the mean values for the columns for each year of age, we could run the following code:
End of explanation
"""


mean_body_measurement_df = body_measurement_df.groupby('Age (yrs)').mean()

mean_body_measurement_df.columns

"""
Explanation: We get a DataFrame sorted by the column that we chose to group by. The 'Height (cm)' and 'Weight (kg)' columns now represent the mean height and weight for each age represented in our dataset.
Looking at this data, you can now see a steady increase in height and weight as age increases, which is what you are likely to expect.
You might notice here that the 'Age (yrs)' column looks a little different. It is now not a regular column, but is instead an index column.
Let's see what this means by saving the grouped data into a new DataFrame:
End of explanation
"""


mean_body_measurement_df.index

"""
Explanation: You'll notice that 'Age (yrs)' is no longer listed as a column. In order to access the age you instead have to use the .index property of the DataFrame.
Note that we get an Int64Index object back and not a Series as we would if we referenced a single column.
End of explanation
"""


body_measurement_df.groupby('Age (yrs)').max()

"""
Explanation: We aren't restricted to just using mean(). There are many other aggregate functions that we could use, including max(), which gives us the largest sample in each grouping:
End of explanation
"""


body_measurement_df.groupby('Age (yrs)').min()

"""
Explanation: And min() which gives the smallest value in each grouping:
End of explanation
"""


body_measurement_df.groupby('Age (yrs)').agg({
    'Height (cm)': 'mean',
    'Weight (kg)': ['max', 'min'],
})

"""
Explanation: There are many other aggregate functions. You can see the entire list in the GroupBy documentation.
Sometimes performing a single aggregation across all columns is limiting. What if you want the mean of one column and the max of another? What if you want to perform multiple aggregations on one column?
You can perform different and multiple aggregations using the agg() function:
End of explanation
"""


import pandas as pd

body_measurement_df = pd.DataFrame.from_records((
  (2, 83.82, 8.4),
  (4, 99.31, 16.97),
  (3, 96.52, 14.41),
  (6, 114.3, 20.14),
  (4, 101.6, 16.91),
  (2, 86.36, 12.64),
  (3, 92.71, 14.23),
  (2, 85.09, 11.11),
  (2, 85.85, 14.18),
  (5, 106.68, 20.01),
  (4, 99.06, 13.17),
  (5, 109.22, 15.36),
  (4, 100.84, 14.78),
  (6, 115.06, 20.06),
  (2, 84.07, 10.02),
  (7, 121.67, 28.4),
  (3, 94.49, 14.05),
  (6, 116.59, 17.55),
  (7, 121.92, 22.96),
), columns=("Age (yrs)", "Height (cm)", "Weight (kg)"))

body_measurement_df

# Your Solution Goes Here

"""
Explanation: As you can see, agg() accepts a dictionary. The keys are the columns that you want to aggregate. The values are either a single aggregation function name or lists of aggregation function names.
Exercise 4: Grouping
Given the body measurement dataset in a DataFrame, group the data by 'Age (yrs)' and find the following aggregations using the agg() function:

'Age (yrs)' count
'Height (cm)' min
'Height (cm)' max
'Height (cm)' mean
'Height (cm)' standard deviation
'Weight (kg)' min
'Weight (kg)' max
'Weight (kg)' mean
'Weight (kg)' standard deviation

Student Solution
End of explanation
"""


import pandas as pd

nutrition_information_df = pd.DataFrame.from_records((
  ('Cupcake', 178, 5.26, 32.54, 1.37),
  ('Donut', 190, 10.51, 21.62, 2.62),
  ('Eclair', 267, 16.01, 24.68, 6.53),
  ('Froyo', 214, 2.94, 39.24, 9.4),
  ('Gingerbread', 130, 5, 19, 2),
  ('Honeycomb', 190, 13, 23, 2),
  ('Ice Cream Sandwich', 143, 5.6, 21.75, 2.61),
  ('Jelly Bean', 100, 0, 25, 0),
  ('KitKat', 210, 11, 27, 3),
  ('Lollipop', 110, 0, 28, 0),
  ('Marshmallow', 100, 0, 24, 1),
  ('Nougat', 56, 0.23, 12.93, 0.47),
  ('Oreo', 160, 7, 25, 1),
  ('Pie', 356, 16.5, 51, 2.85),
), columns=('Name', 'Calories', 'Fat (g)', 'Carbs (g)', 'Protein (g)'))

nutrition_information_df

"""
Explanation: Merging Data
It is common for related data to be stored in different locations. When this happens you sometimes need to merge the data into a single DataFrame in order to work with all of the data in an easy manner.
Let's take a look at some data about popular desserts. First, we have nutritional information:
End of explanation
"""


import pandas as pd

costs_df = pd.DataFrame.from_records((
  ('Cupcake', 1.24, 4.50),
  ('Donut', 0.17, 0.99),
  ('Eclair', 0.54, 2.50),
  ('Froyo', 0.78, 3.50),
  ('Gingerbread', 0.45, 0.99),
  ('Honeycomb', 1.25, 3.00),
  ('Ice Cream Sandwich', 1.21, 2.99),
  ('Jelly Bean', 0.04, 0.99),
  ('KitKat', 0.33, 1.50),
  ('Lollipop', 0.11, 1.10),
  ('Marshmallow', 0.03, 0.50),
  ('Nougat', 0.75, 1.50),
  ('Oreo', 0.78, 2.00),
  ('Pie', 0.66, 2.25),
), columns=('Name', 'Manufacturing (USD)', 'Retail (USD)'))

costs_df

"""
Explanation: We also have data about the manufacturing costs and the retail price of each of the desserts:
End of explanation
"""


pd.merge(nutrition_information_df, costs_df)

"""
Explanation: If we want to combine the data into a single DataFrame, we can merge the data:
End of explanation
"""


import pandas as pd

inventory_df = pd.DataFrame.from_records((
  ('Marshmallow', 1004),
  ('Nougat', 563),
  ('Oreo', 789),
  ('Pie', 33),
), columns=('Name', '# In Stock'))

inventory_df

"""
Explanation: Pandas searches for columns with the same name and uses those columns to match rows of data. The result is a single DataFrame with columns from the merged DataFrame objects.
What if we have yet another DataFrame that contains the inventory of desserts that we have in stock:
End of explanation
"""


pd.merge(costs_df, inventory_df)

"""
Explanation: If we want to join our inventory with our cost data to see how much earning potential we have in stock, we can join the costs_df with the inventory_df:
End of explanation
"""


pd.merge(costs_df, inventory_df, how='outer')

"""
Explanation: If we wanted we could then sum up our retail prices multiplied by inventory to see how much gross revenue potential we currently have.
Notice that we only have four desserts. What happened?
By default when merging DataFrame objects only rows that match across DataFrame objects are returned. Non-matching rows are filtered out.
We can change this by telling merge to do an outer join. This will keep all of the data in the first DataFrame passed to merge() and fill in any missing data with null values.
End of explanation
"""


import pandas as pd

costs_df = pd.DataFrame.from_records((
  ('Cupcake', 1.24, 4.50),
  ('Donut', 0.17, 0.99),
  ('Eclair', 0.54, 2.50),
  ('Froyo', 0.78, 3.50),
  ('Gingerbread', 0.45, 0.99),
  ('Honeycomb', 1.25, 3.00),
  ('Ice Cream Sandwich', 1.21, 2.99),
  ('Jelly Bean', 0.04, 0.99),
  ('KitKat', 0.33, 1.50),
  ('Lollipop', 0.11, 1.10),
  ('Marshmallow', 0.03, 0.50),
  ('Nougat', 0.75, 1.50),
  ('Oreo', 0.78, 2.00),
  ('Pie', 0.66, 2.25),
), columns=('Name', 'Manufacturing (USD)', 'Retail (USD)'))

costs_df

"""
Explanation: There are many options for merging data. You have options available to keep rows in specific DataFrames, to use different columns to join on, and much more. Check out the merge documentation to learn more.
Exercise 5: Merging DataFrame Objects
In this exercise we will answer a few questions about our dessert-making operation. In order to answer these questions, you are provided with the costs_df DataFrame, which contains names of treats and costs related to them.
The columns are:
  * Name: The name of the treat.
  * Manufacturing (USD): The cost in United States dollars to create one saleable unit of the treat.
  * Retail (USD): The price that one serving of the treat is sold for.
End of explanation
"""


inventory_df = pd.DataFrame.from_records((
  ('Marshmallow', 1004),
  ('Nougat', 563),
  ('Oreo', 789),
  ('Pie', 33),
), columns=('Name', '# In Stock'))

inventory_df

"""
Explanation: The other DataFrame that we have at our disposal is the inventory_df. This DataFrame contains information about how many of each type of treat we have in stock and ready to sell.
The columns are:
  * Name: The name of the treat.
  * # In Stock: The number of saleable units of the treat that we have.
Any treats not in inventory are assumed to be out of stock.
End of explanation
"""


# Merge the DataFrame objects
dessert_df = None

# Calculate potential profit
potential_profit = None 

# Print the potential profit

"""
Explanation: Question 1: Potential Profit
For this question we want to determine the potential profit that we can make with the items that we have in stock.

$profit = \Sigma^{t}_{i=1} n * (r - m)$

Where:

t is every type of treat in stock
n is the number of units of that treat
r is the retail price of the treat
m are the manufacturing costs for the treat

Merge inventory_df and costs_df to calculate the potential_profit. Print out the potential profit.
Student Solution
End of explanation
"""


# Merge the DataFrame objects
dessert_df = None

# Identify the missing desserts
missing_dessert_df = None

# Calculate the cost to make 100 of each of the missing treats
cost_to_make = None

# Print the cost

"""
Explanation: Question 2: Restocking Cost
There are only four different treats available for sale. We need to get some more inventory in this shop!
In this portion of the exercise we will calculate the total cost to get 100 units of each of the missing treats onto the shelves and ready to sale.
The cost is calculated with:

$cost = \Sigma^{t}_{i=1} 100 * m$

Where:

t is every type of treat NOT in stock
100 is the number of units of that treat that we'd like to make
m are the manufacturing costs for the treat

Merge inventory_df and costs_df to calculate the cost_to_make. Print out the cost.
Student Solution
End of explanation
"""


import pandas as pd

airport_df = pd.DataFrame.from_records((
  ('Atlanta', 498044, 2),
  ('Austin', 964254, 2),
  ('Kansas City',  491918, 8),
  ('New York City', 8398748, 3),
  ('Portland', 653115, 1),
  ('San Francisco', 883305, 3),
  ('Seattle', 744955, 2),
), columns=("City Name", "Population", "Airports"))

airport_df

"""
Explanation: Sorting
It is often important to sort data in order to visually examine the data for patterns and anomalies. Luckily this is easy to do in Pandas.
To start off, let's build a DataFrame to sort. For this example we will use a DataFrame containing information about cities, their populations, and the number of airports in-and-around the cities.
End of explanation
"""


airport_df.sort_values('Population')

"""
Explanation: The data seems to be sorted by City Name. If we want to sort the data by Population we can use the sort_values() method:
End of explanation
"""


airport_df.sort_values(['Airports', 'Population'])

"""
Explanation: We can see that Kansas City is the smallest city in our dataset, and New York City is the largest.
If you were thinking, Why does Kansas City have so many airports?, good for you!
This is one of the benefits we can get from viewing our data in different sorting orders. We can see that the smallest city by population has the largest number of airports. This doesn't seem right.
If we were going to be using this dataset for an actual data science project, we would want to investigate this further. We could:

Verify that Kansas City actually does have 8 airports
Verify that a few of the other cities, especially the larger ones, have so few airports
Look into how the data was collected to see if the count for Kansas City was collected differently:
Does it contain regional airports while others do not?
What counts as an airport for the city? Farm landing strips? Military bases?
How close to a city does an airport need to be to be considered an airport for that city?

You can probably think of many more questions to ask about the data and how it was collected.
When you see something that looks odd in your data, ask questions!
For now, let's get back to sorting. What if we wanted to sort by more than one column?
For instance, we can sort by the number of airports in a city and then by population:
End of explanation
"""


airport_df

"""
Explanation: Using this we can now answer questions such as What is the smallest city with two airports?
Notice that although we sorted the DataFrame, we didn't actually change the DataFrame itself:
End of explanation
"""


sorted_airport_df = airport_df.sort_values(['Airports', 'Population'])

sorted_airport_df

"""
Explanation: If we do want to save the sort order we can assign the return value of sort_values() to another variable:
End of explanation
"""


airport_df.sort_values(['Airports', 'Population'], inplace=True)

airport_df

"""
Explanation: But this doesn't modify the original DataFrame. To do that, use the inplace argument:
End of explanation
"""


import pandas as pd

airport_df = pd.DataFrame.from_records((
  ('Atlanta', 498044, 2),
  ('Austin', 964254, 2),
  ('Kansas City',  491918, 8),
  ('New York City', 8398748, 3),
  ('Portland', 653115, 1),
  ('San Francisco', 883305, 3),
  ('Seattle', 744955, 2),
), columns=("City Name", "Population", "Airports"))

airport_df

"""
Explanation: References and Copies
Both Python and Pandas strive to hide lower-level programming details from you whenever they can. However, there are some cases where you do have to be aware of how your data is being managed.
One place where this often happens is when Pandas is working indirectly with with a DataFrame.
We'll walk through some examples using the airport data we have seen many times in this lab.
End of explanation
"""


airport_df2 = airport_df

airport_df2.loc[:, 'Airports'] *= 2

airport_df

"""
Explanation: We'll start simple and assign the airport_df to another variable, airport_df2. We then try to double the number of airports in airport_df2.
What happens to airport_df and airport_df2?
End of explanation
"""


import pandas as pd

airport_df = pd.DataFrame.from_records((
  ('Atlanta', 498044, 2),
  ('Austin', 964254, 2),
  ('Kansas City',  491918, 8),
  ('New York City', 8398748, 3),
  ('Portland', 653115, 1),
  ('San Francisco', 883305, 3),
  ('Seattle', 744955, 2),
), columns=("City Name", "Population", "Airports"))

airport_df

"""
Explanation: Yikes! When we modified airport_df2 we also modified airport_df.
This actually has nothing to do with Pandas, but instead is a case where Python creates a reference to our original DataFrame instead of a copy.
When we assign airport_df to airport_df2 Python just makes airport_df2 refer to the object that is in airport_df. Both refer to the same copy of the data.
This is desirable in many cases. Your data might be big. Having many copies can consume a lot of memory and take a lot of time.
But sometimes you need to actually copy data. Let's reset our airport DataFrame and do just that.
End of explanation
"""


airport_df2 = airport_df.copy()

airport_df2.loc[:, 'Airports'] *= 2

airport_df

"""
Explanation: To make a copy of a DataFrame use the copy() method.
End of explanation
"""


airport_df2

"""
Explanation: As you can see, airport_df did not change.
But you can see below that airport_df2 did:
End of explanation
"""


many_airports_df = airport_df[airport_df['Airports'] > 2]

many_airports_df

"""
Explanation: Pandas adds an additional level of abstraction called views. Views are a way to look at the same data from a different perspective.
Let's work through an example using our airport dataset.
Say we wanted to filter to only rows with more than two airports:
End of explanation
"""


many_airports_df = airport_df[airport_df['Airports'] > 2]

many_airports_df['City Name'] = \
  many_airports_df['City Name'].apply(lambda s: s.upper())

airport_df

"""
Explanation: What is many_airports_df? Is it a new DataFrame? Does it only contain three rows of data? Are the rows separate or the same as the rows in airport_df? If we modify many_airports_df will airports_df be modified?
Let's try and see:
End of explanation
"""


many_airports_df = airport_df[airport_df['Airports'] > 2].copy()

many_airports_df['City Name'] = \
  many_airports_df['City Name'].apply(lambda s: s.upper())

airport_df

"""
Explanation: We didn't modify airport_df, so we must be working with a copy.
We did get a warning though:

SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

In this case Pandas created a copy of the data, but it was uncertain if we wanted to modify the copy or the original DataFrame.
Warnings are typically a bad sign. We can get rid of the warning by being explicit about what we want to do.
If we want to copy the data into a new DataFrame, we can use .copy():
End of explanation
"""


has_many_airports = airport_df['Airports'] > 2

airport_df.loc[has_many_airports, 'City Name'] = \
  airport_df.loc[has_many_airports, 'City Name'].apply(lambda s: s.upper())

airport_df

"""
Explanation: And if we want to not copy the data and to modify the original we need to index into airport_df for the modification:
End of explanation
"""


import pandas as pd

nutrition_information_df = pd.DataFrame.from_records((
      ('Cupcake', 178, 5.26, 32.54, 1.37),
      ('Donut', 190, 10.51, 21.62, 2.62),
      ('Eclair', 267, 16.01, 24.68, 6.53),
      ('Froyo', 214, 2.94, 39.24, 9.4),
      ('Gingerbread', 130, 5, 19, 2),
      ('Honeycomb', 190, 13, 23, 2),
      ('Ice Cream Sandwich', 143, 5.6, 21.75, 2.61),
      ('Jelly Bean', 100, 0, 25, 0),
      ('KitKat', 210, 11, 27, 3),
      ('Lollipop', 110, 0, 28, 0),
      ('Marshmallow', 100, 0, 24, 1),
      ('Nougat', 56, 0.23, 12.93, 0.47),
      ('Oreo', 160, 7, 25, 1),
      ('Pie', 356, 16.5, 51, 2.85),
), columns=('Name', 'Calories', 'Fat (g)', 'Carbs (g)', 'Protein (g)'))

# Update 'Lollipop' and 'Jelly Bean' calories by 10%

"""
Explanation: Exercise 6: Updating Calories
We just learned that the calorie count for our candy shop's jelly beans and lollipops is 10% too low. We need to update the calorie count for these two treats.
Below you'll find the nutrition_information_df which contains nutritional information about our treats. Write some code to increase the calories for 'Jelly Bean' and 'Lollipop' by 10%. Be sure that the data stored in nutrition_information_df is updated.
Be sure that no warnings are issued!
Student Solution
End of explanation
"""


import pandas as pd

traffic_df = pd.DataFrame.from_records((
      ('43 Crescent Way', 2036),
      ('1001 Main St.', 1399),
      ('235 Pear Lane', 1386),
      ('199 Forest Way', 1295),
      ('703 Grove St.', 1154),
      ('55 Orchard Blvd.', 1022),
      ('202 Pine Drive', 968),
      ('98 Mountain Circle', 730),
      ('2136 A St.', 729),
      ('3430 17th St.', 504),
      ('7766 Ocean Ave.', 452),
      ('1797 Albatross Ct.', 316),
), columns=('Location', 'Traffic'))

traffic_df

"""
Explanation: Additional Exercises
Exercise 7: Retail Data
You have been hired to organize a small-town retail chain's data and report to them which of their stores have the most effective marketing, measured by how many dollars of merchandise are sold per visitor.
To accomplish this you are given access to two tables of data.
The first table keeps track of the average daily traffic to each store. We store it in traffic_df:
End of explanation
"""


locations_df = pd.DataFrame.from_records((
  ('43 Crescent Way', 6832),
  ('55 Orchard Blvd.', 13985),
  ('98 Mountain Circle', 3956),
  ('199 Forest Way', 572),
  ('202 Pine Drive', 3963),
  ('235 Pear Lane', 25653),
  ('703 Grove St.', 496),
  ('1001 Main St.', 38532),
  ('1797 Albatross Ct.', 26445),
  ('2136 A St.', 34560),
  ('3430 17th St.', 1826),
  ('7766 Ocean Ave.', 5124),
), columns=('Location', 'Revenue'))
  
locations_df

"""
Explanation: The second table contains the average revenue from each store. We store in it locations_df:
End of explanation
"""


# Part 1: Perform merge
performance_df = None # ...

# Part 2: Create column
# ...

# Part 3: Print location of store with the most revenue per customer
# ...

"""
Explanation: Given the two DataFrame objects mentioned above, perform the following tasks:


Merge the two dataframes to create a single dataframe with store names: average daily traffic and average daily revenue. Call this new DataFrame performance_df.


Make a new column in performance_df, showing the average daily revenue per customer. Call the new column 'Revenue per Customer'. Revenue per customer is defined as rpc = revenue / traffic.


Print the 'Location' of the store that has the highest 'Revenue per Customer'.
End of explanation
"""


