Change the format of your data

Question

For some of you these is an easy question; I would like to reshape my data

My data structure:

structure(list(Time = c("00:01", "00:02", "00:03", "00:04", "00:05", 
"00:06", "00:07", "00:08", "00:09", "00:10"), Week_Day = c("Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday", "Wednesday", 
"Wednesday", "Wednesday", "Wednesday", "Wednesday"), Value = c(4.293333, 
4.293333, 6.708333, 6.171667, 5.635, 5.098333, 5.098333, 5.098333, 
5.098333, 4.293333), Week_Day = c("Thursday", "Thursday", "Thursday", 
"Thursday", "Thursday", "Thursday", "Thursday", "Thursday", "Thursday", 
"Thursday"), Value = c(2.415, 1.878333, 1.878333, 1.878333, 1.878333, 
1.878333, 1.878333, 1.878333, 1.878333, 1.878333), Week_Day = c("Friday", 
"Friday", "Friday", "Friday", "Friday", "Friday", "Friday", "Friday", 
"Friday", "Friday"), Value = c(4.025, 4.293333, 4.293333, 4.293333, 
3.756667, 3.756667, 3.756667, 4.293333, 4.83, 4.83), Week_Day = c("Saturday", 
"Saturday", "Saturday", "Saturday", "Saturday", "Saturday", "Saturday", 
"Saturday", "Saturday", "Saturday"), Value = c(14.758333, 10.196667, 
4.561667, 4.293333, 4.83, 5.098333, 4.83, 4.83, 5.366667, 4.561667
), Week_Day = c("Sunday", "Sunday", "Sunday", "Sunday", "Sunday", 
"Sunday", "Sunday", "Sunday", "Sunday", "Sunday"), Value = c(5.098333, 
4.83, 5.098333, 4.83, 4.025, 4.025, 4.293333, 4.293333, 4.293333, 
4.293333), index = 1:10), row.names = c(NA, 10L), class = "data.frame")

The initial data format is

"Time"     "Week_Day" "Value"    "Week_Day" "Value"    "Week_Day" "Value"    "Week_Day" "Value"    "Week_Day" "Value"    "index"

Hoe can I change this format into

"Time" "Week_Day" "Value" "Index"

many thanks

@Sotos index is a counter defined as Data$index <- 1:nrow(Data) — Rstudent, Nov 18 '19 at 12:48

s_baldur · Accepted Answer · 2019-11-18T13:12:39.900

0

Using data.table:

library(data.table)
setDT(data)
data_long <- melt(
  data, 
  id.vars = c("Time", "index"), 
  measure.vars = patterns(Week_Day = "Week_Day", Value = "Value")
)[, !"variable"]

 head(data_long)
    Time index  Week_Day    Value
1: 00:01     1 Wednesday 4.293333
2: 00:02     2 Wednesday 4.293333
3: 00:03     3 Wednesday 6.708333
4: 00:04     4 Wednesday 6.171667
5: 00:05     5 Wednesday 5.635000
6: 00:06     6 Wednesday 5.098333

# To convert back to a regular data.frame:
setDF(data_long)

In base R:

data_long <- data.frame(
  Time = data$Time,
  index = data$index,
  Week_Day = unlist(data[names(data) == "Week_Day"]),
  Value = unlist(data[names(data) == "Value"]),
  row.names = NULL
)

head(data_long)
   Time index  Week_Day    Value
1 00:01     1 Wednesday 4.293333
2 00:02     2 Wednesday 4.293333
3 00:03     3 Wednesday 6.708333
4 00:04     4 Wednesday 6.171667
5 00:05     5 Wednesday 5.635000
6 00:06     6 Wednesday 5.098333

edited Nov 18 '19 at 13:12

answered Nov 18 '19 at 12:55

s_baldur

29,441
4
36
69

many thanks when you have time could please explain the steps.many thanks – Rstudent Nov 18 '19 at 13:05
many thanks Week_Day = unlist(data[names(data) == "Week_Day"]), Value = unlist(data[names(data) == "Value"]), – Rstudent Nov 18 '19 at 13:31
1

Step 1: Creating a logical vector `names(data) == "Value"`. Step 2: the `data.frame` with only the columns named `Value`: `data[names(data) == "Value"]`. Step 3: use `unlist()` to convert to a simple vector. – s_baldur Nov 18 '19 at 13:36

Change the format of your data

1 Answers1