TA’s Session TDS – 2024 10 27 19 55 IST – Recording#

Duration: 1h 45m

Here’s an FAQ summary of the TDS live tutorial:

Q1: What is the main focus of Week 4’s content?

A1: Week 4 is primarily about data analysis. We’ll be covering topics like Excel, Pandas, Parquet files, SQL, and database interactions, with a specific focus on the GA4 assignment.

Q2: What should I know about the Excel-related content?

A2: You should at least be aware of the functions and concepts covered in the videos regarding Excel for correlation analysis, regression, and forecasting. Even if you don’t have Excel itself, understanding what the functions do and how they work is important. I won’t be repeating the Excel content since it’s already detailed in the videos.

Q3: Why is Pandas frequently discussed and important?

A3: Pandas is extremely useful for almost any data analysis task, so it’s a recurring topic. It’s considered the de facto industry tool for a lot of data analysis, and we’ll be using it again today for the GA4 assignment.

Q4: What are Parke files, and why are they relevant?

A4: Parke files are a newer file format compared to CSV. Their specialty is that they are designed for handling data on a column-wise basis, unlike CSVs which are row-wise. This makes them significantly more efficient for storage and processing, especially with large datasets, due to better performance and compression benefits. They are very much in vogue right now for dealing with big data.

Q5: Do I need any special libraries to work with Parke files?

A5: If you’re using VS Code for Parke files, you might need to install a library called pyarrow (p-y-arrow). Google Colab usually comes with most important libraries pre-installed, so it should work there directly.

Q6: What’s a good resource for learning SQL basics?

A6: I highly recommend using SQL Zoo (sqlzoo.net). It’s a very useful resource for learning the fundamentals of SQL. You don’t need to become an expert, but understanding the basics is important, especially since the GA4 assignment includes some SQL tasks.

Q7: What is DuckDB, and why is it special?

A7: DuckDB is a “mind-blowing” database that is very fast for data analysis. It boasts immense capabilities beyond many other tools.

Q8: If DuckDB is so good, why not use it for everything?

A8: DuckDB doesn’t adhere to ACID properties (Atomicity, Consistency, Isolation, Durability) like traditional databases. These properties are crucial for transactional processing, ensuring data integrity and reliability. Since DuckDB doesn’t focus on these, it’s not suitable for main, day-to-day transactional databases where data manipulation needs strict guarantees.

Q9: When should I use DuckDB if it lacks ACID properties?

A9: DuckDB is excellent for analysis. When you’re analyzing data, you’re typically not manipulating it, so transactional integrity isn’t a primary concern. It’s incredibly fast and efficient for running analytical queries on large datasets. Usually, you’d take a copy of your main database’s data and run DuckDB on that copy for analysis, not directly on the live operational database. It’s a tool worth learning for its value in a data science career.

Q10: Did we all attempt the GA4 assignment?

A10: Yes, it seems everyone at least attempted the assignment, which is good.

Q11: How do I convert a decimal time column (like scheduled departure) into separate hours and minutes columns?

A11: You can use Pandas’ apply function with a lambda expression for data transformation.

For hours: df['hours'] = df['scheduled_departure'].apply(lambda x: int(x)) This takes the decimal value (e.g., 23.98) and extracts just the integer part (23).
For minutes: df['minutes'] = df['scheduled_departure'].apply(lambda x: (x - int(x)) * 60) This removes the integer part (x - int(x) = 0.98) and then multiplies by 60 to get minutes (0.98 * 60 ≈ 59 minutes). These steps aren’t always strictly necessary for problems but are useful for data exploration and transformations.

Q12: How do I check for null values in my DataFrame?

A12: You can use df.isnull().sum() to get a count of null values in each column. This is good practice for understanding your data.

Q13: How do I find the Pearson correlation between departure_delay and arrival_delay after filtering my data?

A13: First, ensure your data is filtered correctly (e.g., distance < 1000 miles and scheduled_departure between 15 and < 21). Then, you can use the corr method on your DataFrame: temp_df.corr(method='pearson'). This will give you a correlation matrix. Locate the intersection of departure_delay and arrival_delay in this matrix to find the Pearson correlation coefficient. For instance, you might find a value like 0.9529.

Q14: How can I calculate percentiles and identify outliers for a column like arrival_delay?

A14: Pandas has a describe() function that gives you descriptive statistics, including quartiles (25th, 50th/median, 75th percentiles). To manually calculate:

Quartiles (Q1, Q3): Use np.quantile(column, 0.25) for Q1 and np.quantile(column, 0.75) for Q3.
Interquartile Range (IQR): IQR = Q3 - Q1.
Lower Bound: Q1 - 1.5 * IQR.
Upper Bound: Q3 + 1.5 * IQR. Outliers are any values in the column that are less than the lower bound or greater than the upper bound.

Q15: How do I use SQL queries within Pandas in Google Colab?

A15: 1. First, make sure you have the necessary libraries. SQLAlchemy is commonly used to create a database engine, and you might need a specific driver like pymysql (which you can pip install pymysql if not present). 2. Create a connection engine using create_engine from SQLAlchemy, providing a database-specific connection string (e.g., mysql+pymysql://user:password@host/database). 3. Formulate your SQL query as a string (e.g., sql_query = "SELECT * FROM general_info"). 4. Use pd.read_sql(sql_query, connection_engine) to execute the query and load the results directly into a Pandas DataFrame. (Note: During the demo, there were issues connecting to a localhost database from Google Colab. A common solution is to ensure your connection string points to the correct, accessible host and credentials.)

Q16: How do I calculate the percentage of restaurants that serve a certain food type in each city (e.g., “Cafe”)?

A16: This requires grouping and merging data:

Get total restaurants per city: Use df.groupby('city')['restaurant_name'].count().reset_index() to create a DataFrame (city_totals) with the total number of restaurants in each city.
Get counts of the specific food type per city: Filter your main DataFrame for the desired food_type (e.g., ‘Cafe’), then groupby('city')['restaurant_name'].count().reset_index() to get a DataFrame (city_cafe) with the count of that food type per city.
Merge the two DataFrames: Use pd.merge(city_totals, city_cafe, on='city', how='left'). A left merge is often suitable here because you want to keep all cities from city_totals and add the cafe counts.
Handle missing values (NaNs): If a city has no cafes, the merged table will show NaN for the cafe count. Use df.fillna(0, inplace=True) to replace these NaNs with 0s, as it means zero cafes.
Calculate percentage: Add a new column: df['percent'] = (df['cafe_count'] / df['total_restaurants']) * 100.

Q17: What are the key takeaways about pd.groupby?

A17: 1. You can group by one or more columns (e.g., groupby('city') or groupby(['city', 'food_type'])). 2. You must specify an aggregation function (e.g., count(), mean(), sum(), avg(), or other mathematical/statistical functions) after groupby to tell Pandas what to do with the grouped data. 3. Grouping can be done on multiple levels, which helps in complex analyses.

Q18: How do pd.merge joins (left, right, inner, outer) work?

A18: pd.merge is used to combine two DataFrames based on common columns. The how parameter specifies the type of join:

left join: Keeps all rows from the left DataFrame. If there’s a match in the right DataFrame, it includes the right’s columns. If no match, it fills with NaN.
right join: Keeps all rows from the right DataFrame. If there’s a match in the left DataFrame, it includes the left’s columns. If no match, it fills with NaN.
inner join: Keeps only rows where there’s a match in both DataFrames.
outer join: Keeps all rows from both DataFrames. If there are no matches, it fills with NaN.

Q19: How can I verify my data transformations and merges, especially with complex operations?

A19: It’s often helpful to break down complex problems into smaller, verifiable steps. This allows you to:

Debug: Identify where an error might be occurring.
Trace: Understand exactly what’s happening at each stage of the transformation.
Verify: Confirm that intermediate results are correct before proceeding. While a single, complex line of code might seem “clever,” breaking it down into multiple lines with intermediate DataFrames makes the process more transparent and easier to manage, especially for ROE.

Q20: What is the issue with Question 13 on Project 1 regarding the “@” symbol and bio length?

A20: This question is currently ambiguous. We’ve emailed Anand for clarification but haven’t received a definitive answer yet. We also encountered issues internally when trying to solve it. My current hypothesis is that the “@” symbol might cause parsing difficulties on the server-side (as Anand uses JavaScript for his server scripts). Regarding “bio length,” Anand previously indicated just character length, but there’s a suggestion it might be “word splits.” This detail needs clarification. Advice: Submit your best logical answer. If your logic is sound and there are multiple valid interpretations, we typically give credit. We will provide a definitive answer on Discourse as soon as Anand clarifies.

Q21: Was today’s session helpful?

A21: Yes, it was definitely helpful, and I learned some useful things today!