logo
Tags down

shadow

When and how to use as.name() vs.get() in data.table (ex. in looping over columns)?


By : Abhi Kashyap
Date : October 17 2020, 06:10 AM
fixed the issue. Will look into that further To answer the overall question - "When and how to use as.name() vs.get() in data.table?".
In my experience, it is rarely needed when you can use existing R programming methods:
code :
dt0[, 
  newcol := Reduce(`+`, Map(`*`, c(100,75,50,25,0), .SD)) / Reduce(`+`, .SD),
  .SDcols = ANSWER1:ANSWER5
]

#   ANSWER1 ANSWER2 ANSWER3 ANSWER4 ANSWER5 ANSCOUNT SCORE100   newcol
#1:      21      37      16      14      11   200784       61 60.85859
#2:      20      37      16      15      11   177165       60 60.10101


Share : facebook icon twitter icon

Looping through table columns


By : logslogs
Date : March 29 2020, 07:55 AM
it should still fix some issue I have a query that is supposed to loop through all the tables in the database. It does that fine, I test it by outputting the table names. , Something like :
code :
$host = "127.0.0.1";
$username = "username";
$password = "password";
$database = "database";

$link = new mysqli($host, $username, $password, $database);

if($link->connect_error)
{
    die("Connection died: ".$link->connect_error);
}

$showtables = $link->query("DESCRIBE name_of_table;");

foreach($showtables->fetch_all() as $table)
{
    printf($table[0] . "\n");
}
select * from information_schema.columns
where table_schema = 'your_db'
order by table_name,ordinal_position

R data.table looping over columns to conditionally replace row values


By : Frug
Date : March 29 2020, 07:55 AM
I hope this helps . Looking for what should be an incredibly simple solution. I'd like to conditionally replace a value in a row if it meets a given condition (less than zero), and I would like to do this for hundreds (twenty in the example) of columns each with 150 million rows. I'm on hour seven of trying every solution that I found on the stack, so please don't mark this as duplicate. :-) , Option 1 using :=:
code :
dt[, (paste0("y", 50:70)) := lapply(.SD, function(x) {x[x<0] <- 0; x}), .SDcols=paste0("y", 50:70)]
for (j in paste0("y", 50:70)) {
    set(dt, dt[,which(get(j) < 0)], j, 0)
}
library(data.table)
dt <- data.table(id=c(1:1000), x=rnorm(1:1000,60,20))
for(i in 50:70) {
    dt[, paste0("y", i) := i-x]
}

Cleaning data dynamically after looping through table and columns


By : user2300511
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further Before I insert the results into the staging table, it needs to be cleaned of all white spaces and carriage returns. I can sadly not provide sample data because it is private company data. All tables have different columns of type NVARCHAR* , You should probably replace
code :
INSERT INTO [LIST].[STG].[' + @NAME + '] (' + @CNAME + ', [Inserted_Date])
SELECT ' + @CNAME + ', GETDATE()
FROM #TEMP2
INSERT INTO [LIST].[STG].[' + @NAME + '] (' + @CNAME + ', [Inserted_Date])
SELECT ' + REPLACE(REPLACE(REPLACE(CAST(@CNAME AS NVARCHAR(MAX)), CHAR(13), ''), CHAR(10), ''), ' ','') + ', GETDATE()
FROM #TEMP2

Looping over columns in data.table R


By : user3595854
Date : March 29 2020, 07:55 AM
it should still fix some issue I am trying to loop over columns in data.table package in R. I have been having trouble trying to get the for loop to accurately input the column when I subset the datatable. , You can use colSums, which is much simpler and faster than looping.
code :
dt <- data.table(va=c(1,0,1), vb=c(1,0,0), vc=c(1,1,1))
col.names <- c("va", "vc")
dt[, colSums(.SD==1), .SDcols = col.names]
# va vc 
# 2  3 
for (col in col.names) {
  dt[get(col) == 1, print(.N)]
}

How do I move the same columns data within the same table without looping


By : user3812123
Date : March 29 2020, 07:55 AM
I hope this helps you . I would suggest creating fake data instead of obfuscating real data. With fake data there's no possibility that you might expose sensitive information. Red Gate has a nifty tool to create test data, SQL Data Generator, that makes it trivial to generate realistic, fake data using various types of rules given your table schema.
Related Posts Related Posts :
  • R group_by return number of largest unique type
  • Different ways of selecting columns inside function resulting in different results, why?
  • How do I join a Y variable to each X variable in a dataframe?
  • World map: filtering by 'subregion' removes many regions
  • Generate data frame with parameters
  • Star (*) notation in R session Information
  • Why does formals function return NULL on functions defined with arguments?
  • How to call a list style parameter in snakemake
  • as_tibble only returns a single variable
  • Can't create design matrix from user input
  • R - how to sum each columns from df
  • R devtools::check LICENSE is not mentioned and other issues in DESCRIPTION FILE
  • Simple arithmetic leads to floating point difference in R
  • why does the data I input into R plot function change?
  • How can I import my data.frame as an igraph object?
  • Join each row with each other row
  • Join complementary dataframes, no NAs where a value is available
  • How to restart R and continue a benchmark script from previous line (on Windows)?
  • using dplyr to calculate consecutive days with a particular value
  • How to combine similar strings showing most common characters
  • Adjust spacing between text and chunk output in a R Markdown PDF document
  • Transform data to use lubridate on it
  • I need to know why I get the error 'unexpected input in "p<-ggplot(data=mov2, aes(x=Genre,y=Gross % US))" '
  • ggplot different lm formulas
  • change border color of a county in ggplot in R
  • position_dodgev causes error in order of connecting points in geom_line
  • How can I delete lines in which the name appears only once?
  • mutate_if, summarize_at etc coerce data.table to data.frame
  • How to get different values for same ID in dataframe. And replace any of that different value for the same ID
  • Lagging data based on condition (non-fix lag)
  • How to use 'sparklyr::replace.na()' for replacing NaN on one column?
  • How to create lollipop graph
  • R: Why is pmap not working while map2 does?
  • How to have different legends and colour schemes for different geom_*(aes(col= ) in ggplot?
  • How to check if a value under condition is within an interval under other condition in R?
  • Remove character string from multiple columns in R
  • subset a data frame with dplyr and conditions
  • R: How to show forecast and actual data in a single plot?
  • Calculate Grouped mean and populate in new column in R
  • Creating a new data set with same attributes (mean, skew, kurt, product) as old one in R
  • How to get series highlight on hover in highcharter?
  • cross validation predictions from H2O autoML model
  • Hack in R Markdown or Bookdown for including LaTeX environments which appear in html or docx output?
  • In R Shiny, can one interactively highlight cells using DT::dataframe?
  • Does the Sandwich Package work for Robust Standard Errors for Logistic Regression with basic Survey Weights
  • how to average rows based on two duplicated rows?
  • For loop help (R)
  • Is there a way around casting large integers as string when querying data from BigQuery through R?
  • Formatting datetime in Highcharter tooltip
  • R - How do I draw a radius around a point and use that result to filter other points?
  • How to order the coefficients in LM summary?
  • R: reordering columns based on order of different column
  • Find the maximum of a variable by overlapping time intervals
  • Vectorize function operating on a two-argument function
  • How to load rJava into RStudio?
  • Joining duplicate columns in single dataframe
  • I have some question about predicting new data in random forest
  • Overlaying a histogram with normal distribution
  • Warning message that giant component of disconnected graph is itself disconnected
  • How to sum categorical variable across variables
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk