[R] Syntax help for 'Pivot_longer'
Philip Monk
prmonk @end|ng |rom gm@||@com
Mon Nov 29 11:19:29 CET 2021
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 using 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 using 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 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/
> >
> >______________________________________________
> >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.
>
> --
> Sent from my phone. Please excuse my brevity.
More information about the R-help
mailing list