Reshape wide data to long format

Question

I am facing trouble reshaping my wide data into a long format using reshape melt.But I couldn't find a solution best suited to my need. Sorry if I am duplicating this question. My monthly data (CSV file)of a single variable is in the following format;

Date        level1  level2  level3  level4 ..... level100
1/1/2003    191.3    191.4  191.4   191.4
1/2/2003    184.3    184.3  184.3   184.3
1/3/2003    176.5    176.5  176.5   176.5
1/4/2003    175.5    175.5  175.5   175.5
..
..
1/12/2003

I am trying to reshape in this format

Date        Level   value
1/1/2003    1       191.3
1/1/2003    2       191.4
..      ..      ..
1/1/2003    100     #value
1/2/2003    1       #value
1/2/2003    2       #value
..
1/2/2003    100     #value

and so on...

Does this answer your question? [Reshaping data.frame from wide to long format](https://stackoverflow.com/questions/2185252/reshaping-data-frame-from-wide-to-long-format) — mhovd, Jul 04 '21 at 15:42

Ronak Shah · Answer 1 · 2021-07-04T11:12:41.490

0

You can do this with pivot_longer from tidyr -

result <- tidyr::pivot_longer(df, 
                    cols = -Date, 
                    names_to = 'Level',
                    names_pattern = 'level(\\d+)'
                    )

result

#    Date     Level value
#   <chr>    <chr> <dbl>
# 1 1/1/2003 1      191.
# 2 1/1/2003 2      191.
# 3 1/1/2003 3      191.
# 4 1/1/2003 4      191.
# 5 1/2/2003 1      184.
# 6 1/2/2003 2      184.
# 7 1/2/2003 3      184.
# 8 1/2/2003 4      184.
# 9 1/3/2003 1      176.
#10 1/3/2003 2      176.
#11 1/3/2003 3      176.
#12 1/3/2003 4      176.
#13 1/4/2003 1      176.
#14 1/4/2003 2      176.
#15 1/4/2003 3      176.
#16 1/4/2003 4      176.

To write the output to csv use write.csv -

write.csv(result, 'result.csv', row.names = FALSE)

edited Jul 04 '21 at 11:12

answered Jul 04 '21 at 11:04

Ronak Shah

377,200
20
156
213

how can I write this output in another csv file? – linux_lover Jul 04 '21 at 11:07
You can use `write.csv` to write the file. See updated answer. – Ronak Shah Jul 04 '21 at 11:13
Wow! that is what I wanted. I also added extra `values_to='variable_name'` to get my desired variable name in the output. – linux_lover Jul 04 '21 at 11:32
I am still eager to know solution using `melt` – linux_lover Jul 04 '21 at 11:34

score 0 · Answer 2 · answered Jul 04 '21 at 15:33

Came up with a solution using melt after a bit of search. Though, couldn't completely done using melt. order and gsub were used alongside to get the exact output. Maybe it will be useful to somebody. Please feel free to edit if any improvement needed.

Date=as.Date(d$Date,format='%d%m%Y')
df_melt <- melt(d, id.vars = c("Date"), 
                variable.name = "Level", 
                value.name = "name_of_interest", 
                measure.vars = names(d[,2:101]))
b<- df_melt[order(as.Date(df_melt$Date, format="%d/%m/%Y")),] # to get the date in order
b$Level<-gsub("level","",as.numeric(b$Level)) # to remove the string "level"
write.csv(b,"result.csv", row.names = FALSE)

Reshape wide data to long format

2 Answers2