Extracting specific values from column based on other column

Question

I have a data frame with the following structure:

d1 <- data.frame(
  stringsAsFactors = FALSE,
             Stock = c("Stock 1","Stock 1","Stock 1",
                       "Stock 2","Stock 2","Stock 2","Stock 3","Stock 3",
                       "Stock 3"),
          Column.1 = c("...","...","...","...",
                       "...","...","...","...","..."),
          Column.2 = c("...","...","...","...",
                       "...","...","...","...","..."),
             Value = c(1, 1, 1, 0.5, 0.5, 0.5, 0.2, 0.2, 0.2)
)

Stock	Column 1	Column 2	Value
Stock 1	...	...	1
Stock 1	...	...	1
Stock 1	...	...	1
Stock 2	...	...	0.5
Stock 2	...	...	0.5
Stock 2	...	...	0.5
Stock 3	...	...	0.2
Stock 3	...	...	0.2
Stock 3	...	...	0.2

But with 2000 stocks. Basically each stock has the same value in the given column. I would like to extract this value for each stock and put it in another data frame which is a list of stocks with different variables that looks like this:

d2 <- data.frame(
  stringsAsFactors = FALSE,
             Stock = c("Stock 1", "Stock 2", "Stock 3", "Stock 4", "Stock 5"),
        Variable.1 = c("y", "y", "y", "y", "y"),
        Variable.2 = c("x", "x", "x", "x", "x"),
             Value = c("1", "0.5", "0.2", "...", "...")
)

Stock	Variable 1	Variable 2	Value
Stock 1	y	x	1
Stock 2	y	x	0.5
Stock 3	y	x	0.2
Stock 4	y	x	...
Stock 5	y	x	...

How do I do this? :)

Edit: There are other columns than the one I want to extract the data from. :)

bouncyball · Accepted Answer · 2021-03-18 14:55:06Z

1

Suppose the first data set you posted is called d1, and the second you posted is called d2. The unique function will remove duplicate elements from your first data.frame.

merge(d2, unique(d1[,c("Stock", "Value")]))

    Stock Variable1 Variable2 Value
1 Stock 1         y         x   1.0
2 Stock 2         y         x   0.5
3 Stock 3         y         x   0.2

If you want to keep all rows from d2, even if there is not a match in d1, you can specify all.x = TRUE in the call to `merge.

merge(d2, unique(d1[,c("Stock", "Value")]), all.x = TRUE)

    Stock Variable1 Variable2 Value
1 Stock 1         y         x   1.0
2 Stock 2         y         x   0.5
3 Stock 3         y         x   0.2
4 Stock 4         y         x    NA
5 Stock 5         y         x    NA

edited Mar 18, 2021 at 14:55

answered Mar 18, 2021 at 14:31

bouncyball

10.8k23 silver badges32 bronze badges

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

3 Comments

Julian Over a year ago

Thanks! I tried this but it didn't work. I have more columns in the first data frame, could that be a problem? Maybe I should have shown this in the question more clearly. I will update it to show how it looks.

bouncyball Over a year ago

If I understand you, you just want unique to work on the Stock and Value columns? If so, my edit should do that

Julian Over a year ago

I must have done something wrong before but now it worked! Thank you! :D

ThomasIsCoding · Accepted Answer · 2021-03-18 14:31:15Z

0

Try merge like below

> merge(df2, unique(df1), all.x = TRUE)
   Stock Variable1 Variable2 Value
1 Stock1         y         x   1.0
2 Stock2         y         x   0.5
3 Stock3         y         x   0.2
4 Stock4         y         x    NA
5 Stock5         y         x    NA

Data

> dput(df1)
structure(list(Stock = c("Stock1", "Stock1", "Stock1", "Stock2",
"Stock2", "Stock2", "Stock3", "Stock3", "Stock3"), Value = c(1,
1, 1, 0.5, 0.5, 0.5, 0.2, 0.2, 0.2)), class = "data.frame", row.names = c(NA,
-9L))

> dput(df2)
structure(list(Stock = c("Stock1", "Stock2", "Stock3", "Stock4",
"Stock5"), Variable1 = c("y", "y", "y", "y", "y"), Variable2 = c("x",
"x", "x", "x", "x")), class = "data.frame", row.names = c(NA,
-5L))

answered Mar 18, 2021 at 14:31

ThomasIsCoding

106k9 gold badges38 silver badges110 bronze badges

Collectives™ on Stack Overflow

Extracting specific values from column based on other column

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related