CODEWITHSUNDEEP
r
Julian
Julian

Reputation: 53

Extracting specific values from column based on other column

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. :)

Upvotes: 1

Views: 62

Answers (2)

bouncyball
bouncyball

Reputation: 10761

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

Upvotes: 1

ThomasIsCoding
ThomasIsCoding

Reputation: 101014

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))

Upvotes: 0

Related Questions