PandAudit

Streamline Data Analysis With Pythons's Merge_as_of Function

Posted on July 2, 2023

Title: Streamline Data Analysis with Python’s “merge_as_of” Function: Empowering Internal Audit, Financial Reporting, and the Finance Industry [Read More]
Unveiling The Limitations Of Data Sampling

Posted on July 1, 2023

Title: Unveiling the Limitations of Data Sampling: Empowering Auditors and Finance Professionals with Cutting-Edge Data Services [Read More]
Streamlining Data From Legacy And Web Based Systems

Posted on July 1, 2023

Title: Streamlining Data from Legacy and Web-Based Systems: Unleashing the Power of Data Analytics [Read More]
Converting Legacy System Unstructured Documents Into Workable Excel Formats

Posted on July 1, 2023

Title: Unlocking Data Analytics: Converting Legacy System Unstructured Documents into Workable Excel Formats [Read More]
Extracting Monetary Amount From Text

Posted on December 31, 2022

[Read More]
Merge/Join tables on the closest date in time

Merge_asof pandas example

Posted on October 6, 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. [Read More]
Tags:
- pandas
- merge
- merge_asof
- dataframe
- closest match
Amount in US dollars/cents extracted from a string

A method for extracting US dollar amounts into a separate column of a dataframe.

Posted on September 18, 2022

If you have an unstructured file with messy strings containing amounts, and you want to extract just the amounts, you might consider the following approach: [Read More]
Tags:
- pandas
- US dollars
- regex
- dataframe
- extract
Formatting legacy system negative balances

Convert old legacy system balances ending with '-' OR 'CR' to negative values

Posted on 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... [Read More]
Tags:
- pandas
- Excel
- totals
- formulas
- hardcoded
Locating rows with empty spaces (missing data)

Posted on September 5, 2022

Removing rows with empty values (nan) This code is performing the following operations on a Pandas DataFrame called df: [Read More]
Assign unique ID to a group in a dataframe

Assign an id that is unique to each element in a group

Posted on May 5, 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. [Read More]
Tags:
- pandas
- Excel
- unique
- unique ID
- group
- dataframe
The as of merge - python [pandas]

Merging datasets on the nearest or closest mathing value

Posted on May 5, 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.... [Read More]
Tags:
Unique list of items contained in a dataframe column

Create a new column in the dataframe that contains a list of unique items from the 'source' column.

Posted on May 4, 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 [Read More]
Tags:
- pandas
- unique ID
- dataframe
- assign

Streamline Data Analysis With Pythons's Merge_as_of Function

Unveiling The Limitations Of Data Sampling

Streamlining Data From Legacy And Web Based Systems

Converting Legacy System Unstructured Documents Into Workable Excel Formats

Extracting Monetary Amount From Text

Merge/Join tables on the closest date in time

Merge_asof pandas example

Amount in US dollars/cents extracted from a string

A method for extracting US dollar amounts into a separate column of a dataframe.

Formatting legacy system negative balances

Convert old legacy system balances ending with '-' OR 'CR' to negative values

Locating rows with empty spaces (missing data)

Assign unique ID to a group in a dataframe

Assign an id that is unique to each element in a group

The as of merge - python [pandas]

Merging datasets on the nearest or closest mathing value

Unique list of items contained in a dataframe column

Create a new column in the dataframe that contains a list of unique items from the 'source' column.