0

I have a data set that is currently being pulled from Redshift of the following format

  name        nps  index
1 company1    8    1
2 company1    4    2
3 company1    7    3
4 company2    6    1
5 company2    9    2

and so on for 82krows. I added the index column when trying to pivot the table in Redshift before I realized that wasn't an option so I'm not sure if it has any use now and I don't necessarily need to keep it.

Ideally I want to get to a point where the data looks like this:

  name        1  2  3  4  5  6  7 ...
1 company1    8  4  7  
2 company2    6  9

I've tried using the reshape package in r to melt and cast the data but I'm only able to get a count of the nps values that way and I really want the specific scores so I can do and ICC on them by company.

Is there another package in R that can accomplish this? I really don't want to do it by hand as there are 4500+ different names.

JTepper
  • 15
  • 4
  • Search here for `[r] reshape` for these sorts of problems. `reshape(dat, idvar="name", timevar="index", direction="wide")` – thelatemail Jun 21 '17 at 21:57
  • Possibly duplication https://stackoverflow.com/questions/29720473/dplyr-pivot-table – Lowpar Jun 21 '17 at 21:59
  • https://stackoverflow.com/questions/3835280/pivoting-rows-into-columns – M-- Jun 21 '17 at 22:06
  • https://stackoverflow.com/questions/6471226/how-to-pivot-a-table-to-make-columns-fro-a-variable-row-values-in-r – M-- Jun 21 '17 at 22:06
  • thank you for the links @thelatemail and @Lowpar, but these don't address my problem- I have carefully read all of the instruction for the reshape package [link](http://had.co.nz/reshape/introduction.pdf) and as far I can understand I must use a function to cast the data, which I don't want to do, I want to retain the individual values in the `nps` column – JTepper Jun 21 '17 at 22:11
  • It is possible with Dplyr, just look into it, it can do everything in one swift command. Understand the verbs of the package. – Lowpar Jun 21 '17 at 22:17
  • @JTepper - did you run the code I provided? It works to give you your required result. Also `reshape::cast(dat, name ~ index, value="nps")` and `reshape2::dcast(dat, name ~ index, value.var="nps")` and `tidyr::spread(dat, key = index, value = nps)` from the `reshape`, `reshape2` and `tidyr` packages respectively. – thelatemail Jun 21 '17 at 22:23
  • I think the issue is just that you need to specify `value="nps"` when `cast`-ing. Admittedly, this is only implied in the duplicate I selected. I have made a comment over there and hopefully the answer will be updated. – thelatemail Jun 21 '17 at 22:44
  • @thelatemail- I did end up using the first option you provided and got exactly what I was looking for- much easier than using melt and cast- thanks – JTepper Jun 22 '17 at 17:34

0 Answers0