Setup i2c on Raspberry Pi Zero W using Arch Linux

This article builds on the previous, where I ran us through setting up Arch Linux for the Raspberry Pi Zero W.

Let’s not stop, let’s get I2C going so we can interact with some cool hardware.

1. Installing sudo

If you’ve followed my previous guide on installing Arch Linux on a Raspberry Pi then you’ll have ended up with a bare bones system, which is good. No unneeded fat. But sometimes fat is needed, it’s what gives us curves, and curves are beautiful….I feel this metaphor is breaking down. In short, we need extra packages to get work done.

The first package we need is sudo

It will allow us to easily manage file permissions.

First, make sure your system is up to date. To do this we are going to login as the root user. You can do this by typing su followed by the root user’s password, which for a barebone Arch Linux installation is root.

$ su
Password: root

Oh, and if you haven’t figured out yet, alarm stands for Arch Linux ARM.

Next we need to update the package libraries and system.

pacman -Syu

After hitting enter, it should look like this:

root@alarmpi alarm]# pacman -Syu
:: Synchronizing package databases...
 core                                                        179.0 KiB   448K/s 00:00 [#################################################] 100%
 extra                                                      1982.8 KiB  1279K/s 00:02 [#################################################] 100%
 community                                                     4.0 MiB  1689K/s 00:02 [#################################################] 100%
 alarm                                                        35.0 KiB   583K/s 00:00 [#################################################] 100%
 aur                                                           6.0 KiB  0.00B/s 00:00 [#################################################] 100%
:: Starting full system upgrade...
resolving dependencies...
looking for conflicting packages...

It should give you a list of packages with update and upgrade candidates and prompting you to confirm the updates. Go ahead and say yes.

Now we should be good to install sudo

$ pacman -S sudo

Even after sudo is installed, we still need to add the main user, which is alarm to the sudo’er group. This in effect gives the alarm user the superpowers of the root user.

Now, the way sudo works is by adding a user to a special Linux group. Anyone added to this group will be given root superpowers. To get a list of those currently in the sudo group:

sudo -ll

You should get something like

User root may run the following commands on alarmpi:

Sudoers entry:
    RunAsUsers: ALL

Ok, let’s get the alarm user added to the sudoer group.


EDITOR=nano visudo

This should allow you to edit the visudo file and add alarmpi to sudoers. Oh, the write permissions for the visudo file are limited to root, so if you have switched back from the root user to alarmpi you will need to run su again and log back in as root before editing this file.

Let’s find the entry for adding users to the sudo’er group.

Find the part which looks like this:

## User privilege specification
root ALL=(ALL) ALL

And add alarm ALL=(ALL) ALL right below the root entry. It should look like this after editing.

## User privilege specification
root ALL=(ALL) ALL
alarm ALL=(ALL) ALL

Then hit CTRL+O to write the changes and CTRL+X to exit.

Before we can check the changes took, we will need to exit our root session.


This should land you back at your alarm session. To see you the alarm user is now added to the sudoer group type

sudo -ll

And if all went well, you’ll get this output

User alarm may run the following commands on alarmpi:

Sudoers entry:
    RunAsUsers: ALL

Notice, we now have access to ALL commands. Booyah!

We can do a hard test by typing:

sudo ls

We should get

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for alarm:

Type the alarm user password (which is alarm, if you haven’t changed it).

2. Install needed packages

pacman -S git python2 i2c-tools base-devel python2-distribute python2-pip

Use Python’s Package Index (pip) to install Raspberry Pi GPIO support

sudo pip2 install RPi.GPIO

3. Install raspi-config

sudo pacman -S xorg-xrandr libnewt
git clone
cd raspi-config
makepkg -i

Use the Raspi-Config tool to enable I2C

sudo raspi-config

Select “Interfacing Options” and enable I2C.

Note: Going back through these instructions I did notice when I started raspi-config I received this warning:

/usr/bin/raspi-config: line 997: warning: command substitution: ignored null byte in input

And when I attempted to enable I2C it gave this error.

* Failed to read active DTB

But it still seemed to do the job. I’ll investigate more when I’ve time.

Now, we need to reboot the system for everything to register.

4. Test the I2C Setup

We should be all setup. Try running

sudo i2cdetect -y 1

If all has went well then you should get

[alarm@alarmpi ~]$ sudo i2cdetect -y 1
     0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f
00:          -- -- -- -- -- -- -- -- -- -- -- -- --
10: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
20: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
30: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
40: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
50: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
60: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
70: -- -- -- -- -- -- -- --

Now, we just need to connect an I2C device to the bus and we should the hex address of where the device may be found.

Installing Arch Linux on Raspberry Pi with Immediate WiFi Access

Disclaimer: The easiest way to setup an SD card with Arch Linux for the Raspberry Pi Zero W (rp0w) is using Linux–and the following guide will assume you have access to Linux somewhere. For Mac and Windows users, it is possible to setup an SD card using Linux inside of a virtual machine. The interwebs will have more on the subject.

The hardest part of setting up Arch Linux for the rp0w is getting the WiFi working on boot. This allows accessing the OS through ssh immediately. This is known as a “headless setup.” I’ve created instructions on doing something similar in Kali. However, I was lucky when I hit Arch–as there is a fine fellow who has already written a script to setup the WPA Supplicant needed for a headless build.

1. Create an SD Card by following the Arch Linux instructions

Really, the only piece of information not provided by Arch Linux community is which ARM architecture you need for the rp0w. It’s armv6.

A few notes on using the installation instructions.

  • I had to run most of the commands as root (sudo)
  • We are going to insert a step afte the SD card is setup and before we boot our rp0w
  • MOST IMPORTANT NOTE: If you accidently select a different device instead of your SD card bad poop will happen. For real. To know which device is your card make heavy use of fdisk -l which will provide a list of all devices. Your SD card is approximately the same size as the card states. For example, this is the output I get when I run fdisk -l on my PC with the SD card in.
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disklabel type: gpt

Device         Start       End   Sectors   Size Type
/dev/sda1         40    409639    409600   200M EFI S
/dev/sda2     409640 578929663 578520024 275.9G unkno
/dev/sda3  578929664 586480023   7550360   3.6G Micro
/dev/sda4  586480024 586742167    262144   128M Apple
/dev/sda5  586743808 976842751 390098944   186G Linux
/dev/sda6  976842880 977105023    262144   128M Apple

Cleaning up

Disk /dev/sdb: 7.5 GiB, 8053063680 bytes, 15728640 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0xd0ca12f8

Device     Boot  Start      End  Sectors  Size Id Type
/dev/sdb1         2048   206847   204800  100M  c W95
/dev/sdb2       206848 15728639 15521792  7.4G 83 Linu

So, the main device path for my SD card is /dev/sdb. And to the first partition it’s /dev/sdb1

2. Create Script to Enable WiFi on Boot

We are going to need to create a script on the Linux OS you used to setup the SD card. This script will access the rp0w’s Arch Linux files and inject our WiFi information. This will allow the rp0w to automatically connect to your WiFi router when it boots, thus, giving you immediate access to it via SSH.

At the command prompt (of your PC, not the rp0w)


This will open a blank nano editor. Inside, paste the following, then save the file.


set -e

if [[ $# -ne 3 ]] ; then
   echo "Usage: $0 </dev/disk> <ssid> <passphase>"
   exit 1


if [[ ! -b "${DISK}" ]] ; then
   echo "Not a block device: ${DISK}"
   exit 1

if [[ "${USER}" != "root" ]] ; then
   echo "Must run as root."
   exit 1

echo Mounting
mkdir root
mount "${DISK}2" root

cat << EOF >> root/etc/systemd/network/


wpa_passphrase "${SSID}" "${PASS}" > root/etc/wpa_supplicant/wpa_supplicant-wlan0.conf

ln -s \
   /usr/lib/systemd/system/wpa_supplicant@.service \

echo Unmounting
umount root

echo Cleaning up
rmdir root

For those curious or wary, this script takes three parameters

  1. The location of SD card in the PC’s device tree
  2. SSID of your WiFi router
  3. Password for the WiFi router

It then mounts the SD card, accesses the files needed to setup WiFi, and inserts the connection information appropriately.

Thanks again, Stasiana.

Let’s keep going.

Before we can run the script it must be given executable permissions.

chmod +x

Note: If you execute the script in the same path as where you built the SD card then the script will complain

mkdir: cannot create directory ‘root’: File exists

That’s because the Arch Linux instructions didn’t mention removing the SD card paths.

To delete the paths root and boot which were required for setup run (make sure your not in the / path first).

sudo rm -R boot root

Now, let’s execute it, passing /dev/sdX, your_wifi_name, and your_wifi_password. Like so.

./ /dev/sdb wifi_name wifi_password

If all goes well, you should see.

Cleaning up

Anything else, leave me a comment and I’ll help troubleshoot.

3. Connecting

Ok! That’s it. Now, put the SD card into the rp0w and fire it up. The green light should begin flashing.

The last tricky part is knowing what IP address has been assigned to the rp0w on boot. After waiting a few minutes for it to connect to the wifi, visit your router’s admin page. It’s usually

You’ll need the router login information. But once in there should ba a section like “Attached Devices”. In there you should see an entry for “alarm” (which stands for Arch Linux ARM). This your rp0w.

Now, at the command line type:


Replacing the xs with the address of your Raspberry Pi. If you don’t know the address of the Raspberry Pi you can log into router and look for ALARMPI.

Where the xxx is the address assigned to the Pi. You should be prompted with an EDSCA warning (say yes). Then, you will need to enter the password which is alarm.

Welcome to Arch Linux ARM

         IRC: #archlinux-arm on
Last login: Thu Apr 12 12:18:05 2018 from
[alarm@alarmpi ~]$

Happy Arching.

Lumi5 – AVR / Arduino Wireless Uploader

This article will focus on setting up the Bluetooth 4.0 module, preparing the uploader, and uploading sketches over Bluetooth 4.0


This article builds off:

Upload Arduino Sketches to ATtiny85 with UART

The above article will need to be followed to prepare your ATtiny85 and Windows computer.

  1. ATtiny85 (or any ATtiny or ATmega chip)
  2. Arduino Uno (or compatible, this will be used once to burn the bootloader to the ATtiny85).
  3. FTDI (or compatible UART).
  4. Lumi (a browser based uploader for TinySafeBoot).
  5. Arduino IDE
  7. HM-10 or HM-11 Breakout*
  8. 1k Resistor
  9. Soldering iron

*Note: There are much cheaper versions of the breakout listed on eBay, but beware, there are many clones which will not work with this project. The one I’ve listed I’ve verified as working. Of course, I always recommend you roll your own breakout :)


Over-the-air uploading of programs to embedded devices is one of the more useful implementations in the history of embedded hardware. It allows the post-production and delivery of gadgets to have their behavior tweaked in reaction to end-user feedback.

Likewise, the Arduino phenomenon probably needs little explanation in this venue. However, I’ve personally not found many solutions for over-the-air uploading of Arduino sketches which have acceptable trade-offs. This article, along with the preceding, are nothing more than attempts to share what I’ve found.


This is simply for those who are curious on how this works, it may be skipped.

This tutorial will show you how to wirelessly upload Arduino sketches to an ATtiny85 over a PC’s built in Bluetooth 4.0 hardware and an HM-10. The concept is fairly simple.

The HM-10 has firmware which allow it to monitor all incoming serial data for AT commands. It can then intercept these commands from the stream, allowing the user to remotely control the behavior of the HM-10. In short, the HM-10 allows you to send a command to send a pin HIGH or LOW.

Now, the TinySafeBootloader is a serial bootloader for the ATtiny and ATmega AVR chip sets (these are the heart of Arduino). The most important difference between the TinySafeBootloader and others is it allows serial control over the bootloader behavior, this allows programmers to write uploader software for it, which is what I’ve done for this project.

To the point, we will wire up an HM-10 and an ATtiny with the TSB bootloader. One of the HM-10’s IO pins will be connected to the ATtiny’s RESET line. This will allow us to send a command to the remote HM-10, sending the ATtiny85 into bootloader mode, upload a sketch over the Bluetooth connection, then send another command to the HM-10 to send the ATtiny85 back into program execution mode.


Select a Pin

You will need to select a IO pin on the HM-10 to connect the 1k resistor. Any of the pins listed above should be supported.

Solder the 1k Resistor

After you have selected the IO pin you would like to use, solder a 1k resistor to the castellated (the half-via leads) on the HM-10.

A few notes:

This is not as hard as it looks; believe in yourself Flux and a fine-tip iron are helpful, but required Tweezers allow you to work angles. Tacky-putty helps hold the board in place, leaving both hands free to hold components.

Configure the HM-10 Firmware for Remote Uploading

Before the uploader will work, the HM-10 must be set as a peripheral device and remote transmission mode.

  1. Connect the FTDI chip to the HM-10 breakout, as shown.
  2. Open the Arduino IDE.
  3. Select the COM port connected to your FTDI chip
  4. Open the Serial terminal
  5. Make sure the terminal is set to, “No line ending”
  6. Type AT+ROLE0 and hit “Send”. The response “OK+Set:0” come up.
  7. Now, type AT+MODE2 and hit “Send. The response should be “OK+Set:2”.


  1. You can set the HM-10’s baud rate up to 57,600 (it gets iffy above this rate). This allows the bootloader to run more quickly. Here are the commands:
    • AT+BAUD0 = 9600
    • AT+BAUD1 = 19200
    • AT+BAUD2 = 38400
    • AT+BAUD3 = 57600
  2. You can change the name of your device using “AT+NAMExxxxxxxx”.

Wire It Up

The following connections will need to be made:

VCC should be 5V

Compile a Sketch to Upload

In the Arduino IDE, write a program you want to upload to your wireless TSB setup. However, instead of using the Arduino to upload the file, we are going to use it only to compile. To do this, go to Sketch -> Export Compiled Binary Then, select Sketch -> Show Sketch Folder. In this folder there will be two .HEX files. You want the one which does not have “…_with_bootloader.ino”. (Since, we already have a bootloader installed on our ATtiny.)

Connect to the HM-10 Module from Lumi5

I’ve provided a web based uploader for TinySafeBoot. A couple of notes, it only works in Chrome on Linux and Mac.

Before attempting to use the uploader, please open Chrome and type:


To get Chrome’s “experimental” Bluetooth LE web API to work we have to turn it on. Go ahead and select Enable.


Go ahead and navigate to:

This is the work-in-progress web based TinySafeBoot loader I’ve been working on. The HM-10 uploader is functional, though.

Turn on your HM-10. Select Bluetooth from the dropdown box and hit Search. If all has gone well, then you will see the name of your HM-10 module listed. Go ahead and connect to it.

Now, in the Pin # box, type the pin number corresponding with the pin on your HM-10 which you are using to reset the TinySafeBootloader.

Select the multi-gear button. This should give you a bootloader handshake button. Try it. It may take a couple of tries, but you should see the bootloader respond with the device signature and memory information.

If all went well, go ahead and click the Upload File button. Select the compiled HEX file from the Compile a Sketch step and hit upload.

Cross your fingers. It should respond with tell you it has written the file.

Important: Go ahead and refresh the webpage and reconnect. Once you’ve reconnected, the program you uploaded should be running.

We setup the HM-10 to hold the reset line low when the module is not connected, this is to save power. But it can be frustrating if your unaware of the setup, as it’s difficult to troubleshoot. “Why the heck isn’t the program running!?”

Please, please be kind and provide feedback. This is a work-in-progress, using a lot of custom hardware and experimental code.

List any questions below and I’ll try to troubleshoot ASAP.

Upload Arduino or AVR Programs to ATtiny84/85 Over UART

Upload Arduino Sketches to ATtiny85 With UART This article will show you how use a two-wire interface (UART) to upload Arduino sketches to the ATtiny85. In short, we will burn the TinySafeBootloader onto the ATtiny85 using an Arduino Uno as an ISP programmer. After the initial burning of the bootloader, we can then program the ATtiny85 with any FTDI compatible USB-to-UART. And very similar steps may be followed to do the same for an ATtiny84.

There are a few requirements and a lot of setup needed:


Useful Components:

  • Breadboardable Push Button
  • 220-330 Ohm Resistor
  • LED


Many may ask, “Really, why would I want to go through all this trouble to install a bootloader on an ATtiny85 when it is less trouble just to use the Arduino ISP?” Good question.

This article was not meant to be independent. The entire purpose of the this is to prepare an ATtiny85 (actually, any ATtiny orATmega uCs) for wireless upload of Arduino sketches or AVR binaries.

Step 1: Upload the ArduinoISP Sketch

Upload the ArduinoISP Sketch There are several guides on how to use an Arduino as an ISP:

But outline the steps briefly, just in case.

before wiring up the Arduino Uno to the ATtiny85 you will need to install the Arduino ISP sketch onto the Arduino Uno. This is built into the Arduino IDE.

  • File –> Examples –> 11. ArduinoISP –> ArduinoISP

Then hit the Upload button. Wire up the ATtiny85 After the ArduinoISP sketch has been uploaded it is time to burn our bootloader to the ATtiny85. Go ahead and wire your ATtiny85 to the Arduino Uno as shown in the image above.

Step 2: Wire Up the ATtiny85 and Arduino

Now the ArduinoISP sketch has been uploaded it is time to burn our bootloader to the ATtiny85. Go ahead and wire your ATtiny85 to the Arduino Uno as shown in the image.

Step 3: Install AVRDUDE

Now, we will need to install WinAVR

Step 4: Burn TinySafeBootloader on ATtiny85

Burn TinySafeBootloader on ATtiny85 Once AVRDUDE has successfully installed, open it by going to the Start Menu and typing

  • cmd

This should open the Windows command prompt. Now, let’s make sure AVRDUDE is installed. Type:

  • avrdude

And press return. You should see output similar to what’s in the image.

*Please hear my warning* what we are about to do has the potential of brick your ATtiny85. More specifically, if we set the fuses wrong on the ATtiny85 it might render the chip unusable. That stated, let’s take a moment and talk through what we are doing.

At this point our Arduino Uno is setup as an ISP. We plan to use AVRDUDE to tell the ISP to burn the TinySafeBootloader onto our ATtiny85. In the process we will also set the fuses on the ATtiny85. These fuses are bits of memory which tell the ATtiny85 how to act. There are two will need to set to use TinySafeBoot on any ATtiny supported.

ATtiny85 & ATtiny84 (or any other ATtiny supported):

  1. SELFPRGEN – must be set to enable flash writes from firmware
  2. BODLEVEL – should be set to avoid flash corruption during unsafe device power-up.

If you are following this guide for the ATmega series the fuses need are:

  1. BOOTRST – activated lets the MCU jump into the Bootloader section with every hardware reset.
  2. BODLEVEL – should be set to avoid flash corruption during unsafe device power-up.
  3. BOOTSZ=11 – to reserve 512 bytes for a Bootloader Section.
  4. BLB – set to MODE 2 or 3 to protect Bootloader section from undesirable write access by firmware.

If you don’t quite trust me or if you would like to read more about fuses, here’s a great explanation.

The fuse settings will be written to the ATtiny85 when when burn the bootloader by using AVRDUDE. You are welcome to calculate your own fuses using the nifty EngBedded web app:

However, I’ve provided the two commands you will need for the to program the ATtiny 84, 85, and ATmega328P. Copy the first command and paste it to the Windows prompt. If the command is successful, copy the second command. If both commands are successful, you should now have the TSB setup on your ATtiny or ATmega chip.

AVRDUDE command to upload:

ATtiny85 at 1mhz

avrdude -P COM# -b 19200 -c avrisp -p t85 -v -e -U lfuse:w:0x62:m -U hfuse:w:0xdd:m -U efuse:w:0xfe:m
avrdude -P COM# -b 19200 -c avrisp -p t85 -v -e -U flash:w:tsb_tn85_b3b4_20150826.hex
ATtiny85 at 8mhz

avrdude -P COM# -b 19200 -c avrisp -p t85 -v -e -U lfuse:w:0xe2:m -U hfuse:w:0xdd:m -U efuse:w:0xfe:m
avrdude -P COM# -b 19200 -c avrisp -p t85 -v -e -U flash:w:tsb_tn85_b3b4_20150826.hex

ATtiny84 at 1mhz

avrdude -P COM# -b 19200 -c avrisp -p t84 -v -e -U lfuse:w:0x62:m -U hfuse:w:0xdf:m -U efuse:w:0xfe:m
avrdude -P COM# -b 19200 -c avrisp -p t84 -v -e -U flash:w:tsb_tn84_a0a1_20150826.hex

ATtiny84 at 8mhz

avrdude -P COM# -b 19200 -c avrisp -p t84 -v -e -U lfuse:w:0xe2:m -U hfuse:w:0xdf:m -U efuse:w:0xfe:m
avrdude -P COM# -b 19200 -c avrisp -p t84 -v -e -U flash:w:tsb_tn84_a0a1_20150826.hex

ATtmega328P at 8mhz

avrdude -P COM# -b 19200 -c avrisp -p m328p -v -e -U lfuse:w:0xFF:m -U hfuse:w:0xDA:m -U efuse:w:0x05:m
avrdude -P COM# -b 19200 -c avrisp -p m328p -v -e -U flash:w:tsb_m328p_d0d1_20150826.hex

ATmega328P at 16mhz

avrdude -P COM# -b 19200 -c avrisp -p m328p -v -e -U efuse:w:0x05:m -U hfuse:w:0xD6:m -U lfuse:w:0xFF:m
avrdude -P COM# -b 19200 -c avrisp -p m328p -v -e -U flash:w:tsb_m328p_d0d1_20150826.hex

If you have any issues, please leave any questions in the comments below.

Step 5: Setup Arduino for ATtiny Boards – 1

Setup Arduino for ATtiny Boards – 1 You will need to add ATtiny board support to the Arduino IDE. This may be done by going to:

  • File —> Preferences

Then enter the following into the box marked “Additional Boards Manager URLs:”


Step 6: Setup Arduino for ATtiny Boards – 2

Then go to,

  • Tools —> Boards —> Board Manager

Now you have added the additional boards URL there should be an option:

  • “attiny by David A. Mellis”

Install this package.

Step 7: Select the ATtiny85

Select the ATtiny85 Now, you should have the ATtiny group listed under

  • Tools –> Boards —> ATtiny

Select the processor and speed you are working with, then, type out whatever program you would like to upload. Once you have finished your code go to:

  • Sketch —> Export Compiled Binary

Usually, the Arduino IDE takes care of the upload, however, it doesn’t know how to interface with the TinySafeBootloader. This is where the Lumi Uploader comes in.

Step 8: Download the Uploader

Download the Uploader Download my Lumi Uploader (Windows 10). It only costs a bazillion-million dollars; nah, it’s free.

Lumi Uploader

Warning: this uploader is a work in progress. If you would like to delve into the source it may be found here:

Lumi Uploader Source

Eventually, I’ll probably re-write this app for Mac and iOS. But gotta get the bugs outta of the Windows version first.

Step 9: Connect UART IC to ATtiny85

Connect UART IC to ATtiny85 Connect the UART IC to the ATtiny85 as shown.

Step 10: Upload Arduino Sketch to ATtiny85

Time to upload the sketch!

Open the Lumi Uploader Select COM port which your FTDI chip is connected Set the baud rate between 9600-56700 Click”Connect” Then click connect “Connect to TSB” If the chip is found, it will display the information of the connect chip Use the “Open File” to navigate to the binary we exported in step 8 (make sure to select the version that doesn’t include the bootloader). Once the file is fully loaded, click the button “Write File” Congratulations, if all went well your sketch should now be on the ATtiny85! If not, well, let’s troubleshoot:

What happens if you click the “Reset” button? This should send the DTR line low, then high, essentially resetting the ATtiny85 Did you accidentally select the bootloader included version of your binary? Was the correct COM port selected? Often, windows will list Bluetooth bridges as COM ports, which can make it confusing. Did you try lower baud rates? The TSB uses software serial, which doesn’t work too well on higher baud rates. Did I make a mistake? If so, shoot me a line or leave a comment–I’ll do my best.

SQL Case

The SQL CASE function is one of my favorite. The command basically works like if-then command. If you are familiar with if-then commands, then feel free to skip this next bit.


One of the reasons we have the amazing devices we do today is because a computer is capable of reasoning. A computer can compare two things and decide which one it likes.

Now, this may sound simple, but it’s actually a subtle miracle. Anyone who has been stuck on the toothpaste isle trying to decide between the 45 kinds of toothpaste probably understands making decisions is difficult. Of course, human decision making and computer decision making are not even on the same level. Humans can make comparisons of all 45 products at once(sort of). Computers, they have to make a decision between two objects, then, two objects, then two objects, so forth, until it has made it through all 45. Fortunately, computers can make these decisions blazing fast.

In computer programming we call this computer decision making process control flow. But let’s write some pseudocode for a little better understanding:

    If (Computer Likes Toothpaste 1) then buy Toothpaste 1

Pretty simple, right? The only thing a computer can’t do is decide if it likes Toothpaste 1 on its own. We have to program it to do that.

Well, this sentence makes sense if a computer is trying to decide to buy toothpaste or no toothpaste, but what if there are more than two toothpaste options? We just create another if-then statement.

    If (Computer Likes Toothpaste 1 Best) then buy Toothpaste 1
    If (Computer Likes Toothpaste 2 Best) then buy Toothpaste 2

Because the computer makes decisions in order it read them, then if it buys Toothpaste 1 then it will not buy Toothpaste 2. However, if he doesn’t like Toothpaste 1 the best, then if he thinks Toothpaste 2 is the best he’ll buy it. Otherwise, he will not buy any toothpaste–which makes sense, computers don’t have teeth.

This is almost everything we need to know about if-then, two more little catches.

First, what do we do if the computer doesn’t like any of the Toothpaste and don’t want him to just give up? We need a way to say, “Look computer, if you don’t like any toothpaste the best then go ask for help.”

In programming this is known as if-then-else statements. They are similar to if-then but with a contingency clause if something goes wrong.

Let’s take a look:

    if (Computer Likes Toothpaste 1 Best) then buy Toothpaste 1
    if (Computer Likes Toothpaste 2 Best) then buy Toothpaste 2
    else Go Ask a Computer Dentist what to buy

Ok, that’s it. Now let’s apply it to SQL.


SQL applies if-then logic in several ways. We’ve already looked at the WHERE statement, which basicaly works like an if-then.

    SELECT * FROM data WHERE Name = 'Bob'

See how this could be written as

    SELECT * FROM data IF Name = 'Bob'

But the most likely SQL statement used for if-then-else logic is the CASE WHEN statement.

Here’s an example to be run in R.

################### Data DO NOT CHANGE ###########################
                       FirstName=c("Timmy", "Fela", "Sarah"),
                       LastName=c("Tesa", "Falla", "Kerrigan"),
                       DOB=c("2010-01-01", "1999-1-1", "1992-04-01"))

peopleDf1 <- sqldf("SELECT *, 
                  CASE WHEN DOB > '2000-1-1' THEN 'Yes' ELSE 'No' END As 'Millennial' 
                  FROM peopleDf")

Here is the output:

PersonalID FirstName LastName DOB Gender Millennial
ZP1U3EPU2FKAWI6K5US5LDV50KRI1LN7 Timmy Tesa 2010-01-01 Male Yes
IA26X38HOTOIBHYIRV8CKR5RDS8KNGHV Fela Falla 1999-1-1 Female No
LASDU89NRABVJWW779W4JGGAN90IQ5B2 Sarah Kerrigan 1992-04-01 Female No

The SQL query, specifically the CASE WHEN statement created a column called Millennial, it then went through every person’s date of birth, comparing it. When the query found a person who was born after 2000-01-01 it inserted a ‘Yes’ in the Millennial column. If they were not born after 2000-01-01 then it set the Millennial column to ‘No.’ Nifty, right?

Notice, the ELSE is required to get the ‘No’. Otherwise, the query would leave everyone else blank.

Here’s a few more examples of using CASE WHEN for powerful results.

Using OR with CASE WHEN

peopleDf2 <- sqldf("SELECT *, 
                  CASE WHEN DOB > '2000-1-1' OR FirstName = 'Sarah' THEN 'PersonIsCool' ELSE 'NotHip' END As 'Cool?' 
                  FROM peopleDf")
PersonalID FirstName LastName DOB Gender Cool
ZP1U3EPU2FKAWI6K5US5LDV50KRI1LN7 Timmy Tesa 2010-01-01 Male PersonIsCool
IA26X38HOTOIBHYIRV8CKR5RDS8KNGHV Fela Falla 1999-1-1 Female NotHip
LASDU89NRABVJWW779W4JGGAN90IQ5B2 Sarah Kerrigan 1992-04-01 Female PersonIsCool

Using AND with CASE WHEN

peopleDf3 <- sqldf("SELECT *, 
                  CASE WHEN FirstName = 'Sarah' AND LastName = 'Kerrigan' THEN 'Yes' ELSE '' 
                  END As 'Queen of Blades' 
                  FROM peopleDf")
PersonalID FirstName LastName DOB Gender Queen of Blades
ZP1U3EPU2FKAWI6K5US5LDV50KRI1LN7 Timmy Tesa 2010-01-01 Male  
IA26X38HOTOIBHYIRV8CKR5RDS8KNGHV Fela Falla 1999-1-1 Female  
LASDU89NRABVJWW779W4JGGAN90IQ5B2 Sarah Kerrigan 1992-04-01 Female Yes

Using SUM with CASE WHEN

Using CASE WHEN in combination with SUM is a great way to get counts of different discrete data. Below is an example of getting total counts of males and females within the peopleDf

count1 <- sqldf("SELECT 
                      CASE WHEN Gender = 'Female' THEN 1 ELSE 0 END
                    ) As 'NumberOfFemales',
                      CASE WHEN Gender = 'Male' THEN 1 ELSE 0 END
                    ) As 'NumberOfMales'
                   FROM peopleDf")
NumberOfFemales NumberOfMales
2 1

Using Multiple CASES

So far, we’ve only covered one if-then statement, but in our example with the toothpaste we could string them together. The same can be done with CASE WHEN.

peopleDf4 <- sqldf("SELECT *, CASE WHEN DOB >= '1980-01-01' AND DOB < '1990-01-01' THEN 'X'
                           WHEN DOB >= '1990-01-01' AND DOB < '2000-01-01' THEN 'Y'
                           WHEN DOB >= '2000-01-01' AND DOB < '2010-01-01' THEN 'Millennial'
                           WHEN DOB >= '2010-01-01' AND DOB < '2020-01-01' THEN 'NotYetDefined'
                           END As 'Generation'
                   FROM peopleDf")
PersonalID FirstName LastName DOB Gender Generation
ZP1U3EPU2FKAWI6K5US5LDV50KRI1LN7 Timmy Tesa 2010-01-01 Male NotYetDefined
IA26X38HOTOIBHYIRV8CKR5RDS8KNGHV Fela Falla 1999-1-1 Female Y
LASDU89NRABVJWW779W4JGGAN90IQ5B2 Sarah Kerrigan 1992-04-01 Female Y


The paste() in R is meant for manipulating strings of text. You pass it strings as parameters and it returns one string containing all the strings passed into it. Let’s take a look.

greeting <- paste("Hello how are you,", "Bob?")

After running this line the greeting variable contains the following string Hello how are you, Bob?. This can be used by printing the contents of the variable using the print()


Side note, print() will actually print out anything you pass it to the console. This can be useful when trying to debug code.

Back to our combined strings, notice whenever the greeting prints out there is a space inserted between ‘you,’ and ‘Bob?’, this is done automatically by paste. It will insert a space between every string you pass it, unless you pass the additional parameter sep. This parameter will take whatever you set it as and insert it between the two strings.

greeting <- paste("Hello how are you,", "Bob?", sep = "!!")

This time print() will display “Hello how are you,!!Bob?” in the console. But, inserting exclamation marks is probably not what we want. Most of the time we will not want paste to insert anything and we can tell it to insert nothing.

greeting <- paste("Hello how are you,", "Bob?", sep = "")

Print will spit out “Hello how are you,Bob?”. Notice, there is no longer any character between “you,” and “Bob?”.

Paste is a pretty straightforward function, the one last trick is knowing you can pass in multiple strings.

greeting <- paste("Hello", " how are you,", " Bob?", sep = "")

This will produce the string “Hello how are you, Bob?”. Notice the spaces were inserted manually so the end string is readable to humans.

Dynamic SQL with Paste()

Prepare to have your mind blown. One of the powers of the paste() is building a sqldf string. Remember using SQLdf like this?

################### Data DO NOT CHANGE ###########################
                       FirstName=c("Timmy", "Fela", "Sarah"),
                       LastName=c("Tesa", "Falla", "Kerrigan"),
                       DOB=c("2010-01-01", "1999-1-1", "1992-04-01"))

peopleDf1 <- sqldf("SELECT * FROM peopleDf WHERE DOB > '2001-01-01'")

This creates the table

PersonalID FirstName LastName DOB
ZP1U3EPU2FKAWI6K5US5LDV50KRI1LN7 Timmy Tesa 2010-01-01

This is a dataframe of everyone who was born after January 1st, 2001. This method of filtering data works for a static date. But let’s say you wanted to easily change out the 2001-01-01 with other dates. You could replace the date with a different date, but when that date is in multiple SQL calls it can be easy to miss one. A better way to do it is using the paste(). And remember, everything inside the sqldf() parentheses is a string.

targetDate <- "2001-01-01"
sqlString <- paste("SELECT * FROM peopleDf WHERE DOB > '", targetDate, "'", sep = "")
peopleDf5 <- sqldf(sqlString)

Ok, let’s take this slow, there’s a lot going on. First, we create a variable called targetDate and assign it the string 2001-01-01. Next, we create a complex string using the paste() which looks a lot like a SQLdf string, but instead of hardcoding the date, we insert the targetDate variable. This creates the following string:

"SELECT * FROM peopleDf WHERE DOB > '2001-01-01'"

Which is then inserted into the variable sqlString, which is a string.

Lastly, we pass the sqlString variable into the sqldf() which executes the fancy SQL query. Awesome, right?

Now, if we want to look at those born after a different date, we simply change the targetDate variable and re-run the script.

targetDate <- "1980-01-01"
sqlString <- paste("SELECT * FROM peopleDf WHERE DOB > '", targetDate, "'", sep = "")
peopleDf5 <- sqldf(sqlString)