extracting columns from 2 datasets

em_y · April 1, 2023, 3:41pm

Hi,

I have 2 datasets, one called "meta" containing sample names as row names, and one called "ITS_counts" containing the corresponding sample names as column names. I would like to extract the samples found from "meta" from ITS_counts to create a subtable. I have used this code...

bsswSamples=rownames(meta)[which(meta[,"Field"]=="BSSW")]

bsswSamples

output is [1] "12" "13" "14" "15" "16" "17" "18" "19" "20" "21" "22" "23"

subTable=ITS_counts[, bsswSamples]

output is:
Error in ITS_counts[, bsswSamples]:
! Can't subset columns that don't exist.
Columns 12, 13, 14, 15, 16, etc. don't exist.
Run rlang::last_trace() to see where the error occurred.

how would I resolve this issue to create a subtable of the extracted samples from ITS_counts dataset found in meta dataset? Thanks

jrkrideau · April 1, 2023, 4:46pm

Try this:

dat1   <- subset(meta, Field == "BSSW")
dat2  <- row.names(dat1)
new_data  <-  ITS_counts[ , dat2]

which is giving you the row number.

em_y · April 1, 2023, 5:21pm

it said this error when I ran that code...

Error in ITS_counts[, dat2]:
! Can't subset columns that don't exist.
Columns 1, 2, 3, 4, 5, etc. don't exist.
Run rlang::last_trace() to see where the error occurred.

jrkrideau · April 1, 2023, 6:42pm

What does dat1 look like?

I think we need to see some data both from ITS_counts and Meta.

Con you supply us with a bit of data from both? Probably 5 or 10 rows of each would be fine.

A handy way to supply some sample data is the dput() function. In this case, something like dput(head(mydata, 10)) should supply the data we need where "mydata" ins the name of your data.frame or tibble.

Just do dput(head(Meta, 10)). Copy the output and paste it here.

Repeat for ITS_counts.

em_y · April 2, 2023, 9:36am

dput(head(ITS_counts, 10))
structure(list(OTU = c("OTU.W36.543937", "OTU.W25.265387", "OTU.W14.3337",
"OTU.W25.265241", "OTU.W23.192303", "OTU.W34.462356", "OTU.W22.160264",
"OTU.W66.1110248", "OTU.W50.759148", "OTU.W18.93769"), W14 = c(0L,
0L, 47L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W17 = c(0L, 0L, 76L, 0L,
0L, 0L, 0L, 0L, 0L, 0L), W18 = c(0L, 0L, 109L, 0L, 0L, 0L, 0L,
0L, 0L, 18L), W19 = c(0L, 0L, 187L, 2L, 0L, 0L, 0L, 0L, 0L, 0L
), W22 = c(0L, 0L, 239L, 0L, 0L, 0L, 25L, 0L, 0L, 0L), W23 = c(0L,
0L, 309L, 0L, 20L, 0L, 0L, 0L, 0L, 0L), W24 = c(0L, 0L, 126L,
0L, 0L, 0L, 0L, 0L, 0L, 0L), W25 = c(0L, 166L, 19L, 30L, 0L,
0L, 0L, 0L, 0L, 0L), W26 = c(0L, 0L, 4L, 0L, 0L, 0L, 0L, 0L,
0L, 0L), W27 = c(0L, 0L, 119L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W28 = c(0L,
0L, 108L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W33 = c(0L, 0L, 28L, 0L,
0L, 0L, 0L, 0L, 0L, 0L), W34 = c(0L, 0L, 25L, 0L, 0L, 10L, 0L,
0L, 0L, 0L), W35 = c(0L, 0L, 306L, 0L, 0L, 0L, 0L, 0L, 0L, 0L
), W36 = c(4L, 0L, 59L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W3 = c(0L,
0L, 1396L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W41 = c(0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L, 0L), W42 = c(0L, 0L, 0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L), W43 = c(0L, 0L, 42L, 0L, 0L, 0L, 0L, 0L, 0L, 0L),
W44 = c(0L, 0L, 75L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W50 = c(0L,
0L, 146L, 0L, 0L, 0L, 0L, 0L, 37L, 0L), W51 = c(0L, 0L, 27L,
0L, 0L, 0L, 0L, 0L, 0L, 0L), W52 = c(0L, 0L, 258L, 0L, 0L,
0L, 0L, 0L, 0L, 0L), W53 = c(0L, 0L, 50L, 0L, 0L, 0L, 0L,
0L, 0L, 0L), W58 = c(0L, 0L, 806L, 0L, 0L, 0L, 0L, 0L, 0L,
0L), W59 = c(0L, 0L, 16L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W60 = c(0L,
0L, 511L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W61 = c(0L, 0L, 597L,
0L, 0L, 0L, 0L, 0L, 0L, 2L), W65 = c(0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L), W66 = c(0L, 0L, 672L, 0L, 0L, 0L, 0L,
50L, 0L, 0L), W69 = c(0L, 0L, 228L, 0L, 0L, 0L, 0L, 0L, 0L,
0L), W6 = c(0L, 0L, 9L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W70 = c(0L,
0L, 24L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W73 = c(0L, 0L, 150L,
0L, 0L, 0L, 0L, 0L, 0L, 0L), W74 = c(0L, 0L, 322L, 0L, 0L,
0L, 0L, 0L, 0L, 0L), W76 = c(0L, 0L, 20L, 0L, 0L, 0L, 0L,
0L, 0L, 0L), W77 = c(0L, 0L, 47L, 0L, 0L, 0L, 0L, 0L, 0L,
0L), W79 = c(0L, 0L, 4L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W81 = c(0L,
0L, 1L, 0L, 0L, 0L, 0L, 0L, 1L, 0L), W83 = c(0L, 0L, 198L,
0L, 0L, 0L, 0L, 0L, 0L, 0L), W85 = c(0L, 0L, 0L, 0L, 0L,
0L, 0L, 0L, 0L, 0L), W86 = c(0L, 0L, 42L, 0L, 0L, 0L, 0L,
0L, 0L, 0L), W87 = c(0L, 0L, 2810L, 0L, 0L, 0L, 0L, 0L, 19L,
0L), W89 = c(0L, 0L, 110L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W8 = c(0L,
0L, 2L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), W95 = c(0L, 0L, 288L,
0L, 0L, 0L, 0L, 0L, 0L, 0L), W9 = c(0L, 0L, 31L, 0L, 0L,
0L, 0L, 0L, 0L, 0L), sum = c(4L, 166L, 10643L, 32L, 20L,
10L, 25L, 50L, 57L, 20L), taxonomy = c("k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Botryosphaeriales;f__Botryosphaeriaceae;g__Diplodia;s__Diplodia_subglobosa",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Capnodiales;f__Capnodiales_fam_Incertae_sedis;g__Vermiconia;s__Vermiconia_calcicola",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Capnodiales;f__Cladosporiaceae;g__Cladosporium;s__Cladosporium_exasperatum",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Capnodiales;f__Cladosporiaceae;g__Cladosporium;s__Cladosporium_halotolerans",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Capnodiales;f__Mycosphaerellaceae;g__Mycosphaerella;s__Mycosphaerella_ulmi",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Capnodiales;f__Mycosphaerellaceae;g__unidentified;s__unidentified",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Capnodiales;f__unidentified;g__unidentified;s__unidentified",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Capnodiales;f__unidentified;g__unidentified;s__unidentified",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Dothideales;f__Dothioraceae;g__Aureobasidium;s__Aureobasidium_pullulans",
"k__Fungi;p__Ascomycota;c__Dothideomycetes;o__Dothideomycetes_ord_Incertae_sedis;f__Dothideomycetes_fam_Incertae_sedis;g__Biatriospora;s__Biatriospora_mackinnonii"
), p.value = c(1e-126, 5e-110, 5e-113, 9e-118, 8e-112, 8e-115,
3e-71, 1e-76, 9e-121, 5e-79), something = c("SH419951.07FU_GQ923856_refs",
"SH585084.07FU_KP791759_refs", "SH422672.07FU_HM148090_refs",
"SH127902.07FU_DQ780364_refs", "SH205983.07FU_DQ019377_reps",
"SH206860.07FU_KX287539_reps", "SH202792.07FU_JF691278_reps",
"SH192620.07FU_AM999677_reps", "SH195774.07FU_AJ244232_refs",
"SH204844.07FU_KF015654_refs"), sequence = c("AACGCACATTGCGCCCCTTGGCATTCCGAGGGGCATGCCTGTTCGAGCGTCATTACAACCCTCAAGCTCTGCTTGGTATTGGGCGCCGTCCTCTCTGCGGACGCGCCTTAAAGACCTCGGCGGTGGCTGTTCAGCCCTCAAGCGTAGTAGAATACACCTCGCTTTGGAGCGGTTGGCGTCGCCCGCCGGACGAACCTTCTGAACTTTTCTCAAGGTTGACCTCGGATCAGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCCTTGGTATTCCGGGGGGCATGCCCGTTCGAGCGTCATTACACCACTCAAGCCTGGCTTGGTATTGGGCGTCCGGCACTCACCTGCCCGCCCCAAATTCTCTTCGGCTGATCGGTTTACTTTCTTAGCGTTGGAGTGTTGTCATTACGTTCCGCTAATCGAAAGACGCCGGCCGGCCGTTAAACCATTTAATCACGGTTGACCTCGGATCGGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCCCTGGTATTCCGGGGGGCATGCCTGTTCGAGCGTCATTTCACCACTCAAGCCTCGCTTGGTATTGGGCAACGCGGTCCGCCGCGTGCCTCAAATCGACCGGCTGGGTCTTCTGTCCCCTAAGCGTTGTGGAAACTATTCGCTAAAGGGTGTTCGGGAGGCTACGCCGTAAAACAACCCCATTTCTAAGGTTGACCTCGGATCAGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCCCTGGTATTCCGGGGGGCATGCCTGTTCGAGCGTCATTTCACCACTCAAGCCTCGCTTGGTATTGGGCGACGCGGTCCGCCGCGCGCCTCAAATCGACCGGCTGGGTCTTTCGTCCCCTCAGCGTTGTGGAAACTATTCGCTAAAGGGTGCCGCGGGAGGCCACGCCGTAAAACAACCCCATTTCTAAGGTTGACCTCGGATCAGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCTCTGGTATTCCGGAGGGCATGCCTGTTCGAGCGTCATGTCACCACTCAAGCTTGGCTTGGTGTTGGGCGCCGCGGTCCCCCCGCGCGCCTCAAAGTCCCCCGGCTGAGCCGTCCGCCTCCAAGCGTCGTGATTTCATTAATCGCTTCGGGGCGCGGGCGGCCGCGGCCGTTAAATCTCGTCACTGGTTGACCTCGGATCAGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCCCTGGTATTCCGGGGGGCATGCCTGTTCGAGCGTCATTTCACCACTCAAGCCTCGCTTGGTATTGGGCGTCGCGAGTCTCTCGCGCGCCTCAAAGTCTCCGGCTGAGCGGTTCGTCTCCCAGCGTTGTGGCAACTATTCGCAGAGGAGTTCGAGTCGTCGCGGCCGTTAAATCTTTCAAAGGTTGACCTCGGATCAGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCCTTGGCATTCCGGGGGGCATGCCCGTTCGAGCGTCATTAAACCCCATCAAGCCTGGCTTGGTATTGGGCGTCGGGGGTCCCCCGCGCCTCAATTTCACCGGCTGGCCGTCGCCCGTCTTAGCGTTGTGACACACTTCTCGCTAGCGATGGGACCGGACCACGCCGTTAGAACCCCACGAATTTCAATCGAAATTCGATCAAGGTTGACCTCGGATCGGGTAGGAATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCTTTGGCATTCCGAAGGGCATGCCCGTTCGAGCGTCATTACACCACTCAAGCCTGGCTTGGTATTGGGCGCCGGGAGTCTCCCGCGCCCCTAATTCGTCGGCTTGACGGTTCGAATCTCAGCGTTGTGGTTTCATCAAATCGCTGGCGAGGACGACCGGACGCGCCGTAAAACATTCATTACAGGTTGACCTCGGATCGGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCCTTGGTATTCCGAGGGGCATGCCTGTTCGAGCGTCATTACACCACTCAAGCTATGCTTGGTATTGGGCGTCGTCCTTAGTTGGGCGCGCCTTAAAGACCTCGGCGAGGCCACTCCGGCTTTAGGCGTAGTAGAATTTATTCGAACGTCTGTCAAAGGAGAGGAACTCTGCCGACTGAAACCTTTATTTTTCTAGGTTGACCTCGGATCAGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA",
"AACGCACATTGCGCCCTTCGGTATTCCGTTGGGCATGCCTGTTCGAGCGTCATTAGTATATTCAAGCTCAGCTTGGTGTTGGGTGCTTGTCCTCCCCCGCGGTGGACTCACCTCAAATGCATTGGCGGCCGGTATGTTGGCTTCGAGCGCAGTAGAAACGTGCCCGGTGTCCTGACGTACTGGTCCCCCACAAGATCATTTTTCAGCTTGACCTCGGATCAGGTAGGGATACCCGCTGAACTTAAGCATATCAATAAGCGGAGGA"
)), row.names = c(NA, 10L), class = "data.frame")

dput(head(meta, 10))
structure(list(Sample = c("W3", "W6", "W8", "W9", "W14", "W17",
"W18", "W19", "W22", "W23"), Field = c("BSSE", "BSSE", "BSSE",
"BSSE", "BSSE", "BSSE", "BSSE", "BSSE", "BSSE", "BSSE"), HedgeLocation = c(1,
1, 1, 2, 2, 3, 3, 3, 3, 3), Root number = c(3, 6, 8, 9, 14,
17, 18, 19, 22, 23), Latitude = c(53.86877, 53.86877, 53.86877,
53.868908, 53.868908, 53.869014, 53.869014, 53.869014, 53.869014,
53.869014), Longitude = c(-1.326241, -1.326242, -1.326243, -1.326051,
-1.326052, -1.325844, -1.325845, -1.325846, -1.325847, -1.325848
)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"
))

jrkrideau · April 2, 2023, 11:38am

It looks like the problem is that meta does not have row.names. Neither data.frame have row.names

row.names(meta)
 [1] "1"  "2"  "3"  "4"  "5"  "6"  "7"  "8"  "9"  "10"
row.names(ITS_counts)
 [1] "1"  "2"  "3"  "4"  "5"  "6"  "7"  "8"  "9"  "10"

Just a guess but will

new_data  <-  ITS_counts[ , meta$Sample]

give you what you need?

BTW, you have a column in meta called "Root number". R does not deal well with spaces in a variable name. If possible I'd suggest renaming it, Otherwise you will need to escape it:

`Root number`

em_y · April 2, 2023, 4:44pm

new_data gives output ...

A tibble: 939 × 47

  W3    W6    W8    W9   W14   W17   W18   W19   W22   W23   W24   W25   W26

1 0 0 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0 0 0 166 0
3 1396 9 2 31 47 76 109 187 239 309 126 19 4
4 0 0 0 0 0 0 0 2 0 0 0 30 0
5 0 0 0 0 0 0 0 0 0 20 0 0 0
6 0 0 0 0 0 0 0 0 0 0 0 0 0
7 0 0 0 0 0 0 0 0 25 0 0 0 0
8 0 0 0 0 0 0 0 0 0 0 0 0 0
9 0 0 0 0 0 0 0 0 0 0 0 0 0
10 0 0 0 0 0 0 18 0 0 0 0 0 0

929 more rows

34 more variables: W27 , W28 , W33 , W34 , W35 ,

W36 , W41 , W42 , W43 , W44 , W50 , W51 ,

W52 , W53 , W58 , W59 , W60 , W61 , W65 ,

W66 , W69 , W70 , W73 , W74 , W76 , W77 ,

W79 , W81 , W83 , W85 , W86 , W87 , W89 ,

W95

Use `print(n = ...)` to see more rows

what would I do next?

jrkrideau · April 2, 2023, 6:51pm

I am afraid the question is a bit vague.

Does the result look like something you were expecting? Does it look like you have the variables you want?

em_y · April 2, 2023, 6:55pm

I am attempting to create a subtable of ITS_counts, containing only the rows that I have isolated from the table meta (the row names of meta correspond to the column names of ITS_counts). How would I generate this subtable?

bsswSamples=rownames(meta)[which(meta[,"Field"]=="BSSW")]

allows me to isolate the row numbers from meta, which I then need to use to isolate the corresponding columns from ITS_counts for the subtable.

subTable=ITS_counts[, bsswSamples]

should work, but gives the error

Error in ITS_counts[, bsswSamples]:
! Can't subset columns that don't exist.
Columns 12, 13, 14, 15, 16, etc. don't exist.
Run rlang::last_trace() to see where the error occurred.

let me know if you need any further information. Thank you.

jrkrideau · April 2, 2023, 6:58pm

We have a new data set new_data . Does that look anything like what you want?

em_y · April 2, 2023, 6:59pm

would that new dataset be substituted into subTable=ITS_counts[, bsswSamples] instead of ITS_counts to generate the subtable?

jrkrideau · April 2, 2023, 7:05pm

I do not know enough about what you are trying to do to say yes or no.

We need more information about what you are doing and what your code looks like to try and ansver such a question.

You might find this FAQ useful. We have some idea of the structure of your data but no idea in substantive or R coding of what you are trying to accomplish.

em_y · April 2, 2023, 7:18pm

I have managed to sort it out now!! thank you for your patience and help!!

system · April 9, 2023, 7:19pm

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.