Data wrangling in R - case

Question

I have this dataset and I am not able to treat the way I need it, I tried to use tidyverse without success. https://drive.google.com/file/d/1hQXIQJN5EAnTsv6Q6PeWGLqcQXLsWDBo/view?usp=share_link

I need to have the variables in the column and their values on the line like the example on the image (its just an example, I need all the variables)

Here is the head of the dataframe:

df <- structure(list(`system:index` = c(2020010100, 20200101000, 202001010000, 
2020010100000, 2.0200101e+13, 2.0200101e+14), date = structure(c(18262, 
18262, 18262, 18262, 18262, 18262), class = "Date"), variables = c("{temperature_2m_above_ground=23.895884392030794, specific_humidity_2m_above_ground=0.017470000311732292, relative_humidity_2m_above_ground=92.35649444184031, u_component_of_wind_10m_above_ground=-0.18645360776383815, v_component_of_wind_10m_above_ground=-1.2460013894282929, precipitable_water_entire_atmosphere=51.31695964387123}", 
"{temperature_2m_above_ground=23.83225027937787, specific_humidity_2m_above_ground=0.017439924955252276, relative_humidity_2m_above_ground=92.55575399332133, u_component_of_wind_10m_above_ground=-0.08373254963077834, v_component_of_wind_10m_above_ground=-1.035465734171007, total_precipitation_surface=0.0, precipitable_water_entire_atmosphere=52.016960378680764, total_cloud_cover_entire_atmosphere=100.0, downward_shortwave_radiation_flux=0.0}", 
"{temperature_2m_above_ground=23.782214160027753, specific_humidity_2m_above_ground=0.017370442922914904, relative_humidity_2m_above_ground=92.46239220257213, u_component_of_wind_10m_above_ground=-0.2246980803986558, v_component_of_wind_10m_above_ground=-0.590033268247212, total_precipitation_surface=0.06203911999166578, precipitable_water_entire_atmosphere=52.61696266749921, total_cloud_cover_entire_atmosphere=100.0, downward_shortwave_radiation_flux=0.0}", 
"{temperature_2m_above_ground=23.793045653364253, specific_humidity_2m_above_ground=0.01731066276911266, relative_humidity_2m_above_ground=92.0705021535876, u_component_of_wind_10m_above_ground=0.04523793126077347, v_component_of_wind_10m_above_ground=-0.8964589202090556, total_precipitation_surface=0.1861173599749976, precipitable_water_entire_atmosphere=53.11475044908516, total_cloud_cover_entire_atmosphere=100.0, downward_shortwave_radiation_flux=0.0}", 
"{temperature_2m_above_ground=23.757141644622575, specific_humidity_2m_above_ground=0.017170736365467565, relative_humidity_2m_above_ground=91.36903041182309, u_component_of_wind_10m_above_ground=0.23694871750069027, v_component_of_wind_10m_above_ground=-1.3036258255295403, total_precipitation_surface=0.4398044000416714, precipitable_water_entire_atmosphere=53.41253746773154, total_cloud_cover_entire_atmosphere=100.0, downward_shortwave_radiation_flux=0.0}", 
"{temperature_2m_above_ground=23.59914161191089, specific_humidity_2m_above_ground=0.01703926195904595, relative_humidity_2m_above_ground=91.55501659655967, u_component_of_wind_10m_above_ground=0.08872217801927586, v_component_of_wind_10m_above_ground=-1.3571136717243975, total_precipitation_surface=0.5161308002916981, precipitable_water_entire_atmosphere=53.91327486449315, total_cloud_cover_entire_atmosphere=100.0, downward_shortwave_radiation_flux=0.0}"
), .geo = c("{\"type\":\"MultiPoint\",\"coordinates\":[]}", "{\"type\":\"MultiPoint\",\"coordinates\":[]}", 
"{\"type\":\"MultiPoint\",\"coordinates\":[]}", "{\"type\":\"MultiPoint\",\"coordinates\":[]}", 
"{\"type\":\"MultiPoint\",\"coordinates\":[]}", "{\"type\":\"MultiPoint\",\"coordinates\":[]}"
)), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"
))

Answer 1

Here's a simple way of doing what you want:

df |> 
   select(date, variables) |>
   mutate(variables = str_split(string = substr(variables, 2, nchar(variables)-1), pattern = ", ")) |>
   unnest_longer(variables) |>
   separate_wider_delim(variables, delim = "=", names= c("name", "value")) |>
   pivot_wider(values_fn = first)

# Output:
# A tibble: 1,095 × 10
   date       temperature_2m_above_ground specific_humidity_2m_above_ground
   <date>     <chr>                       <chr>                            
 1 2020-01-01 23.895884392030794          0.017470000311732292             
 2 2020-01-02 24.0066277207827            0.0162591150494058               
 3 2020-01-03 24.344726868797647          0.01754970409279526              
 4 2020-01-04 23.703274935399428          0.016650367434708633             
 5 2020-01-05 23.78863850061258           0.01591376038759363              
 6 2020-01-06 23.41825090527936           0.016000883793668073             
 7 2020-01-07 23.757992796821384          0.01612111640373382              
 8 2020-01-08 23.58771029777161           0.015456099852257101             
 9 2020-01-09 23.048014431718414          0.017091164323546536             
10 2020-01-10 23.33719205468589           0.017121990974593382             
# ℹ 1,085 more rows
# ℹ 7 more variables: relative_humidity_2m_above_ground <chr>,
#   u_component_of_wind_10m_above_ground <chr>,
#   v_component_of_wind_10m_above_ground <chr>,
#   precipitable_water_entire_atmosphere <chr>,
#   total_precipitation_surface <chr>,
#   total_cloud_cover_entire_atmosphere <chr>, …

Note: in your screenshot, you are only selecting one value per date per column. You haven't specified how that value is selected. I just chose the first value. You might want to do something else - if you do, just replace first with whatever you would like to do e.g. if it's getting the sum, replace it with values_fn = sum, if it's the mean values_fn = mean etc.