Matching Values in One Column with Names of Another Column and Calculating Percentage Change

In this article, we’ll go over a step-by-step process to solve the problem presented by matching values in one column with names of another column present in a pandas DataFrame, and then calculating the corresponding percentage change.

Step 1: Understanding the Problem

We are given a DataFrame df with columns ID, col1, col2, col3, col4, and col5. The values in col1 are actually names of other columns in the same DataFrame. We need to match these column names with their corresponding values, calculate the percentage change between each matched value and its corresponding value in col2.

Step 2: Preparing Data for Analysis

To solve this problem, we will first import the necessary libraries, including pandas for data manipulation and numpy for numerical operations.

import pandas as pd
import numpy as np

Next, we’ll create a sample DataFrame df with the given structure:

# Create a sample DataFrame
data = {
    'ID': [1, 2, 3, 4],
    'col1': ['col3', 'col5', 'col3', 'col4'],
    'col2': [10, 6, 12, 9],
    'col3': [9, 7, 4, 5],
    'col4': [5, 4, 2, 8],
    'col5': [4, 8, 11, 10]
}
df = pd.DataFrame(data)

Step 3: Matching Column Names with Values and Calculating Percentage Change

We’ll use the factorize function from pandas to convert column names in col1 into unique integer indices that can be used for matching.

# Get values by lookup and then count new column with arithmetic operations:
idx, cols = pd.factorize(df['col1'])

Next, we’ll reindex the DataFrame using these indices to find the corresponding values in col2:

s = df.reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]

Then, we can calculate the percentage change by subtracting each matched value from its corresponding value in col2, dividing by the original value, and multiplying by 100.

df['percent_change'] = df['col2'].sub(s).div(s).mul(100)

Step 4: Combining the Code into a Function

We’ll combine all these steps into a single function that takes no arguments:

def calculate_percentage_change():
    # Create a sample DataFrame
    data = {
        'ID': [1, 2, 3, 4],
        'col1': ['col3', 'col5', 'col3', 'col4'],
        'col2': [10, 6, 12, 9],
        'col3': [9, 7, 4, 5],
        'col4': [5, 4, 2, 8],
        'col5': [4, 8, 11, 10]
    }
    df = pd.DataFrame(data)

    # Get values by lookup and then count new column with arithmetic operations:
    idx, cols = pd.factorize(df['col1'])
    
    s = df.reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]

    df['percent_change'] = df['col2'].sub(s).div(s).mul(100)

    return df

Step 5: Running the Code and Viewing Results

Finally, we’ll run this function and print the resulting DataFrame to verify our results.

# Run the code and view results:
df_result = calculate_percentage_change()
print(df_result)

This code provides a clear step-by-step solution to the problem presented by matching column names with values in pandas DataFrames and calculating percentage change.

Last modified on 2025-04-15