concat two CSV files using two columns

user_16842428 0
Viewed 52

I have two csv files one for blood pressure for patient and the other for heart rate for the same patients and the same hour measure , please see the following example

First csv:

    subject_id     hour_id     blood_pressure
    
    1                 1           96
    
    2                 2           99

Second csv:

    subject_id     hour_id     heart rate
    
    1                 1           80
    
    2                 2           89

Desired output:

    subject_id     hour_id     blood_pressure    heart rate 
    
    1                 1           96               80
    
    2                 2           99                89

I tried:

df = pd.read_csv('D:\\....', low_memory=False, error_bad_lines=False)
df2 = pd.read_csv('D:\\Users', low_memory=False, error_bad_lines=False)
merged = pd.concat([df, df2], axis=1, keys=['subject_id', 'hour_mesaure'])
print(merged)

But it gives me:

  subject_id     hour_id        blood_pressure      id          hour_id    heart rate
    
    1                 1              96             1             1            80
         
    2                 2               99             2            2            89
pandas csv python
2 Answers

Try df.merge instead:

merged = df.merge(df2, on=['subject_id', 'hour_id'])
user_87083640

Here is simple ad-hoc solution, which will work for your CSV:

import sys

def main():
    # CSV file names should be specified as command line arguments
    file1 = open(sys.argv[1])
    file2 = open(sys.argv[2])
    lines1 = []
    for i, line in enumerate(file1):
        # skip first line (header)
        if i == 0:
            continue
        line_list = line.split()
        subject_id = int(line_list[0])
        hour_id = int(line_list[1])
        blood_pressure = int(line_list[2])
        line_tuple = (subject_id, hour_id, blood_pressure)
        lines1.append(line_tuple)

    lines1.sort(key=lambda x: x[0])

    lines2 = {}
    for i, line in enumerate(file2):
        # skip first line (header)
        if i == 0:
            continue
        line_list = line.split()
        subject_id = int(line_list[0])
        hour_id = int(line_list[1])
        heart_rate = int(line_list[2])
        key = (subject_id, hour_id)
        # save lines from seconds CSV to hash for quich lookup
        lines2[key] = heart_rate

    # process sorted lines
    print("subject_id hour_id blood_pressure heart_rate")
    for line in lines1:
        (subject_id, hour_id, blood_pressure) = line
        # lookup by subject_id, hour_id in tuple
        key = (subject_id, hour_id)
        heart_rate = lines2.get(key, "NOT FOUND")
        print("{} {} {} {}".format(subject_id, hour_id, blood_pressure, heart_rate))



if __name__ == "__main__":
    main()

Usage example from command line:

$ cat csv1.txt                                                                                                                                                                                            
# subject_id     hour_id     blood_pressure
1 1 96
2 2 99

$ cat csv2.txt
# subject_id     hour_id     blood_pressure
1 1 80
2 2 89

$ python merge_csv.py csv1.txt csv2.txt
subject_id hour_id blood_pressure heart_rate
1 1 96 80
2 2 99 89
user_1156550
Related