CODEWITHSUNDEEP
rspss
blacksheep
blacksheep

Reputation: 31

How to manipulate or transpose a dataset for use in R or SPSS

I need to manipulate the raw data (csv) to a wide format so that I can analyze in R or SPSS.

It looks something like this: 

1,age,30 
1,race,black 
1,scale_total,35
2,age,20
2,race,white
2,scale_total,99

Ideally it would look like: 

ID,age,race,scale_total, etc
1, 30, black, 35 
2, 20, white, 99

I added values to the top row of the raw data (ID, Question, Response) and tried the cast function but I believe this aggregated data instead of just transforming it:

data_mod <- cast(raw.data2, ID~Question, value="Response")
Aggregation requires fun.aggregate: length used as default

Upvotes: 3

Views: 164

Answers (3)

eli-k
eli-k

Reputation: 11310

For SPSS:

data list list/ID (f5) Question Response (2a20).
begin data
1 "age" "30" 
1 "race" "black" 
1 "scale_total" "35"
2 "age" "20"
2 "race" "white"
2 "scale_total" "99"
end data.

casestovars /id=id /index=question.

Note that the resulting variables age and scale_total will be string variables - you'll have to turn them into numbers before further transformations:

alter type age scale_total (f8).

Upvotes: 0

akrun
akrun

Reputation: 887048

We need a sequence column to be created to take care of the duplicate rows which by default results in aggregation to length

library(data.table)
dcast(setDT(df1), ID + rowid(Question) ~ Question, value.var = 'Response')

NOTE: The example data clearly works (giving expected output) without using the sequence column.

dcast(setDT(df1), ID ~ Question)
#   ID age   race scale_total
#1:  1 30  black           35
#2:  2  20  white          99

So, this is a case when applied on the full dataset with duplicate rows

data

df1 <- structure(list(ID = c(1L, 1L, 1L, 2L, 2L, 2L), Question = c("age", 
"race", "scale_total", "age", "race", "scale_total"), Response = c("30", 
 "black ", "35", "20", "white", "99")), class = "data.frame", 
 row.names = c(NA, -6L))

Upvotes: 1

Andrew Gustar
Andrew Gustar

Reputation: 18425

You could use tidyr...

library(tidyr)
df<-read.csv(text="1,age,30 
    1,race,black 
    1,scale_total,35
    2,age,20
    2,race,white
    2,scale_total,99", header=FALSE, stringsAsFactors=FALSE)

df %>% spread(key=V2,value=V3)

  V1 age   race scale_total
1  1 30  black           35
2  2  20  white          99

Upvotes: 1

Related Questions