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.