I am trying to create a pivot table with columns as the KEYWORDs from the data frame and the rows corresponding to the person ID (MCN) and fill the values as 1/0 (binary) such that the value should be 1 for that particular keyword if it is present in the text of that particular MCN (person id) and 0 if that keyword is not present.
My code is :
df_pivot=df1.explode('keyword').pivot_table(index ="MCN" , columns = "keyword").fillna(0).astype(int).reset_index()
df_pivot.columns=df_pivot.columns.droplevel(0)
df_pivot.head()
My output is :
keyword SBP TIPS ascites asterixis black stools jaundice jaundiced lactulose melena ... SBP TIPS ascites asterixis black stools jaundice jaundiced lactulose melena rifaximin
0 1175880 -2147483648 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
1 1376151 -2147483648 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
2 1784428 -2147483648 0 0 0 0 0 0 0 0 ... 0 0 0 0 0 0 0 0 0 0
3 1932574 0 1199667889 0 0 0 -2147483648 0 0 0 ... 0 0 0 0 0 1 0 0 0 0
4 1977098 -2147483648 0 0 0 0 0 0 -2147483648 -2147483648 ... 0 0 0 0 0 0 0 1 1 0
5 rows × 41 columns
I am trying to figure out a way to get just 1 or 0 values in my pivot table because some values are not binary. I would appreciate some guidance on this.
Thank you!