Insights betwwen two columns/variables in Dataframe

Question

I have data in two columns:

• a range of old credit score Input -score_range
• new credit score CVSC100

How do I find insights from both of them ? Where the old is range of values and other column is not CVSC100

I know how to calculate Pearson Correlation in Python of a Dataframe of two columns, but that should not be sufficient I believe. How should I proceed can you please advise

Answer 1

The first step would be to bin cvsc100 in the same bin you have for your input, that will surely help your comparisons.

The second step would be to build a frequency table with your input range in columns and the binned CVS100 in rows, and at the intersection the count of values. This would help to observe how CVSC100 is distributed compared to your input and understand the underlying process.

Assuming that the score you have can be written as score for period n and period n+1, with some hypotheses on the stationarity of the underlying process and modulo some slight renormalisation you can get transfer probabilities and use this matrix for simulations to get the distribution of scores for period n+2,... and so on.

Without more data (explanatory variables or actual default values), you can't really get more usefull metrics.

Answer 2

I'm late but I reproduced the data to see potential relationship/correlation visually. I attempted to plot data of 2 columns using plt.errorbar() for better understanding as follows:

import matplotlib.pyplot as plt
import pandas as pd

# Clean and structure the extracted text into a DataFrame format
data = [
    ("674-689", 664),
    ("706-719", 734),
    ("805-816", 823),
    ("658-673", 618),
    ("639-657", 684),
    ("690-705", 652),
    ("743-753", 733),
    ("674-689", 685),
    ("608-638", 679),
    ("658-673", 667),
    ("720-731", 748),
    ("840-852", 849),
    ("720-731", 714),
    ("754-767", 752),
    ("674-689", 676),
    ("658-673", 649),
    ("732-742", 727),
    ("690-705", 687),
    ("608-638", 639),
    ("732-742", 773),
    ("805-816", 836),
    ("780-792", 763),
    ("793-804", 778),
    ("840-852", 843),
    ("768-779", 753),
    ("732-742", 734),
    ("639-657", 714),
    ("732-742", 748),
    ("706-719", 727),
    ("608-638", 620),
    ("706-719", 706),
]

# Create a pandas DataFrame
df = pd.DataFrame(data, columns=["Score_Range", "CVSC100"])

# Extract numeric values from 'Score_Range'
df[['Score_Lower', 'Score_Upper']] = df['Score_Range'].str.split('-', expand=True).astype(float)

# Correcting the error bar calculation by using absolute differences
lower_error = abs(df['CVSC100'] - df['Score_Lower'])
upper_error = abs(df['Score_Upper'] - df['CVSC100'])


# Plotting the error bars and 'CVSC100' values over a single axis
plt.figure(figsize=(10, 6))  

# Plotting the error bars by projecting them over a single 'CVSC100' axis
plt.errorbar(df['CVSC100'], df.index, xerr=[lower_error, upper_error], fmt='o', ecolor='red', capsize=5, label='CVSC100 with Error Bars')

# Adding title and labels
plt.title('Projection of CVSC100 Values and Error Bars on the Same Axis')
plt.xlabel('CVSC100')
plt.ylabel('Index')
plt.grid(True)
plt.legend()
plt.show()

I attempted to separete and generated new columns for intervals' ends as 'Score_Lower' and 'Score_Upper' and tried create frequency table:

# Step 1: Define the bins for CVSC100 based on the Score_Range values
score_bins = []
for score_range in df['Score_Range']:
    lower, upper = map(int, score_range.split('-'))
    score_bins.append((lower, upper))

# Create the bins for CVSC100 using the score range boundaries
bin_edges = sorted(set([lower for lower, upper in score_bins] + [score_bins[-1][1]]))

# Create the bins for CVSC100
df['CVSC100_Binned'] = pd.cut(df['CVSC100'], bins=bin_edges, right=True, labels=False)

# Step 2: Build Frequency Table (Contingency Table)
frequency_table = pd.crosstab(df['CVSC100_Binned'], df['Score_Range'])

import seaborn as sns
import matplotlib.pyplot as plt

# Plotting heatmap for the frequency table
plt.figure(figsize=(12, 8))
sns.heatmap(frequency_table, annot=True, cmap="YlGnBu", cbar_kws={'label': 'Frequency'}, fmt="d", linewidths=0.5)

# Adding title and labels
plt.title('Heatmap of CVSC100 Frequency Table vs Score Range')
plt.xlabel('Score Range')
plt.ylabel('CVSC100 Binned')

# Show the plot
plt.show()

so I transformed df and plotted frequency table using sns.heatmap(), where each cell represents the count of occurrences for a particular combination of binned CVSC100 values and Score_Range:

I could not comment on heatmap output table