Technology Tales

Using SQL for R data.frames with sqldf

The R package sqldf offers a way to query R data frames using standard SQL syntax, making it particularly appealing to those with a SQL background. While numerous R packages exist for data manipulation and wrangling, including dplyr, data.table and tidyverse, sqldf provides an alternative approach by allowing users to filter and retrieve data using familiar SQL statements. A simple comparison using the iris dataset demonstrates that fetching filtered columns can be achieved through base R indexing, dplyr piping or sqldf queries, with each method producing equivalent results and the choice ultimately coming down to personal preference and familiarity.

How to conditionally execute SAS global statements

SAS global statements, which include options such as LIBNAME, FILENAME, TITLE and FOOTNOTE, define objects and configure system settings that persist throughout a SAS session. Although these statements can appear within data steps, they are not considered executable in that context, meaning they cannot form part of an IF-THEN/ELSE conditional structure. Instead, they take effect immediately after compilation, before any data processing begins.

This behaviour can cause confusion, as a global statement placed inside a conditional block will still execute regardless of whether the condition is met. To work around this, two practical approaches exist. The first involves using the SAS macro language, whereby the macro processor runs before the SAS compiler and can conditionally generate global statements that are then passed to the compiler for execution. The second approach uses the CALL EXECUTE routine inside a data step, which allows code to be constructed dynamically based on actual data values and then submitted for execution once the step boundary is crossed, making it particularly useful for data-driven scenarios such as generating individual report titles for each unique category within a dataset.

The Best Resources for Learning Shiny App Development

Shiny is an R package developed by Posit (formerly RStudio) that enables users to build interactive web applications directly from R, combining the computational power of the language with modern web interactivity. Posit offers a range of learning materials for Shiny, including written and video tutorials, an app gallery, development articles and community support. Beyond what Posit itself provides, a number of books cover the subject in depth, including works by Hadley Wickham, Colin Fay and David Granjon, while tutorials from contributors such as Zev Ross, Ted Laderas and Dean Attali offer structured, practical introductions to building Shiny applications.

Python Pandas Series.str.strip(), lstrip() and rstrip()

The Pandas library in Python offers three string methods for removing whitespace, including newlines, from data in a Series. The str.lstrip() method removes spaces from the left side of a string, str.rstrip() removes them from the right side and str.strip() removes them from both sides.

Because these methods share names with built-in Python functions, the .str prefix must be used to ensure the compiler recognises them as Pandas functions. Each method returns a new Series with the relevant spaces removed, and their behaviour can be verified by comparing the cleaned output against known string values, as demonstrated using data in which extra spaces are deliberately introduced into team name entries before each method is applied and the results checked.

Adding lines or other geoms to a plot in ggplot2 by calling a custom function

When creating multiple similar plots in R using ggplot2, a common requirement is to add the same vertical and horizontal reference lines across all of them. The intuitive approach of combining geom calls using the addition operator inside a custom function fails because ggplot2 cannot add ggproto objects together outside a plot context.

The solution is to have the custom function return a list of geom calls rather than a sum of them, as ggplot2 can process a list of geom objects when added to an existing plot. This approach allows the same set of reference lines to be reused across multiple plots cleanly and efficiently.

Tips for Selecting Columns in a Pandas DataFrame

Working with large datasets in Python often requires efficient column selection techniques, and Pandas offers several useful approaches for this purpose. The iloc function enables integer-location based indexing, allowing users to select individual columns, lists of columns or ranges using slice notation. When combining non-sequential ranges, NumPy's r_ object can translate a mix of slice notation and individual integers into a single array that iloc can process. Boolean arrays offer another powerful method, using string accessor functions on a column index to filter columns based on their names, including support for regular expressions to match multiple patterns simultaneously.

The Pandas filter function provides a more straightforward alternative for selecting columns by name or pattern, accepting either partial string matching or regular expression inputs. Practical utilities such as list and dictionary comprehensions can also help users build reference mappings of column names and their indices, reducing the need to repeatedly consult original data files during analysis. One important consideration when using numerical indexing is ensuring that column positions remain consistent across different data inputs, as changes in column order could cause errors in subsequent processing steps.

What is Spaghetti Code (And Why You Should Avoid It)

Spaghetti code is a derogatory term in IT jargon used to describe poorly structured, convoluted programming that arises from a lack of planning, inexperience, unclear project scopes and the gradual accumulation of changes made by multiple developers over time.

Rather than following sound architectural principles, spaghetti code tends to rely on shortcuts such as GOTO statements, making programmes difficult to maintain and scale. The term has been in use since at least the late 1970s, with historical references linking it to early programming languages such as FORTRAN.

For enterprise businesses, the consequences can be significant, as untangling years of disorganised code wastes time, money and developer effort. To prevent it, developers are encouraged to plan architecture carefully from the outset, conduct regular unit testing, seek peer reviews and use lightweight frameworks with clearly defined layers. Related coding problems include ravioli code, which affects object-oriented projects, lasagna code, which results from overly interdependent layers and pizza code, which describes an architecture that is too flat.

Spyder: The Scientific Python Development Environment

Spyder is an Integrated Development Environment designed for scientific computing in Python, offering an editor, an interactive console, a variable explorer and various other tools to support program development. Users can write and execute code directly within the editor, run individual lines or sections using keyboard shortcuts, and interact with defined objects through the IPython Console.

The environment supports incremental coding and debugging by allowing objects to persist between executions, though this can occasionally cause issues if code inadvertently relies on variables defined in a previous session rather than within the script itself. Spyder includes a step-by-step debugger that lets users move through code line by line, inspect variables at each stage and modify them as needed.

Plotting with Matplotlib is supported either inline within the console or in a separate window, and documentation strings can be formatted using reStructuredText and the Numpydoc standard to produce well-rendered output in the built-in help panel. Configuration options allow users to manage code style compliance, symbolic mathematics via SymPy and a range of run settings, while keyboard shortcuts and customisable preferences help streamline the development workflow.

Python KeyError Exceptions and How to Handle Them

A KeyError is a common Python exception raised when attempting to access a key that does not exist in a dictionary or similar mapping structure. It is a subtype of the LookupError exception and can occasionally appear in standard library modules such as zipfile, where it signals that a requested item cannot be located.

When encountered, the traceback provides useful details, including the missing key and the line of code responsible for the error. There are several practical ways to handle a KeyError, depending on the situation.

The most common approach is to use the .get() method on a dictionary, which returns a default value rather than raising an exception when a key is absent. In cases where it is important to confirm whether a key exists without retrieving its value, the in operator offers a straightforward check. For more general scenarios, particularly when dealing with third-party code or modules that do not support .get(), a try-except block provides reliable control over program flow by catching the exception and executing a fallback action instead.

10 Tips And Tricks For Data Scientists Vol.10

Volume 10 of a series aimed at data scientists covers a range of practical coding techniques in Python and R. In Python, the tips include retrieving the key of the maximum value in a dictionary, sorting dictionaries by value in ascending or descending order, shuffling a Pandas data frame using fractional sampling, repositioning a column to the end of a data frame through re-indexing, performing circular shifts on arrays using NumPy, replacing data frame values by specifying column and index positions with the loc function, automatically generating a requirements file for a Python project using the PIGAR package, producing random names with the names library and reading header-free CSV files by manually defining column names. The single R tip addresses a statistics problem typical of data science interviews, demonstrating how to calculate the standard deviation of a normal distribution from a known probability threshold using the standard normal distribution and confirming the result through simulation.