How to go about removing duplicates column by column in a pandas data frame so that:
set1 set2 set3 set4
apple apple orange orange
apple orange banana orange
orange banana pear
banana banana lemon
pear lemon
grape lemon
becomes:
set1 set2 set3 set4
apple apple orange orange
orange orange banana
banana banana pear
pear lemon
grape
Use:
m=df.apply(lambda x:dict.fromkeys(x).keys())
pd.DataFrame(m.values.tolist(),index=m.index).T
Or a better way courtesy @piRSquared:
pd.DataFrame.from_dict({k: {*df[k].dropna()} for k in df}, orient='index').T
set1 set2 set3 set4
0 apple apple orange orange
1 orange orange banana NaN
2 banana banana pear None
3 pear NaN lemon None
4 grape None None None
pd.DataFrame.from_dict({k: {*df[k].dropna()} for k in df}, orient='index').THere is another way pivot
df.melt().dropna().drop_duplicates(['variable','value']).\
assign(key=lambda x : x.groupby('variable').cumcount()).pivot(index='key',columns='variable',values='value')
Out[806]:
variable set1 set2 set3 set4
key
0 apple apple orange orange
1 orange orange banana NaN
2 banana banana pear NaN
3 pear NaN lemon NaN
4 grape NaN NaN NaN
itertools.zip_longestfrom itertools import zip_longest
pd.DataFrame(
[*zip_longest(*({*df[c].dropna()} for c in df))],
columns=[*df]
)
set1 set2 set3 set4
0 banana orange banana orange
1 grape banana lemon None
2 pear apple pear None
3 apple None orange None
4 orange None None None
collections.defaultdict and itertools.count# %%timeit
from collections import defaultdict
from itertools import count
i = defaultdict(count)
pd.DataFrame({c: {next(i[c]): v for v in {*df[c].dropna()}} for c in df})
set1 set2 set3 set4
0 pear apple orange orange
1 grape banana lemon NaN
2 apple orange banana NaN
3 banana NaN pear NaN
4 orange NaN NaN NaN
You can also use drop_duplicates :
df.apply(lambda x : x.drop_duplicates().reset_index(drop=True))
>
set1 set2 set3 set4
0 apple apple orange orange
1 orange orange banana NaN
2 banana banana pear NaN
3 pear NaN lemon NaN
4 grape NaN NaN NaN
df['column_name'].unique().