Capturing Values Of Variables Across Dataframes From A Dict Of Dataframes In Python

- 1 answer

I have 3 dataframes in a dict where the key is a month identifier and value is the dataframe:

enter image description here

Below is a snapshot of the dataframes along with the keys:

enter image description here

Now, for each unique variable I want to capture it's correlation strength across all the months/dataframes. If a variable has correlation values in a df then that should be captured or else the value would be 0. Something like VLOOKUP in excel.

The final dataframe would look like below:

enter image description here

This seems very complicated to me to implement in python so can someone please help me with this?

Below is the code to generate the sample data and create the dict of dataframes:

import pandas as pd
import numpy as np

df1 = pd.DataFrame([{'Variable_Name':'Pending_Disconnect','correlation': 0.553395448},
                    {'Variable_Name':'status_Active','correlation': 0.539464806},

df2 = pd.DataFrame([{'Variable_Name':'Pending_Change','correlation': 0.043461995},
                    {'Variable_Name':'status_Active','correlation': 0.038057697},

df3 = pd.DataFrame([{'Variable_Name':'active_frq_N','correlation':0.025697016},
                    {'Variable_Name':'active_frq_Y','correlation': 0.025697016},

dfs = [df1,df2,df3]
months = ['Jan - Feb 2018','Jan - Mar 2018','Jan - Apr 2018']

sample_dict = dict(zip(months,dfs))


you can replace the column name of your dataframe and then use pd.concat to concatenate the dataframes.

for key, df in sample_dict.items():
    df.rename(columns={'correlation':'correlation '+ key}, inplace=True)

EDIT: you can also omit the dictionary and do this from the list of dataframes.

for i, df in enumerate(dfs):
    df.rename(columns={'correlation':'correlation '+ months[i]}, inplace=True)