Notebook firsts draft review

[adelnehme]

Hi @brianpiccolo :wave:

Hope you're well! In this issue, I will go over some feedback to get your live training notebook to the next level 🚀 I've divided this issue into 2 sections: Notebook (where I give feedback on the content itself, both general and section-specific) and Intangibles (things to look out for while giving the live session itself).

Notebook

General Feedback

• [x] A. Great work on this draft - I think the outline of the session really mimics a real-life data scientists' work, where learners will have to explore a dataset, identify what it is needed to be cleaned, and clean the data before performing analysis. To "add more meat" to this session, I recommend adding a couple more data cleaning steps here (so far we have 4) - these can be easy text manipulation problems (like removing trailing or leading white spaces, aligning categorical variables who have different capitalization, separating lat long fields, etc..).

• [x] B. Given A, check out this session on data cleaning in Python that has a similar outline, where the first section was a diagnosis of data cleaning problems and a second part was a range of data cleaning tasks (in your case there would be a third section on analysis with less verbose diagnosis + data cleaning 😄).

Setting up PostgreSQL

• [x] 1. Not sure if selecting the data to make sure it's loaded here makes sense since in the exact following cell you will also re-select it to grab some insights.

Exploring the Dataset

• [x] 2. 🔍 What is really intended by questioning The dataset is much smaller than you would have expected with only 281 rows. Let's think about this a bit. Do we believe it is correct? - especially since we're not adding more rows in that sense?

• [x] 3. Check out in this session how we used the Problem n: and To do list format with markdown when diagnosing and identifying data cleaning problems.

What have we learned about the short term rentals data?

• [x] 🔧 4. Pending the response of 2, the truly relevant piece of information here is just the data cleaning tasks. Then, consider using renaming this section to a to-do list format similar to this session to keep learners grounded on the tasks at hand and have more control over the narrative of the session.

Using built-in functions to manipulate string and character data*

• [x] 5. This could be removed if we elect to do the to-do list format and instead be replaced with a section called "Getting our data analysis-ready" wherein we have subsections called "Task n: Task description" and then you can have a bit of markdown text explaining the syntax used to explain how we will solve each task.

Converting data from one type to another using CAST()

• [x] 6. It could be worth combining an example query with a couple of explanatory sentences as below instead of dedicating 6 sentences to explain it CAST- since you can also explain the rationale with your voice during the session. Check out this session in the data cleaning section when describing the tasks.

Task 1: Converting issue_date to a date/time value. To convert columns from one data type to another in PostgreSQL, we can use the CAST() function as below here:

SELECT cast(column_name as new_type) 
FROM table;

Since issue_date does not contain time, we can convert it to DATE instead of TIMESTAMP.

Extracting string data using SUBSTRING() and POSTIION() (<- typo here)

• [x] 7. I'm anticipating this could be a relative source of struggle for students here, so what I recommend is having a bit more of a fleshed-out markdown where you add example syntax that will be used and where you can have an example value of the location column and then break it down verbally as well as in text. For example:

To divide strings (or extract substrings) in PostgreSQL, we can use SUBSTRING(column_name, substring_start_position, substring_end_position) where:

• column_name is the name of the column being processed
• substring_start_position is the numbered position of the first string of the substring being extracted.
• substring_end_position is the numbered position of the substring of the last string of the substring being extracted.

A common value from the location column has the format:

street_address \n city_state_zip \n lat_long

for example: 65 Pearl St\nCambridge, MA 02139\n(42.363228, 42.36322)

To divide location into substrings, will divide it based on the position of the break-line character \n by using the POSITION(character IN column_name) function which finds the numbered position of a character in a string column, as well as the char(10) function which returns \n - you can find other special characters here.

Extracting string data using split_part()

• [x] 8. Usually, I'd recommend not solving the same problem with 2 different methods in a live training since it's not really a realistic process - however since this is used when creating a table later, I think it would make a great opportunity to then introduce it as "there is actually an easier alternative to do x y z that we'll use moving forward" - moreover, this is a great place to introduce this data visualization (I uploaded it to the assets folder under string_part.png:

Using ARRAYS to manipulate strings stored as comma-separated-values

• [x] 9. Could be worth changing the markdown to align more with the style of the previous feedback in 7 and 8. One thing students may struggle with could be nested SELECT statements here - could be worth adding a note as well explaining a really simple nested select statement example like the one below:

-- A note on nested select statements
SELECT AVG(column_name) 
FROM
(
    SELECT * FROM table
)
GROUP BY column_name;

Creating a temporary table with our rental services data

• [x] 10. I think this could become a subsection of the section "Analyzing competitors and the best locations for short term rentals" and then you can go into detail about how creating a temporary table could be useful here.

Using temporary tables to simplify complex queries

• [x] 11. Could be worth adding a small visualization explaining joins as well - check this one out and adding really simple example syntax like:

SELECT table_1_alias.* , table_2_alias.* 
FROM table_1 AS table_1_alias INNER JOIN table_2 AS table_2_alias 
ON table_1_alias.common_column = table_2_alias.common_column;

User-defined functions to create reusable code

• [x] 11. Similar to how we did in 7 - I'm anticipating a bit of struggling with user-defined functions thus some explanatory text and sample syntax used would be great!

Putting it all together

• [x] 12. I recommend changing this section name to the name of the problem you're trying to solve 😄

Intangibles

• [ ] 13. I see that there have been no updates to the database and it seems that some of the cleaning tasks mentioned earlier were just done through selects and then (re-selected at a later time). Given that we do create temporary tables that's fine and dandy for most cleaning tasks, however, what is the reason of not updating the date field in rentals, or maybe adding the street_address, city_state_zip and lat_long columns as well? This does not really go against the creation of temp tables but it strikes me that not updating means we're insinuating that tables need to stay dirty and temp tables can be clean.

[brianpiccolo]

Thanks @adelnehme !! I was able to get through most of the feedback this morning so we can discuss in more detail at 10am. I uploaded a new Solution Notebook to Github - https://github.com/datacamp/string-manipulation-in-sql-live-training/blob/master/notebooks/20200527_String_maniuplation_SQL_live_solution.ipynb

Talk to you in a bit.

[brianpiccolo]

Hi @adelnehme I've implemented all of your feedback into the notebook referenced in the previous comment. Let me know if you have any additional comments or suggestions.

Thanks Brian

[brianpiccolo]

Hi @adelnehme please use this link instead:

https://github.com/datacamp/string-manipulation-in-sql-live-training/blob/master/notebooks/20200528_String_maniuplation_SQL_live_solution.ipynb

Thanks!