This "data_long" appears to be a mess you are not satisfied with, rather than the "data" you started with or a model of what you want. On November 28, 2021 9:06:54 AM PST, Philip Monk <prmonk at gmail.com> wrote:>Thanks for your suggestions, Chris. > >I'm writing from Gmail's web client, and have checked the message is >being sent as plain text (though I also did this previously so it may >be that I need to change to Outlook). Let me know if it doesn't work. > >Hopefully I've used dput properly (see example below with apologies to >Burt and the PG). In my defence, I have very limited time due to >caring responsibilities so am time-poor out of necessity rather than >laziness. > >Reading the 'pivot_longer' documentation I think I need to use >'names_pattern' to split the weather data into different columns, but >I don't understand the required syntax. > >Thanks for your help, > >Philip > >rm(list=ls()) >library(ggplot2) >library(ggpubr) >library(tidyverse) >library(rstatix) >library(ez) >library(dplyr) > >data_long <- > structure( > list( > Buffer = c( > "100", > "200", > "300", > "400", > "500", > "600", > "700", > "800", > "900", > "1000", > "1100", > "1200", > "1300", > "1400", > "1500", > "1600", > "1700", > "1800", > "1900", > "Temperature", > "Wind speed", > "Wind trend", > "Wind direction", > "Humidity", > "Pressure", > "Pressure trend" > ), > `15/01/2010` = c( > 6.091741043, > 5.271975614, > 4.451891901, > 3.385694303, > 2.900508112, > 3.110238149, > 3.150580922, > 3.079728958, > 2.327902499, > 1.641887823, > 1.63370882, > 0.986559368, > 0.920601397, > 0.571882394, > 0.340505009, > 0.813480877, > 0.471988882, > 0.269067515, > 0.303179244, > 12, > 10, > 1, > 22.5, > 40, > 1024, > 1 > ), > `16/02/2010` = c( > 6.405879111, > 5.994054977, > 5.61142085, > 4.77953426, > 4.305900444, > 3.616699448, > 2.848148846, > 2.016807672, > 1.452876728, > 2.120099832, > 1.661317381, > 1.133219897, > 1.237239562, > 0.93675648, > 0.7379146, > 1.026085605, > 0.566766122, > 0.13349775, > 0.082892149, > 15, > 9, > 1, > 45, > 44.5, > 1018.5, > 1 > ), > `20/03/2010` = c( > 8.925945159, > 7.375445078, > 6.120095292, > 5.608927408, > 5.61367474, > 4.800003992, > 4.216782177, > 4.05288041, > 3.779922823, > 4.267840277, > 3.747342619, > 2.414025636, > 2.647100163, > 2.272566024, > 2.526476424, > 2.643863876, > 1.290173713, > 0.612263766, > 0.465457136, > 16, > 10.5, > 1, > 67.5, > 22, > 1025, > 1 > ), > `24/04/2011` = c( > 6.278147269, > 5.805619599, > 5.149985946, > 4.542354226, > 4.320657374, > 4.006103489, > 3.642003696, > 3.315992643, > 3.181741995, > 3.321634055, > 2.814670223, > 2.180686348, > 2.253223258, > 2.07198929, > 1.912840489, > 1.825988411, > 1.360936689, > 0.666152106, > 0.537232782, > 23, > 19.5, > 0, > 191.25, > 24.5, > 1005.5, > 1 > ) > ), > row.names = c(NA, 26L), > class = "data.frame" > ) > ># Converts data table from wide (many columns) to long (many rows) and >creates the new object 'data_long' ># Column 1 is the 'Buffer' number (100-2000), Columns 2-25 contain >monthly data covering 2 years. ># Column headers for columns 2:25 are mutated into a column called >'Date', values for each buffer and each date into the column 'LST' > >data_long <- data %>% pivot_longer(cols = 2:25, names_pattern = names_to > "Date", values_to = "LST") > ># Instructs R to treat the 'Date' column data as a date >data_long$Date <- as.Date(data_long$Date, format = "%d/%m/%Y") > ># Creates a new column called 'Month' by extracting the month number >from the date in the 'Date' column >data_long <- mutate(data_long, Month = format(data_long$Date, "%m")) > ># Creates a new column called 'Year' by extracting the Year number >(YYYY as %Y not %y) from the date in the 'Date' column >data_long <- mutate(data_long, Year = format(data_long$Date, "%Y")) > ># Creates a new column called 'JulianDay' by calculating the Julian >Day (day of the year from 1 January) from the date in the 'Date' >column. >data_long <- mutate(data_long, JulianDay = format(data_long$Date, "%j")) > ># Creates a new column called 'TimePeriod' where 1 = pre-construction, >and 2 = post-construction of solar park. ># Uses 'if_else' - If Year < 2015 value = 1, else 2. >data_long <- mutate(data_long, TimePeriod = if_else(data_long$Year < >2015, 1,2, missing = NULL)) > ># Instructs R to treat the 'TimePeriod' column as a (categorical) >factor - it is either 1 (pre-construction, or 2 (post-construction) >data_long$TimePeriod <- as.factor(data_long$TimePeriod) > ># Change data types of Month, Year nad JulianDay >data_long$Month <- as.numeric(data_long$Month) >data_long$Year <- as.numeric(data_long$Year) >data_long$JulianDay <- as.numeric(data_long$JulianDay) > ># 'Compactly display the internal structure of an R object' >str(data_long) > ># Adds 'data_long' to R search path so that R can access it when >evaluating a variable (simplifies syntax). >attach(data_long) > > >On Sun, 28 Nov 2021 at 15:59, Chris Evans <chrishold at psyctc.org> wrote: >> >> Often the issue is that different variables in the wide format are of different types so won't simply >> pivot_longer without you making decisions which the function shouldn't make for you. However, I think >> the error messages when that happens are fairly clear so perhaps that's not what's happening here. >> >> I'm happy to have a look at this as I've slowly become a convert to using tidyverse principles and tools >> (sometimes seen, legalistically correctly I think, as outside the remit of this Email list) but I agree >> that the help for pivot_longer() and many other tidyverse functions is not as good as it could be particularly >> for people new to R. Often there is better documentation in vignettes (so look for that) or in other things >> on the web. >> >> However, for me the data that was posted are mangled by the post coming in HTML format. Please read the >> list documentation and resubmit the question in raw text Email and submit a bit of your data using >> dput() (see ?dput and search out "R reproducible examples") and then I'll look at it. >> >> Very best (all), >> >> Chris >> >> ----- Original Message ----- >> > From: "Philip Monk" <prmonk at gmail.com> >> > To: "R-help Mailing List" <r-help at r-project.org> >> > Sent: Sunday, 28 November, 2021 13:57:07 >> > Subject: [R] Syntax help for 'Pivot_longer' >> >> > Hello, >> > >> > I have a wide table that I transform to a long table for analysis. >> > The wide table has 25 columns - the first is labels, then columns 2:25 >> > are monthly data of LST which is in 19 rows. >> > >> > I mutate this with : >> > >> > data_long <- data %>% pivot_longer(cols = 2:25, names_to >> > "Date", values_to = "LST") >> > >> > I've decided to add some weather data which might be relevant, >> > inputting this as an additional 7 rows of data in the wide format (see >> > example below of the first 5 months of data). >> > >> > I have belatedly realised that I cannot work out how to pivot this >> > into the long format I need - the documentation doesn't provide enough >> > syntax examples for me to work it out (I've not long been using 'R'). >> > >> > How do I mutate this to provide the additional columns in the long >> > table for the weather variables? >> > >> > Thanks for your time, >> > >> > Philip >> > >> > Part-time PhD Student (Environmental Science) >> > Lancaster University, UK. >> > >> > >> > >> > Wide data >> > ------------------ >> > >> > Buffer 15/01/2010 16/02/2010 20/03/2010 >> > 24/04/2011 07/05/2010 >> > >> > 100 6.091741043 6.405879111 8.925945159 >> > 6.278147269 6.133940129 >> > >> > 200 5.271975614 5.994054977 7.375445078 >> > 5.805619599 5.537759202 >> > >> > 300 4.451891901 5.61142085 6.120095292 >> > 5.149985946 5.353001442 >> > >> > 400 3.385694303 4.77953426 5.608927408 >> > 4.542354226 4.824773827 >> > >> > 500 2.900508112 4.305900444 5.61367474 >> > 4.320657374 4.520022189 >> > >> > 600 3.110238149 3.616699448 4.800003992 >> > 4.006103489 4.188421662 >> > >> > 700 3.150580922 2.848148846 4.216782177 >> > 3.642003696 3.725611032 >> > >> > 800 3.079728958 2.016807672 4.05288041 >> > 3.315992643 3.278124347 >> > >> > 900 2.327902499 1.452876728 3.779922823 >> > 3.181741995 3.29577819 >> > >> > 1000 1.641887823 2.120099832 4.267840277 >> > 3.321634055 3.551965361 >> > >> > 1100 1.63370882 1.661317381 3.747342619 >> > 2.814670223 2.807355369 >> > >> > 1200 0.986559368 1.133219897 2.414025636 >> > 2.180686348 2.166547946 >> > >> > 1300 0.920601397 1.237239562 2.647100163 >> > 2.253223258 2.411947081 >> > >> > 1400 0.571882394 0.93675648 2.272566024 >> > 2.07198929 1.954723088 >> > >> > 1500 0.340505009 0.7379146 2.526476424 >> > 1.912840489 2.003872651 >> > >> > 1600 0.813480877 1.026085605 2.643863876 >> > 1.825988411 2.278799668 >> > >> > 1700 0.471988882 0.566766122 1.290173713 >> > 1.360936689 1.45967449 >> > >> > 1800 0.269067515 0.13349775 0.612263766 >> > 0.666152106 0.680354177 >> > >> > 1900 0.303179244 0.082892149 0.465457136 >> > 0.537232782 0.287185161 >> > >> > Temperautre 12 15 16 >> > 23 21.5 >> > >> > Wind speed 10 9 10.5 >> > 9.5 9.5 >> > >> > Wind trend 1 1 1 >> > 0 1 >> > >> > Wind direction 22.5 45 67.5 >> > 191.25 56.25 >> > >> > Humidity 40 44.5 22 >> > 24.5 7 >> > >> > Pressure 1024 1018.5 1025 >> > 1005.5 1015.5 >> > >> > Pressure trend 1 1 1 >> > 1 1 >> > >> > >> > >> > >> > long data >> > ----------------- >> > Buffer Date LST Temperature Wind >> > speed ...... >> > 1 01.01.21 4 5 10 >> > 2 01.02.21 5 2 11 >> > 3 01.03.21 7 5 15 >> > 4 01.04.21 9 6 7 >> > 5 01.05.21 7 5 10 >> > >> > ______________________________________________ >> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >> > https://stat.ethz.ch/mailman/listinfo/r-help >> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >> > and provide commented, minimal, self-contained, reproducible code. >> >> -- >> Chris Evans (he/him) <chris at psyctc.org> >> Visiting Professor, UDLA, Quito, Ecuador & Honorary Professor, University of Roehampton, London, UK. >> Work web site: https://www.psyctc.org/psyctc/ >> CORE site: https://www.coresystemtrust.org.uk/ >> Personal site: https://www.psyctc.org/pelerinage2016/ >> OMbook: https://ombook.psyctc.org/book/ > >______________________________________________ >R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >and provide commented, minimal, self-contained, reproducible code.-- Sent from my phone. Please excuse my brevity.
Apologies, all. No offence was intended. I'll go away and do a lot more reading and thinking then come back with a clearly articulated query if and when that is appropriate. I'm out of time this week, so it won't be for some time. Thank you all for the helpful replies. Best wishes, Philip On Mon, 29 Nov 2021 at 00:31, Jeff Newmiller <jdnewmil at dcn.davis.ca.us> wrote:> > This "data_long" appears to be a mess you are not satisfied with, rather than the "data" you started with or a model of what you want. > > On November 28, 2021 9:06:54 AM PST, Philip Monk <prmonk at gmail.com> wrote: > >Thanks for your suggestions, Chris. > > > >I'm writing from Gmail's web client, and have checked the message is > >being sent as plain text (though I also did this previously so it may > >be that I need to change to Outlook). Let me know if it doesn't work. > > > >Hopefully I've used dput properly (see example below with apologies to > >Burt and the PG). In my defence, I have very limited time due to > >caring responsibilities so am time-poor out of necessity rather than > >laziness. > > > >Reading the 'pivot_longer' documentation I think I need to use > >'names_pattern' to split the weather data into different columns, but > >I don't understand the required syntax. > > > >Thanks for your help, > > > >Philip > > > >rm(list=ls()) > >library(ggplot2) > >library(ggpubr) > >library(tidyverse) > >library(rstatix) > >library(ez) > >library(dplyr) > > > >data_long <- > > structure( > > list( > > Buffer = c( > > "100", > > "200", > > "300", > > "400", > > "500", > > "600", > > "700", > > "800", > > "900", > > "1000", > > "1100", > > "1200", > > "1300", > > "1400", > > "1500", > > "1600", > > "1700", > > "1800", > > "1900", > > "Temperature", > > "Wind speed", > > "Wind trend", > > "Wind direction", > > "Humidity", > > "Pressure", > > "Pressure trend" > > ), > > `15/01/2010` = c( > > 6.091741043, > > 5.271975614, > > 4.451891901, > > 3.385694303, > > 2.900508112, > > 3.110238149, > > 3.150580922, > > 3.079728958, > > 2.327902499, > > 1.641887823, > > 1.63370882, > > 0.986559368, > > 0.920601397, > > 0.571882394, > > 0.340505009, > > 0.813480877, > > 0.471988882, > > 0.269067515, > > 0.303179244, > > 12, > > 10, > > 1, > > 22.5, > > 40, > > 1024, > > 1 > > ), > > `16/02/2010` = c( > > 6.405879111, > > 5.994054977, > > 5.61142085, > > 4.77953426, > > 4.305900444, > > 3.616699448, > > 2.848148846, > > 2.016807672, > > 1.452876728, > > 2.120099832, > > 1.661317381, > > 1.133219897, > > 1.237239562, > > 0.93675648, > > 0.7379146, > > 1.026085605, > > 0.566766122, > > 0.13349775, > > 0.082892149, > > 15, > > 9, > > 1, > > 45, > > 44.5, > > 1018.5, > > 1 > > ), > > `20/03/2010` = c( > > 8.925945159, > > 7.375445078, > > 6.120095292, > > 5.608927408, > > 5.61367474, > > 4.800003992, > > 4.216782177, > > 4.05288041, > > 3.779922823, > > 4.267840277, > > 3.747342619, > > 2.414025636, > > 2.647100163, > > 2.272566024, > > 2.526476424, > > 2.643863876, > > 1.290173713, > > 0.612263766, > > 0.465457136, > > 16, > > 10.5, > > 1, > > 67.5, > > 22, > > 1025, > > 1 > > ), > > `24/04/2011` = c( > > 6.278147269, > > 5.805619599, > > 5.149985946, > > 4.542354226, > > 4.320657374, > > 4.006103489, > > 3.642003696, > > 3.315992643, > > 3.181741995, > > 3.321634055, > > 2.814670223, > > 2.180686348, > > 2.253223258, > > 2.07198929, > > 1.912840489, > > 1.825988411, > > 1.360936689, > > 0.666152106, > > 0.537232782, > > 23, > > 19.5, > > 0, > > 191.25, > > 24.5, > > 1005.5, > > 1 > > ) > > ), > > row.names = c(NA, 26L), > > class = "data.frame" > > ) > > > ># Converts data table from wide (many columns) to long (many rows) and > >creates the new object 'data_long' > ># Column 1 is the 'Buffer' number (100-2000), Columns 2-25 contain > >monthly data covering 2 years. > ># Column headers for columns 2:25 are mutated into a column called > >'Date', values for each buffer and each date into the column 'LST' > > > >data_long <- data %>% pivot_longer(cols = 2:25, names_pattern = names_to > > "Date", values_to = "LST") > > > ># Instructs R to treat the 'Date' column data as a date > >data_long$Date <- as.Date(data_long$Date, format = "%d/%m/%Y") > > > ># Creates a new column called 'Month' by extracting the month number > >from the date in the 'Date' column > >data_long <- mutate(data_long, Month = format(data_long$Date, "%m")) > > > ># Creates a new column called 'Year' by extracting the Year number > >(YYYY as %Y not %y) from the date in the 'Date' column > >data_long <- mutate(data_long, Year = format(data_long$Date, "%Y")) > > > ># Creates a new column called 'JulianDay' by calculating the Julian > >Day (day of the year from 1 January) from the date in the 'Date' > >column. > >data_long <- mutate(data_long, JulianDay = format(data_long$Date, "%j")) > > > ># Creates a new column called 'TimePeriod' where 1 = pre-construction, > >and 2 = post-construction of solar park. > ># Uses 'if_else' - If Year < 2015 value = 1, else 2. > >data_long <- mutate(data_long, TimePeriod = if_else(data_long$Year < > >2015, 1,2, missing = NULL)) > > > ># Instructs R to treat the 'TimePeriod' column as a (categorical) > >factor - it is either 1 (pre-construction, or 2 (post-construction) > >data_long$TimePeriod <- as.factor(data_long$TimePeriod) > > > ># Change data types of Month, Year nad JulianDay > >data_long$Month <- as.numeric(data_long$Month) > >data_long$Year <- as.numeric(data_long$Year) > >data_long$JulianDay <- as.numeric(data_long$JulianDay) > > > ># 'Compactly display the internal structure of an R object' > >str(data_long) > > > ># Adds 'data_long' to R search path so that R can access it when > >evaluating a variable (simplifies syntax). > >attach(data_long) > > > > > >On Sun, 28 Nov 2021 at 15:59, Chris Evans <chrishold at psyctc.org> wrote: > >> > >> Often the issue is that different variables in the wide format are of different types so won't simply > >> pivot_longer without you making decisions which the function shouldn't make for you. However, I think > >> the error messages when that happens are fairly clear so perhaps that's not what's happening here. > >> > >> I'm happy to have a look at this as I've slowly become a convert to using tidyverse principles and tools > >> (sometimes seen, legalistically correctly I think, as outside the remit of this Email list) but I agree > >> that the help for pivot_longer() and many other tidyverse functions is not as good as it could be particularly > >> for people new to R. Often there is better documentation in vignettes (so look for that) or in other things > >> on the web. > >> > >> However, for me the data that was posted are mangled by the post coming in HTML format. Please read the > >> list documentation and resubmit the question in raw text Email and submit a bit of your data using > >> dput() (see ?dput and search out "R reproducible examples") and then I'll look at it. > >> > >> Very best (all), > >> > >> Chris > >> > >> ----- Original Message ----- > >> > From: "Philip Monk" <prmonk at gmail.com> > >> > To: "R-help Mailing List" <r-help at r-project.org> > >> > Sent: Sunday, 28 November, 2021 13:57:07 > >> > Subject: [R] Syntax help for 'Pivot_longer' > >> > >> > Hello, > >> > > >> > I have a wide table that I transform to a long table for analysis. > >> > The wide table has 25 columns - the first is labels, then columns 2:25 > >> > are monthly data of LST which is in 19 rows. > >> > > >> > I mutate this with : > >> > > >> > data_long <- data %>% pivot_longer(cols = 2:25, names_to > >> > "Date", values_to = "LST") > >> > > >> > I've decided to add some weather data which might be relevant, > >> > inputting this as an additional 7 rows of data in the wide format (see > >> > example below of the first 5 months of data). > >> > > >> > I have belatedly realised that I cannot work out how to pivot this > >> > into the long format I need - the documentation doesn't provide enough > >> > syntax examples for me to work it out (I've not long been using 'R'). > >> > > >> > How do I mutate this to provide the additional columns in the long > >> > table for the weather variables? > >> > > >> > Thanks for your time, > >> > > >> > Philip > >> > > >> > Part-time PhD Student (Environmental Science) > >> > Lancaster University, UK. > >> > > >> > > >> > > >> > Wide data > >> > ------------------ > >> > > >> > Buffer 15/01/2010 16/02/2010 20/03/2010 > >> > 24/04/2011 07/05/2010 > >> > > >> > 100 6.091741043 6.405879111 8.925945159 > >> > 6.278147269 6.133940129 > >> > > >> > 200 5.271975614 5.994054977 7.375445078 > >> > 5.805619599 5.537759202 > >> > > >> > 300 4.451891901 5.61142085 6.120095292 > >> > 5.149985946 5.353001442 > >> > > >> > 400 3.385694303 4.77953426 5.608927408 > >> > 4.542354226 4.824773827 > >> > > >> > 500 2.900508112 4.305900444 5.61367474 > >> > 4.320657374 4.520022189 > >> > > >> > 600 3.110238149 3.616699448 4.800003992 > >> > 4.006103489 4.188421662 > >> > > >> > 700 3.150580922 2.848148846 4.216782177 > >> > 3.642003696 3.725611032 > >> > > >> > 800 3.079728958 2.016807672 4.05288041 > >> > 3.315992643 3.278124347 > >> > > >> > 900 2.327902499 1.452876728 3.779922823 > >> > 3.181741995 3.29577819 > >> > > >> > 1000 1.641887823 2.120099832 4.267840277 > >> > 3.321634055 3.551965361 > >> > > >> > 1100 1.63370882 1.661317381 3.747342619 > >> > 2.814670223 2.807355369 > >> > > >> > 1200 0.986559368 1.133219897 2.414025636 > >> > 2.180686348 2.166547946 > >> > > >> > 1300 0.920601397 1.237239562 2.647100163 > >> > 2.253223258 2.411947081 > >> > > >> > 1400 0.571882394 0.93675648 2.272566024 > >> > 2.07198929 1.954723088 > >> > > >> > 1500 0.340505009 0.7379146 2.526476424 > >> > 1.912840489 2.003872651 > >> > > >> > 1600 0.813480877 1.026085605 2.643863876 > >> > 1.825988411 2.278799668 > >> > > >> > 1700 0.471988882 0.566766122 1.290173713 > >> > 1.360936689 1.45967449 > >> > > >> > 1800 0.269067515 0.13349775 0.612263766 > >> > 0.666152106 0.680354177 > >> > > >> > 1900 0.303179244 0.082892149 0.465457136 > >> > 0.537232782 0.287185161 > >> > > >> > Temperautre 12 15 16 > >> > 23 21.5 > >> > > >> > Wind speed 10 9 10.5 > >> > 9.5 9.5 > >> > > >> > Wind trend 1 1 1 > >> > 0 1 > >> > > >> > Wind direction 22.5 45 67.5 > >> > 191.25 56.25 > >> > > >> > Humidity 40 44.5 22 > >> > 24.5 7 > >> > > >> > Pressure 1024 1018.5 1025 > >> > 1005.5 1015.5 > >> > > >> > Pressure trend 1 1 1 > >> > 1 1 > >> > > >> > > >> > > >> > > >> > long data > >> > ----------------- > >> > Buffer Date LST Temperature Wind > >> > speed ...... > >> > 1 01.01.21 4 5 10 > >> > 2 01.02.21 5 2 11 > >> > 3 01.03.21 7 5 15 > >> > 4 01.04.21 9 6 7 > >> > 5 01.05.21 7 5 10 > >> > > >> > ______________________________________________ > >> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > >> > https://stat.ethz.ch/mailman/listinfo/r-help > >> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > >> > and provide commented, minimal, self-contained, reproducible code. > >> > >> -- > >> Chris Evans (he/him) <chris at psyctc.org> > >> Visiting Professor, UDLA, Quito, Ecuador & Honorary Professor, University of Roehampton, London, UK. > >> Work web site: https://www.psyctc.org/psyctc/ > >> CORE site: https://www.coresystemtrust.org.uk/ > >> Personal site: https://www.psyctc.org/pelerinage2016/ > >> OMbook: https://ombook.psyctc.org/book/ > > > >______________________________________________ > >R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > >https://stat.ethz.ch/mailman/listinfo/r-help > >PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > >and provide commented, minimal, self-contained, reproducible code. > > -- > Sent from my phone. Please excuse my brevity.
Hi Phillip, Let's say your original data is in a data.frame called "mydata". What you want to do is issue de command dput(mydata) then just copy the output and paste it into the message. Here is a very simple example dat1 <- data.frame(xx = LETTERS[1:10], yy = 10:1) dput(dat1) ====================output to copy and paste========== structure(list(xx = c("A", "B", "C", "D", "E", "F", "G", "H", "I", "J"), yy = 10:1), class = "data.frame", row.names = c(NA, -10L)) ============================================ If you have a very large data set then something like head(dput(myfile), 100) will likely supply enough data for us to work with. On Mon, 29 Nov 2021 at 05:20, Philip Monk <prmonk at gmail.com> wrote:> > Apologies, all. No offence was intended. > > I'll go away and do a lot more reading and thinking then come back > with a clearly articulated query if and when that is appropriate. > I'm out of time this week, so it won't be for some time. > > Thank you all for the helpful replies. > > Best wishes, > > Philip > > On Mon, 29 Nov 2021 at 00:31, Jeff Newmiller <jdnewmil at dcn.davis.ca.us> wrote: > > > > This "data_long" appears to be a mess you are not satisfied with, rather than the "data" you started with or a model of what you want. > > > > On November 28, 2021 9:06:54 AM PST, Philip Monk <prmonk at gmail.com> wrote: > > >Thanks for your suggestions, Chris. > > > > > >I'm writing from Gmail's web client, and have checked the message is > > >being sent as plain text (though I also did this previously so it may > > >be that I need to change to Outlook). Let me know if it doesn't work. > > > > > >Hopefully I've used dput properly (see example below with apologies to > > >Burt and the PG). In my defence, I have very limited time due to > > >caring responsibilities so am time-poor out of necessity rather than > > >laziness. > > > > > >Reading the 'pivot_longer' documentation I think I need to use > > >'names_pattern' to split the weather data into different columns, but > > >I don't understand the required syntax. > > > > > >Thanks for your help, > > > > > >Philip > > > > > >rm(list=ls()) > > >library(ggplot2) > > >library(ggpubr) > > >library(tidyverse) > > >library(rstatix) > > >library(ez) > > >library(dplyr) > > > > > >data_long <- > > > structure( > > > list( > > > Buffer = c( > > > "100", > > > "200", > > > "300", > > > "400", > > > "500", > > > "600", > > > "700", > > > "800", > > > "900", > > > "1000", > > > "1100", > > > "1200", > > > "1300", > > > "1400", > > > "1500", > > > "1600", > > > "1700", > > > "1800", > > > "1900", > > > "Temperature", > > > "Wind speed", > > > "Wind trend", > > > "Wind direction", > > > "Humidity", > > > "Pressure", > > > "Pressure trend" > > > ), > > > `15/01/2010` = c( > > > 6.091741043, > > > 5.271975614, > > > 4.451891901, > > > 3.385694303, > > > 2.900508112, > > > 3.110238149, > > > 3.150580922, > > > 3.079728958, > > > 2.327902499, > > > 1.641887823, > > > 1.63370882, > > > 0.986559368, > > > 0.920601397, > > > 0.571882394, > > > 0.340505009, > > > 0.813480877, > > > 0.471988882, > > > 0.269067515, > > > 0.303179244, > > > 12, > > > 10, > > > 1, > > > 22.5, > > > 40, > > > 1024, > > > 1 > > > ), > > > `16/02/2010` = c( > > > 6.405879111, > > > 5.994054977, > > > 5.61142085, > > > 4.77953426, > > > 4.305900444, > > > 3.616699448, > > > 2.848148846, > > > 2.016807672, > > > 1.452876728, > > > 2.120099832, > > > 1.661317381, > > > 1.133219897, > > > 1.237239562, > > > 0.93675648, > > > 0.7379146, > > > 1.026085605, > > > 0.566766122, > > > 0.13349775, > > > 0.082892149, > > > 15, > > > 9, > > > 1, > > > 45, > > > 44.5, > > > 1018.5, > > > 1 > > > ), > > > `20/03/2010` = c( > > > 8.925945159, > > > 7.375445078, > > > 6.120095292, > > > 5.608927408, > > > 5.61367474, > > > 4.800003992, > > > 4.216782177, > > > 4.05288041, > > > 3.779922823, > > > 4.267840277, > > > 3.747342619, > > > 2.414025636, > > > 2.647100163, > > > 2.272566024, > > > 2.526476424, > > > 2.643863876, > > > 1.290173713, > > > 0.612263766, > > > 0.465457136, > > > 16, > > > 10.5, > > > 1, > > > 67.5, > > > 22, > > > 1025, > > > 1 > > > ), > > > `24/04/2011` = c( > > > 6.278147269, > > > 5.805619599, > > > 5.149985946, > > > 4.542354226, > > > 4.320657374, > > > 4.006103489, > > > 3.642003696, > > > 3.315992643, > > > 3.181741995, > > > 3.321634055, > > > 2.814670223, > > > 2.180686348, > > > 2.253223258, > > > 2.07198929, > > > 1.912840489, > > > 1.825988411, > > > 1.360936689, > > > 0.666152106, > > > 0.537232782, > > > 23, > > > 19.5, > > > 0, > > > 191.25, > > > 24.5, > > > 1005.5, > > > 1 > > > ) > > > ), > > > row.names = c(NA, 26L), > > > class = "data.frame" > > > ) > > > > > ># Converts data table from wide (many columns) to long (many rows) and > > >creates the new object 'data_long' > > ># Column 1 is the 'Buffer' number (100-2000), Columns 2-25 contain > > >monthly data covering 2 years. > > ># Column headers for columns 2:25 are mutated into a column called > > >'Date', values for each buffer and each date into the column 'LST' > > > > > >data_long <- data %>% pivot_longer(cols = 2:25, names_pattern = names_to > > > "Date", values_to = "LST") > > > > > ># Instructs R to treat the 'Date' column data as a date > > >data_long$Date <- as.Date(data_long$Date, format = "%d/%m/%Y") > > > > > ># Creates a new column called 'Month' by extracting the month number > > >from the date in the 'Date' column > > >data_long <- mutate(data_long, Month = format(data_long$Date, "%m")) > > > > > ># Creates a new column called 'Year' by extracting the Year number > > >(YYYY as %Y not %y) from the date in the 'Date' column > > >data_long <- mutate(data_long, Year = format(data_long$Date, "%Y")) > > > > > ># Creates a new column called 'JulianDay' by calculating the Julian > > >Day (day of the year from 1 January) from the date in the 'Date' > > >column. > > >data_long <- mutate(data_long, JulianDay = format(data_long$Date, "%j")) > > > > > ># Creates a new column called 'TimePeriod' where 1 = pre-construction, > > >and 2 = post-construction of solar park. > > ># Uses 'if_else' - If Year < 2015 value = 1, else 2. > > >data_long <- mutate(data_long, TimePeriod = if_else(data_long$Year < > > >2015, 1,2, missing = NULL)) > > > > > ># Instructs R to treat the 'TimePeriod' column as a (categorical) > > >factor - it is either 1 (pre-construction, or 2 (post-construction) > > >data_long$TimePeriod <- as.factor(data_long$TimePeriod) > > > > > ># Change data types of Month, Year nad JulianDay > > >data_long$Month <- as.numeric(data_long$Month) > > >data_long$Year <- as.numeric(data_long$Year) > > >data_long$JulianDay <- as.numeric(data_long$JulianDay) > > > > > ># 'Compactly display the internal structure of an R object' > > >str(data_long) > > > > > ># Adds 'data_long' to R search path so that R can access it when > > >evaluating a variable (simplifies syntax). > > >attach(data_long) > > > > > > > > >On Sun, 28 Nov 2021 at 15:59, Chris Evans <chrishold at psyctc.org> wrote: > > >> > > >> Often the issue is that different variables in the wide format are of different types so won't simply > > >> pivot_longer without you making decisions which the function shouldn't make for you. However, I think > > >> the error messages when that happens are fairly clear so perhaps that's not what's happening here. > > >> > > >> I'm happy to have a look at this as I've slowly become a convert to using tidyverse principles and tools > > >> (sometimes seen, legalistically correctly I think, as outside the remit of this Email list) but I agree > > >> that the help for pivot_longer() and many other tidyverse functions is not as good as it could be particularly > > >> for people new to R. Often there is better documentation in vignettes (so look for that) or in other things > > >> on the web. > > >> > > >> However, for me the data that was posted are mangled by the post coming in HTML format. Please read the > > >> list documentation and resubmit the question in raw text Email and submit a bit of your data using > > >> dput() (see ?dput and search out "R reproducible examples") and then I'll look at it. > > >> > > >> Very best (all), > > >> > > >> Chris > > >> > > >> ----- Original Message ----- > > >> > From: "Philip Monk" <prmonk at gmail.com> > > >> > To: "R-help Mailing List" <r-help at r-project.org> > > >> > Sent: Sunday, 28 November, 2021 13:57:07 > > >> > Subject: [R] Syntax help for 'Pivot_longer' > > >> > > >> > Hello, > > >> > > > >> > I have a wide table that I transform to a long table for analysis. > > >> > The wide table has 25 columns - the first is labels, then columns 2:25 > > >> > are monthly data of LST which is in 19 rows. > > >> > > > >> > I mutate this with : > > >> > > > >> > data_long <- data %>% pivot_longer(cols = 2:25, names_to > > >> > "Date", values_to = "LST") > > >> > > > >> > I've decided to add some weather data which might be relevant, > > >> > inputting this as an additional 7 rows of data in the wide format (see > > >> > example below of the first 5 months of data). > > >> > > > >> > I have belatedly realised that I cannot work out how to pivot this > > >> > into the long format I need - the documentation doesn't provide enough > > >> > syntax examples for me to work it out (I've not long been using 'R'). > > >> > > > >> > How do I mutate this to provide the additional columns in the long > > >> > table for the weather variables? > > >> > > > >> > Thanks for your time, > > >> > > > >> > Philip > > >> > > > >> > Part-time PhD Student (Environmental Science) > > >> > Lancaster University, UK. > > >> > > > >> > > > >> > > > >> > Wide data > > >> > ------------------ > > >> > > > >> > Buffer 15/01/2010 16/02/2010 20/03/2010 > > >> > 24/04/2011 07/05/2010 > > >> > > > >> > 100 6.091741043 6.405879111 8.925945159 > > >> > 6.278147269 6.133940129 > > >> > > > >> > 200 5.271975614 5.994054977 7.375445078 > > >> > 5.805619599 5.537759202 > > >> > > > >> > 300 4.451891901 5.61142085 6.120095292 > > >> > 5.149985946 5.353001442 > > >> > > > >> > 400 3.385694303 4.77953426 5.608927408 > > >> > 4.542354226 4.824773827 > > >> > > > >> > 500 2.900508112 4.305900444 5.61367474 > > >> > 4.320657374 4.520022189 > > >> > > > >> > 600 3.110238149 3.616699448 4.800003992 > > >> > 4.006103489 4.188421662 > > >> > > > >> > 700 3.150580922 2.848148846 4.216782177 > > >> > 3.642003696 3.725611032 > > >> > > > >> > 800 3.079728958 2.016807672 4.05288041 > > >> > 3.315992643 3.278124347 > > >> > > > >> > 900 2.327902499 1.452876728 3.779922823 > > >> > 3.181741995 3.29577819 > > >> > > > >> > 1000 1.641887823 2.120099832 4.267840277 > > >> > 3.321634055 3.551965361 > > >> > > > >> > 1100 1.63370882 1.661317381 3.747342619 > > >> > 2.814670223 2.807355369 > > >> > > > >> > 1200 0.986559368 1.133219897 2.414025636 > > >> > 2.180686348 2.166547946 > > >> > > > >> > 1300 0.920601397 1.237239562 2.647100163 > > >> > 2.253223258 2.411947081 > > >> > > > >> > 1400 0.571882394 0.93675648 2.272566024 > > >> > 2.07198929 1.954723088 > > >> > > > >> > 1500 0.340505009 0.7379146 2.526476424 > > >> > 1.912840489 2.003872651 > > >> > > > >> > 1600 0.813480877 1.026085605 2.643863876 > > >> > 1.825988411 2.278799668 > > >> > > > >> > 1700 0.471988882 0.566766122 1.290173713 > > >> > 1.360936689 1.45967449 > > >> > > > >> > 1800 0.269067515 0.13349775 0.612263766 > > >> > 0.666152106 0.680354177 > > >> > > > >> > 1900 0.303179244 0.082892149 0.465457136 > > >> > 0.537232782 0.287185161 > > >> > > > >> > Temperautre 12 15 16 > > >> > 23 21.5 > > >> > > > >> > Wind speed 10 9 10.5 > > >> > 9.5 9.5 > > >> > > > >> > Wind trend 1 1 1 > > >> > 0 1 > > >> > > > >> > Wind direction 22.5 45 67.5 > > >> > 191.25 56.25 > > >> > > > >> > Humidity 40 44.5 22 > > >> > 24.5 7 > > >> > > > >> > Pressure 1024 1018.5 1025 > > >> > 1005.5 1015.5 > > >> > > > >> > Pressure trend 1 1 1 > > >> > 1 1 > > >> > > > >> > > > >> > > > >> > > > >> > long data > > >> > ----------------- > > >> > Buffer Date LST Temperature Wind > > >> > speed ...... > > >> > 1 01.01.21 4 5 10 > > >> > 2 01.02.21 5 2 11 > > >> > 3 01.03.21 7 5 15 > > >> > 4 01.04.21 9 6 7 > > >> > 5 01.05.21 7 5 10 > > >> > > > >> > ______________________________________________ > > >> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > >> > https://stat.ethz.ch/mailman/listinfo/r-help > > >> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > > >> > and provide commented, minimal, self-contained, reproducible code. > > >> > > >> -- > > >> Chris Evans (he/him) <chris at psyctc.org> > > >> Visiting Professor, UDLA, Quito, Ecuador & Honorary Professor, University of Roehampton, London, UK. > > >> Work web site: https://www.psyctc.org/psyctc/ > > >> CORE site: https://www.coresystemtrust.org.uk/ > > >> Personal site: https://www.psyctc.org/pelerinage2016/ > > >> OMbook: https://ombook.psyctc.org/book/ > > > > > >______________________________________________ > > >R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > >https://stat.ethz.ch/mailman/listinfo/r-help > > >PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > > >and provide commented, minimal, self-contained, reproducible code. > > > > -- > > Sent from my phone. Please excuse my brevity. > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.-- John Kane Kingston ON Canada