Code snippet collection

A collection of snippets that might be useful for among others:

Linux
sql
R
python
iPython
conda

and more

Screen

screen -S <name_screen>

ctrl+a, d -> detach -> go back to original terminal screen -ls -> show all active screens screen -r to reconnect

Cleaning Folders

find . -name "*.pkl" -delete du -h --max-depth=1 | sort -rh

Firefox

"security.enterprise_roots.enabled" to trust company root certs

VS Code

vim.useSystemClipboard

pytest --collect-only

VIM

Surround Command Description

y s Add desired surround around text defined by

d s Delete existing surround

c s Change existing surround to desired

S Surround when in visual modes (surrounds full selection)

Linux

Execute an .sh file: You might need chmod +x file.sh, then you can execute it with ./file.sh.

Add ssh key to server: ssh-copy-id -i ~/.ssh/mykey.pub user@host

System commands: ps -u ... list all user processes pkill <-9 or -15> -u ... kill all user processes: -9 force, -15=default: let them shut down free -g ... ram in GB

du -h folder ... disk usage folder human readable

sql

Oracle snippet

select date '2000-01-02' - date '2000-01-01' as dateDiff
from dual;

Oracle compression

create table xxx compress as select * from yyy

alter table xxx move compress online

UPDATE table
SET 
    column = value
WHERE column1 = condition
;
COMMIT;

cte

with cte1 as (
  select *
  from dbo.someTable
), cte2 as (
  select *
  from cte1
)  select *
from cte2

SELECT COLUMN_VALUE AS id
FROM   TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       )

SYS.ODCIVARCHAR2LIST and SYS.ODCINUMBERLIST are collection types that are supplied in the SYS schema.

You can join this directly to whichever table you are SELECTing from without needing to use the DUAL table:

SELECT y.*
FROM   your_table y,
       TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       ) i
WHERE  y.id = i.COLUMN_VALUE;

Source: https://stackoverflow.com/a/41161057

sql switch using case:

SELECT 
  var = case 
             when col1 = 1 then 1
             when col1 = 2 then 2
        end
FROM dbo.someTable

Stored procedure pattern:

/*
EXEC sp_name
*/

USE DATABASE
GO

DROP PROCEDURE IF exists sp_name
GO

CREATE PROCEDURE sp_name
AS 
BEGIN
  ...
END

r

Knitting sql chunks with output.var -> put in quotes:

<SQL>

Copy output to clipboard Note: Number after '-' indicates the amount of Kb that are allowed for clipboard

snippet clipboard
	write.table(${1:dataframe}, file='clipboard-16384', dec = ',', sep = '\t', row.names = FALSE)

data.table HAVING:

dt[,.SD[condition], by = group]

python

Pandas copy to clipboard with comman:

df.to_clipboard(decimal=',', index=False)

Pandas parse tables from website:

url = 'https://en.wikipedia.org/wiki/All-time_Olympic_Games_medal_table'
df = pd.read_html(url, skiprows=2, header = None)

Remove whitespace from sql server:

df['column'].str.strip()

Pandas select all columns except X:

df_wo_X = df.drop(['X'], axis=1)

Force pip to install binary packages:

pip install --only-binary :all: <package-name>

R

Format dates for charting

## YYYY-MM format:
format(as.Date('1900-01-01'), "%Y-%m")

git

Set username and email:

git config --global user.name "Your Name"
git config --global user.email "[email protected]"

Clean up remote branches on local machine:

git remote prune origin

Add remote to local repo:

# set remote with naming convention:
git remote add origin <repo-url>
git remote add upstream <repo-url-fork>

# verify remote
git remote -v

Change remote:

git remote set-url origin <url>

RStudio activate PULL/PUSH buttons after manually adding remote:

git push -u origin master

# If you get an error run:
git pull origin <branch-name> --allow-unrelated-histories

See: Link

Removing files that are already commited:

git rm -r --cached <stuff-you-want-to-remove>

Revert to some previous commit:

git revert --no-commit <commit-id>..HEAD
git commit

"This will revert everything from the HEAD back to the commit hash, meaning it will recreate that commit state in the working tree as if every commit since had been walked back. You can then commit the current tree, and it will create a brand new commit essentially equivalent to the commit you "reverted" to. (The --no-commit flag lets git revert all the commits at once- otherwise you'll be prompted for a message for each commit in the range, littering your history with unnecessary new commits.) This is a safe and easy way to rollback to a previous state. No history is destroyed, so it can be used for commits that have already been made public". By link

Show git user/config:

git config user.name

git config --list

iPython

Install kernel in conda environment using:

source activate myenv
pip install ipykernel
python -m ipykernel install --user --name myenv --display-name "Python (myenv)"

List/delete all jupyter kernels

jupyter kernelspec list
jupyter kernelspec remove myenv

Autoreload python modules:

## install package as editable
pip install -e .

Then copy the following to your Jupyter notebook:

# Load autoreload module 
# autoreload 1: reload modules imported with %aimport everytime before execution
# autoreload 2: reload all modules
%load_ext autoreload
%autoreload 2
%matplotlib inline

Connect a console to a running Jupyter kernel:

%connect_info
# In terminal:
jupyter console --existing <...>

conda

Don't forget to install pip using conda install pip in your new environment

Ubuntu

To enable Backspace = return to previous page on FF for Ubuntu do:

about:config
browser.backspace_action = 0

harlecin/CodeSnippets.md