I am trying to work on a function that has the following input:
ID Year C01 C02 C03 C04 C05
0 1 2019 0 0 0 0 0
1 1 2020 1 0 0 0 1
2 2 2019 0 1 0 0 0
3 3 2020 0 0 0 0 0
4 4 2020 1 0 0 0 0
5 5 2020 0 0 1 0 0
I want to write a function that would return the following table:
ID Year CC
0 1 2019
1 1 2020 1,5
2 2 2019 2
3 3 2020
4 4 2020 1
5 5 2020 3
This is what I have tried so far:
new['C01'] = np.where(np.array(new['C01']==1),1,0)
new['C02'] = np.where(np.array(new['C02']==1),2,0)
new['C03'] = np.where(np.array(new['C03']==1),3,0)
new['C04'] = np.where(np.array(new['C04']==1),4,0)
new['C05'] = np.where(np.array(new['C05']==1),5,0)
new['C06'] = np.where(np.array(new['C06']==1),6,0)
new['C07'] = np.where(np.array(new['C07']==1),7,0)
new['C08'] = np.where(np.array(new['C08']==1),8,0)
new['C09'] = np.where(np.array(new['C09']==1),9,0)
new['C10'] = np.where(np.array(new['C10']==1),10,0)
After this, I tried to use the dot join with row values which didn't work quite well.
I want to write a function that would do this and concat these values in a single column separated by a comma.