Archive for the ‘Quick Tips’ Category
SQL Server: Delete all tables in a database (T-SQL)
USE MyDB
GO
GO
EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”
GO
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__))
Quick-Tip: 3 Ways to Load Ctypes in Python
- cdll()
- windll()
- oledll()
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]))
3. 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_statsWHERE 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.