# Employee Database: A Mystery in Two Parts  ## Background It is a beautiful spring day, and it is two weeks since you have been hired as a new data engineer at Pewlett Hackard. Your first major task is a research project on employees of the corporation from the 1980s and 1990s. All that remain of the database of employees from that period are six CSV files. In this assignment, you will design the tables to hold data in the CSVs, import the CSVs into a SQL database, and answer questions about the data. In other words, you will perform: 1. Data Modeling 2. Data Engineering 3. Data Analysis ## Instructions #### Data Modeling Inspect the CSVs and sketch out an ERD of the tables. Feel free to use a tool like [http://www.quickdatabasediagrams.com](http://www.quickdatabasediagrams.com). #### Data Engineering * Use the information you have to create a table schema for each of the six CSV files. Remember to specify data types, primary keys, foreign keys, and other constraints. * Import each CSV file into the corresponding SQL table. #### Data Analysis Once you have a complete database, do the following: 1. List the following details of each employee: employee number, last name, first name, gender, and salary. 2. List employees who were hired in 1986. 3. List the manager of each department with the following information: department number, department name, the manager's employee number, last name, first name, and start and end employment dates. 4. List the department of each employee with the following information: employee number, last name, first name, and department name. 5. List all employees whose first name is "Hercules" and last names begin with "B." 6. List all employees in the Sales department, including their employee number, last name, first name, and department name. 7. List all employees in the Sales and Development departments, including their employee number, last name, first name, and department name. 8. In descending order, list the frequency count of employee last names, i.e., how many employees share each last name. ## Bonus (Optional) As you examine the data, you are overcome with a creeping suspicion that the dataset is fake. You surmise that your boss handed you spurious data in order to test the data engineering skills of a new employee. To confirm your hunch, you decide to take the following steps to generate a visualization of the data, with which you will confront your boss: 1. Import the SQL database into Pandas. (Yes, you could read the CSVs directly in Pandas, but you are, after all, trying to prove your technical mettle.) This step may require some research. Feel free to use the code below to get started. Be sure to make any necessary modifications for your username, password, host, port, and database name: ```sql from sqlalchemy import create_engine engine = create_engine('postgresql://localhost:5432/<your_db_name>') connection = engine.connect() ``` * Consult [SQLAlchemy documentation](https://docs.sqlalchemy.org/en/latest/core/engines.html#postgresql) for more information. * If using a password, do not upload your password to your GitHub repository. See [https://www.youtube.com/watch?v=2uaTPmNvH0I](https://www.youtube.com/watch?v=2uaTPmNvH0I) and [https://martin-thoma.com/configuration-files-in-python/](https://martin-thoma.com/configuration-files-in-python/) for more information. 2. Create a bar chart of average salary by title. 3. You may also include a technical report in markdown format, in which you outline the data engineering steps taken in the homework assignment. ## Epilogue Evidence in hand, you march into your boss's office and present the visualization. With a sly grin, your boss thanks you for your work. On your way out of the office, you hear the words, "Search your ID number." You look down at your badge to see that your employee ID number is 499942. ## Submission * Create an image file of your ERD. * Create a `.sql` file of your table schemata. * Create a `.sql` file of your queries. * (Optional) Create a Jupyter Notebook of the bonus analysis. * Create and upload a repository with the above files to GitHub and post a link on BootCamp Spot.

Part I - WeatherPy In this example, you’ll be creating a Python script to visualize the weather of 500+ cities across the world of varying distance from the equator. To accomplish this, you’ll be utilizing a simple Python library, the OpenWeatherMap API, and a little common sense to create a representative model of weather across world cities. Your first objective is to build a series of scatter plots to showcase the following relationships: Temperature (F) vs. Latitude Humidity (%) vs. Latitude Cloudiness (%) vs. Latitude Wind Speed (mph) vs. Latitude After each plot add a sentence or too explaining what the code is and analyzing. Your next objective is to run linear regression on each relationship, only this time separating them into Northern Hemisphere (greater than or equal to 0 degrees latitude) and Southern Hemisphere (less than 0 degrees latitude): Northern Hemisphere - Temperature (F) vs. Latitude Southern Hemisphere - Temperature (F) vs. Latitude Northern Hemisphere - Humidity (%) vs. Latitude Southern Hemisphere - Humidity (%) vs. Latitude Northern Hemisphere - Cloudiness (%) vs. Latitude Southern Hemisphere - Cloudiness (%) vs. Latitude Northern Hemisphere - Wind Speed (mph) vs. Latitude Southern Hemisphere - Wind Speed (mph) vs. Latitude After each pair of plots explain what the linear regression is modelling such as any relationships you notice and any other analysis you may have. Your final notebook must: Randomly select at least 500 unique (non-repeat) cities based on latitude and longitude. Perform a weather check on each of the cities using a series of successive API calls. Include a print log of each city as it’s being processed with the city number and city name. Save a CSV of all retrieved data and a PNG image for each scatter plot. Part II - VacationPy Now let’s use your skills in working with weather data to plan future vacations. Use jupyter-gmaps and the Google Places API for this part of the assignment. Note: if you having trouble displaying the maps try running jupyter nbextension enable --py gmaps in your environment and retry. Create a heat map that displays the humidity for every city from the part I of the homework. heatmap Narrow down the DataFrame to find your ideal weather condition. For example: A max temperature lower than 80 degrees but higher than 70. Wind speed less than 10 mph. Zero cloudiness. Drop any rows that don’t contain all three conditions. You want to be sure the weather is ideal. Note: Feel free to adjust to your specifications but be sure to limit the number of rows returned by your API requests to a reasonable number. Using Google Places API to find the first hotel for each city located within 5000 meters of your coordinates. Plot the hotels on top of the humidity heatmap with each pin containing the Hotel Name, City, and Country. hotel map As final considerations: Create a new GitHub repository for this project called API-Challenge (note the kebab-case). Do not add to an existing repo You must complete your analysis using a Jupyter notebook. You must use the Matplotlib or Pandas plotting libraries. For Part I, you must include a written description of three observable trends based on the data. You must use proper labeling of your plots, including aspects like: Plot Titles (with date of analysis) and Axes Labels. For max intensity in the heat map, try setting it to the highest humidity found in the data set. Hints and Considerations The city data you generate is based on random coordinates as well as different query times; as such, your outputs will not be an exact match to the provided starter notebook. You may want to start this assignment by refreshing yourself on the geographic coordinate system. Next, spend the requisite time necessary to study the OpenWeatherMap API. Based on your initial study, you should be able to answer basic questions about the API: Where do you request the API key? Which Weather API in particular will you need? What URL endpoints does it expect? What JSON structure does it respond with? Before you write a line of code, you should be aiming to have a crystal clear understanding of your intended outcome. A starter code for Citipy has been provided. However, if you’re craving an extra challenge, push yourself to learn how it works: citipy Python library. Before you try to incorporate the library into your analysis, start by creating simple test cases outside your main script to confirm that you are using it correctly. Too often, when introduced to a new library, students get bogged down by the most minor of errors – spending hours investigating their entire code – when, in fact, a simple and focused test would have shown their basic utilization of the library was wrong from the start. Don’t let this be you! Part of our expectation in this challenge is that you will use critical thinking skills to understand how and why we’re recommending the tools we are. What is Citipy for? Why would you use it in conjunction with the OpenWeatherMap API? How would you do so? In building your script, pay attention to the cities you are using in your query pool. Are you getting coverage of the full gamut of latitudes and longitudes? Or are you simply choosing 500 cities concentrated in one region of the world? Even if you were a geographic genius, simply rattling 500 cities based on your human selection would create a biased dataset. Be thinking of how you should counter this. (Hint: Consider the full range of latitudes). Once you have computed the linear regression for one chart, the process will be similar for all others. As a bonus, try to create a function that will create these charts based on different parameters. Remember that each coordinate will trigger a separate call to the Google API. If you’re creating your own criteria to plan your vacation, try to reduce the results in your DataFrame to 10 or fewer cities. Lastly, remember – this is a challenging activity. Push yourself! If you complete this task, then you can safely say that you’ve gained a strong mastery of the core foundations of data analytics and it will only go better from here. Good luck!

Problem Statement This assignment is a programming assignment wherein you have to build a multiple linear regression model for the prediction of demand for shared bikes. You will need to submit a Jupyter notebook for the same. Problem Statement A bike-sharing system is a service in which bikes are made available for shared use to individuals on a short term basis for a price or free. Many bike share systems allow people to borrow a bike from a "dock" which is usually computer-controlled wherein the user enters the payment information, and the system unlocks it. This bike can then be returned to another dock belonging to the same system. A US bike-sharing provider BoomBikes has recently suffered considerable dips in their revenues due to the ongoing Corona pandemic. The company is finding it very difficult to sustain in the current market scenario. So, it has decided to come up with a mindful business plan to be able to accelerate its revenue as soon as the ongoing lockdown comes to an end, and the economy restores to a healthy state. In such an attempt, BoomBikes aspires to understand the demand for shared bikes among the people after this ongoing quarantine situation ends across the nation due to Covid-19. They have planned this to prepare themselves to cater to the people's needs once the situation gets better all around and stand out from other service providers and make huge profits. They have contracted a consulting company to understand the factors on which the demand for these shared bikes depends. Specifically, they want to understand the factors affecting the demand for these shared bikes in the American market. The company wants to know: Which variables are significant in predicting the demand for shared bikes. How well those variables describe the bike demands Based on various meteorological surveys and people's styles, the service provider firm has gathered a large dataset on daily bike demands across the American market based on some factors. Business Goal: You are required to model the demand for shared bikes with the available independent variables. It will be used by the management to understand how exactly the demands vary with different features. They can accordingly manipulate the business strategy to meet the demand levels and meet the customer's expectations. Further, the model will be a good way for management to understand the demand dynamics of a new market. Data Preparation: You can observe in the dataset that some of the variables like 'weathersit' and 'season' have values as 1, 2, 3, 4 which have specific labels associated with them (as can be seen in the data dictionary). These numeric values associated with the labels may indicate that there is some order to them - which is actually not the case (Check the data dictionary and think why). So, it is advisable to convert such feature values into categorical string values before proceeding with model building. Please refer the data dictionary to get a better understanding of all the independent variables. You might notice the column 'yr' with two values 0 and 1 indicating the years 2018 and 2019 respectively. At the first instinct, you might think it is a good idea to drop this column as it only has two values so it might not be a value-add to the model. But in reality, since these bike-sharing systems are slowly gaining popularity, the demand for these bikes is increasing every year proving that the column 'yr' might be a good variable for prediction. So think twice before dropping it. Model Building In the dataset provided, you will notice that there are three columns named 'casual', 'registered', and 'cnt'. The variable 'casual' indicates the number casual users who have made a rental. The variable 'registered' on the other hand shows the total number of registered users who have made a booking on a given day. Finally, the 'cnt' variable indicates the total number of bike rentals, including both casual and registered. The model should be built taking this 'cnt' as the target variable. Model Evaluation: When you're done with model building and residual analysis and have made predictions on the test set, just make sure you use the following two lines of code to calculate the R-squared score on the test set. from sklearn.metrics import r2_score r2_score(y_test, y_pred) where y_test is the test data set for the target variable, and y_pred is the variable containing the predicted values of the target variable on the test set. Please don't forget to perform this step as the R-squared score on the test set holds some marks. The variable names inside the 'r2_score' function can be different based on the variable names you have chosen. Downloads: You can download the dataset file from the link given below: Bike Sharing Dataset Download Assignment - Data Dictionary Download Submissions Expected: Python Notebook: One Python notebook with the whole linear model, predictions, and evaluation. Subjective Questions PDF: Apart from the Python notebook, you also need to answer some subjective questions related to linear regression which can be downloaded from the file below. Answer these questions and submit it as a PDF. Note: There are some questions in the subjective questions doc that you might not be familiar with. So you're expected to research these questions and give an appropriate answer in order to expand your learnings of this topic.

Introduction : This data set is a masked data set which is similar to what data analysts at Uber handle. Solving this assignment will give you an idea about how problems are systematically solved using EDA and data visualisation. Business Understanding : You may have some experience of travelling to and from the airport. Have you ever used Uber or any other cab service for this travel? Did you at any time face the problem of cancellation by the driver or non-availability of cars? Well, if these are the problems faced by customers, these very issues also impact the business of Uber. If drivers cancel the request of riders or if cars are unavailable, Uber loses out on its revenue. As an analyst, you decide to address the problem Uber is facing - driver cancellation and non-availability of cars leading to loss of potential revenue. Business Objectives : The aim of analysis is to identify the root cause of the problem (i.e. cancellation and non-availability of cars) and recommend ways to improve the situation. As a result of your analysis, you should be able to present to the client the root cause(s) and possible hypotheses of the problem(s) and recommend ways to improve them. There are six attributes associated with each request made by a customer: 1. Request id: A unique identifier of the request 2. Time of request: The date and time at which the customer made the trip request 3. Drop-off time: The drop-off date and time, in case the trip was completed 4. Pick-up point: The point from which the request was made 5. Driver id: The unique identification number of the driver 6. Status of the request: The final status of the trip, that can be either completed, cancelled by the driver or no cars available Note: For this assignment, only the trips to and from the airport are being considered. Results Expected : 1. Visually identify the most pressing problems for Uber. Hint: Create plots to visualise the frequency of requests that get cancelled or show 'no cars available'; identify the most problematic types of requests (city to airport / airport to city etc.) and the time slots (early mornings, late evenings etc.) using plots. 2. Find out the gap between supply and demand and show the same using plots. a. Find the time slots when the highest gap exists b. Find the types of requests (city-airport or airport-city) for which the gap is the most severe in the identified time slots 3. What do you think is the reason for this issue for the supply-demand gap? Write the answer in less than 100 words. You may accompany the write-up with plot(s). 4. Recommend some ways to resolve the supply-demand gap. IDE : jupyter notebook Language : Python Libraries : Numpy, Pandas, Matplotlib, Seaborn Please do explore the dataset further to your own and see what kind of other insights you can get across various other columns.