11
$\begingroup$

I have a data set of movies which has 28 columns. One of them is genres. For each row in this data set, the value for column genres is of the form "Action|Animation|Comedy|Family|Fantasy". I want to encode them using pandas.get_dummies() but since the columns have multiple values, how to deal with such conditions? Additinal information on below link(question moved from stackoverflow) https://stackoverflow.com/q/40331558/4028904

$\endgroup$
1
  • $\begingroup$ I just want to ask whether in this case if we are doing a linear regression(with these as independent variables) should we drop one of the dummy variables or not (sorry cant comment due to my reputation ) $\endgroup$ Jun 29, 2018 at 19:46

1 Answer 1

12
$\begingroup$

I'm starting with the following dataset:

import pandas as pd
data = pd.DataFrame({'title': ['Avatar', 'Pirates', 'Spectre', 'Batman'],
                 'genres': ['Action|Adventure|Fantasy|Sci-Fi',
                            'Action|Adventure|Fantasy',
                            'Action|Adventure|Thriller',
                            'Action|Thriller']},
                columns=['title', 'genres'])


     title                           genres
0   Avatar  Action|Adventure|Fantasy|Sci-Fi
1  Pirates         Action|Adventure|Fantasy
2  Spectre        Action|Adventure|Thriller
3   Batman                  Action|Thriller

First, you want to have your data in a structure pairing titles with one genre at a time, multiple rows per title. You can get it in a series like this:

cleaned = data.set_index('title').genres.str.split('|', expand=True).stack()


title
Avatar   0       Action
         1    Adventure
         2      Fantasy
         3       Sci-Fi
Pirates  0       Action
         1    Adventure
         2      Fantasy
Spectre  0       Action
         1    Adventure
         2     Thriller
Batman   0       Action
         1     Thriller
dtype: object

(There's an extra index level that we don't want, but we'll get rid of it soon.) get_dummies will now work, but it only works on one row at a time, so we need to re-aggregate the titles:

pd.get_dummies(cleaned, prefix='g').groupby(level=0).sum()


         g_Action  g_Adventure  g_Fantasy  g_Sci-Fi  g_Thriller
title
Avatar        1.0          1.0        1.0       1.0         0.0
Batman        1.0          0.0        0.0       0.0         1.0
Pirates       1.0          1.0        1.0       0.0         0.0
Spectre       1.0          1.0        0.0       0.0         1.0
$\endgroup$
4
  • 3
    $\begingroup$ Here is a bit more efficient method: x = data.set_index('title').genres.str.split(r'|', expand=True).stack().reset_index(level=1, drop=True).to_frame('genre'); pd.get_dummies(x, prefix='g', columns=['genre']).groupby(level=0).sum() $\endgroup$ Oct 31, 2016 at 22:51
  • $\begingroup$ @MaxU yes indeed, thanks. I didn't know about the expand arg. I've edited a variant on that into the post. (It seemed cleaner to keep the intermediate value as a series, not a dataframe.) $\endgroup$
    – philh
    Nov 1, 2016 at 19:05
  • $\begingroup$ Thank you for the great tip. Can you please elaborate more on the part where there are more than 10+ different values (basically dynamic field with increasing numbers with time) in the field. For Example: "Competitors" Field in the dataset of Deals made by an Organisation. In this case, each deal contains multiple competitors like ['12334', "Amazon; Microsoft; Gartner"] ['12334', "Amazon; Google"] Unique competitors will be going to increase, probably each time some new competitor will be added to the dataset. In this case above method would go for a toss as the number of fields will now n $\endgroup$ Jul 19, 2018 at 1:01
  • $\begingroup$ Would you suggest the same method if instead of 5 genres there are more than 100? $\endgroup$
    – Joe
    Oct 2, 2018 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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