2
$\begingroup$

I have multiple lists on python:

a = ["house","garden", "living room", "dog","cat"]

b= ["cat","dog", "chicken"]

c=["house", "garden","bathroom"]

I'd like to create the following dataframe:

a b c
house 0 1
garden 0 1
living room 0 0
dog 1 0
cat 1 0

Thanks for the help!

$\endgroup$
1
  • $\begingroup$ Hi @Marco if you do find the answer below okay (it does work), could you very kindly mark the post as 'accepted'. There's a little grey 'tick' sign to the left of the answer, click that and it goes green. I noted on your past questions you forgotten to do that. Its a small thing but helps everyone, which includes you (you will get +2). $\endgroup$
    – M__
    Commented Sep 13, 2022 at 13:31

3 Answers 3

1
$\begingroup$

Here you go, just add column 'C' and you are sorted. Just to note, you must be seeking a pandas solution because merge is a pandas command and you're asking for a Python solution.

import pandas as pd

a = ["house","garden", "living room", "dog","cat"]
b= ["cat","dog", "chicken"]
df = pd.DataFrame(a, columns = ['a'])
df2 = pd.DataFrame(b, columns = ['b'])
dfa = df['a'].value_counts()
dfa.columns = ['a']
dfb = df2['b'].value_counts()
dfb.columns = ['b']
dfa = dfa.to_frame()
dfb = dfb.to_frame()
df3 = dfa.join(dfb).replace(np.nan, 0).astype(int)
print (df3)

Output

             a  b
house        1  0
garden       1  0
living room  1  0
dog          1  1
cat          1  1

and if you want to remove the 'a' column

print (df3.drop('a', axis=1))

             b
house        0
garden       0
living room  0
dog          1
cat          1

Notes The key command here is value_counts and makes a frequency plot. Its output is a Series rather than a DataFrame so it needs converting.

If you want to keep all inputs the commands is

df3 = pd.concat([dfa, dfb]).replace(np.nan, 0).astype(int)

Alternatively,

a = ["house","garden", "living room", "dog","cat"]
b= ["cat","dog", "chicken"]
df = pd.DataFrame(a).value_counts().to_frame('a')
df2 = pd.DataFrame(b).value_counts().to_frame('b')
df3 = df.join(df2).replace(np.nan, 0).astype(int).rename_axis(None)
print (df3.drop('a', axis=1))
$\endgroup$
1
$\begingroup$

I'm aware that this asks specifically for a python solution, but I'd like to add an R answer as well, just in case someone using R has a similar problem:

> a <- c("house","garden", "living room", "dog","cat")
> b <- c("cat","dog", "chicken")
> c <- c("house", "garden","bathroom")

> (result <- data.frame(row.names=a, a=a %in% a, b=a %in% b, c=a %in% c))
               a     b     c
house       TRUE FALSE  TRUE
garden      TRUE FALSE  TRUE
living room TRUE FALSE FALSE
dog         TRUE  TRUE FALSE
cat         TRUE  TRUE FALSE

or for strictly what you asked for, the TRUE/FALSE values can be encouraged into numbers by adding zero:

> (result <- data.frame(a=a, b=(a %in% b) + 0, c=(a %in% c) + 0))
            a b c
1       house 0 1
2      garden 0 1
3 living room 0 0
4         dog 1 0
5         cat 1 0
$\endgroup$
1
$\begingroup$

Both existing answers seem to work to my eye. An alternative approach:

import pandas as pd

a = ["house","garden", "living room", "dog","cat"]
b = ["cat","dog", "chicken"]
c = ["house", "garden","bathroom", "unique"]

First we generate all options available. This way your 'a' list is not restricting the options. We use sets to get unique values.

options = list(set(a + b + c))
wanted = pd.DataFrame({'options' : options} )

Next we loop over all the lists. We use tuples to assign both name and variable.

for name, _ in [('a',a),('b',b),('c',c)]:
    wanted.loc[wanted['options'].isin(_), name ] = 1

Finally we fill the missing values with zeroes.

wanted = wanted.fillna('0')

In case you have large number of lists, you can iterate over ascii_lowercase letters:

my_tuples = ()
from string import ascii_lowercase

for _ in ascii_lowercase[:3]: #<-magic number for number of lists here
     my_tuples = my_tuples + (str(_), eval(_))

Then change for loop as:

for name, _ in my_tuples:
     ....
$\endgroup$

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.