[R] Syntax help for 'Pivot_longer'
John Kane
jrkr|de@u @end|ng |rom gm@||@com
Wed Dec 1 15:17:15 CET 2021
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 using 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 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.
>
> ______________________________________________
> 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.
--
John Kane
Kingston ON Canada
More information about the R-help
mailing list