Column wise count in Pandas DF

[mr.salt]

I have the below DF

A	B	C	D	COLUMNS
1	ABC	ABC		2,3
	DEF	ABC	DEF	2,4
	LSD	LSD	LSD	2,4
	ERR	ERR		2,3
	ERR	ABC	LSD	2,4

Expected Output

A-1	B-2	C-3	D-4	F-5	COLUMNS INDEX	ABC	DEF	ERR	LSD
1	ABC	ABC			2,3	2	0	0	0
	DEF	ABC	DEF	DEF	2,4	1	2	0	0
	LSD	LSD	LSD	LSD	2,3	0	0	0	2
	ERR	ERR			2,3	0	0	2	0
	ERR	ABC	LSD	LSD	2,4	1	0	1	1

I HAVE CREATED THE COULUMN INDEX FOR SUMMING BUT I COULDN'T ABLE TO LOOP EACH COLUMNS INDED AND GET THE INTENTED OUTPUT.

 

[sevef93249]

Use DataFrame.melt with crosstab and DataFrame.join:

a = df[['B','C','D']].melt(ignore_index=False)['value']

df1 = df.join(pd.crosstab(a.index, a))

df1.insert(4, 'F', df1['D'])
d = {v: f'{v}-{k}' for k, v in enumerate(df1.columns[:5], 1)}
df1 = df1.rename(columns=d)
print (df1)
   A-1  B-2  C-3  D-4  F-5 COLUMNS  ABC  DEF  ERR  LSD
0  1.0  ABC  ABC  NaN  NaN     2,3    2    0    0    0
1  NaN  DEF  ABC  DEF  DEF     2,4    1    2    0    0
2  NaN  LSD  LSD  LSD  LSD     2,4    0    0    0    3
3  NaN  ERR  ERR  NaN  NaN     2,3    0    0    2    0
4  NaN  ERR  ABC  LSD  LSD     2,4    1    0    1    1

If need slice by COLUMN:

from collections import Counter

L = [Counter(b[a[0]-1:a[1]]) for a, b in zip(df['COLUMNS'].str.split(',', expand=True)
                                                        .astype(int).to_numpy(),
                                             df.to_numpy())]

df = df.join(pd.DataFrame(L, index=df.index).fillna(0).astype(int))
print (df)
     A    B    C    D COLUMNS  ABC  DEF  LSD  ERR
0  1.0  ABC  ABC  NaN     2,3    2    0    0    0
1  NaN  DEF  ABC  DEF     2,4    1    2    0    0
2  NaN  LSD  LSD  LSD     2,4    0    0    3    0
3  NaN  ERR  ERR  NaN     2,3    0    0    0    2
4  NaN  ERR  ABC  LSD     2,4    1    0    1    1