Posts

The most common form of data I have played with is generally found in CSV format. This makes the data easy to understand but difficult to modify or change, especially when the CSV file contains lots of columns. In this article I will demonstrate how I frequently use this file format to import it into a useable data structure in Python, namely, a list of Python dictionaries. Let’s start with defining the basic terminology so we’re on the same page before jumping into some examples. ...

When working with an array of different data sets you’ll undoubtedly come across the issue where you want to perform a count on your data (or something similar) and you want to exclude the blank or empty cells in your Google Sheets. There are several ways to check if a cell is blank and you can use either type of these expressions to check. Here is a short list of operators and functions that can assist: ...

Why does a Python list not append an item? When you use the list method .append() to insert a list item to the end of its existing contents, you could be in for a surprise if this doesn’t happen in some instances. The main reasons for why this may occur are due to the improper usage of the list method or adding a variable that doesn’t actually contain the information expected. ...

Continuing my work on RTU assets from yesterday, I found I needed to program some of the common formulas you see in Google Sheets into Javascript to be used in my Suitescript code. These common functions include: PV(rate, number_periods, payment_amount, future_value, end_or_beginning) FV(rate, number_periods, payment_amount, present_value, end_or_beginning) PMT(rate, number_periods, present_value, future_value, end_or_beginning) Each of the above functions comes in handy when trying to calculate the present value of an annuity, investment or mortgage. ...

How do you highlight an entire row in Google Sheets on a table of data where a column in your table containing dates matches the same month and year as today? For this technique, you will need to use the Conditional Formatting section in Google Sheets and a Custom Formula. Here’s the Custom Formula I needed to enter (I’ll explain it underneath so that you can amend it for your use case): ...

How can you highlight a column of dates based upon a condition where comparison is needed with the current column of dates to another column containing dates? I had a requirement where I needed to compare a column of dates to another column of dates and if a specific date was between those dates to highlight one of the columns. For example, I needed to highlight a cell when the start date and the end date crossed over the end of the financial year. As the end of the financial year in Australia is 30th June each year if the start date was before the 30th June and the end date was after the 30th June then I wanted to highlight the row. ...

How can you randomise a range in Google Sheets without needing a plugin or Google App Script? To randomise a range, simply use the RAND() formula in a column to set the random numbers for each element in your range, and then with the INDEX() and RANK() formulas combined, produce the new random range. Here’s an example of what this looks like in a Google Sheet spreadsheet starting with the range you’d like to randomise. ...

How can you create a list with identical elements in it with Python? There are two ways to create a list with identical elements in Python. One method is fairly straightforward and easy to remember, whereas the other involves the itertools library. The one I apply the most in my Python coding is the method that uses the asterisk operator to a list containing the item I want to replicate. The other method uses the resources and techniques of a library with a function that is difficult to forget: itertools.repeat() ...

How do you fix the NameError: Python list is undefined type errors? When you get this error in your Python code there are a few ways to fix the problem and in essence, the easy fix is to check you’re working on a list variable that has been initiated as a list. The three common causes I have found in my own Python code for why this error pops up is due to: 1. not instantiating a variable as a list; 2. incorrectly typing the name of the list variable; or 3. using a variable in a list that hasn’t been declared. ...

Why does a list not sort properly in Python as expected? There are a couple of reasons why a list may not have sorted as expected in Python. Here are a couple of ways to diagnose the problem to help you check. Here’s a quick list (no pun intended!) you can use to quickly check: Are all elements within the list of the same data type? Look at setting them to the same data type using the key parameter Are you capturing the result of a .sort() method? Change this sort operation to the sorted() function Are you sorting items with mixed case? Apply the key parameter converting all items to .lower() Do you understand the key parameter? Extract the lambda function to check it works on elements in your list. Are you sorting in reverse? Check the reverse parameter to your sort function. False is the default setting it this parameter is not defined. Are you sorting unicode characters? Look to use a standard encoding type with the key parameter using lambda x : x.encode('utf-8'). Are there any whitespace characters wrapping the elements? Apply the .strip() string method to each element with the key parameter. Let’s explore each of these items in a little more detail with examples. Here are some reasons why your Python lists may not be sorting properly: ...