Access Data.Table Columns with Strings

Access data.table columns with strings

You can use get() as the j argument using single brackets:

library(data.table)
dt <- data.table(iris)
dt[, get("Species")]

The result:

[1] setosa     setosa     setosa     setosa     setosa     setosa .....

You can also use a string directly inside the double bracket operator, like this:

dt[["Species"]]

Get columns by string from data.table

It would be nice if you had provided a reproducible example, or at the very least shown what the column names of raw are and what r_responseTime and c_filesetSize contain. This being said, get is your function for dereferencing so give these a try:

raw[, get(col1)]
raw[, get(col2)]
plot(raw[, get(col1)] ~ raw[, get(col2)])

Get column results from DataTable into a string

Solution 1

1. Iterate each DataRow and get the value from DataColumn.

2. Add value into array/list.

3. Convert array/list to string with String.Join() with ; as separator.

using System.Collections.Generic;

List<string> emails = new List<string>();

foreach (DataRow row in dt.Rows)
{
    emails.Add(row["Email"].ToString());
}

string result = String.Join(";", emails);

---

Solution 2

1. Working with System.Linq to get the email as List<string>.

2. Convert array/list to string with String.Join() with ; as separator.

using System.Linq;

string result = String.Join(";", dt.AsEnumerable()
                                      .Select(x => x["Email"].ToString())
                                      .ToList());

Sample .NET Fiddle

Calculating columns using number strings in column names, data.table

I think pivoting (longer), summarizing, then unpivoting (wider) works. (I wonder if keeping it in long form might be better in the long run, over to you.)

library(data.table)
money_table <- setDT(structure(list(ID = 1:3, LunchMoney_1213 = c(12L, 234L, 14L), DinnerMondey_1213 = c(24L, 12L, 19L), LunchMoney_1314 = c(17L, 43L, 2L), DinnerMondy_1314 = c(18L, 44L, 12L)), row.names = c(NA, -3L), class = "data.frame"))

dcast(
  melt(money_table, id.vars = "ID"
    )[, yr := paste0("TotalMoney_", gsub(".*_", "", variable))
    ][, .(value = sum(value)), by = .(ID, yr)
    ],
  ID ~ yr, value.vars = "value")
#       ID TotalMoney_1213 TotalMoney_1314
#    <int>           <int>           <int>
# 1:     1              36              35
# 2:     2             246              87
# 3:     3              33              14

If you're already using magrittr for other things (whether with dplyr or not ... I use it with data.table all the time), this can be slightly more readable as:

library(magrittr)
melt(money_table, id.vars = "ID") %>%
  .[, yr := paste0("TotalMoney_", gsub(".*_", "", variable))] %>%
  .[, .(value = sum(value)), by = .(ID, yr)] %>%
  dcast(., ID ~ yr, value.vars = "value")

Use string as column name in data.table constructor

Maybe using setnames() like so:

dt <- setnames(data.table(1:5), mycol)

or

dt <- data.table(1:5)
setnames(dt, mycol) # Update by reference

How to retrieve an entire column from a data table using it's column name

Try following linq :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication108
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable dt = new DataTable();

            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("foo", typeof(int));
            dt.Columns.Add("bar", typeof(int));

            dt.Rows.Add(new object[] { 0 , 321 , 33  });
            dt.Rows.Add(new object[] { 1 , 100 , 4  });
            dt.Rows.Add(new object[] { 2 , 355 , 23  });

            List<int> results = dt.AsEnumerable().Select(x => x.Field<int>("foo")).ToList();

        }
    }
}

Find a string in all DataTable columns

This can be achieved by filtering. Create a (re-usable) filtering string based on all the columns:

        bool UseContains = false;
        int colCount = MyDataTable.Columns.Count;

        string likeStatement = (UseContains) ? " Like '%{0}%'" : " Like '{0}%'"; 
        for (int i = 0; i < colCount; i++)
        {
            string colName = MyDataTable.Columns[i].ColumnName;
            query.Append(string.Concat("Convert(", colName, ", 'System.String')", likeStatement));

            if (i != colCount - 1)
                query.Append(" OR ");
        }

        filterString = query.ToString();

Now you can get the rows where one of the columns matches your searchstring:

 string currFilter = string.Format(filterString, searchText);
 DataRow[] tmpRows = MyDataTable.Select(currFilter, somethingToOrderBy);

---

Related Topics