<jupyter_start><jupyter_text>Case Study  Part 1*To be completed at the conclusion of Day 1* For the following exercises, you should use the data stored at `../data/companies.csv`You aren't expected to finish all the exercises; just get through as many as time allows and we will review them together.1. Start by becoming familiar with the data. How many rows and how many columns does it have? What are the data types of the columns?<jupyter_code># Load data
import pandas as pd
companies = pd.read_csv('../data/companies.csv')
companies.head()
# Count of rows and columns
companies.shape
# Data types
companies.dtypes<jupyter_output><empty_output><jupyter_text>2. Set the data's index to be the "Symbol" column.<jupyter_code># Remember to assign the result of companies.set_index() back into the companies variable
companies = companies.set_index('Symbol')
companies<jupyter_output><empty_output><jupyter_text>3. Look up the company with the symbol NCLH. What company is this? What sector is it in?<jupyter_code># Get the row with index "NCLH"
companies.loc['NCLH']<jupyter_output><empty_output><jupyter_text>4. Filter down to companies that *either* in the "Consumer Discretionary" or the "Consumer Staples" sectors.<jupyter_code># Write a condition to see which rows are in these two sectors. "|" means "OR"
condition = (companies['Sector'] == 'Consumer Discretionary') | (companies['Sector'] == 'Consumer Staples')
# Filter the data down based on this.
matching_companies = companies[condition]
matching_companies.head()
# Or, more concisely, we can use .isin to test if values are in a list of things.
condition = companies['Sector'].isin(['Consumer Discretionary', 'Consumer Staples'])
# Filter the data down based on this.
matching_companies = companies[condition]
matching_companies.head()<jupyter_output><empty_output><jupyter_text>5. How many companies are left in the data now?<jupyter_code>matching_companies.count()<jupyter_output><empty_output><jupyter_text>6. Create a new column, "Symbol_Length", that is the length of the symbol of each company. *Hint: you may need to reset an index along the way.*<jupyter_code># First, we need to move Symbol out of the index and back to being a regular column.
companies = companies.reset_index()
companies.head()
# The .str.len() method returns the length of each element in a column
companies['Symbol_Length'] = companies['Symbol'].str.len()
companies.head()<jupyter_output><empty_output><jupyter_text>7. Find the company named "Kroger Co.". Change its name to "The Kroger Company".<jupyter_code># First, let's figure out the syntax to get the cell where "Kroger Co." is stored.
# Simultaneously filter by that name AND select the Name column:
companies.loc[companies['Name'] == 'Kroger Co.', 'Name']
# With that syntax, we can use assignment to *overwrite it*.
companies.loc[companies['Name'] == 'Kroger Co.', 'Name'] = 'The Kroger Company'
# Let's look up that name in the data to make sure it's been updated.
companies.loc[companies['Name'] == 'The Kroger Company']<jupyter_output><empty_output><jupyter_text>**Bonus**: *For these two exercises, you won't find examples of the solution in our notebooks.You'll need to search for help on the internet.**Don't worry if you aren't able to solve them.*1. Filter down to companies whose symbol starts with A. How many companies meet this criterion?<jupyter_code># .str.startswith() returns True/False for each element in a column
a_companies = companies.loc[companies['Symbol'].str.startswith('A')]
a_companies.shape<jupyter_output><empty_output><jupyter_text>2. What is the longest company name in the dataset? You could just search the data visually, but try to find a programmatic solution.<jupyter_code># First, let's make a column representing name length
companies['Name Length'] = companies['Name'].str.len()
# Then get the max of this column (the length of the longest name)
max_len = companies['Name Length'].max()
max_len
# Now with that number, let's see which row in the data it came from
companies.loc[companies['Name Length'] == max_len]<jupyter_output><empty_output><jupyter_text>Part 2*To be completed at the conclusion of Day 2* This section again uses the data at `../data/companies.csv`.1. Re-create the "Symbol_Length" column (see above).<jupyter_code>import pandas as pd
companies = pd.read_csv('../data/companies.csv')
companies.head()
# Create the Symbol_Length column
companies['Symbol_Length'] = companies['Symbol'].str.len()
companies.head()<jupyter_output><empty_output><jupyter_text>2. What is the average symbol length of companies in the data set?<jupyter_code>companies['Symbol_Length'].mean()<jupyter_output><empty_output><jupyter_text>3. What is the average symbol length by sector? That is, after grouping by sector, what is the average symbol length for each group?<jupyter_code>companies.groupby('Sector', as_index=False).agg({'Symbol_Length': 'mean'})<jupyter_output><empty_output><jupyter_text>4. How long is the longest company name? How long is the longest company name by sector?<jupyter_code># Longest company name ... start by adding a column for name length.
companies['Name Length'] = companies['Name'].str.len()
# Longest name:
companies['Name Length'].max()
# Longest name by sector -- groupby sector and aggregate with the max name length
lengths = companies.groupby('Sector', as_index=False).agg({'Name Length': 'max'})
pd.merge(lengths, companies, on=['Name Length', 'Sector'])<jupyter_output><empty_output><jupyter_text>Now open the pricing data at `../data/prices.csv`.Note that this data is entirely fabricated and does not exhibit the qualities of real stock market data!1. Become familiar with this data. What is its shape? What are its data types?<jupyter_code>prices = pd.read_csv('../data/prices.csv')
prices.head()
# Shape
prices.shape
# Data types
prices.dtypes<jupyter_output><empty_output><jupyter_text>2. Get summary metrics (count, min, max, standard deviation, etc) for both the Price and Quarter columns. *Hint: we saw a method of DataFrames that will do this for you in a single line.*<jupyter_code># .describe() gets lots of metrics on all numeric columns
prices.describe()<jupyter_output><empty_output><jupyter_text>3. Perform an inner join between this data set and the companies data, on the Symbol column.<jupyter_code># The default type of join is inner, so we don't need to specify that
joined = pd.merge(prices, companies, on='Symbol')
joined<jupyter_output><empty_output><jupyter_text>4. How many rows does our data have now?<jupyter_code>joined.shape<jupyter_output><empty_output><jupyter_text>5. What do you think this data represents? Form a hypothesis and look through the data more carefully until you are confident you understand what it is and how it is structured.<jupyter_code># You could inspect this data through filtering and aggregation. It's stock prices over 4 quarters -- rows are unique by Symbol-Quarter.
# (Also the prices are entirely fabricated, they're not real stock data)<jupyter_output><empty_output><jupyter_text>6. Group the data by sector. What is the average first quarter price for a company in the Real Estate sector? What is the minimum fourth quarter price for a company in the Industrials sector?<jupyter_code># If we need average for Sector AND Quarter, we need to group by both.
# We can do multiple aggregations at once by providing a list of aggregations in our dictionary.
grouped = joined.groupby(['Sector', 'Quarter'], as_index=False).agg({'Price': ['mean', 'min']})
grouped
# Now we filter to the rows we're interested in. We can write a condition for each and apply them both with an OR (|)
is_q1_real_estate = (grouped['Sector'] == 'Real Estate') & (grouped['Quarter'] == 1)
is_q4_industrials = (grouped['Sector'] == 'Industrials') & (grouped['Quarter'] == 4)
grouped[is_q1_real_estate | is_q4_industrials]
# From the above we have our answers: $232 and $56, respectively<jupyter_output><empty_output><jupyter_text>7. Filter the data down to just prices for Apple, Google, Microsoft, and Amazon.<jupyter_code># First, let's look for these in our data to make sure we know their names exactly
joined[joined['Name'].str.contains('Apple')]
joined[joined['Name'].str.contains('Microsoft')]
joined[joined['Name'].str.contains('Amazon')]
# From the above, we know the full company names are:
# - Apple Inc.
# - Microsoft Corp.
# - Amazon.com Inc.
# But Google isn't in here!?
joined[joined['Name'].str.contains('Google')]
# Or is it?
joined[joined['Name'].str.contains('Alphabet')]
big4_names = ['Apple Inc.', 'Microsoft Corp.', 'Amazon.com Inc.', 'Alphabet Inc Class A', 'Alphabet Inc Class C']
big4 = joined[joined['Name'].isin(big4_names)]
big4<jupyter_output><empty_output><jupyter_text>8. Save this data as big_4.csv in the `../data` directory.<jupyter_code># Since we have a default index (nothing useful in there), remember not to save it.
big4.to_csv('../data/big_4.csv', index=False)<jupyter_output><empty_output><jupyter_text>9. Using Seaborn, plot the price of these companies over 4 quarters. Encode the quarter as the x-axis, the price as the y-axis, and the company symbol as the hue.<jupyter_code>import matplotlib.pyplot as plt
import seaborn as sns
# I'd recommend a lineplot here as we're looking at change over time.
sns.lineplot(data=big4, x='Quarter', y='Price', hue='Name');
# You can expand the figure size to make it clearer
plt.figure(figsize=(15, 10))
sns.lineplot(data=big4, x='Quarter', y='Price', hue='Name');<jupyter_output><empty_output><jupyter_text>**Bonus**:This data is in a form that is useful for plotting.But in this shape, it would be quite difficult to calculate the difference between each company's fourth quarter price and its first quarter price.Reshape this data so it is of a form like the below:| Symbol | Name | Sector | Q1 | Q2 | Q3 | Q4 ||--------|------|--------|----|----|----|----|| AAPL   | Apple Inc. | Information Technology | 275.20 | 269.96 | 263.51 | 266.07From which we could easily calculate Q4 - Q1.*You will probably want to google something like "python reshaping data". This is a very challenging problem!*<jupyter_code># It might require some googling to discover, but the method of a DFs that we need is .pivot...
pivoted = joined.pivot(index=['Symbol', 'Name', 'Sector'], columns='Quarter', values='Price')
pivoted
# This is really close, but if we wanted to *exactly* match the example row, we'd need to do a couple things:
# First, move these columns out of the index make them regular columns again:
reindexed = pivoted.reset_index()
reindexed
# Then, rename the columns (1, 2, 3, 4) -> (Q1, Q2, Q3, Q4)
final = reindexed.rename(columns={
    1: 'Q1',
    2: 'Q2',
    3: 'Q3',
    4: 'Q4'
})
final<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1119716338527573, 'fraction_numerical': 0.008771111318465988, 'mean_word_length': 5.547342700061088, 'pattern_counts': {'":': 0, '<': 93, '<?xml version=': 0, '>': 94, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '389822', 'license': 'MIT', 'n_tokens_mistral': 3392, 'n_tokens_neox': 3240, 'n_words': 1264, 'path': 'notebooks/Case-Study-Solutions.ipynb', 'repo_name': 'uc-python/intro-python-datasci', 'size': '10717'}