Merging common Columns values in two DataFrame Pandas

Question

I have a dataset of patients from which I want to predict whether patient suffering from diabetes or not. In that I have a DataFrame res_total_Df with columns suppose [patient_id, urine output, Blood pressure] and

another DataFrame key_df with columns suppose [patient_id , haemoglobin, Blood pressure].

I want to merge into single dataFrame in which common columns values should be added as list(for which later I would take mean). Resultant dataFrame would be [patient_id, urine output, haemoglobin, Blood pressure].

How can I achieve it using pandas. the number of columns in second dataFrame can vary because I am extracting them from the text. I cannot paste the dataFrame snapshot because it is health data.

Here is my code

def find_common_col(res_total_df, key_df):
    # patient_id is always common
    common_list = ['patient_id']
    for i in res_total_df.columns:
        if i in key_df.columns:
            if i not in common_list:
                common_list.append(i)



common_col = find_common_col(res_total_Df, key_df)
res_total_Df = pd.merge(res_total_Df, key_df, how='outer', on=common_col)

The problem with above code it has duplicates rows. Suppose Blood pressure value for patient_id 1993 in res_total_Df is 180 and in key_df is 200. Then it adds two rows one with value 180 and other with value 200 for patient_id 1993.

It want Blood pressure for patient_id 1993 as [180, 200].

Answer 1

you can simply merge the two frame together and then perform zip on columns which appears in both frames, see short example:

import pandas as pd

# create sample dataframes
res_total_Df = pd.DataFrame([[2, 65.4, 150], [3, 85.7, 160], [4, 75.4, 170]],
                            columns=['patient_id', 'urine output', 'Blood pressure'])
key_df = pd.DataFrame([[2, 0.36, 180], [4, 0.72, 200], [5, 0.28, 190]],
                      columns=['patient_id', 'haemoglobin', 'Blood pressure'])

# set the dataframe indexes
res_total_Df.set_index('patient_id', inplace=True)
key_df.set_index('patient_id', inplace=True)
# merge the two datafremes with left as master
simple = pd.merge(res_total_Df, key_df, left_index=True, right_index=True, 
                  how='left', suffixes=(' - 1', ' - 2'))
# find the columns appearing in both dataframes
for col in (c for c in res_total_Df.columns if c in key_df.columns):
    # zip their values
    simple[col] = list(zip(simple[col + ' - 1'], simple[col + ' - 2']))
    # remove the origibal columns
    del simple[col + ' - 1'], simple[col + ' - 2']

print(simple)
#             urine output  haemoglobin Blood pressure
# patient_id                                          
# 2                   65.4         0.36     (150, 180)
# 3                   85.7          NaN     (160, nan)
# 4                   75.4         0.72     (170, 200)

Answer 2

Dropping your duplicate rows and merging on 'Patient ID' should solve your issue.

data = pd.merge(res_total_Df, key_df,
                        left_on=['patient_id'], right_on = ['patient_id'], how = 'left')