Applying melt function to multiple .txt files

Question

I am having multiple .txt files each representing some parameters in the following format

Parameter1 = structure(list(Year = 1969:1974, Jan = c(16.6, 15.6, 15.8, 16.9, 
    16.2, 15.4), Feb = c(17, 15.2, 16.6, 14.8, 12.9, 17.9), Mar = c(14.2, 
    13.3, 16.9, 14.9, 15.5, 13.4), Apr = c(11.6, 10.7, 10.7, 11.6, 
    10.3, 9.7), May = c(9.9, 9.2, 9.7, 9.6, 8.2, 8.8), Jun = c(7.6, 
    7.2, 7.1, 7.2, 6, 6.9), Jul = c(7, 6.7, 6.7, 6.9, 6.9, 5.2), 
        Aug = c(7, 7.2, 7.2, 6.1, 6.8, 6.5), Sep = c(8.4, 7.6, 8.5, 
        7.3, 6.6, 6.8), Oct = c(10.4, 8.5, 8.5, 9.1, 8.3, 7.7), Nov = c(14, 
        12.2, 12.9, 12.2, 10.8, 10.2), Dec = c(15.5, 16.7, 15.9, 
        15.5, 13.2, 15.4), Annual = c(11.6, 10.8, 11.4, 11, 10.1, 
        10.3), winter = c(16.8, 15.4, 16.2, 15.8, 14.5, 16.6), premonsoon = c(11.9, 
        11.1, 12.4, 12, 11.3, 10.6), monsoon = c(7.5, 7.2, 7.3, 6.8, 
        6.6, 6.3), postmonsoon = c(13.3, 12.5, 12.5, 12.2, 10.7, 
        11.1)), row.names = c(NA, 6L), class = "data.frame")

I want to melt those .txt files and have it into a single file like the following format

I could able to do the reshaping operation for a single file like

A <- read.delim("Parameter1.txt", sep="\t")

Transpose <- t(A)
colnames(Transpose) = Transpose[1, ] # the first row will be the header
Transpose = Transpose[-1, ]          # removing the first row.
arranged_data <- melt(Transpose) 
melt(A)
head(arranged_data)
Arranged_data <- data.frame(cbind(arranged_data$X2,as.character(arranged_data$X1),arranged_data$value))
colnames(Arranged_data) <- c("Year","Month","Parameter1")
head(Arranged_data)
#Removing the seasonal and annual data
Final <- subset(Arranged_data, !Month %in% c("postmonsoon","monsoon","premonsoon","winter","Annual"))
numMonth<-function(x)c(JAN=1,FEB=2,MAR=3,APR=4,MAY=5,JUN=6,JUL=7,AUG=8,SEP=9,OCT=10,NOV=11,DEC=12)
Final$Month <- numMonth(Final$Month)
write.csv(Final,"arranged_data_TAMILNADU.csv",row.names = F)

Now how to apply it on multiple .txt files and get the output into single file.

Adrian Lindsey · Accepted Answer · 2019-11-03T14:30:01.977

I would suggest writing the melting / reshaping into a function and calling it over a list of inputs. Also, I'm using dplyr::gather() instead of reshape2::melt(). I hope this helps!

library(tidyverse)

A = structure(list(Year = 1969:1974, 
                   Jan = c(16.6, 15.6, 15.8, 16.9, 16.2, 15.4), 
                   Feb = c(17, 15.2, 16.6, 14.8, 12.9, 17.9), 
                   Mar = c(14.2, 13.3, 16.9, 14.9, 15.5, 13.4), 
                   Apr = c(11.6, 10.7, 10.7, 11.6, 10.3, 9.7),
                   May = c(9.9, 9.2, 9.7, 9.6, 8.2, 8.8), 
                   Jun = c(7.6, 7.2, 7.1, 7.2, 6, 6.9), 
                   Jul = c(7, 6.7, 6.7, 6.9, 6.9, 5.2), 
                   Aug = c(7, 7.2, 7.2, 6.1, 6.8, 6.5), 
                   Sep = c(8.4, 7.6, 8.5, 7.3, 6.6, 6.8), 
                   Oct = c(10.4, 8.5, 8.5, 9.1, 8.3, 7.7), 
                   Nov = c(14, 12.2, 12.9, 12.2, 10.8, 10.2), 
                   Dec = c(15.5, 16.7, 15.9, 15.5, 13.2, 15.4), 
                   Annual = c(11.6, 10.8, 11.4, 11, 10.1, 10.3), 
                   winter = c(16.8, 15.4, 16.2, 15.8, 14.5, 16.6), 
                   premonsoon = c(11.9, 11.1, 12.4, 12, 11.3, 10.6), 
                   monsoon = c(7.5, 7.2, 7.3, 6.8, 6.6, 6.3), 
                   postmonsoon = c(13.3, 12.5, 12.5, 12.2, 10.7, 11.1)), 
              row.names = c(NA, 6L), 
              class = "data.frame")                                                                                                                                                                                                                                                                                                                                                   11.1)), row.names = c(NA, 6L), class = "data.frame")

B <- A
mylist <- list(A,B)

myfunc <- function (x){
  x %>% 
    gather(key = "Month",
           value = "Value",
            c(format(ISOdatetime(2000,1:12,1,0,0,0),"%b"))) %>% # list of month names
    arrange(Year) %>% # sort dataframe by Year
    select(Year, Month, Value, winter, premonsoon, monsoon, postmonsoon)
}

mylist_melted <- lapply(mylist, myfunc)

Your code is working but the only problem is the Month is becoming constant while the year is varying in the output. I want the reverse i.e. year should remain constant with 12 months. See the image of my question that is the desired output. Could you make some changes to your code so that I can accept your answer? — UseR10085, Nov 03 '19 at 03:40
By "becoming constant," do you mean repeated at the top? If so, then adding `arrange(Year)` will sort the dataframes by Year for you. If not, would you help me understand? — Adrian Lindsey, Nov 03 '19 at 13:55
Yes. But it should be 1969 repeated 12 times in the year column with months from Jan to Dec. Then 1970 repeated 12 times and months from Jan to Dec. See the image in my question which is the desired output. — UseR10085, Nov 03 '19 at 14:18
I see. So, I've edited the function to include `arrange()` to sort the dataframe and `select()` to order the columns. I hope this helps! — Adrian Lindsey, Nov 03 '19 at 14:31
Thanks a lot, it has worked perfectly now. I have just modified your function as I don't want Annual, premonsoon, monsoon and postmonsoon in the output. — UseR10085, Nov 03 '19 at 14:51

Applying melt function to multiple .txt files

1 Answers1