What makes your code slow is the repeated calls to unique
, and manually shunting all entries into lists repeatedly. Ideally you want to do it all in pandas
for the most speed.
If I understand it correctly, you want to group by the subject ID, collect all hadm IDs and texts. In that case, you can just use pandas.DataFrame.groupby
and pandas.DataFrame.aggregate
to achieve (almost) the same result:
notes_1_df = notes_df.drop(columns=["ROW_ID"]) \
.groupby("SUBJECT_ID") \
.aggregate(list) \
.reset_index()
Which directly produces this output:
SUBJECT_ID HADM_ID TEXT
0 4 [89] [Here is the text]
1 23 [433, 112, 773, 1212] [Here is the text,and so on, Here is the text,...
2 65 [1212, 1210] [Here is the text,and, Here is the text,and so]
3 914 [2212] [Here is the text,and so on]
Whereas your code produces:
SUBJECT_ID HADM_ID NOTES
0 4 [89.0] Here is the text
1 23 [433.0, 112.0, 773.0, 1212.0] Here is the text,and so on
2 65 [1212.0, 1210.0] Here is the text,and so on
3 914 [2212.0] Here is the text,and so on
This differs mainly in the notes. Here your code is a bit weird. Instead of doing what you did, let's just pick the first text from each subject for now.
I am assuming that having integers for the hadm ID is fine, otherwise you can be more specific and supply two different functions to aggregate
, in which case you don't even need the drop
anymore:
notes_1_df = notes_df.groupby("SUBJECT_ID") \
.aggregate({"HADM_ID": lambda x: list(map(float, x)),
"TEXT": "first"}) \
.rename(columns={"TEXT": "NOTES"}) \
.reset_index()
SUBJECT_ID HADM_ID NOTES
0 4 [89.0] Here is the text
1 23 [433.0, 112.0, 773.0, 1212.0] Here is the text,and so on
2 65 [1212.0, 1210.0] Here is the text,and
3 914 [2212.0] Here is the text,and so on
To see why I think your code produces weird results, let's replace the text with unique texts:
df["TEXT"] = "Text from subject " + df.SUBJECT_ID.astype(str) + ", hadm " + df.HADM_ID.astype(str)
Then my (last) code produces:
SUBJECT_ID HADM_ID NOTES
0 4 [89.0] Text from subject 4, hadm 89
1 23 [433.0, 112.0, 773.0, 1212.0] Text from subject 23, hadm 433
2 65 [1212.0, 1210.0] Text from subject 65, hadm 1212
3 914 [2212.0] Text from subject 914, hadm 2212
Where each text is the first text from that actual subject. In contrast, your code produces:
SUBJECT_ID HADM_ID NOTES
0 4 [89.0] Text from subject 4, hadm 89
1 23 [433.0, 112.0, 773.0, 1212.0] Text from subject 23, hadm 433
2 65 [1212.0, 1210.0] Text from subject 23, hadm 112
3 914 [2212.0] Text from subject 23, hadm 773
Note how the texts do not correspond to the same subjects anymore!