Creating multiple dataframes simultaneously based on different values in one column

Question

I want to create multiple dataframes based on values in a column.

sample data

df
Index Product ID Amount
200   Prod1   01 100
201   Prod1   01 150
202   Prod1   01 123
203   Prod1   01 123
204   Prod1   02 110
205   Prod1   02 175
206   Prod1   02 190
207   Prod2   03 120
208   Prod2   03 135
209   Prod2   03 150

I would like to add a column as Base for each ID. The value of Base is the first amount value in each ID.

>df1
Index Product ID Amount Base
200   Prod1   01 100    100
201   Prod1   01 150    100
202   Prod1   01 123    100
203   Prod1   01 123    100
204   Prod1   02 110    110
205   Prod1   02 175    110
206   Prod1   02 190    110
207   Prod2   03 120    120
208   Prod2   03 135    120
209   Prod2   03 150    120

I am thinking of subset the df by ID first. Just wondering if any way to do this?

If your df is sorted: do.call(rbind.data.frame, lapply(split(df, df$ID), function(sset) within(sset, Base <- Amount[1]))) — lukeA
– lukeA, Commented Jun 1, 2014 at 18:00
Do you need the first Amount value that appears or the minimum Amount value to be put in Base column? — talat
– talat, Commented Jun 1, 2014 at 18:10

David Arenburg · Accepted Answer · 2014-06-13 10:07:07Z

7

You could create a list of data frames and then embed them into any environment you want using list2env

SubData <- lapply(unique(df$ID), function(x) cbind(df[df$ID == x, ], Base = df$Amount[df$ID == x][1]))


# [[1]]
#   Index Product ID Amount Base
# 1   200   Prod1  1    100  100
# 2   201   Prod1  1    150  100
# 3   202   Prod1  1    123  100
# 4   203   Prod1  1    123  100
# 
# [[2]]
#   Index Product ID Amount Base
# 5   204   Prod1  2    110  110
# 6   205   Prod1  2    175  110
# 7   206   Prod1  2    190  110
# 
# [[3]]
#    Index Product ID Amount Base
# 8    207   Prod2  3    120  120
# 9    208   Prod2  3    135  120
# 10   209   Prod2  3    150  120

Now give your data frames whatever names you want and use list2env in order to create them in the environment

names(SubData) <- c("df1", "df2", "df3")
list2env(SubData, envir = .GlobalEnv)

Now you have these datasets in the global enviroment, e.g.

df1
##   Index Product ID Amount Base
## 1   200   Prod1  1    100  100
## 2   201   Prod1  1    150  100
## 3   202   Prod1  1    123  100
## 4   203   Prod1  1    123  100

edited Jun 13, 2014 at 10:07

answered Jun 1, 2014 at 18:21

David Arenburg

92.4k18 gold badges145 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David Arenburg Over a year ago

user3689870, see my edit re list2env, I think this would be the proper way to do this

David Arenburg Over a year ago

user3689870, didn't you want multiple data frames?

agstudy · Accepted Answer · 2014-06-01 18:58:18Z

4

Using ave:

dat$Base <- ave(dat$Amount,dat$ID,FUN=min)

# Index Product ID Amount Base
# 1    200   Prod1  1    100  100
# 2    201   Prod1  1    150  100
# 3    202   Prod1  1    123  100
# 4    203   Prod1  1    123  100
# 5    204   Prod1  2    110  110
# 6    205   Prod1  2    175  110
# 7    206   Prod1  2    190  110
# 8    207   Prod2  3    120  120
# 9    208   Prod2  3    135  120
# 10   209   Prod2  3    150  120

EDIT

In case you want the first value and the minium one:

dat$Base <- ave(dat$Amount,dat$ID,FUN=function(x)x[1])

edited Jun 1, 2014 at 18:58

answered Jun 1, 2014 at 18:08

agstudy

122k18 gold badges205 silver badges265 bronze badges

2 Comments

talat Over a year ago

I didn't downvote any of you, also wondered why (thought perhaps bc in @agstudy's answer you are using min and not the first value of Amount)?

agstudy Over a year ago

@beginneR Maybe..I edit my answer to add first value.

Arun · Accepted Answer · 2014-06-01 18:49:42Z

3

Assuming your data.frame is called dat, here's a data.table solution:

require(data.table)
setDT(dat)[, Base := Amount[1L], by=ID]
#    Index Product ID Amount Base
#  1:   200   Prod1  1    100  100
#  2:   201   Prod1  1    150  100
#  3:   202   Prod1  1    123  100
#  4:   203   Prod1  1    123  100
#  5:   204   Prod1  2    110  110
#  6:   205   Prod1  2    175  110
#  7:   206   Prod1  2    190  110
#  8:   207   Prod2  3    120  120
#  9:   208   Prod2  3    135  120
# 10:   209   Prod2  3    150  120

answered Jun 1, 2014 at 18:49

Arun

119k28 gold badges290 silver badges396 bronze badges

Comments

talat · Accepted Answer · 2014-06-01 18:05:03Z

2

You could use dplyr to create the Base column, but just to be clear, this does not yet create different data.frames (as indicated in your question).

require(dplyr)

df <- df %.% group_by(ID) %.% mutate(Base = first(Amount))

answered Jun 1, 2014 at 18:05

talat

70.5k22 gold badges130 silver badges158 bronze badges

Comments

lukeA · Accepted Answer · 2014-06-01 18:05:07Z

1

Or using dplyr:

library(dplyr)
df1 <- df %>% 
  arrange(ID, Amount) %>%
  group_by(ID) %>% 
  mutate(Base = Amount[1])

answered Jun 1, 2014 at 18:05

lukeA

54.4k5 gold badges102 silver badges101 bronze badges

4 Comments

talat Over a year ago

Nice one! I'm not sure if the sorting/arranging is necessary, since in the question the OP says "The value of Base is the first amount value in each ID", not the minimum value.

lukeA Over a year ago

@beginneR Oops, you are right. Well I'll leave it anyway to show how easy sorting is with dplyr. :)

Roman Luštrik Over a year ago

Can't find where %>% is documented. Any tips?

lukeA Over a year ago

%>% is documented here (and originally here )

Collectives™ on Stack Overflow

Creating multiple dataframes simultaneously based on different values in one column

sample data

5 Answers 5

2 Comments

EDIT

2 Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

sample data

5 Answers 5

2 Comments

EDIT

2 Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related