[R] Syntax help for 'Pivot_longer'
Philip Monk
prmonk @end|ng |rom gm@||@com
Sun Nov 28 18:06:54 CET 2021
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 using 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 using gmail.com>
> > To: "R-help Mailing List" <r-help using 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 using 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 using 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/
More information about the R-help
mailing list