8
$\begingroup$

How can I transform the following DataFrame into one with cities as rows and each cuisine as a column, and 1 or 0 as values (1 if the city has that kind of cuisine)?

I think this turns out to be a very common problem in transforming data into features for machine learning.

I am aware of the Pandas pivot_table functionality, but it asks for a value column, and in this case we don't have any.

import pandas as pd

data = {
    'city': ['NY','NY', 'SF','SF','SF'],
    'cuisine': ['Japanese', 'Chinese', 'French', 'Japanse', 'German']
}

df = pd.DataFrame(data)
$\endgroup$

3 Answers 3

8
$\begingroup$

If there is no value column - introduce it yourself!

df["value"]=1
pd.pivot_table(df, values="value", index=["city"], columns="cuisine", fill_value=0) 

For your example I got (after fixing the misprint in 'Japanse' to 'Japanese')

cuisine  Chinese  French  German  Japanese
city                                      
NY             1       0       0         1
SF             0       1       1         1
$\endgroup$
4
$\begingroup$

Here is a more pythonic way:

df.pivot_table(index=["city"], columns="cuisine", aggfunc=lambda x: 1, fill_value=0)

This is a late answer, but it is for people who meet the same probelm.

$\endgroup$
0
$\begingroup$

In recent Pandas versions there is a method for exactly this use-case:

https://pandas.pydata.org/docs/reference/api/pandas.get_dummies.html

pd.get_dummies(df.set_index(["city"]).cuisine)

Result:

      Chinese  French  German  Japanese  Japanse
city                                            
NY          0       0       0         1        0
NY          1       0       0         0        0
SF          0       1       0         0        0
SF          0       0       0         0        1
SF          0       0       1         0        0
$\endgroup$

Your Answer

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

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