Python - Search multiple columns for string to set categorical variable value

Question

I am very new to Python and Pandas, and am trying to use it for a statistical analysis of a very large dataset (10 million cases) because the other options (SPSS and R) are unable to handle the dataset on the authorized hardware.

In this analysis, I need to search a range of columns (30 to be exact) row-wise to extract individual strings (about 200 are possible, not sure how many will actually be present in the dataset) and then create a categorical variable for each string.

The data looks like this

  Dx1     Dx2     Dx3   etc... 
  001     234     456 
  231     001     444
  245     777     001

What is needed is

Dx1     Dx2     Dx3  Var001   Var234  Var456  Var231   etc..   
001     234     456  True     True    True    False
231     001     444  True     False   False   True
245     777     001  True     False   False   False

Any thoughts on how to do this?

---

df.dtypes shows

AGE                      int64
DISPUNIFORM              int64
DRG                      int64
DRGVER                   int64
Readmit_30D              int64
DXCCS1                   int64
DXCCS2                   int64
DXCCS3                   int64
DXCCS4                   int64
...on to DXCCS30

Answer 1

I think you want to keep "one hot encoded" data set as a sparse matrix.

So try the following memory saving approach:

from sklearn.feature_extraction.text import CountVectorizer

cv = CountVectorizer()

r = pd.SparseDataFrame(cv.fit_transform(df.astype(str).add(' ').sum(axis=1)),
                       columns=cv.get_feature_names(),
                       index=df.index,
                       default_fill_value=0).add_prefix('Var')

Result:

In [85]: r
Out[85]:
   Var001  Var231  Var234  Var245  Var444  Var456  Var777
0       1       0       1       0       0       1       0
1       1       1       0       0       1       0       0
2       1       0       0       1       0       0       1

In [86]: r.memory_usage()
Out[86]:
Index     80
Var001    24
Var231     8
Var234     8
Var245     8
Var444     8
Var456     8
Var777     8
dtype: int64

Explanation:

I used the following trick in order to collect all data into one column:

In [89]: df.astype(str).add(' ').sum(axis=1)
Out[89]:
0    001 234 456
1    231 001 444
2    245 777 001
dtype: object

PS don't join resulting sparse DF with the source DF as it may result in "exploding" it back to normal (not sparse) DF:

In [87]: df.join(r)
Out[87]:
   Dx1  Dx2  Dx3  Var001  Var231  Var234  Var245  Var444  Var456  Var777
0  001  234  456       1       0       1       0       0       1       0
1  231  001  444       1       1       0       0       1       0       0
2  245  777  001       1       0       0       1       0       0       1

In [88]: df.join(r).memory_usage()
Out[88]:
Index     80
Dx1       24
Dx2       24
Dx3       24
Var001    24
Var231    24
Var234    24
Var245    24
Var444    24
Var456    24
Var777    24
dtype: int64

Answer 2

Use get_dummies with max, convert to bool and last join to original:

df = (df.join(pd.get_dummies(df, prefix_sep='', prefix='')
                .max(level=0, axis=1)
                .astype(bool)
                .add_prefix('Var')))
print (df)

   Dx1  Dx2  Dx3  Var001  Var231  Var245  Var234  Var777  Var444  Var456
0  001  234  456    True   False   False    True   False   False    True
1  231  001  444    True    True   False   False   False    True   False
2  245  777  001    True   False    True   False    True   False   False