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))