CODEWITHSUNDEEP
r
Manrique
Manrique

Reputation: 2221

R reshape and put the values in an array column

The problem is simple to understand, but I can't do it. I've tried reshape and tidiverse without success. This the dataframe I have:

       X        X.1 close  high   low  open ticker     vol
   A3TV 2005-07-08 10.67 10.67 10.46 10.57   A3TV 2248201
   A3TV 2005-07-11 10.53 10.73 10.50 10.67   A3TV 1372371
   GOOG 2005-07-08 10.47 10.59 10.37 10.59   GOOG 1135093
   GOOG 2005-07-11 10.41 10.59 10.36 10.48   GOOG 722398

And I am trying to get this result:

       X.1    A3TV                                        GOOG
   2005-07-08 [10.67, 10.67, 10.46, 10.57, A3TV, 2248201]      [10.47, 10.59, 10.37, 10.59, GOOG, 1135093]
   2005-07-11 [10.53, 10.73, 10.50, 10.67, A3TV, 1372371]      [10.41, 10.59, 10.36, 10.48, GOOG, 722398]

So, what I want is to group by the X.1 (date), and make a column which contains every other data for every X (ticker) I have.

Upvotes: 0

Views: 90

Answers (2)

denis
denis

Reputation: 5673

I have a solution using dcast from data.table

library(data.table)
setDT(df)

catcol = function(x){paste0(x,collapse = " ")}
df[,valcol := apply(df[,-c("X","X.1")],1,catcol)]
dcast(df,X.1~X,value.var = "valcol")

          X.1                                 A3TV                                 GOOG
1: 2005-07-08 10.67 10.67 10.46 10.57 A3TV 2248201 10.47 10.59 10.37 10.59 GOOG 1135093
2: 2005-07-11 10.53 10.73 10.50 10.67 A3TV 1372371 10.41 10.59 10.36 10.48 GOOG  722398

The idea is to create the column your are going to use to fill when you will pass from long to wide:

> apply(df[,-c("X","X.1")],1,catcol)
[1] "10.67 10.67 10.46 10.57 A3TV 2248201" "10.53 10.73 10.50 10.67 A3TV 1372371"
[3] "10.47 10.59 10.37 10.59 GOOG 1135093" "10.41 10.59 10.36 10.48 GOOG  722398"

the data :

df <- read.table(text = "X        X.1 close  high   low  open ticker     vol
A3TV 2005-07-08 10.67 10.67 10.46 10.57   A3TV 2248201
A3TV 2005-07-11 10.53 10.73 10.50 10.67   A3TV 1372371
GOOG 2005-07-08 10.47 10.59 10.37 10.59   GOOG 1135093
GOOG 2005-07-11 10.41 10.59 10.36 10.48   GOOG 722398",header = T)

Upvotes: 1

Lief Esbenshade
Lief Esbenshade

Reputation: 833

Here is a tidyverse solution.

First we combine the desired variables into a single column, then we widen the data. 

# data read taken from @denis answer
df <- read.table(text = "X        X.1 close  high   low  open ticker     vol
A3TV 2005-07-08 10.67 10.67 10.46 10.57   A3TV 2248201
A3TV 2005-07-11 10.53 10.73 10.50 10.67   A3TV 1372371
GOOG 2005-07-08 10.47 10.59 10.37 10.59   GOOG 1135093
GOOG 2005-07-11 10.41 10.59 10.36 10.48   GOOG 722398",header = T)

library(tidyverse)

df2 <- df %>% 
  mutate(data = paste(close, high, low, open, ticker, vol, sep = " ")) %>% 
  select(X, X.1, data) %>% 
  pivot_wider(names_from = X, values_from = data)

Upvotes: 1

Related Questions