Preserving How String Columns Look When Reading Data into R

Tara Chiatovich
Nov 12, 2019 · 2 min read

I work primarily with quantitative data, so recently when I had a few projects that involved merging on strings, I had to enter a new, messy world. My first challenge? Encountering bizarre character strings from a .csv file that I read into R. As many in the quantitative analysis field have been trained to do (see the From Excel section in this tutorial and the Answer to this StackOverflow question), I started by saving my .xlsx file as a .csv. This step resulted in bits of string that looked completely normal when viewing in Excel, as shown below:

Image for post
Image for post

And then in R, those same bits got messed up, as shown here:

Image for post
Image for post

Certainly, this threw more than a small monkey wrench into my task of merging data by string columns. If you Google this problem, you might think you need to specify the encoding in R (e.g., encoding = “utf-8”).

But the solution is actually much simpler: Read the data in directly from Excel instead!

Obviously, for this solution to work, you need to have your data saved as an .xlsx spreadsheet in the first place. The following code walks through the few steps needed:

# Load library needed to read directly from Excel
library(openxlsx)
# Establish the file path for the working directory
wd_file_path <- “~/example_directory/”
# Set the working directory to the file path established above
setwd(“wd_file_path”)
# Read in the data!
matched_content <- read.xlsx(“example_data_with_strings.xlsx”)

How the same string looks in R now:

Image for post
Image for post

Problem solved!

Research at Panorama Education is expanding! Check out this new Senior Data Scientist role.

Building Panorama Education

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store