Pandas: Retain the column value based on the value present in dictionary and make other columns as blank

user_12883297 0
Viewed 70

I have a dataframe

df = pd.DataFrame([["A","X",98,56,1,2,3,4], ["B","Z",79,54,36,3,4,8], ["C","Y",98,56,2,5,6,7],["A","Y",79,54,36,12,13,24], ["B","X",98,56,3,6,7,8], ["C","Z",48,51,85,5,6,5]], columns=["id","key","c1","c2","c3","c4","C5","C6"])

And I have a dictionary

dic = {"X":['c1','c3'],"Y":['c2','c4'],"Z":['c5','c6']}

based on key column of df, select the columns using dictionary dic, keep row values only in those columns and make other row values as blank.

Ex: for key X of df, keep values in C1 and C3 as in the dictionary and make other columns blank.

Expected Output:

df_out = pd.DataFrame([["A","X",98,"",1,"","",""], ["B","Z","","","","",4,8], ["C","Y","",56,"",5,"",""],["A","Y","",54,"",12,"",""], ["B","X",98,"",3,"","",""], ["C","Z","","","","",6,5]], columns=["id","key","c1","c2","c3","c4","C5","C6"])

How to do it?

pandas python-2.7 dataframe python-3.x python
1 Answers

Use Index.difference for not matched columns and set empty strings in DataFrame.loc:

dic = {"X":['c1','c3'],"Y":['c2','c4'],"Z":['C5','C6']}

for k, v in dic.items():
    df.loc[df.key == k, df.columns.difference(v + ['id', 'key'])] = ''

print (df)
  id key  c1  c2 c3  c4 C5 C6
0  A   X  98      1          
1  B   Z                 4  8
2  C   Y      56      5      
3  A   Y      54     12      
4  B   X  98      3          
5  C   Z                 6  5
user_29010020
Related