CODEWITHSUNDEEP
r
Jolien .A
Jolien .A

Reputation: 173

Convert columns in individual rows from an Excel table

I need to create a "vertical view" on a data frame in order to perform association rules. I created the data frame by reading the following Excel table into R:

dataset_c <- read.table("crimeschicago2014to2016correction.csv", sep=",", header=TRUE)

enter image description here

Creating a vertical view implies that all columns are translated to individual rows, and these rows include the ID they had in the original table. The same ID is thus repeated until all the columns in the original row for that ID are translated to their own individual row. Hence the table above would look like this:

ID 9446748 - CaseNumber HX100020

ID 9446478 - Date 1/1/2014 0:00

ID 9446478 - Block 006XX N DEARBORN ST

ID 9446478 - IUCR 890

ID 9446478 - PrimaryType THEFT

ID 9446478 - Description FROM BUILDING

ID 9446846 - CaseNumber HX100086 

etc.

The methods I've tried before all involved transposing the table, but that did not repeat the ID as it does in the example above. Note that the resulting table must also be a data frame.

Upvotes: 0

Views: 41

Answers (1)

Paul Rougieux
Paul Rougieux

Reputation: 11399

You can achieve this with 

library(tidyr)
gather(iris, key, value, -Species)

#      Species          key value
#1       setosa Sepal.Length   5.1
#2       setosa Sepal.Length   4.9
#3       setosa Sepal.Length   4.7
#4       setosa Sepal.Length   4.6
#5       setosa Sepal.Length   5.0

I'm using the sample data iris, you can replace it by your data frame.

This is probably a duplicate question, see for example this answer for the reshaping from long to wide format.

Upvotes: 1

Related Questions