lubridate - Using Mutate() to Create a Month, Day and Year Column in R returning NA's

Question

Sample of data:

Agreement   Ext.descr. Description of agreement             Valid From       Valid To 
    1001015314  254819  FWIN-HOFPA-AEP/AMERICAN ELECTRIC POWER  12/31/2020    12/31/2021
    1001009372  2552652 MULTI-TBCPE-STOREROOM SOLUTIONS/SYNOVOS 12/31/2020    12/31/2021 
    1001026488  2553601 MULTI-ABB MVD/LP/LVD/DRC/SENATOBIA      12/31/2020    12/31/2021 
    1001017623  2553659 D31-GRAYBAR CONTRACTOR                  12/31/2020    12/31/2021 
    1001004469  2553784 D31-GRAYBAR CONTRACTOR BLANKET          12/31/2020    12/31/2021 
    1001013206  2553786 D31-BUIST BLANKET-GRAYBAR ELECTRIC      12/31/2020    12/31/2021
    1001003012  2553790 GRMI-ALLIED ELECTRIC BLANKET- GRAYB     12/31/2020    12/31/2021 
    1001003264  2553792 D31-GRAYBAR INDUSTRIAL BLANKET / NE     12/31/2020    12/31/2021 
    1001002666  2554099 D31-INGREDION - 20AR03                  12/31/2020    12/31/2021 
    1001020806  255420  DAIA-HOFPA-MIDAMERICAN ENERGY           12/18/2020    12/31/2021 
    1001020461  2554587 GHIL-HELUKABEL LUG PROJECT              12/31/2020    12/31/2021 
    1001003015  2554595 GHIL-CHARLES INDUSTRIES                 12/31/2020    12/31/2021
    1001023615  255858  HAIN-HOFPA-PANDUIT                      12/31/2020    12/31/2021 
    1001007084  2567168 ININ-SOUTH BEND SOLAR PROJECT           12/31/2020    12/31/2021 
    1001007283  2567336 GHIL-CDW/MONRO CARLON PROJECT           12/31/2020    12/31/2021 
    1001010247  2571407 GRMI-BLACK RIVER FLATS                  12/31/2020    12/31/2021 
    1001010939  2572288 ININ-CRANE NAVAL BASE                   12/31/2020    12/31/2021 
    1001021887  2574755 LAMI-MCLAREN LANSING                    12/31/2020    12/31/2021 
    1001029671  2594599 ININ-MALLOR BUILDING                    12/31/2020    12/31/2021 
    1001029673  2594598 NATL-BUNPA AGR                          12/31/2020    12/31/2021 
    1001029673  2594598 MIWI-NATL-SQDPA                         12/31/2020    12/31/2021 

Load your libraries

library(tidyverse)
library(readxl)
library(plyr)
library(dplyr)
library(lubridate)
library(rebus)
library(stringi)

Find you working directory

getwd()

Set your working directory

setwd('C:/Users/mcgilas/Desktop')

Import excel files

Sales_Offices <- read.csv("All Sales Offices.csv", head(T))
Sales_Offices

District_31 <- read.csv("District 31.csv", head(T))
District_31

Merge D31 csv into Sales Offices csv

merged <- Sales_Offices %>%
  full_join(District_31)
view(merged)

Filter out ZCRJ rows

merged %>%
  filter(!ATyp %in% c("ZCRJ")) -> merged

Filter out SCHNEIDER ELECTRIC USA INC in vendor name

merged %>%
  filter(!Name %in% c("SCHNEIDER ELECTRIC USA INC")) -> merged
view(merged)

Display your column names

colnames(merged)

Remove columns that you don't need

merged[,c("Pri.Vendo", "OwnRole", "Time", "DChl", "On", "Part..Role", "Chgd.By", "Dv", "Chngd.On",
          "Partner", "TimChng", "Crcy", "Crtd.By", "SOrg.")] <- list(NULL)
view(merged)

Rename column names for visual purposes

colnames(merged)[colnames(merged) == 'Name'] <- 'Vendor'
colnames(merged)[colnames(merged) == 'Name.1'] <- 'Name'
view(merged)

class(merged$Valid.From)
class(merged$Valid.To)

[1] "Date" [1] "Date"

merged <- merged %>% mutate(Year = year(Valid.From),
                            Month = month(Valid.From),
                            Day = day(Valid.From))
view(merged)

I am getting all NA's in my new Year, Month, and Day columns as well as my original Valid.From and Valid.To column.

Answer 1

You have not specified the format for year, month and day. Check format in lubridate