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/
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.