2025-02-18 Week 6 - Session 1 - TDS Jan 25#

Duration: 2h 8m

Here’s the FAQ for the provided audio:

Q1: Is the recording going?

A1: Yes, I guess the recording is up, and let’s get started.

Q2: What is this entire week about?

A2: This entire week is about data preprocessing and cleaning up data.

Q3: What will we be doing in this particular session?

A3: We’ll be focusing on data cleaning in Excel. It’s a really simple task.

Q4: Can you explain how to work with the Excel file?

A4: I’ll download the Excel file, open it, and show you a few functions.

Q5: What’s the main goal in this question?

A5: You need to clean up the columns and calculate the total margin. The formula for total margin has to be used with this data.

Q6: What’s the first step before applying the formula?

A6: Before applying the formula, you have to apply some filters. And before applying those filters, you have to clean up the data in these columns.

Q7: How is total margin calculated?

A7: Total margin requires the total sales and the total cost. You need to sum these two columns.

Q8: What is the first filter we need to apply?

A8: The first filter is the time filter. It requires all sales that occurred up to and including May 8, 2023.

Q9: How should we approach the time filter given the date column?

A9: The date column contains dates in two formats: MM-DD-YYYY and YYYY/MM/DD. You’ll notice that Excel has a DATEVALUE function that converts a date in text format to a date format. You can use this function.

Q10: I used the DATEVALUE function, but I’m getting errors and numerical values. Why is that?

A10: If you’re getting numerical values, that’s expected. Dates in Excel are stored as numerical values, which can then be converted to a date-time format. The numerical value represents the number of days that have passed since a specific date, which I believe was January 1, 1900.

Q11: How do I convert these numerical date values to a standard date format?

A11: Simply select the entire column with the numerical values and apply the “Short Date” format. This will convert them into a readable date format.

Q12: Why am I getting “VALUE!” errors for some dates, specifically those with hyphens?

A12: The DATEVALUE function by default assumes the format is DD-MM-YYYY. However, for the dates causing errors, the format is MM-DD-YYYY. So when the “day” part exceeds 12 (like “17”), it results in an error because there’s no 17th month. The function is converting the date incorrectly.

Q13: How can we fix this issue where Excel assumes the wrong date format for hyphenated dates?

A13: You need to write an Excel function to debug this. First, you can use the FIND function to locate the hyphen in the date string. If FIND returns a number, it means a hyphen is present.

Q14: How can I classify if a date has a hyphen (Yes/No)? The FIND function only gives me the position.

A14: You can use the ISNUMBER function to convert the result of FIND into a true/false (Boolean) value. If FIND returns a number (meaning a hyphen is found), ISNUMBER will return TRUE. If FIND returns an error (meaning no hyphen), ISNUMBER will return FALSE.

Q15: What should I do once I have a TRUE or FALSE value indicating the presence of a hyphen?

A15: If TRUE (a hyphen is found), you’ll need to process the date using a different method to ensure correct conversion (as it’s MM-DD-YYYY). If FALSE (no hyphen), the DATEVALUE function should work correctly.

Q16: How do I extract the year, month, and day parts from a date string to reconstruct it correctly?

A16: You can use string manipulation functions: LEFT extracts characters from the left, RIGHT from the right, and MID from a specified starting position for a certain number of characters. For example, to get ‘17’ from ‘12-17-2022’, you’d use MID(text, 4, 2).

Q17: Once I have the separate year, month, and day values, how do I reconstruct them into a correct date?

A17: You can use the DATE function. It takes three arguments: DATE(year, month, day). Simply pass your extracted year, month, and day values to it, and it will give you the correctly formatted date.

Q18: How do I apply this logic to the entire date column to handle both hyphenated and non-hyphenated dates?

A18: You can use an IF statement. If ISNUMBER(FIND("-", date_cell)) is TRUE, then use the DATE function with the string manipulation functions (MID, LEFT, RIGHT) to extract parts in MM-DD-YYYY order. Otherwise (if ISNUMBER is FALSE), use the DATEVALUE function.

Q19: My Excel version seems to automatically handle the different date formats without errors. Is this expected?

A19: Yes, it’s possible your Excel version, or Google Sheets, has more advanced parsing capabilities that automatically correct such inconsistencies. That’s a good thing! However, it still converts hyphenated dates to MM/DD/YYYY which might not be the intended output if DD-MM-YYYY was desired.

Q20: After converting my dates, some of them are still wrong, specifically the ones that use a slash (/). My hyphenated dates are correct now. Why is that?

A20: It seems your Excel is converting all dates, including the slash ones, to MM/DD/YYYY. So if your original slash dates were in DD/MM/YYYY, they would now be incorrect. This means you have an issue with the slash-separated dates being misinterpreted, just as the hyphenated ones were initially. You’ll need to apply a similar IF logic for slash-separated dates if their format is also different from what DATEVALUE expects.

Q21: How do I remove extra spaces and separate currency codes from values?

A21: For extra spaces, use the TRIM function. It removes leading, trailing, and excessive spaces between words. To separate currency codes (like “USD”) from numerical values, you can use the “Text to Columns” feature in Excel, which is basic Excel functionality.

Q22: I need to process a text file containing IDs and other data. How do I clean it up in VS Code?

A22: You’ll need to remove extra spaces, hyphens, colons, “marks”, and new-line characters. Use the “Find and Replace” feature in VS Code (Ctrl+H).

1. Remove extra spaces: Replace double spaces with single spaces, then triple with single, etc., until only single spaces remain.
2. Remove “marks”: Find “marks” and replace it with an empty string. You can select “marks” and use Ctrl+Shift+Right Arrow to select everything after it, then delete.
3. Remove new-line characters: Find \n (or \r\n for Windows) and replace it with an empty string.
4. Remove hyphens/colons: Find specific patterns like “space-dash” or “double-colon” and replace them with a single dash or an empty string, based on desired output.

Q23: How do I convert the cleaned text file (TXT) into a CSV file?

A23: You can use an online TXT to CSV converter. Simply upload your TXT file, and it will convert it. It’s recommended over converting directly in file explorer to avoid encoding errors.

Q24: After converting to CSV, how do I separate columns based on a delimiter?

A24: Use Excel’s “Text to Columns” feature. Select the column, specify the delimiter (e.g., dash -), and it will split the data into multiple columns.

Q25: What is OpenRefine, and what is its purpose?

A25: OpenRefine is a tool for data preparation, cleaning, and preprocessing. It’s designed to make data manipulation easier, especially for messy data. It even incorporates deep learning concepts to solve problems quickly.

Q26: How do I launch OpenRefine?

A26: OpenRefine doesn’t have a direct GUI application. You need to run a command (usually via a batch file or terminal) to start its server. Once the server is running, you access its interface through your web browser.

Q27: How can OpenRefine help with data cleaning, like consolidating similar items?

A27: OpenRefine’s strength lies in its “cluster” feature. You can select a column (e.g., “item name”), create a text facet, and then click “Cluster”. It will suggest various clustering methods (like key collision, nearest neighbors) and group similar but slightly different entries together. For instance, “Table Cover (Small)” and “Table Cover (Large)” can be clustered and then merged into a single “Table Cover” entry.

Q28: Can you explain how OpenRefine’s clustering works and how to merge items?

A28: After selecting a column and creating a text facet, click “Cluster”. You’ll see different methods like “key collision” and “nearest neighbors”. “Nearest neighbors” uses algorithms to find similar entries within a specified “radius” (distance parameter). You can adjust this radius to find more or fewer similar items.

To merge:

1. Select a clustering method (e.g., “key collision” with “fingerprint”).
2. OpenRefine will show groups of similar items.
3. For each group, choose a new value (e.g., “Table Cover”) to replace all items in that group.
4. Check the “Merge Selected and Recluster” box to apply the changes and re-evaluate for further clustering.

Q29: What’s the core idea behind learning tools like OpenRefine?

A29: The idea is to introduce you to various tools that can help in your future work. We’re building a “tools arsenal” for students. OpenRefine, for example, is excellent for data preparation. We teach you the basics, and if a future scenario requires it, you’ll know that such a tool exists and how to get started, rather than it being completely foreign.

Q30: How can I download data from YouTube using Unix commands?

A30: You can use yt-dlp (a YouTube downloader). This tool can extract videos and other information from YouTube.

Q31: What’s the overall structure for the BDM project, and how did tools like OpenRefine help you?

A31: The BDM project (which is considered very challenging) involves downloading data, preprocessing it, and then performing analysis. OpenRefine was crucial for quickly cleaning and clustering data, allowing me to complete the project in one month, whereas the typical timeline is four months. It helped with tedious tasks like standardizing item names.

Q32: What specific problem did OpenRefine help you solve in your BDM project?

A32: In my project, I had data from a handloom shop with various products like “table covers (large)”, “table covers (small)”, “AC curtains”, “curtains (gajri color)”, “curtains (5 ft)”, etc. For data analysis, I needed to group all “table covers” into one category, all “curtains” into another, and so on. OpenRefine allowed me to efficiently cluster these variations into single, standardized categories (e.g., “table cover”, “curtain”, “bedsheet”).

Q33: Can you explain the difference between the grep and egrep commands?

A33: egrep is equivalent to grep -E. It treats the pattern as an extended regular expression. It’s often preferred for more complex patterns.

Q34: How can I download images from a URL using Unix commands?

A34: You can use wget to download images.

Q35: What’s the distinction between control+shift and function+shift when navigating text in VS Code?

A35: control+shift+right arrow jumps to the end of the next “word” (delimited by spaces or special characters). function+shift+right arrow (or end key on Windows) jumps to the very end of the current line, regardless of words.

Q36: Why use i-json instead of simple json for large JSON files?

A36: i-json (incremental JSON) is designed for handling large JSON files efficiently. It parses the file in “batches” or incrementally, rather than loading the entire file into memory at once, which can prevent memory issues and improve performance.

Q37: What was the main challenge you faced when trying to reconstruct an image from smaller pieces?

A37: The main challenge was that even after stitching the pieces together, the resulting image sometimes had a different size or some distortion compared to the original, even though the individual pieces were the correct size. The expected output wasn’t always perfectly matched.

Q38: Is it possible that the image format or extension caused issues during reconstruction?

A38: Yes, it’s possible. If the original image was a .webp but was converted to .jpeg (or another format) during reconstruction, there could be subtle differences or issues with metadata that lead to discrepancies in size or appearance. Sticking to a consistent format is generally best.

Q39: What’s your advice for approaching the coding challenges given in these sessions?

A39: My advice is to get clarification if you have doubts, try using tools like Pandas or JQ for JSON processing, and be prepared to use various tools and techniques. Don’t be afraid to experiment, and remember that there are often multiple ways to solve a problem. It’s about building your skills and understanding the tools available.

Q40: How can I clarify my doubts or share my solutions during the live sessions?

A40: Feel free to open your mic and speak up, or type in the chat. Even if you get it wrong, it’s an opportunity to learn and for me to clarify. Participation makes the discussion more fruitful.

Q41: I’m getting compilation issues with my code. Can you help debug it?

A41: (Debugging a specific code snippet from a participant). The issue seems to be in your IF statement logic. You’re passing the DATEVALUE function when you find a hyphen, but it should be the other way around. If a hyphen is found, you need to manually parse the date string (MM-DD-YYYY), otherwise use DATEVALUE.