0

I have tried solutions from multiple posts on stackoverflow, but haven't had any success. If you think this is a duplicate, it's not. I have already referred to this post and this post.

enter image description here

> head(t)
  locality_id observer_id observation_date total_duration total_dist total_area
1    L1366429  obsr529319       2017-01-12            100      1.609          0
2    L1366429   obs613305       2017-10-09            120      2.000          0
3    L1366429  obsr644174       2017-11-01             90      1.000          0
4    L1619491   obs729967       2017-03-27            240      2.000          0
5    L1845345   obs360918       2017-09-30            120      5.000          0
6    L1845345   obs575633       2017-10-27             45      0.300          0
                        localities            species observation loc_type prot_type num_observers
1         Eravikulam National Park Ficedula nigrorufa           1        H Traveling             7
2         Eravikulam National Park Ficedula nigrorufa           0        H Traveling             1
3         Eravikulam National Park Ficedula nigrorufa           1        H Traveling             2
4                           Munnar Ficedula nigrorufa           0        H Traveling             8
5 Nelliampathy Hills--General Area Ficedula nigrorufa           0        H Traveling             1
6 Nelliampathy Hills--General Area Ficedula nigrorufa           0        H Traveling             3
  Abundance
1         1
2         0
3         1
4         0
5         0
6         0

I need to transpose the values in the observation_date column as multiple columns and add the values of the total_duration in the same. All solution I have tried end up in me getting 0s or 1s.

Solutions Tried from Post 1

library(data.table)
tcast <- dcast(t, locality_id + localities + species +observer_id 
                  ~ observation_date, value.var="total_duration")
Aggregate function missing, defaulting to 'length'

I end up getting a dataframe with 0s and 1s.

Solutions tried from Post 2

> colnames(t)
 [1] "locality_id"      "observer_id"      "observation_date" "total_duration"   "total_dist"      
 [6] "total_area"       "localities"       "species"          "observation"      "loc_type"        
[11] "prot_type"        "num_observers"    "Abundance"

s <- c("locality_id","observation_date","total_duration")

>     any(duplicated(t[s]))
[1] TRUE

>     library(splitstackshape)
>     melteddata <- melt(getanID(t, s), c(".id", s))
'measure.vars' [observer_id, total_dist, total_area, localities, ...] are not all of the same type. By order of hierarchy, the molten data value column will be of type 'character'. All measure variables not of type 'character' will be coerced too. Check DETAILS in ?melt.data.table for more on coercion.

>     fin <- dcast(melteddata, .id + observation_date ~ total_duration)
Aggregate function missing, defaulting to 'length'

Apart from the above error, the second solution is giving me a weird table.

Please help! Been struggling with this for the last few days!

Vijay Ramesh
  • 191
  • 1
  • 2
  • 20
  • 1
    Please share the output of `dput(head(data, 10))` and also show expected output. – markus Dec 08 '18 at 14:30
  • @markus The output is neverending. Since the overall size of data is 4339 obs for 13 variables. – Vijay Ramesh Dec 08 '18 at 15:03
  • It looks like you are only using 5 variables. Maybe you could at least select only these to show in your `head` command? – jsta Dec 08 '18 at 16:16
  • Hi, @Vijay Ramesh could you solve this? I am struggling with a similar problem – MyName Jul 06 '20 at 22:11
  • Can't you just pivot the columns into wide format, and then select for those values? i.e., ```data %>% pivot_wider(names_from = observation_date, values_from = total_duration, values_fill = list(total_duration = 0))``` – Lime Jul 08 '21 at 20:30

0 Answers0