0
$\begingroup$

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].

$\endgroup$
0
$\begingroup$

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)
$\endgroup$
0
$\begingroup$

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')
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.