Watching a sorting, grouping, or just a defragmentation process graphically is both educational and curiously satisfying. So, I created a Python script that sorts a set of randomly placed, randomly colored, ungrouped tiles on a board by colors so that all like-colored tiles are placed consecutively, adjacently. Think of it like the good old Windows […]
Category: STEM
Data cleansing challenge: non-ASCII characters
Non-ASCII characters can pose challenges in data cleansing for several reasons: Therefore, it’s a good practice to standardize or normalize text data to ASCII when possible, or ensure correct handling of non-ASCII characters. This helps to maintain data integrity and simplifies subsequent data processing tasks. Superscripts, subscripts, or “special” characters often look like ascii characters […]
Comparing and merging lists in Excel, Python
Identifying anomalies, duplicates, and updating data necessitates comparing information from various sources. Accurate execution of these tasks is crucial, whether working solely with spreadsheets or using a mix of tools and languages like databases and web services. In this post, I will demonstrate various methods for comparing lists of identical or differing sizes across different […]
How much does it cost to retire in each state?
Recently, I collected data on cost of living, and average longevity in every state + D.C. From the data, I derived the COLI, or Cost of Living Index, which is then normalized to 100 (where 100 represents the national average cost of living). Additionally, using data from Bureau of Labor Statistics (BLS), I populated by […]
How to export a Youtube playlist for free
In this post, I’ll explain the steps to export your playlist on Youtube or one that’s shared with you for free. No, not the actual content like music tracks or videos, as for that, you’ll need a premium account, but the list of names such as artist or author names, tracks or video titles, and […]
Data Normalization & Rescaling
Normalizing data is a common task in many applications, especially when working with large datasets, machine learning, or statistical analysis. There are two common statistical methods for normalization: Min-Max Scaling, Standardization or Z-score Normalization. But there are other ways too, which I will demonstrate in the examples below. 1. Min-Max Scaling (Normalizes Data to Between […]
Comparing Apples with Oranges?
The familiar saying, “Comparing apples to oranges,” suggests that it’s illogical to compare two distinct items. However, in the realm of statistics, such comparisons are not only possible but sometimes necessary. By establishing a uniform standard or metric, we can evaluate items that, at first glance, appear incomparable. In this article, I present some straightforward […]
Conditional Coloring of Bar, Column Charts: 3 Examples
There are times when we want specific colors for bars or column in Excel charts to represent certain values or categories. Unfortunately, this isn’t a default feature, and it necessitates manually coloring each bar or series, which doesn’t automatically adjust when the data is altered. However, there are ways to achieve that with a little […]
Creating a dynamic weather report visual in Excel
In this blog post, I’ll share how I created a professional looking weather report tile using Excel without using any charting or pivot tables, but just just formulas and data inputs that is repeatable and scalable to daily reports without any change in the workbook. Just input the new data, and the new visual is […]
McKinsey-style bar chart in Excel
In this post, I’ll demonstrate how to create a McKinsey-style bar chart using just symbols and formulas, without using any Excel’s built-in chart components. BENEFITS OF THIS TYPE OF CHART:▪ It’s basically a bar chart with end points of our choice and much more vertically space-efficient and flexible.▪ It’s based on formulas, we can even […]