Archive for the ‘Quick Tips’ Category

SQL Server: Delete all tables in a database (T-SQL)

USE MyDB
GO
EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”
GO
microsoft-sql-00

R: Annotate the panels in a multi-panel lattice plot in 1 line

Just use panel.lmline()

Python: Get the Directory of a Given File in 1 Line

 os.path.dirname(os.path.realpath(__file__)) 

python1

Quick-Tip: 3 Ways to Load Ctypes in Python

  1. cdll()
  2. windll()
  3. oledll()

python-300x187

Ten 1-line R Hacks to Boost your Productivity

Here are 10 1-line R hacks that will streamline your workflow and increase your productivity.

 

1. Install problematic packages from source (solves 99% of installation problems)

install.packages(file_name_and_path, repos = NULL, type="source")

The Bash/shell equivalent would be:

 R CMD INSTALL source.library 

2. Count number of unique rows with a condition

 length(unique(df$col[df$col > 0])) 

Rlogo3. Force R not to use scientific notation

options("scipen"=100, "digits"=4)

4. Count number of non-unique, non-NA rows with a condition

sum(data$x<0, na.rm=T)

5. Randomly order rows (useful for drawing a random sample)

sample(1:nrow(df))

6. Conduct pattern matching with wildcards (no REGEX required)

 glob2rx("blue*")[1] "^blue"

7. Add a column of row numbers to a dataframe/object

df$row <- 1:nrow(df)

8. Loop through each unique date and build a subset

uniq <- unique(unlist(data$Date)); for (i in 1:length(uniq)){ data_1 <- subset(data, date == uniq[i]) #your desired function}

9. Drop columns by name

treatment.data <- treatment.data[, !names(treatment.data) %in% c(“name”, “name1”, “name2”)]

10. Append something to column names

names(df) <-  str_c(nam, '.yourstring’)

Quick Tip: Create Table as Select (CTaS) Hack in MS SQL Server in 1 Line

select * into target_table from [DB]. [dbo].[table]

Quick Tip: Check Last Update Time of a SQL Table (Note: requires permission)

SELECT OBJECT_NAME (OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys .dm_db_index_usage_stats
WHERE database_id = DB_ID( ‘TomasTest’)
AND OBJECT_ID =OBJECT_ID( ‘dbo.forecast’)

Quick Tip: Show Postgres/pSQL Tables in Postgres OR R in 1 Line

Outside of R:

\dt

In R:

dt <- dbGetQuery(con1, “SELECT * FROM pg_catalog.pg_tables”)

 

Quick Tip: Search for SQL User-defined Functions in 1 Line

SELECT * FROM sys.objects WHERE RIGHT(type_desc, 8) = 'FUNCTION'
 

SAS: How to Define a Variable for Path to Folder/Project

%let prj = X:\Your\Path\;
libname dat “&prj” ;
 
It’s a best practice to use this because it makes it easier for other people to edit and reuse with their own paths later.
Stackoverflow Solutions

Just started! Have not answered any questions.