1. Given following CSV file $cat data.csv
ID,City,Zip,Price,Rating1,A,95123,100,01,B,95124,102,11,A,95126,100,12,B,95123,200,02,B,95124,201,12,C,95124,203,03,A,95126,300,13,C,95124,280,04,C,95124,400,1 We want to group by ID, and make new composite columns of Price and Rating based on the value of $City-$Zip. 2. The Expected Result: ID A_95123_Price A_95123_Rating A_95126_Price A_95126_Rating B_95123_Price B_95123_Rating B_95124_Price B_95124_Rating C_95124_Price C_95124_Rating 1 100 1 100 2 0 0 102 2 0 0 2 0 0 0 0 200 1 201 2 203 1 3 0 0 300 2 0 0 0 0 280 1 4 0 0 0 0 0 0 0 0 400 2 Any tips would be greatly appreciated! Thank you. Regards, Rex