# [R] Problem with adding a row in a data table

Jeff Newmiller jdnewmil at dcn.davis.ca.us
Sun Sep 4 22:56:05 CEST 2016

```My suggested approach:

dta <- structure(list(Prod_name = c("Banana", "Apple", "Orange",
"Yoghurt",
"Eggs", "Milk", "Day_num"), X1.1.2000 = c("1", "0", "4", "3",
"6", "2", "1"), X2.1.2000 = c("2", "4", "1", "5", "3", "0", "2"
), X3.1.2000 = c("1", "5", "2", "3", "0", "4", "3"), X4.1.2000 = c("2",
"4", "4", "1", "0", "0", "4"), X5.1.2000 = c("0", "0", "1", "0",
"2", "3", "5"), X6.1.2000 = c("1", "3", "2", "1", "4", "1", "6"
), X7.1.2000 = c("5", "4", "5", "2", "2", "1", "7")), .Names =
c("Prod_name",
"X1.1.2000", "X2.1.2000", "X3.1.2000", "X4.1.2000", "X5.1.2000",
"X6.1.2000", "X7.1.2000"), row.names = c(NA, 7L), class = "data.frame")

# The Day_num values ARE NOT data you will be aggregating and
# should not be in the data frame with meaningful values.
dta <- dta[ 1:6, ] # forget last garbage line
# assuming your data are intended to be numeric
for( i in 2:8 ) {
dta[[ i ]] <- as.numeric( dta[[ i ]] )
}
# you didn't say what computation you want to accomplish on the data
# assuming you want to add values up by product and part of week

# base R functions
# generally useful to set timezone when using POSIXt types
Sys.setenv( TZ="Etc/GMT" )
# gather data values from multiple columns into long form
# I find this function very confusing, but it does work if you
# don't like depending on contributed packages that are easier to
# understand
dtaLong <- reshape( dta
, idvar = "Prod_name"
, varying = 1+seq.int( length( dta ) - 1 )
, v.names = "value"
, timevar = "XDates"
, times = names( dta )[ 1+seq.int( length( dta ) - 1 ) ]
, direction = "long"
)
# extract Date values from column names
dtaLong\$Dates <- as.Date( dtaLong\$XDates, format="X%d.%m.%Y" )
dt_lt <- as.POSIXlt( dtaLong\$Dates )
# extract the weekday information from the POSIXlt
dtaLong\$wday <- dt_lt\$wday # Sunday==0
# identify rows corresponding to time of week
dtaLong\$WkPart <- ifelse( dtaLong\$wday %in% c( 0, 6 )
, "Weekend"
, "Weekday" )
# aggregate by sum the value grouping by Prod_name and WkPart
dtaAgg <- aggregate( dtaLong\$value
, dtaLong[ , c( "Prod_name", "WkPart" ), drop=FALSE ]
, FUN=sum
)

# or using dplyr/tidyr
library(dplyr)
library(tidyr)
library(lubridate)
# "pipe" data frames from one step to the next
dtaAgg2.a <- (   dta
# tidyr way of making long form data
%>% gather( XDates, value, -Prod_name )
)
# dtaAgg2.a is purely for studying what is happening
dtaAgg2.b <- (   dta
# tidyr way of making long form data
%>% gather( XDates, value, -Prod_name )
%>% mutate( Dates = as.Date( XDates, format="X%d.%m.%Y" )
, WkPart = ifelse( wday( Dates ) %in% c( 0, 6 )
, "WeekEnd"
, "WeekDay" )
)
)
# dtaAgg2.b is also for studying what happens
# finally, run the whole pipeline of calculations
dtaAgg2 <- (   dta
# tidyr way of making long form data
%>% gather( XDates, value, -Prod_name )
%>% mutate( Dates = as.Date( XDates, format="X%d.%m.%Y" )
, WkPart = ifelse( wday( Dates ) %in% c( 0, 6 )
, "WeekEnd"
, "WeekDay" )
)
%>% group_by( Prod_name, WkPart )
%>% summarise( SumOfValues = sum( value ) )
)
# the group_by and summarise steps work together

On Sun, 4 Sep 2016, Filippos Katsios wrote:

> Dear all,
> I believe that this will be a more helpful way to put the problem:
> structure(list(Prod_name = c("Banana", "Apple", "Orange", "Yoghurt",
> "Eggs", "Milk", "Day_num"), X1.1.2000 = c("1", "0", "4", "3",
> "6", "2", "1"), X2.1.2000 = c("2", "4", "1", "5", "3", "0", "2"
> ), X3.1.2000 = c("1", "5", "2", "3", "0", "4", "3"), X4.1.2000 = c("2",
> "4", "4", "1", "0", "0", "4"), X5.1.2000 = c("0", "0", "1", "0",
> "2", "3", "5"), X6.1.2000 = c("1", "3", "2", "1", "4", "1", "6"
> ), X7.1.2000 = c("5", "4", "5", "2", "2", "1", "7")), .Names = c("Prod_name",
> "X1.1.2000", "X2.1.2000", "X3.1.2000", "X4.1.2000", "X5.1.2000",
> "X6.1.2000", "X7.1.2000"), row.names = c(NA, 7L), class = "data.frame")
>
> and the code:
>
> Thank you for your help
> Filippos
>
> On 4 September 2016 at 19:30, Jeff Newmiller <jdnewmil at dcn.davis.ca.us> wrote:
>       Please use Reply-all to keep the mailing list in the loop. I cannot provide private assistance,
>       and others may provide valuable input or respond faster than I can.
>
>       It is very common that people cannot provide the original data. That means more work for YOU,
>       though,  not for us.  It is up to you to create a small simulated data set and process it as if
>       it were your original data.
>
>       Your idea will indeed be a good algorithm, but you will fail in R if you don't set it up
>       differently. Read [1] and provide us with a reproducible example data set and desired result and
>       someone here will be able to show you how to do it correctly.
>
>       --
>       Sent from my phone. Please excuse my brevity.
>
>       On September 4, 2016 8:28:39 AM PDT, Filippos Katsios <katsiosf at gmail.com> wrote:
>       >Dear Jeff,
>       >I am sorry but I am not allowed to share the original data. You are
>       >right
>       >about the Prod_name row. However, my goal is to split the columns
>       >"Date 1"
>       >etc into weekdays and weekends and manipulate them separately. I
>       >thought
>       >this would be the best way to do that (Assign to each day a number from
>       >1:7
>       >and then splitting them by a logical vector). Thank you for your help
>       >and
>       >
>       >Filippos
>       >
>       >On 4 September 2016 at 18:20, Jeff Newmiller <jdnewmil at dcn.davis.ca.us>
>       >wrote:
>       >
>       >> The "c" function creates vectors. Rows of data frames are data
>       >frames, not
>       >> vectors.
>       >>
>       >> new_row  <- data.frame( Prod_name = "Day_name",  `Date 1`=1, `Date
>       >> 2`=2,`Date 3`=3 )
>       >> data_may  <- rbind( new_row, data_may )
>       >>
>       >> Furthermore, data frames are NOT spreadsheets. "Day_num" looks
>       >> suspiciously UNlike a product name, which may mean the corresponding
>       >values
>       >> in that row are not Dates, which would also lead you into trouble.
>       >>
>       >making
>       >> your examples reproducible. Part of that is posting in plain text and
>       >using
>       >> the dput function to give us your sample data, because all too often
>       >the
>       >> problem lies in the details of how you have imported and manipulated
>       >your
>       >> data and the shortest way for us to see that the data are okay is to
>       >see it
>       >> as it exists in your R script so far.
>       >> --
>       >> Sent from my phone. Please excuse my brevity.
>       >>
>       >> On September 4, 2016 6:22:48 AM PDT, Filippos Katsios
>       ><katsiosf at gmail.com>
>       >> wrote:
>       >> >Dear All,
>       >> >
>       >> >I am relatively new to R and certainly new to the e-mailing list. I
>       >> >need
>       >> >your help. I am working on a data frame, which looks like this:
>       >> >
>       >> >Prod_name |  Date 1  |  Date 2 |  Date 3  |
>       >> >------------------|-------------|------------|--------------|
>       >> >Product 1    |     3      |      4     |     0       |
>       >> >------------------|-------------|------------|--------------|
>       >> >Product 2    |     5      |      3     |     3       |
>       >> >------------------|-------------|------------|--------------|
>       >> >Product 3    |     2      |      8     |     5       |
>       >> >
>       >> >I am trying to add a new row with the following results:
>       >> >
>       >> >Prod_name |  Date 1  |  Date 2 |  Date 3  |
>       >> >------------------|-------------|------------|--------------|
>       >> >Day_num    |     1      |      2     |      3      |
>       >> >------------------|-------------|------------|--------------|
>       >> >Product 1    |     3      |      4     |     0       |
>       >> >------------------|-------------|------------|--------------|
>       >> >Product 2    |     5      |      3     |     3       |
>       >> >------------------|-------------|------------|--------------|
>       >> >Product 3    |     2      |      8     |     5       |
>       >> >
>       >> >Bellow you can find the things I tried and the results.
>       >> >1)
>       >> >r <- 1
>       >> >newrow <- rep(1:7, 5, len=ncol(data_may)-1)
>       >> >insertRow <- function(data_may, newrow, r) {
>       >> >data_may[seq(r+1,nrow(data_may)+1),] <-
>       >> >data_may[seq(r,nrow(data_may)),]
>       >> >  data_may[r,] <- newrow
>       >> >  data_may
>       >> >}
>       >> >
>       >> >It doesn't put the new row.
>       >> >2)
>       >> >data_may<-rbind(data_may,c("Day_num",newrow))
>       >> >
>       >> >Error: cannot convert object to a data frame
>       >> >
>       >> >3)
>       >> >data_may[2093,]<-c("Day_num",rep(1:7, 5, len=ncol(data_may)-1))
>       >> >
>       >> >It makes all the columns characters and when i try to change it it
>       >says
>       >> >that you can change a list
>       >> >
>       >> >How can I add the row while keeping the columns (apart from the
>       >first
>       >> >one)
>       >> >as numeric or double or integer?
>       >> >
>       >> >
>       >> >Kind regards
>       >> >Filippos
>       >> >
>       >> >       [[alternative HTML version deleted]]
>       >> >
>       >> >______________________________________________
>       >> >R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>       >> >https://stat.ethz.ch/mailman/listinfo/r-help
>       >> >http://www.R-project.org/posting-guide.html
>       >> >and provide commented, minimal, self-contained, reproducible code.
>       >>
>       >>
>
>
>
>

---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...