0

I have a couple of files that I am trying to convert to a wide format. However, I got it to work but I then realized that the way the data is displayed when was a little confusing for what I needed to use it for. For example, all of the data under the variable I have labeled WTMP was labeled "WYLD.January" for every year but I originally did not include a year indicator in the label, so for every year the label is just "WYLD.January". I changed the name of the months in the "months" column to reflect the year also - so instead of just "January", I changed it to "January_08, January_09...January_17, January_18".

But when I changed the months to reflect the dates, the data wouldn't no longer showed up in wide format in R and I'm not sure why because this was the only change I made

This is my current code. I am very new to R so I'm just not experienced enough to know what I need to change to fix this:

require(here)
require(data.table)
require(readxl)

baseline_upper_total = read_xlsx(
  here(
    "CT_total_upper.xlsx"))

baseline_upper_total


head(baseline_upper_total)
names(baseline_upper_total)

baseline_upper_total = data.table(baseline_upper_total)
names(baseline_upper_total)

baseline_upper_wide = dcast(
  setDT(baseline_upper_total),
  name ~ months,
  value.var = c(
    "PRECIP", "ET", "SURQ", "WYLD",
    "FLOW_OUT", "SED_OUT", "DISOX_OUT", "TOT_N", "TOT_P", "SYLD"), fun.aggregate = mean)

I included a reproducible example someone recommended I include in a previous question I had, my data in total has 133 rows and 12 columns:

#  name    months      PRECIP  ET   SURQ   WYLD  FLOW_OUT 
#1 1080101 January_08  13.0   2.08   6.55   17.4   1712     
#2 1080101 February_08 38.2   2.85   3.78    9.07   689     
#3 1080101 March_08    27.5   5.69  13.5    28.3   1781   
#4 1080101 April_08    22.6  10.2    8.57   29.1   3102     
#5 1080101 May_08       6.53 13.5   0.0245   7.68   914     
#6 1080101 June_08     35.4  23.2    1.21    9.83   816    
#7 1080101 July_08     41.5  27.4    2.81   13.2   1056    
#8 1080101 August_08   32.2  25.3    1.39   11.3   1269      
#9 1080101 Sept_08     19.9  17.2   0.782    7.56   768   
#10 1080101 October_08 27.2  10.4    1.73    8.82   730

This is a snippit of the data after it was transformed into the wide format. As you can see, none of the values are there, which is definitely not supposed to be like that.

enter image description here

I also tried using this method and the result was the same:

baseline_upper_wide = reshape(baseline_upper_total, idvar = "name", timevar = "months", direction = "wide")
H_0814
  • 1
  • 1
  • Please share your data in the plain text format not *.png – Farkhod Abdukodirov May 23 '23 at 00:09
  • 1
    If the data you posted is `dt`, then the following works. `dcast(dt,name~month, value.var=names(dt)[3:12],fun.aggregate=mean)`. If the data you posted is not the data that you actually have, then you might consider expanding your example dataset. – langtang May 23 '23 at 00:22
  • Side note: you should almost always use `library`, not `require`. The latter never stops following code when the package is not available, which is almost never what is intended. Refs: https://stackoverflow.com/a/51263513/3358272, https://yihui.org/en/2014/07/library-vs-require/, https://r-pkgs.org/namespace.html#search-path – r2evans May 23 '23 at 01:26
  • 1
    I ran your code on the sample data here and it works, though to be candid I cannot see the appeal or utility in expanding in a way that results in the following columns: `c("name", "PRECIP_April_08", "PRECIP_February_08", "PRECIP_January_08", "PRECIP_June_08", "PRECIP_March_08", "PRECIP_May_08", "ET_April_08", "ET_February_08", "ET_January_08", "ET_June_08", "ET_March_08", "ET_May_08", "SURQ_April_08", "SURQ_February_08", "SURQ_January_08", "SURQ_June_08", "SURQ_March_08", "SURQ_May_08", "WYLD_April_08", "WYLD_February_08", ..., "SYLD_March_08", "SYLD_May_08")` (61 columns here). – r2evans May 23 '23 at 01:31
  • 1
    @r2evans the only reason I want the data this way is to run through GIS as each needs to be converted to a raster file (big task) but the data needs to be this way to do that – H_0814 May 23 '23 at 02:14
  • @langtang thanks for the help! I tried running this but it didn't work, I still got the same result shown in the picture – H_0814 May 23 '23 at 02:18

0 Answers0