Short format to long format in R

Question

This is what my data looks like on excel, where status and events are variables for the mentioned dates. Events columns don't have data.

30/12/2018     31/12/2018           01/01/2019  
Status  event      status   event   status  event

delivered      ncp              ncp 

ncp        delivered            ncp

I want to make these variables my headings and append these dates as a column.

Note: This is just a sample of my data, in reality, dates are available for a month. Please help.

Could you use `dput(head(df,10))` to add sample data? – NelsonGon Jan 30 '19 at 18:18 — NelsonGon, Jan 30 '19 at 18:18
Your data presentation is not clear. Can you clean it up? – Werner Jan 30 '19 at 18:20 — Werner, Jan 30 '19 at 18:20

score 0 · Answer 1 · answered Jan 30 '19 at 18:26

Save your excel file as CSV. Then use R read.csv() function

sample_data <- read.csv(file="c:/mySampleData.csv", header=TRUE, sep=",")

The first parameter of the function is path to your file.
It converts your data into a data frame and you can access the columns using $, e.g. sample_data$Status will return the status column.

score 0 · Answer 2 · answered Jan 31 '19 at 05:14

Not sure if this is the kind of thing you're looking for, but this should put the data into a good structured format.

library(readxl)
library(dplyr)
library(tidyr)

file <- # INSERT FILE PATH HERE

df <- read_excel(file, col_names = FALSE)

df2 <- df %>%
       mutate(SEQ_NO = ifelse(row_number() > 2, row_number() - 2, NA)) %>%
       gather(COL, VAL, -SEQ_NO) %>%
       mutate(DATE = ifelse(grepl('/', VAL), VAL, NA),
              DATE = as.Date(DATE, format = '%d/%m/%Y')) %>%
       fill(DATE) %>%
       mutate(KEY = ifelse(VAL %in% c('status', 'event'), toupper(VAL), NA)) %>%
       fill(KEY) %>%
       filter(!is.na(SEQ_NO)) %>%
       select(-COL) %>%
       spread(KEY, VAL) %>%
       arrange(DATE, SEQ_NO) %>%
       select(DATE, SEQ_NO, STATUS, EVENT)

Short format to long format in R

2 Answers2