How to remove duplicate cells from each row, considering each row separately (and perhaps replace them with NaNs) in a Pandas dataframe?
It would be even better if we could shift all newly created NaNs to the end of each row.
Posts on how to remove entire rows which are deemed duplicate:
Post on how to remove duplicates from a list which is in a Pandas column:
Answer given here returns a series of strings, not a dataframe.
import pandas as pd
Let's create a dataframe:
df = pd.DataFrame({'a': ['A', 'A', 'C', 'B'],
'b': ['B', 'D', 'B', 'B'],
'c': ['C', 'C', 'C', 'A'],
'd': ['D', 'D', 'B', 'A']},
index=[0, 1, 2, 3])
df created:
+----+-----+-----+-----+-----+
| | a | b | c | d |
|----+-----+-----+-----+-----|
| 0 | A | B | C | D |
| 1 | A | D | C | D |
| 2 | C | B | C | B |
| 3 | B | B | A | A |
+----+-----+-----+-----+-----+
(Printed using this.)
One way of dropping duplicates from each row, considering each row separately:
df = df.apply(lambda row: pd.Series(row).drop_duplicates(keep='first'),axis='columns')
using apply(), a lambda function, pd.Series(), & Series.drop_duplicates().
Shove all NaNs to the end of each row, using Shift NaNs to the end of their respective rows:
df.apply(lambda x : pd.Series(x[x.notnull()].values.tolist()+x[x.isnull()].values.tolist()),axis='columns')
Output:
+----+-----+-----+-----+-----+
| | 0 | 1 | 2 | 3 |
|----+-----+-----+-----+-----|
| 0 | A | B | C | D |
| 1 | A | D | C | nan |
| 2 | C | B | nan | nan |
| 3 | B | A | nan | nan |
+----+-----+-----+-----+-----+
Just as we wished.
Is there a more efficient way to do this? Perhaps with some built-in Pandas functions?
You could search for duplicates on the row axis and then sort out the results to "push" the Nan at the end of the rows by sorting them out with a specific key:
duplicates = df.apply(pd.Series.duplicated, axis=1)
df.where(~duplicates, np.nan).apply(lambda x: pd.Series(sorted(x, key=pd.isnull)), axis=1)
Output
| 0 | 1 | 2 | 3 |
|:----|:----|:----|:----|
| A | B | C | D |
| A | D | C | NaN |
| C | B | NaN | NaN |
| B | A | NaN | NaN |
try something new
df = pd.DataFrame(list(map(pd.unique, df.values)))
Out[447]:
0 1 2 3
0 A B C D
1 A D C None
2 C B None None
3 B A None None
Use apply and construct a new dataframe by pd.DataFrame.from_dict with option orient='index'
df_final = pd.DataFrame.from_dict(df.apply(lambda x: x.drop_duplicates().tolist(),
axis=1).to_dict(), orient='index')
Out[268]:
0 1 2 3
0 A B C D
1 A D C None
2 C B None None
3 B A None None
Note: None practically is similar to NaN. If you want exact NaN. Just chain additional .fillna(np.nan)
Apply pd.Series.unique on each row, extract the result and re-contruct the dataframe:
print (pd.DataFrame(df.apply(pd.Series.unique, axis=1).tolist()))
0 1 2 3
0 A B C D
1 A D C None
2 C B None None
3 B A None None