How to split a CHR column, pivot, then combine tables?

0

Issue

So I have two tables:

  1. LST data (24 months in total) (already pivoted_longer)
   Buffer Date       LST        
   <chr>  <chr>      <chr>      
 1 100    15/01/2010 6.091741043
 2 100    16/02/2010 6.405879111
 3 100    20/03/2010 8.925945159
 4 100    24/04/2011 6.278147269
 5 100    07/05/2010 6.133940129
 6 100    08/06/2010 7.705591939
 7 100    13/07/2011 4.066052173
 8 100    11/08/2010 5.962087092
 9 100    12/09/2010 5.761892842
10 100    17/10/2011 3.155769317
# ... with 1,550 more rows
  1. Weather data (24 months in total)
Weather variable 15/01/2010 16/02/2010 20/03/2010 24/04/2011 07/05/2010
1      Temperature       12.0       15.0       16.0      23.00      21.50
2       Wind_speed       10.0        9.0       10.5      19.50       9.50
3       Wind_trend        1.0        1.0        1.0       0.00       1.00
4   Wind_direction       22.5       45.0       67.5     191.25      56.25
5         Humidity       40.0       44.5       22.0      24.50       7.00
6         Pressure     1024.0     1018.5     1025.0    1005.50    1015.50
7   Pressure_trend        1.0        1.0        1.0       1.00       1.00

If I pivot the weather data I get:

 1 Temperature        15/01/2010  12  
 2 Temperature        16/02/2010  15  
 3 Temperature        20/03/2010  16  
 4 Temperature        24/04/2011  23  
 5 Temperature        07/05/2010  21.5
 6 Temperature        08/06/2010  36.5
 7 Temperature        13/07/2011  33  
 8 Temperature        11/08/2010  34.5
 9 Temperature        12/09/2010  33  
10 Temperature        17/10/2011  27 
# ... with 158 more rows

(each weather variable listed in turn).

I need to combine 1) and 3) – using the date and something like data_long <- merge(LST_data,weather_data,by="Date") I think – appending weather data columns to each row in 1).

But I’m stuck.

Solution

The solution I found to this was to pivot the weather data (longer):

weather_long <- weather %>% pivot_longer(cols = 2:21, names_to = "Date", values_to = "Value")

which gives a tibble in the format:

# A tibble: 180 x 3
   `Weather variable` Date       Value
   <chr>              <chr>      <dbl>
 1 Temperature        28/10/2016    17
 2 Temperature        31/12/2016    22
 3 Temperature        16/01/2017    25
 4 Temperature        05/03/2017    19

(as described above in the question).

Because this process changes the ‘Date’ variable type:

tibble [180 x 3] (S3: tbl_df/tbl/data.frame)
 $ Weather variable: chr [1:180] "Temperature" "Temperature" "Temperature" "Temperature" ...
 $ Date            : chr [1:180] "28/10/2016" "31/12/2016" "16/01/2017" "05/03/2017" ...
 $ Value           : num [1:180] 17 22 25 19 20 22 11 10 3 9 ...

I then corrected this:

weather_long$Date <- as.Date(weather_long$Date, format = "%d/%m/%Y")

Next was to convert the weather data to the ‘wide’ format (in preparation for the next step):

weather_wide <- weather_long %>%
  pivot_wider(names_from = "Weather variable", values_from = "Value")

Then join it to the LST data using the Date column as the key:

LST_Weather_dataset <- full_join(data_long, weather_wide, by = "Date") 

This produced the desired result:

str(LST_Weather_dataset)
'data.frame':   380 obs. of  16 variables:
 $ Buffer        : int  100 200 300 400 500 600 700 800 900 1000 ...
 $ Date          : Date, format: "2016-10-28" "2016-10-28" "2016-10-28" "2016-10-28" ...
 $ LST           : num  0.918 0.951 0.791 0.748 0.687 ...
 $ Month         : num  10 10 10 10 10 10 10 10 10 10 ...
 $ Year          : num  2016 2016 2016 2016 2016 ...
 $ JulianDay     : num  302 302 302 302 302 302 302 302 302 302 ...
 $ TimePeriod    : Factor w/ 2 levels "1","2": 1 1 1 1 1 1 1 1 1 1 ...
 $ Temperature   : num  17 17 17 17 17 17 17 17 17 17 ...
 $ Humidity      : num  59 59 59 59 59 59 59 59 59 59 ...
 $ Humidity_trend: num  1 1 1 1 1 1 1 1 1 1 ...
 $ Wind_speed    : num  19 19 19 19 19 19 19 19 19 19 ...
 $ Wind_gust     : num  0 0 0 0 0 0 0 0 0 0 ...
 $ Wind_trend    : num  2 2 2 2 2 2 2 2 2 2 ...
 $ Wind_direction: num  338 338 338 338 338 ...
 $ Pressure      : num  1017 1017 1017 1017 1017 ...
 $ Pressure_trend: num  2 2 2 2 2 2 2 2 2 2 ...

Answered By – strangecharm

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More