Recent Posts

Explore practical guides, tutorials, and insights on data analytics and automation in finance and accounting. Each post tackles real challenges with actionable solutions.

Topics We Cover

Data Analytics - Transform raw data into actionable insights

Process Automation - Eliminate repetitive tasks and reduce errors

Tools and Techniques - Practical implementations using Python, SQL, and modern platforms

Industry Trends - What’s actually changing in the profession

Career Development - Skills and strategies for staying relevant

Browse the latest posts below:

Extracting Monetary Amount From Text

December 31, 2022

<!DOCTYPE html> </p> </article>

Merge/Join tables on the closest date in time

October 06, 2022

In this post, we demonstrate how to use the merge_asof() function in the Pandas library to merge two dataframes, df1 and df2, where df1 contains names and IDs for certain individuals and df2 contains historical addresses for those same individuals. The goal is to match each person in df1 with a corresponding address in df2 that is closest to a specified cutoff date.

Formatting legacy system negative balances

September 17, 2022

This post is about formatting negative balances in a legacy system so that they can be easily processed in a modern system. The post provides a method using the pandas library in Python to convert old legacy system balances, which may be formatted with a ‘-‘ or ‘CR’ suffix, to negative values. The method involves reading the legacy system values into a pandas dataframe, creating a mask to identify rows with negative balances, and then using the mask to replace the ‘-‘ or ‘CR’ suffix with a negative symbol. The resulting values are then formatted by removing any commas and converting them to float data type. The final dataframe contains the correctly formatted negative values.

Assign unique ID to a group in a dataframe

May 05, 2020

The purpose of this code is to group the elements in the ‘A’ column of a Pandas DataFrame and assign a sequential ID to each group. The ID is assigned starting with one and increasing by one for each group.

The as of merge - python [pandas]

May 05, 2020

This post demonstrates how to use the merge_asof() function in the Pandas library to merge two dataframes based on a shared column and the closest date in time. The function takes in two dataframes, data_set_A and data_set_B, and a shared column, ‘key_column’, and returns a new dataframe that includes all rows from data_set_A and only those rows from data_set_B that match the values in data_set_A. If there is no exact match, the function uses the last row in data_set_B whose ‘key_column’ value is less than the ‘key_column’ value in data_set_A. This type of merge is known as a “backward” search. The function also has a parameter called direction that can be set to ‘forward’ to specify that the search should be performed in the opposite direction.

Unique list of items contained in a dataframe column

May 04, 2020

import pandas as pd
df=pd.DataFrame({'state':['NY','CA','CT','NJ','NY','FL','FL','CA'],\
 'source':['source_a','source_a','source_a','source_b','source_b','source_b',\
 'source_d','source_c']})
df