CODEWITHSUNDEEP
pythonpandasdataframe
ni8mr
ni8mr

Reputation: 1785

I need to make a truth table from pandas dataframe based on values on a column

I have a dataframe like this:

  name vals
0  foo    X
1  foo    Y
2  foo    Z
3  bar    Y
4  bar    Z
5  bar    Q

How can I transform it to like this:

      Q  X  Y  Z
name            
foo   0  1  1  1
bar   1  0  1  1

Code for first df:

df = pd.DataFrame({"name": ["foo", "foo", "foo",
                              "bar", "bar", "bar"],
                        "vals": ["X", "Y", "Z",
                              "Y", "Z", "Q"],
                        })

Upvotes: 2

Views: 2020

Answers (2)

piRSquared
piRSquared

Reputation: 294348

groupby

df.groupby(['name', 'vals']).size().unstack(fill_value=0)


vals  Q  X  Y  Z
name            
bar   1  0  1  1
foo   0  1  1  1vals  Q

pd.factorize and slice assignment

This will assign one for existence but not count total occurance

i, r = pd.factorize(df.name)
j, c = pd.factorize(df.vals)
b = np.zeros((r.size, c.size), int)
b[i, j] = 1

pd.DataFrame(b, r, c)

     X  Y  Z  Q
foo  1  1  1  0
bar  0  1  1  1

pd.factorize and np.bincount

i, r = pd.factorize(df.name)
j, c = pd.factorize(df.vals)
n, m = len(r), len(c)
b = np.bincount(i * m + j, minlength=n * m).reshape(n, m)

pd.DataFrame(b, r, c)

     X  Y  Z  Q
foo  1  1  1  0
bar  0  1  1  1

Upvotes: 2

BENY
BENY

Reputation: 323306

IIUC

pd.crosstab(df.name,df.vals)
Out[137]: 
vals  Q  X  Y  Z
name            
bar   1  0  1  1
foo   0  1  1  1

Upvotes: 5

Related Questions