[R] Syntax help for 'Pivot_longer'
Jeff Newmiller
jdnewm|| @end|ng |rom dcn@d@v|@@c@@u@
Sun Nov 28 23:55:28 CET 2021
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