Merging hosp/labevents with core/patients on subject_id

[tchang1997]

Prerequisites

• Put an X between the brackets on this line if you have done all of the following:
  • Checked the online documentation: https://mimic.mit.edu/
  • Checked that your issue isn't already addressed: https://github.com/MIT-LCP/mimic-code/issues?utf8=%E2%9C%93&q=

Description

Hi all,

I'm on MIMICIV-v1.0, trying to link demographic data to lab results.

I was attempting to link the labevents table with lab orders and results to patient demographic data, as stored in the core "patients" table, merging on the subject_id attribute. However, upon closer inspection, the subject_id values in the labevents table do not match up with the subject_id values in the core "patients" table.

I tried switching to hadm_id, but found that the values for that column in the labevents table were all NaNs.

Is there a better way to link demographic information to lab results? Maybe there's an issue with my downloaded version?

[briangow]

However, upon closer inspection, the subject_id values in the labevents table do not match up with the subject_id values in the core "patients" table.

Could you provide more detail around this statement (your sql code and results)? You should be able to use a SQL JOIN command on subject_id to link between tables.

[tchang1997]

I'm using pandas to read in the CSVS; I use an inner join (I need both lab test results and demographic info):

import pandas as pd

lab_results = pd.read_csv("/path/to/physionet.org/files/mimiciv/1.0/hosp/labevents.csv")
admissions_df = pd.read_csv("/path/to/physionet.org/files/mimiciv/1.0/core/admissions.csv")

with_admissions_df = lab_results.merge(admissions_df,
                                              on="subject_id")

Since the subject_id values appear to be disjoint (unexpectedly), I get an empty DataFrame as a result for with_admissions_df (e.g. 0 rows):

Empty DataFrame
Columns: [labevent_id, subject_id, hadm_id_x, specimen_id, itemid, charttime, storetime, value, valuenum, valueuom, ref_range_lower, ref_range_upper, flag, priority, comments, hadm_id_y, admittime, dischtime, deathtime, admission_type, admission_location, discharge_location, insurance, language, marital_status, ethnicity, edregtime, edouttime, hospital_expire_flag]
Index: []

[0 rows x 29 columns]

These are indeed the expected columns.

Then, I manually inspected lab_results.subject_id and admissions_df.subject_id. Each of these is a list (more precisely, a pd.Series object) of integer identifiers. However, the values in each column don't appear to overlap; running a set intersection operation like set(lab_results.subject_id) & set(admissions_df.subject_id) yields an empty set, signifying that the subject_id in each table are disjoint.

EDIT: Erroneously wrote subject_ids instead of subject_id.

[pszolovits]

I don’t know what problem you are encountering, but when I do the same join from a mysql database, I get real results. Here are the first 50: mysql> select * from labevents join admissions on labevents.subject_id = admissions.subject_id limit 50; +-------------+------------+---------+-------------+--------+---------------------+---------------------+-------+----------+----------+-----------------+-----------------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------+---------------------+---------------------+-----------+----------------+--------------------+--------------------+-----------+----------+----------------+-----------------+---------------------+---------------------+----------------------+ | labevent_id | subject_id | hadm_id | specimen_id | itemid | charttime | storetime | value | valuenum | valueuom | ref_range_lower | ref_range_upper | flag | priority | comments | subject_id | hadm_id | admittime | dischtime | deathtime | admission_type | admission_location | discharge_location | insurance | language | marital_status | ethnicity | edregtime | edouttime | hospital_expire_flag | +-------------+------------+---------+-------------+--------+---------------------+---------------------+-------+----------+----------+-----------------+-----------------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------+---------------------+---------------------+-----------+----------------+--------------------+--------------------+-----------+----------+----------------+-----------------+---------------------+---------------------+----------------------+ | 5641698 | 10467237 | NULL | 21649231 | 51146 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 0.3 | 0.3 | % | 0 | 2 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641699 | 10467237 | NULL | 21649231 | 51200 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 1.6 | 1.6 | % | 0 | 4 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641700 | 10467237 | NULL | 21649231 | 51221 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 29.7 | 29.7 | % | 36 | 48 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641701 | 10467237 | NULL | 21649231 | 51222 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 10.2 | 10.2 | g/dL | 12 | 16 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641702 | 10467237 | NULL | 21649231 | 51244 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 13.8 | 13.8 | % | 18 | 42 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641703 | 10467237 | NULL | 21649231 | 51248 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 29.0 | 29 | pg | 27 | 32 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641704 | 10467237 | NULL | 21649231 | 51249 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 34.2 | 34.2 | % | 31 | 35 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641705 | 10467237 | NULL | 21649231 | 51250 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 85 | 85 | fL | 82 | 98 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641706 | 10467237 | NULL | 21649231 | 51254 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 4.7 | 4.7 | % | 2 | 11 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641707 | 10467237 | NULL | 21649231 | 51256 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 79.6 | 79.6 | % | 50 | 70 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641708 | 10467237 | NULL | 21649231 | 51265 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 206 | 206 | K/uL | 150 | 440 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641709 | 10467237 | NULL | 21649231 | 51277 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 12.3 | 12.3 | % | 10.5 | 15.5 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641710 | 10467237 | NULL | 21649231 | 51279 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 3.51 | 3.51 | m/uL | 4.2 | 5.4 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641711 | 10467237 | NULL | 21649231 | 51301 | 2159-03-19 21:30:00 | 2159-03-19 22:03:00 | 15.1 | 15.1 | K/uL | 4 | 11 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641712 | 10467237 | NULL | 34641397 | 50887 | 2159-03-19 21:30:00 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | STAT | HOLD. DISCARD GREATER THAN 24 HRS OLD. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641713 | 10467237 | NULL | 54548381 | 50868 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 19 | 19 | mEq/L | 8 | 20 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641714 | 10467237 | NULL | 54548381 | 50882 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 23 | 23 | mEq/L | 22 | 32 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641715 | 10467237 | NULL | 54548381 | 50893 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 9.1 | 9.1 | mg/dL | 8.4 | 10.3 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641716 | 10467237 | NULL | 54548381 | 50902 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 98 | 98 | mEq/L | 96 | 108 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641717 | 10467237 | NULL | 54548381 | 50912 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 1.2 | 1.2 | mg/dL | 0.4 | 1.1 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641718 | 10467237 | NULL | 54548381 | 50920 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | NULL | NULL | NULL | NULL | NULL | NULL | STAT | Using this patient's age, gender, and serum creatinine value of 1.2,. Estimated GFR = 44 if non African-American (mL/min/1.73 m2). Estimated GFR = 53 if African-American (mL/min/1.73 m2). For comparison, mean GFR for age group 70+ is 75 (mL/min/1.73 m2). GFR<60 = Chronic Kidney Disease, GFR<15 = Kidney Failure. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641719 | 10467237 | NULL | 54548381 | 50931 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 203 | 203 | mg/dL | 70 | 100 | abnormal | STAT | IF FASTING, 70-100 NORMAL, >125 PROVISIONAL DIABETES. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641720 | 10467237 | NULL | 54548381 | 50960 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 1.6 | 1.6 | mg/dL | 1.6 | 2.6 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641721 | 10467237 | NULL | 54548381 | 50970 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 3.2 | 3.2 | mg/dL | 2.7 | 4.5 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641722 | 10467237 | NULL | 54548381 | 50971 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 4.5 | 4.5 | mEq/L | 3.3 | 5.1 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641723 | 10467237 | NULL | 54548381 | 50983 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 135 | 135 | mEq/L | 133 | 145 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641724 | 10467237 | NULL | 54548381 | 51006 | 2159-03-19 21:30:00 | 2159-03-19 22:10:00 | 20 | 20 | mg/dL | 6 | 20 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641725 | 10467237 | NULL | 99254355 | 50955 | 2159-03-19 21:30:00 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | STAT | HOLD. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641726 | 10467237 | NULL | 37333667 | 50813 | 2159-03-19 21:45:00 | 2159-03-19 21:46:00 | 2.9 | 2.9 | mmol/L | 0.5 | 2 | abnormal | NULL | GREEN TOP TUBE. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641727 | 10467237 | NULL | 24884245 | 51463 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | NULL | NULL | NULL | NULL | STAT | FEW. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641728 | 10467237 | NULL | 24884245 | 51464 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | mg/dL | NULL | NULL | NULL | STAT | NEG. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641729 | 10467237 | NULL | 24884245 | 51466 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | NULL | NULL | NULL | NULL | STAT | MOD. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641730 | 10467237 | NULL | 24884245 | 51476 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | #/hpf | NULL | NULL | NULL | STAT | <1. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641731 | 10467237 | NULL | 24884245 | 51478 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | mg/dL | NULL | NULL | NULL | STAT | NEG. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641732 | 10467237 | NULL | 24884245 | 51484 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | mg/dL | NULL | NULL | NULL | STAT | NEG. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641733 | 10467237 | NULL | 24884245 | 51486 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | NULL | NULL | NULL | NULL | STAT | LG. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641734 | 10467237 | NULL | 24884245 | 51487 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | NULL | NULL | NULL | NULL | STAT | NEG. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641735 | 10467237 | NULL | 24884245 | 51491 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | 5.5 | 5.5 | units | 5 | 8 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641736 | 10467237 | NULL | 24884245 | 51492 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | mg/dL | NULL | NULL | NULL | STAT | TR. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641737 | 10467237 | NULL | 24884245 | 51493 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | 6 | 6 | #/hpf | 0 | 2 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641738 | 10467237 | NULL | 24884245 | 51498 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | 1.008 | 1.008 | | 1.001 | 1.035 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641739 | 10467237 | NULL | 24884245 | 51501 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | #/hpf | NULL | NULL | NULL | STAT | <1. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641740 | 10467237 | NULL | 24884245 | 51506 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | NULL | NULL | NULL | NULL | STAT | Hazy. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641742 | 10467237 | NULL | 24884245 | 51514 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | mg/dL | 0.2 | 1 | NULL | STAT | NEG. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641743 | 10467237 | NULL | 24884245 | 51516 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NULL | NULL | #/hpf | 0 | 5 | NULL | STAT | >182*. | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641744 | 10467237 | NULL | 24884245 | 51519 | 2159-03-19 22:35:00 | 2159-03-19 23:17:00 | NONE | NULL | NULL | NULL | NULL | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641745 | 10467237 | NULL | 7034333 | 51146 | 2159-03-20 19:17:00 | 2159-03-20 19:36:00 | 0.3 | 0.3 | % | 0 | 2 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641746 | 10467237 | NULL | 7034333 | 51200 | 2159-03-20 19:17:00 | 2159-03-20 19:36:00 | 0.4 | 0.4 | % | 0 | 4 | NULL | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641747 | 10467237 | NULL | 7034333 | 51221 | 2159-03-20 19:17:00 | 2159-03-20 19:36:00 | 31.1 | 31.1 | % | 36 | 48 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | | 5641748 | 10467237 | NULL | 7034333 | 51222 | 2159-03-20 19:17:00 | 2159-03-20 19:36:00 | 11.0 | 11 | g/dL | 12 | 16 | abnormal | STAT | NULL | 10467237 | 20000019 | 2159-03-20 21:08:00 | 2159-03-23 16:54:00 | NULL | EW EMER. | EMERGENCY ROOM | HOME | Other | ? | SINGLE | HISPANIC/LATINO | 2159-03-20 18:56:00 | 2159-03-20 22:48:00 | 0 | +-------------+------------+---------+-------------+--------+---------------------+---------------------+-------+----------+----------+-----------------+-----------------+----------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------+---------------------+---------------------+-----------+----------------+--------------------+--------------------+-----------+----------+----------------+-----------------+---------------------+---------------------+----------------------+ 50 rows in set (0.05 sec)

…

On Sep 7, 2021, at 4:54 PM, Trenton Chang ***@***.***> wrote: I'm using pandas to read in the CSVS; I use an inner join (I need both lab test results and demographic info): import pandas as pd lab_results = pd.read_csv("/path/to/physionet.org/files/mimiciv/1.0/hosp/labevents.csv") admissions_df = pd.read_csv("/path/to/physionet.org/files/mimiciv/1.0/core/admissions.csv") with_admissions_df = lab_results.merge(admissions_df, on="subject_id") Since the subject_ids values appear to be disjoint (unexpectedly), I get an empty DataFrame as a result for with_admissions_df (e.g. 0 rows): Empty DataFrame Columns: [labevent_id, subject_id, hadm_id_x, specimen_id, itemid, charttime, storetime, value, valuenum, valueuom, ref_range_lower, ref_range_upper, flag, priority, comments, hadm_id_y, admittime, dischtime, deathtime, admission_type, admission_location, discharge_location, insurance, language, marital_status, ethnicity, edregtime, edouttime, hospital_expire_flag] Index: [] [0 rows x 29 columns] These are indeed the expected columns. Then, I manually inspected lab_results.subject_ids and admissions_df.subject_ids. Each of these is a list (more precisely, a pd.Series object) of integer identifiers. However, the values in each column don't appear to overlap; running a set intersection operation like set(lab_results.subject_ids) & set(admissions_df.subject_ids) yields an empty set, signifying that the subject_ids in each table are disjoint. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#1128 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2BAC3IZPBSXQWHWFZ6ETTUAZ3XBANCNFSM5DTEMV3Q>. Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[tompollard]

I've just run the following query on the BigQuery instance of MIMIC-IV (v1.0).

SELECT COUNT(DISTINCT l.subject_id) n_patients
FROM `physionet-data.mimic_hosp.labevents` l
INNER JOIN `physionet-data.mimic_core.admissions` a
ON l.subject_id = a.subject_id;

This query returns 246,569 rows (i.e. around 250k patients have a corresponding entry in labevents). It's possible that your dataset is corrupted in some way, but I think most likely the issue is with the Python join. Perhaps check that the data type of the subject_id column is identical in both imports (e.g. is one an integer, and another a string?).

[tchang1997]

Thanks, I double-checked these. I'd mistakenly assumed that every patient in labevents is represented in the other DataFrames, so I was testing this on too small of a sample of rows. In any case, I have it working now -- thank you for the help!