Pairwise Frequency Table for multiple columns in Python

Question

I have a table of patient diagnostic codes, where each line represents the entire diagnosis for one patient:

      D0             D1        D2       D3       D4        D5       D6  
0          0              0         0        0        0         0        0   
1     I48.91          R60.9    M19.90    Z87.2        0         0        0   
2        496         564.00     477.9        0    J44.9     J30.9      I10   
3        I96          R63.0     Z51.5        0  L97.909    I69.90   F01.50   
4     491.21          428.0    427.31   V58.61        0    I48.91   Z79.01   
5          0              0         0        0        0         0        0   
6      J44.9          F41.9       I10   H61.22        0       Z23        0   
7          0              0         0        0        0         0        0   
8     M48.00          I12.9     N18.9   K59.00        0     N39.0      Z23   
9      I11.9         R41.82     R56.9   E11.49   K59.00         0  J45.901   
10     I11.9          N40.0    F01.50        0    N40.1     J18.9    J44.1   
11     R31.9         M19.90         0   R53.81        0         0        0   
12         0              0         0        0        0         0        0   
13    M48.02         M48.06     I27.2        0   R53.81         0        0   
14     I50.9         M19.90     F41.9   I25.10        0         0        0   
15         0              0         0        0        0         0        0   
16   I69.359         I48.91     R74.8      I10        0  T50.901A    I95.9   

... for 600 patients, each with up to 15 diagnoses. (0 do not represent a diagnosis). I want to create a paired frequency table to count the number of cases when patients have different pairs of diagnoses:

            I48.91                 R60.9                  M19.90
I48.91  count(I48.91)        count(I48.91, R60.9)    count(I48.91, M19.90)
R60.9   count(R60.9, 148.91)
M19.9 ...

I created the table as follows:

FreqTable = pd.DataFrame(columns=UniqueCodes['DCODE'], index=UniqueCodes['DCODE'])
FreqTable = FreqTable.fillna(0)

The Pairwise frequency counts table in Python does this for a single column of data using nested loops, but it becomes complicated for multiple columns. Does anyone have a good Python way to do this?

Answer 1

, :

df = pd.DataFrame({'D0': ['0', 'A', 'B', 'C'],
                   'D1': ['B', '0', 'C', 'D'],
                   'D2': ['C','D','0','A']})
#   D0 D1 D2
# 0  0  B  C
# 1  A  0  D
# 2  B  C  0
# 3  C  D  A

0 , NaNs:

df = df.replace('0', np.nan)

D0, D1, D2 . , . stack , :

code = df.stack()
0  D1    B
   D2    C
1  D0    A
   D2    D
2  D0    B
   D1    C
3  D0    C
   D1    D
   D2    A
dtype: object

, , , :

code.index = code.index.droplevel(1)
code.name = 'code'

0    B
0    C
1    A
1    D
2    B
2    C
3    C
3    D
3    A
Name: code, dtype: object

, df. join code , , :

code = code.to_frame()
pair = code.join(code, rsuffix='_2')
    #   code code_2
# 0    B      B
# 0    B      C
# 0    C      B
# 0    C      C
# 1    A      A
# 1    A      D
# 1    D      A
# 1    D      D
# 2    B      B
# 2    B      C
# 2    C      B
# 2    C      C
# 3    C      C
# 3    C      D
# 3    C      A
# 3    D      C
# 3    D      D
# 3    D      A
# 3    A      C
# 3    A      D
# 3    A      A

pd.crosstab, :

freq = pd.crosstab(pair['code'], pair['code_2'])

---

:

import numpy as np
import pandas as pd
df = pd.DataFrame({'D0': ['0', 'A', 'B', 'C'],
                   'D1': ['B', '0', 'C', 'D'],
                   'D2': ['C','D','0','A']})
#   D0 D1 D2
# 0  0  B  C
# 1  A  0  D
# 2  B  C  0
# 3  C  D  A

df = df.replace('0', np.nan)
code = df.stack()
code.index = code.index.droplevel(1)
code.name = 'code'
code = code.to_frame()
pair = code.join(code, rsuffix='_2')
freq = pd.crosstab(pair['code'], pair['code_2'])

code_2  A  B  C  D
code              
A       2  0  1  2
B       0  2  2  0
C       1  2  3  1
D       2  0  1  2