Add 0's to character column

Question

I have a dataframe where there is a column of character values:

df1
Col1     Col2    
   A        1
   B        2
   C       11
   D      101
   E     1000

I have another dataset that also contains character values but each value has 5 numbers even if it is a small number like below:

df2
Col1A     Col2    
   F     00001
   G     00002
   H     00011
   I     00101
   J     01000

How do I add the 0's to df1 so that I can join the two dataframes together?

Expected Output:

df1
Col1      Col2     Col1A
   A     00001         F
   B     00002         G
   C     00011         H
   D     00101         I
   E     01000         J

Or how about removing 0's from `df2$Col2` and make it numeric ? That would be more reliable. — Ronak Shah, Oct 16 '18 at 14:07
strings: `sprintf("%05s", c("1", "2", "11", "101", "1000"))` ; numbers: `sprintf("%05d", c(1, 2, 11, 101, 1000))` — hrbrmstr, Oct 16 '18 at 14:10
The only reason why I wanted to change the one without the 0's is because that is the only dataset that doesn't have all the 0s and I would have to change multiple datasets — nak5120, Oct 16 '18 at 14:12
@hrbrmstr It looks like OP has numbers in `Col2`. So by "reliable" I mean let's keep numbers as numbers and not treat them as characters. `sprintf("%05d", c(1, 2, 11, 101, 1000))` returns character. — Ronak Shah, Oct 16 '18 at 14:13
@hrbrmstr can you mark the one that I upvoted as an answer. That was the easiest solution and worked best. — nak5120, Oct 16 '18 at 14:21
Based on @hrbrmstr comment, this worked for me and was the easiest way to do this: `df1$Col2<- sprintf("%05d", df1$Col2)` `df1$Col2<-as.character(df1$Col2)` — nak5120, Oct 16 '18 at 14:25

score 1 · Accepted Answer · answered Oct 16 '18 at 14:24

Data:

read.table(text="Col1     Col2    
A        1
B        2
C       11
D      101
E     1000", colClasses=c("character", "character"), header=TRUE) -> xdf1

read.table(text="Col1A     Col2    
F     00001
G     00002
H     00011
I     00101
J     01000", colClasses=c("character", "character"), header=TRUE) -> xdf2

0 dependency base R:

setNames(merge(
  { xdf1$Col3 <- sprintf("%05s", xdf1$Col2) ; xdf1 },
  xdf2, by.x = "Col3", by.y = "Col2"
)[,c(2,1,4)], c("Col1", "Col2", "Col1A"))
##    Col1  Col2 Col1A
## 1    A 00001     F
## 2    B 00002     G
## 3    C 00011     H
## 4    D 00101     I
## 5    E 01000     J

xdf1$Col3 <- NULL # to preserve the original data frame

jyjek · Answer 2 · 2018-10-16T14:36:45.433

0

Like this?

 library(tidyverse)
 df1<-data.frame(Col1=c("A","B","C","D","E"),
                Col2=c(1,2,11,101,1000))

 df2<-data.frame(Col1=c("00001","00002","00011","00101","01000"),
                 Col2=c("F","G","H","I","J"))

 your_function=function(x){
   if(nchar(x)<5){
     x<-glue::collapse(c(rep("0",5-nchar(x)),x),sep="")
   }
   return(x)
 }
 df1%>%
   mutate(Col2=purrr::map_chr(Col2,your_function))%>%
   left_join(df2,by=c("Col2"="Col1"))
  Col1  Col2 Col2.y
1    A 00001      F
2    B 00002      G
3    C 00011      H
4    D 00101      I
5    E 01000      J

edited Oct 16 '18 at 14:36

answered Oct 16 '18 at 14:14

jyjek

2,627
11
23

you should really microbenchmark the difference between that `glue` idiom and `sprintf()`. i suspect the time savings are not trivial. also, your `df1` and `df2` do not represent what the OP says they have. – hrbrmstr Oct 16 '18 at 14:32

Add 0's to character column

2 Answers2