Codebook
These are archives. I wrote them between 2020 and 2023, when I was working as a hands-on data scientist: short snippets of code meant to be useful to other data scientists. Some of them are obsolete today.
Pandas
- Aggregate a pandas DataFrame on multiple functions
- Apply computation within groups in a pandas DataFrame
- Calculate moving averages and rolling windows with pandas
- Convert categorical variables to numeric with pandas
- Create a DataFrame of zeros or nulls
- Cross-join two pandas DataFrames
- Display more DataFrame cells in Jupyter notebooks
- Explode and regroup lists into rows in a pandas DataFrame
- Flatten JSON files with pandas
- Flatten multi-index columns in pandas DataFrames
- Floor datetimes and dates
- Format dates in pandas bar plots
- Forward- or back-fill NA values in DataFrames
- Get differences between two pandas DataFrames
- Get first row(s) of each group
- Get top rows or random rows within groups in a DataFrame
- Go up a hierarchical tree with pandas
- Handle dates in pandas
- Hash values in Python
- Make full use of pandas value_counts()
- Pivot pandas Dataframes with non-numeric values
- Read multiple CSV files into a pandas DataFrame
- Rename pandas DataFrame columns with method chaining
- Select rows with NA values in any pandas DataFrame column
- Split values into columns of a pandas DataFrame
- Use a column value if not Null, or another column from a pandas DataFrame
- Use all options of pandas DataFrame info()
- Use date indexes with pandas DataFrames
Python
- Convert nested string field to dict in Python
- Format numbers the right way in Python
- Get common elements between lists in Python
- Get the perfect color palette with Seaborn
- Get today and yesterday date in Python
- Handle infinity values in Python
- List files in a folder with Python
- Manage markers for lineplots with Python seaborn
- Manually create a legend on a Matplotlib plot
- Open and read/write files in Python
- Plot beautiful time series with Python seaborn
- Plot great categorical distribution graphs with Python seaborn
- Rearrange layers with Matplotlib and Seaborn plots in Python
- Simply loop through multi-axes figures in Matplotlib
- Split ML data into training and test sets in Python
- Train Machine Learning algorithms with Python scikit-learn
SQL
- Avoid division by zero errors in SQL
- Calculate cumulative sum in SQL
- Calculate period-to-period change in SQL
- Compute Haversine distance in Redshift
- Compute percent of column
- Compute sessions and streaks in SQL
- Compute time intervals in PostgreSQL
- Convert between epoch and datetime in Redshift
- Convert rows to columns with PIVOT in BigQuery
- Count the next occurences within a time window
- Count words and characters in a string with PostgreSQL
- Filter with HAVING clause in SQL
- Get first or last value in a group
- Get percentiles in Redshift
- Get rolling average in SQL
- Import foreign PostgreSQL database
- Join on multiple SQL tables with fallback on first non-Null value
- Join tables in SQL with multiple ON conditions
- List all tables from a PostgreSQL database
- Query new vs returning users in SQL
- Query on time window in SQL
- Rank results in SQL with RANK, DENSE_RANK and ROW_NUMBER
- Round timestamp to the nearest minute, hour… in PostgreSQL
- Substract days to a date in SQL
- Use CASE in PostgreSQL
GCP
- BigQuery’s killer functions
- Copy files from URL to Google Cloud Storage
- Schedule BigQuery jobs
- Use Jupyter magic commands for BigQuery
- View BigQuery most expensive queries