CODEWITHSUNDEEP
r
kelvinfrog
kelvinfrog

Reputation: 467

Make another column based using some values in other column

Table look like this and the SampleID and Number is the column names:

SampleID Number
G2Day2   15
G2Day4   11
G11Day13 5

I want to make another column using the values in the SampleID, so the new table will look like this:

SampleID Group Day Number
G2Day2   G2    2   15
G2Day4   G2    4   11
G11Day13 G11   13  5

Upvotes: 1

Views: 97

Answers (5)

Ken
Ken

Reputation: 2808

If your new table already has the column names something like this is what you would do. Pseudo code here: 



    foreach(Datarow row in myoriginaltable.rows)
    {
    int i = 0;
    // Get an index reference to the row you create and add to the new table
    i = myNewTable.AddRow(new DataRow);


myNewTable.Rows[i].Column["Number"] = row.Column["Number"];
// where ColumnName is the name of your column that you want to transfer from one to the other.
myNewTable.Rows[i].Column["ColumnName"] = row.Column["ColumnName"];

// Create a Column , give it a value from the row and Column["ColumnName"] 
DataColumn dc = new DataColumn(row.Column["ColumnName"].Value.ToString());
myNewTable.Columns.Add(dc);
}
</code></pre>

Upvotes: -2

David Arenburg
David Arenburg

Reputation: 92282

Here's a tidyr solution

library(tidyr)
cbind(df[1], separate(df, SampleID, c("Group", "Day"), "Day"))
#   SampleID Group Day Number
# 1   G2Day2    G2   2     15
# 2   G2Day4    G2   4     11
# 3 G11Day13   G11  13      5

Or similarly with data.table v >= 1.9.5

library(data.table) # v 1.9.5
setDT(df)[,  c("Group", "Day") := tstrsplit(as.character(SampleID), "Day")][]
#    SampleID Number Group Day
# 1:   G2Day2     15    G2   2
# 2:   G2Day4     11    G2   4
# 3: G11Day13      5   G11  13

Upvotes: 2

rawr
rawr

Reputation: 20811

with a little regex:

dat <- read.table(header = TRUE, text="SampleID Number
G2Day2   15
G2Day4   11
G11Day13 5")

within(dat, {
  Group <- gsub('Day\\d+', '', SampleID)
  Day <- gsub('.*Day(.*)', '\\1', SampleID)
})

#   SampleID Number Day Group
# 1   G2Day2     15   2    G2
# 2   G2Day4     11   4    G2
# 3 G11Day13      5  13   G11

Upvotes: 1

shadow
shadow

Reputation: 22293

You can use strsplit to split the sampleID and then rbind the resulting list together. 

df[, c("Group", "Day")] <- do.call(rbind, strsplit(as.character(df[, "SampleID"]), "Day"))
df[, c("SampleID", "Group", "Day", "Number")]
##   SampleID Group Day Number
## 1   G2Day2    G2   2     15
## 2   G2Day4    G2   4     11
## 3 G11Day13   G11  13      5

Upvotes: 5

data paRty
data paRty

Reputation: 218

gsub can do this particular task for you quite easily. Try this:

df$Group <- gsub(pattern = "Day.*", replacement = "", x=df$SapmleID)
df$Number <- gsub(pattern = ".*Day", replacement = "", x=df$SampleID)

The .* is a regex that represents any number of characters

Upvotes: 4

Related Questions