How to remove certain values from a pandas dataframe, which are not in a list?

user_10435760 0
Viewed 47

By writing the following code I create a dataframe

data = [['A', 'B','D'], ['A','D'], ['F', 'G','C','B','A']] 
df = pd.DataFrame(data) 
df

enter image description here

My goal is to remove the values from the dataframe that are not in the list below.

list_items = ['A','B','C']

My expected output is as under

enter image description here

I have tried traversing the values in loops and check one by one, but let's say the dataframe is very large in size (9108, 1616) and the list has over 130 items that need to be checked. In that case it's taking too long to run the code. Please suggest the most efficient way to achieve the expected output.

pandas numpy python
2 Answers

I don't think doing it in pandas is a good ideas as columns don't matter here. It's easier to do it with lists, that you can convert to a pandas dataframe in the end if you really need it.

# convert df to list of lists
data = df.values.tolist()
# filter each element of the list to contain only list_items values
data_filtered = [ [el for el in l if el in list_items] for l in data]
# convert back to dataframe
df_filtered = pd.DataFrame(data_filtered) 
print(df_filtered)
#   0   1    2
#0  A   B    None
#1  A   None None
#2  C   B    A
user_33011130

Let us try not use for loop

s=df.where(df.isin(list_items)).reset_index().melt('index').dropna()
s=s.assign(Key=s.groupby('index').cumcount()).pivot('index','Key','value')
Key    0    1    2
index             
0      A    B  NaN
1      A  NaN  NaN
2      C    B    A

Method two not good for the large dataframe 

s=df.where(df.isin(list_items)).T.apply(lambda x : sorted(x,key=pd.isnull)).T.dropna(thresh=1, axis=1)
   0    1    2
0  A    B  NaN
1  A  NaN  NaN
2  C    B    A
user_79645270
Related