10
$\begingroup$

I have a pandas data frame (X11) like this: In actual I have 99 columns up to dx99

    dx1      dx2    dx3    dx4
0   25041   40391   5856    0
1   25041   40391   25081   5856
2   25041   40391   42822   0
3   25061   40391   0       0
4   25041   40391   0       5856
5   40391   25002   5856    3569

I want to create additional column(s) for cell values like 25041,40391,5856 etc. So there will be a column 25041 with value as 1 or 0 if 25041 occurs in that particular row in any dxs columns. I am using this code and it works when number of rows are less.

mat = X11.as_matrix(columns=None)
values, counts = np.unique(mat.astype(str), return_counts=True)

for x in values:
    X11[x] = X11.isin([x]).any(1).astype(int)

I am getting result like this:

dx1     dx2     dx3    dx4  0   25002   25041   25061   25081   3569    40391   42822   5856
25041   40391   5856    0   0   0       1       0       0       0          1        0       1
25041   40391   25081  5856 0   0       1       0       1       0            1      0       1
25041   40391   42822   0   0   0       1       0       0       0           1       1       0
25061   40391   0       0   0   0       0       1       0       0          1        0       0
25041   40391   0    5856   0   0       1       0       0       0          1        0       1
40391   25002 5856   3569   0   1       0       0       0       1          1        0       1

When number of rows are many thousands or in millions, it hangs and takes forever and I am not getting any result. Please see that cell values are not unique to column, instead repeating in multi columns. For ex, 40391 is occurring in dx1 as well as in dx2 and so on for 0 and 5856 etc. Any idea how to improve the logic mentioned above?

$\endgroup$
  • $\begingroup$ Any idea how to solve this? I am still waiting for this to resolve as my data getting bigger and bigger and existing solution takes for ever to generated dummy columns. $\endgroup$ – Sanoj Jan 6 '16 at 21:25
6
$\begingroup$

There's a much more pythonic solution in pandas...

This takes less than a second on 10 Million rows on my laptop:

for x in X11.E.unique():
    X11[x]=(X11.E==x).astype(int)
X11

Here are the details laid out:

Simple small dataframe -

import numpy as np
import pandas as pd

X11 = pd.DataFrame(np.random.randn(6,4), columns=list('ABCD'))
X11['E'] = [25223, 112233,25223,14333,14333,112233]
X11

simple small dataframe

Binarization method -

for x in X11.E.unique():
    X11[x]=(X11.E==x).astype(int)
X11

enter image description here

Dataframe with 10 Million rows -

pd.set_option("display.max_rows",20)
X12 = pd.DataFrame(np.random.randn(10000000,4), columns=list('ABCD'))
foo = [25223, 112233,25223,14333,14333,112233]
bar=[]
import random
for x in range(10000000):
    bar.append(random.choice(foo))
X12['E'] = bar
X12

enter image description here

Timed binarization (aka one-hot encoding) on 10 million row dataframe -

import time
start = time.clock()

for x in X12.E.unique():
    X12[x]=(X12.E==x).astype(int)
elapsed = (time.clock() - start)

print "This is the time that this took in seconds: ",elapsed

X12

enter image description here

Hope this helps!

|improve this answer|||||
$\endgroup$
  • $\begingroup$ This doesn't say how you will dynamically get dummy value (25041) and column names (i.e. dx1) both in the for loop. I can get only one at a time. $\endgroup$ – Sanoj Dec 18 '15 at 17:11
  • $\begingroup$ Take a look now. I added all of the details. $\endgroup$ – AN6U5 Dec 19 '15 at 17:52
  • $\begingroup$ Your solution looks good if I need to create dummy values based in one column only as you have done from "E". But when I have to create it from multiple columns and those cell values are not unique to a particular column then do I need to loop your code again for all those columns? If that is the case then how repetition of values will be taken care of? Otherwise it will over write the previous dummy column created with the same name. I have added my result in question above to make it clear if there was any confusion. Thanks anyway for you looking into it. $\endgroup$ – Sanoj Dec 21 '15 at 17:55
4
$\begingroup$

It looks like you want to create dummy variable from a pandas dataframe column. Fortunately, pandas has a special method for it: get_dummies(). Here is a code snippet that you can adapt for your need:

import pandas as pd
data = pd.read_clipboard(sep=',')

#get the names of the first 3 columns
colN = data.columns.values[:3]

#make a copy of the dataframe
data_transformed = data

#the get_dummies method is doing the job for you
for column_name in colN:
    dummies = pd.get_dummies(data_transformed[column_name], prefix='value', prefix_sep='_')
    col_names_dummies = dummies.columns.values

    #then you can append new columns to the dataframe
    for i,value in enumerate(col_names_dummies):
        data_transformed[value] = dummies.iloc[:,i]

Here is the output of data_transformed:

         dx1    dx2    dx3   dx4    dx5    dx6    dx7  value_25041  value_25061  0  25041  40391   5856     0  V4511  V5867  30000            1            0   
    1  25041  40391  25081  5856   5363   3572      0            1            0   
    2  25041  40391  42822     0   5856      0      0            1            0   
    3  25061  40391      0     0      0      0      0            0            1   
    4  25041  40391      0  5856  25081  V4511  25051            1            0   

      value_40391  value_0  value_5856  value_25081  value_42822  
    0            1        0           1            0            0  
    1            1        0           0            1            0  
    2            1        0           0            0            1  
    3            1        1           0            0            0  
    4            1        1           0            0            0  
|improve this answer|||||
$\endgroup$
  • $\begingroup$ It looks OK but if you will see carefully then you will find that for value_0, it doesn't have 1 in all rows. Since 0 is present in all rows therefore value_0 should have 1 in all row. Same for value_5856, Value_25081 etc. It seems this logic is picking values from a column and then not going back instead move forward. $\endgroup$ – Sanoj Dec 16 '15 at 20:55
  • $\begingroup$ Hi Sanoj. It's not really fair to use my solution and vote me down. The least you can do is to update your question with the new progress you made instead of opening a new question. If you want people to help you, you should play nice with them. $\endgroup$ – michaelg Dec 18 '15 at 13:05
  • $\begingroup$ Hello michaeld: I had no intention to vote you down. I just took off click sign since this solution did not fulfill my needs as asked in question. Initially I thought OK but later when I investigated I found the discrepancies as mentioned in reply above. I was not getting any reply of this therefore I created a new question where I mentioned my original answer and included your reply with correction needed. Sorry I did not mention your name there. I will update that. $\endgroup$ – Sanoj Dec 18 '15 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.