How to preserve number-as-character fields with write.csv (base R)

-
I cannot believe I have spent quite some time on it.

The question in short:

I have variables in numbers: 00, 01, 10. When exporting to a csv file, they become 0, 1, 10. How to keep these numbers as characters? This discussion on the top of google has the same title:
Preserving number-as-character fields with write.csv (base R)

But the answers are confusing and the real answer is actually mentioned in the discussion of the replies. (It was mentioned as a premise in the question but very hard to notice if someone like me is just searching for an answer.)

The quick answer is:

Specify the colClasses options in the read.csv function in R.
  • It is not about the stringsAsFactors option. Actually, as briefed in the R document: stringsAsFactors "is overridden by as.is and colClasses, both of which allow finer control." 
  • It is not about adding "quote".
The right approach is not on the write.table side but on the read.table side.
# df is the sample dataset, all the variables are factors / characters
names(df) <- c("ID", "String_1", "String_2")
# just output it normally with colnames. #"append = F" will create a new file. 
write.table(df, "D:/NYU Marron/Data3_R/AnExample.csv",
          col.names = T, row.names = F, append = F, sep = ",")
  • Dataset:
## 'data.frame':    168 obs. of  3 variables:
##  $ ID      : chr  "100010550000310" "100010550000410" "100010560000110" ...
##  $ String_1: chr  "11" "00" "11" "11" ...
##  $ String_2: chr  "11" "00" "11" "11" ...
  • What I don't want: 
  • If read directly, for example, “00” would be 0, “01” would be 1.
test <- read.table("D:/NYU Marron/Data3_R/AnExample.csv",
                   sep = ",", header = T)
str(test)
## 'data.frame':    168 obs. of  3 variables:
##  $ ID      : num  1e+14 1e+14 1e+14 1e+14 1e+14 ...
##  $ String_1: int  11 0 11 11 11 10 0 11 11 0 ...
##  $ String_2: int  11 0 11 11 11 10 0 11 11 0 ...
  • What I wish to have:
  • If all of them are characters, then it is easy. Use colClasses to claim it.
test <- read.table("D:/NYU Marron/Data3_R/AnExample.csv",
                   sep = ",", header = T,
                   colClasses = "character")
str(test)
## 'data.frame':    168 obs. of  3 variables:
##  $ ID      : chr  "100010550000310" "100010550000410" "100010560000110" "100010560000210" ...
##  $ String_1: chr  "11" "00" "11" "11" ...
##  $ String_2: chr  "11" "00" "11" "11" ...
  • If not all the variables are characters, then we can specify the structure of each column. Let _String_1 be numeric:
test2 <- read.table("D:/NYU Marron/Data3_R/AnExample2.csv",
                   sep = ",", header = T,
                   colClasses = c(ID = "character",
                                  String_1 = "numeric",
                                  String_2 = "character"))
str(test2)
## 'data.frame':    168 obs. of  3 variables:
##  $ ID      : chr  "100010550000310" "100010550000410" "100010560000110" "100010560000210" ...
##  $ String_1: num  11 0 11 11 11 10 0 11 11 0 ...
##  $ String_2: chr  "11" "00" "11" "11" ...
  • Another option to write it:
  • Notice that the colClasses vector must have length equal to the number of imported columns.
test <- read.table("D:/NYU Marron/Data3_R/AnExample2.csv",
                   sep = ",", header = T,
                   colClasses = c("character","numeric","character"))
str(test)
## 'data.frame':    168 obs. of  3 variables:
##  $ ID      : chr  "100010550000310" "100010550000410" "100010560000110" "100010560000210" ...
##  $ String_1: num  11 0 11 11 11 10 0 11 11 0 ...
##  $ String_2: chr  "11" "00" "11" "11" ...
There are more technical discussions on this colClasses option in this post: Specifying colClasses in the read.csv. But the key points have already been illustrated above. 


Comments

Post a Comment

Popular posts from this blog

Power-law distribution (Pareto)& Zipf's Law: connection and how to fit the distribution of global city population

-
Image
- This post counts for 3 days. - In short,  We use the histogram to outline the distribution, use rank-frequency plot (log-log plot) to identify the power-law distribution, and use maximum likelihood estimation to obtain the parameters. -  I will show 1 st , how the power-law function CDF and PDF can be derived from Zipf CDF; and 2 nd , this least square estimation by excel is wrong and how to get the right one. Historical background on the power-law distribution. - Power-law distribution is as common as normal distribution in the real world. In the 19 th century, Italian economist Pareto realized that 20% of the population owns 80% the wealth, which is the famous 80/20 rule. He later named the power-law distribution as Pareto distribution, which is what we studied in the probability course. - In the 20 th century, another main contributor, Zipf made a similar discovery in linguistics about the frequency of words used. - Besides social wealth, many things follow a hig
Read more

How to Draw Heatmap with Colorful Dendrogram

-
Image
Heatmap with Dendrogram: Color Branches and Labels This data visualization example include: * Hierarchical clustering, dendrogram, and heat map based on normalized odds ratios * The dendrogram was built separately to give color to dendrogram’s branches/labels based on clusters using  dendextend * Heatmap is made by  heatmap.2  from  gplots  using the built dendrogram * The rows are sorted by means from highest to lowest, it can be done in either the dendrogram or the  heatmap.2 * Use color palettes from  colorspace Data YRBSS (Youth Risk Behavior Surveillance System) survey data from CDC. This example plots the odds ratios of 63 behavioral questions on one question regarding disordered eating. The odds ratios are median centered by column and log2 transformed Version 1: Color both the branches and labels library (curl) # read file from google drive library (gplots) # heatmap.2 library (dendextend) # make and color dendrogram library (colorspace) #
Read more

The Weighted Standard Deviation

-
Image
I have revised the figures in the Conflicts in Colombia paper to add weighted s.e. Together with confidence intervals. Turns out to be not that complicated. Weighted s.d.: Add { geom_errorbar(aes(ymin=..., ymax=...), width = 0.2 } in ggplot2 to add the confidence interval into plots: The paper discussed how conflicts in Colombia makes cities develop more sluggish and denser. I will work more on it in the later posts. The complete R code for the figure # weighted means for global 200 sample ggplot(data=m4_mean_g_g200, aes(x=year, y=wm, group=Group, color=Group, shape=Group))+ geom_point(size= 4 )+ geom_line(size= 1 )+ geom_errorbar(aes(ymin=CI_low, ymax=CI_high), width= .2 )+ ggtitle( "Urban Density Change Over Time in 200 Cities" ) + labs(x= "Year" , y= "Average City Density" , color = "" , shape= "" ) + scale_y_continuous(limits = c( 30 , 150 ))
Read more