Combine column values into a list of unique values without nan in a new column-CodePudding

I want to combine multiple columns of a Pandas DataFrame into a single column of lists, such that each list does not contain duplicate values and does not contain null values.

So, for example, in the data frame below, columns A, B and C are combined into column D:

                  A              B                 C                                 D
0             "KFC"            NaN             "KFC"                           ["KFC"]
1               NaN   "Mandai Zoo"   "Singapore Zoo"   ["Mandai Zoo", "Singapore Zoo"]
2   "Tampines Mall"            NaN               NaN                 ["Tampines Mall"]

The best I could come up of this was a combination of this and this:

df['D'] = df[['A', 'B', 'C']].values.tolist()
df['D'] = df['D'].map(set).tolist()

which would give me this:

                  A              B                 C                                      D
0             "KFC"            NaN             "KFC"                           ["KFC", NaN]
1               NaN   "Mandai Zoo"   "Singapore Zoo"   [NaN, "Mandai Zoo", "Singapore Zoo"]
2   "Tampines Mall"            NaN               NaN                 ["Tampines Mall", NaN]

But that still leaves the NaN values in the list.

CodePudding user response：

You could use pd.dropna before using pd.Series.unique along axis=1

df['D'] = df.apply(lambda row: row.dropna().unique(), axis=1)
print(df)

               A           B              C                            D
0            KFC         NaN            KFC                        [KFC]
1            NaN  Mandai Zoo  Singapore Zoo  [Mandai Zoo, Singapore Zoo]
2  Tampines Mall         NaN            NaN              [Tampines Mall]

CodePudding user response：

Use:

df[['Name', 'Year', 'Date']].apply(lambda x: [y for y in set(x) if pd.notna(y)], axis=1)

This just holds values if they are not nans.

CodePudding user response：

Because two NaN values are not equal you can use [x for x in my_list if x==x] to remove NaN values:

df = pd.DataFrame({'A': ["KFC", np.nan, "Tampines Mall"], 'B': [np.nan, "Mandai Zoo", np.nan], 'C': ["KFC", "Singapore Zoo", np.nan]}). 

df.assign(D = lambda df: [[z for z in x if z==z] for x in  [set(l) for l in df.values.tolist()]])

Result:

               A           B              C                           D 

0            KFC         NaN            KFC                        [KFC]
1            NaN  Mandai Zoo  Singapore Zoo  [Mandai Zoo, Singapore Zoo]
2  Tampines Mall         NaN            NaN              [Tampines Mall]