0

Can anyone please help me reshape the data (attaching file named dummy data) . This data is a three-day observation for three people, for day1, day2 and day3, on two parameters- s and t.

I have been trying to reshaped the wide format data into long data ( attaching file named dummy data). I want all the variables of one day get standardized into 'day','s', and 't' and all the variables of each day get stacked below each other as shown in output data. At the same time, the 'name' and 'id' should repeat three times for each day in the long data.

I used grep function in r to identify the position of column numbers of the variables 'day1', 'day2' and 'day3', but I am not able to do two things:

  1. Standard the variable names and restructure them into long data as explained above.
  2. Duplicate the 'name' and 'id' three times in the long data.

dummy data

NelsonGon
  • 13,015
  • 7
  • 27
  • 57
shweta singh
  • 9
  • 3
  • Please edit your question to include the output of `dput(head(df,10))` to make it easier for people to help. Include sample code showing what you have tried. – NelsonGon Feb 02 '19 at 05:01
  • It appears your data is in Excel. You can use the package `datapasta` to copy/paste data from Excel into R easily. Then you can use `regex` to copy/paste your code from R into SO so people can easily reproduce your problem. – Prevost Feb 02 '19 at 05:09
  • @Prevost why not just read the data into R? – NelsonGon Feb 02 '19 at 05:19
  • What is your ultimate goal? Why is it necessary to reshape the data? This is important because reshaping the data will likely distort the pairing unless your aim is to simply make a plot. – NelsonGon Feb 02 '19 at 06:04
  • 1
    try with `melt` `data.table::melt(setDT(df1), measure = patterns("^day\\d+", "^s\\d+", "^t\\d+"), value.names = c("day", "s", "t"))` – akrun Feb 02 '19 at 06:18
  • @NelsonGon - i have a long data approx 1200 columns ( variables ) and 400 rows. This is just a dummy one. I want to reshape my data in this manner for better view. – shweta singh Feb 03 '19 at 16:16
  • @jay.sf - This is a different one. I want to standardized variables day, s & t. and all the variables will be in column except days ones. I tried using melt function but it puts all the variables in one column which is not my goal. – shweta singh Feb 03 '19 at 16:18
  • @akrun 's answer works,no? – NelsonGon Feb 03 '19 at 17:08
  • @akrun - Thank you so much. code works but there is only one problem, variable names are coming as value1, value2, value3. I want variable names as day, s, t. And also want day1, day2, day3 in one column named day. could you please help with this one also. – shweta singh Feb 03 '19 at 17:15
  • @shwetasingh Haven't u used you `value.names = c('day', ..` – akrun Feb 03 '19 at 17:16
  • @NelsonGon - yes. but there is some problem in that. mentioned in above comment. – shweta singh Feb 03 '19 at 17:17
  • 1
    @shwetasingh It should be `value.name` instead of `value.names`, sorry a typo – akrun Feb 03 '19 at 17:17
  • @shwetasingh Based on the example you showed, it is working for me – akrun Feb 03 '19 at 17:19
  • @akrun - Thanks. It works but how should i put the day 1 , day2 , day3 in one column. should i use the grep and gather function on variable day first and then will use your code on variable s & t. – shweta singh Feb 03 '19 at 17:21
  • sorry, I didn't understand your question. The `melt` will automatically get the values of the columns based on the patterns in the `measure` – akrun Feb 03 '19 at 17:23
  • 1
    @akrun - Thank you so much. Let me try again once. – shweta singh Feb 03 '19 at 17:27
  • @akrun - Hi, my question is that when i am running your code, in column "day" i am getting value 1,2,3 but i want day1, day2, day3 in place of 1, 2 3. – shweta singh Feb 04 '19 at 04:34
  • @akrun - and when i am running this code on my original data, i am getting some warning message mentioned below : Warning message: In melt.data.table(setDT(dummy_eng), measure = patterns("^day", : 'measure.vars' [cmmnts_jan1, cmmnts_jan2, cmmnts_jan3] are not all of the same type. By order of hierarchy, the molten data value column will be of type 'integer'. All measure variables not of type 'integer' will be coerced too. Check DETAILS in ?melt.data.table for more on coercion. – shweta singh Feb 04 '19 at 04:44
  • sorry, without a small reproducible example using `dput`, it is difficult to comment – akrun Feb 04 '19 at 06:23
  • @akrun - Hi Arun, data is large and i am finding difficulty to load it here. Can i have your email id so that i will send you the dummy file of the data. – shweta singh Feb 04 '19 at 14:48
  • @shwetasingh As per the data you showed, the code should work fine – akrun Feb 04 '19 at 14:48

0 Answers0