Combine Data into One Column
Let’s perform the reverse action by combining data into one column with a spreadsheet formula, also called concatenation, using the ampersand symbol (
&). Imagine you receive address data in four separate columns: street address, city, state, and zip code.
| Street | City | State | Zip | | ------------- | ---------- | ------ | ----- | | 300 Summit St | Hartford | CT | 06106 |
But imagine you need to geocode the addresses using a tool like the one we introduced in Chapter 3, which requires all of the data to be combined into one column like this:
| Location | | --------------------------------- | | 300 Summit St, Hartford, CT 06106 |
Using any spreadsheet, you can write a simple formula to combine (or concatenate) terms
using the ampersand (
&) symbol. Also, you can add separators into your formula, such as quoted space (
" "), or spaces with commas (
", "), or any combination of characters. Let’s try it with some sample data.
- Open the Combine Separate Columns sample data in Google Sheets, sign in with your account, and go to File > Make a Copy to create a version you can edit in your Google Drive. The sheet contains addresses that are separated into four columns: street, city, state, and zip.
- In column E, type a new header named location.
- In cell E2, type in the following formula, which combines the four items using ampersands, and separates them with quoted commas and spaces, as shown in Figure 5.8, and press Enter.
=A2 & ", " & B2 & ", " & C2 & " " & D2
- Click cell E2 and drag the bottom-right corner cross-hair downward to fill in the rest of the column.
Now that you have successfully combined the terms into one location column, you can use the Geocoding by SmartMonkey Google Sheets Add-on we described in Chapter 3 to find the latitude and longitude coordinates, in order to map your data as we’ll discuss in Chapter 7
Note: Lisa Charlotte Rost from Datawrapper has written a brilliant blog post about cleaning and preparing your spreadsheet data for analysis and visualization, which we recommend for further reading.13
Spreadsheets are great tools to find and replace data, split data into separate columns, or combine data into one column. But what if your data table is trapped inside a PDF? In the next section, we will introduce Tabula and show you how to convert tables from text-based PDF documents into tables that you can analyze in spreadsheets.
Lisa Charlotte Rost, “How to Prepare Your Data for Analysis and Charting in Excel & Google Sheets,” Chartable: A Blog by Datawrapper, accessed August 28, 2020, https://blog.datawrapper.de/prepare-and-clean-up-data-for-data-visualization/index.html↩︎