Problem with date formats

General
1

Hi. I want to summarize daily data into months and plot the monthly data, but I can't seem to get a "%Y-%m" format (Y indicates four digit year) to work.

library(readxl)
df <- read_excel("Daily_Birth_Rates_Spreadsheet.xlsx")
head(df)
str(df)
df$Date <- format(as.Date(df$Date), "%Y-%m")
head(df,1)
str(df)
df2 <- df %>%
group_by(Date) %>%
summarise(Freq = sum(Birth))
df2
str(df2)
df2$Date <- format(as.Date(df$Date), "%Y-%m")
plot(df2, xlim=as.Date(c("2000-01","2000-12")), main = "Monthly Graph")

head(df)

A tibble: 6 x 2

Date Birth

1 2000-01-01 00:00:00 69
2 2000-01-02 00:00:00 52
3 2000-01-03 00:00:00 49
4 2000-01-04 00:00:00 65
5 2000-01-05 00:00:00 45
6 2000-01-06 00:00:00 44

str(df)
tibble [365 x 2] (S3: tbl_df/tbl/data.frame)
Date : POSIXct[1:365], format: "2000-01-01" ... Birth: num [1:365] 69 52 49 65 45 44 70 49 66 42 ...

df$Date <- format(as.Date(df$Date), "%Y-%m")
head(df,1)

A tibble: 1 x 2

Date Birth

1 2000-01 69

str(df)
tibble [365 x 2] (S3: tbl_df/tbl/data.frame)
Date : chr [1:365] "2000-01" "2000-01" "2000-01" "2000-01" ... Birth: num [1:365] 69 52 49 65 45 44 70 49 66 42 ...

df2

A tibble: 12 x 2

Date Freq

1 2000-01 1648
2 2000-02 1605
3 2000-03 1609
4 2000-04 1605
5 2000-05 1588
6 2000-06 1676
7 2000-07 1931
8 2000-08 1788
9 2000-09 1709
10 2000-10 1709
11 2000-11 1687
12 2000-12 1575

str(df2)
tibble [12 x 2] (S3: tbl_df/tbl/data.frame)
Date: chr [1:12] "2000-01" "2000-02" "2000-03" "2000-04" ... Freq: num [1:12] 1648 1605 1609 1605 1588 ...

df2$Date <- format(as.Date(df$Date), "%Y-%m")
Error in charToDate(x) :
character string is not in a standard unambiguous format

2

Could you supply some sample data in dput() format?
thanks

3

new.df

A tibble: 365 x 2

Date Birth

1 2000-01-01 00:00:00 69
2 2000-01-02 00:00:00 52
3 2000-01-03 00:00:00 49
4 2000-01-04 00:00:00 65
5 2000-01-05 00:00:00 45
6 2000-01-06 00:00:00 44
7 2000-01-07 00:00:00 70
8 2000-01-08 00:00:00 49
9 2000-01-09 00:00:00 66
10 2000-01-10 00:00:00 42

... with 355 more rows

4

Thanks, but that is not in dput() format. If we assume your data is a data.frame or tibble called new.df then what we need is the output of

dput(new.df)

Here is an example with a data set called animals.

dput(animals)

gives us

structure(list(cat = c(1L, 1L, 1L, 2L), dog = c(2L, 2L, 1L, 5L
), parrot = c(3L, 6L, 1L, 6L)), row.names = c(NA, 4L), class = "data.frame")
5

dput(new.df)
structure(list(Date = structure(c(946684800, 946771200, 946857600,
946944000, 947030400, 947116800, 947203200, 947289600, 947376000,
947462400, 947548800, 947635200, 947721600, 947808000, 947894400,
947980800, 948067200, 948153600, 948240000, 948326400, 948412800,
948499200, 948585600, 948672000, 948758400, 948844800, 948931200,
949017600, 949104000, 949190400, 949276800, 949363200, 949449600,
949536000, 949622400, 949708800, 949795200, 949881600, 949968000,
950054400, 950140800, 950227200, 950313600, 950400000, 950486400,
950572800, 950659200, 950745600, 950832000, 950918400, 951004800,
951091200, 951177600, 951264000, 951350400, 951436800, 951523200,
951609600, 951696000, 978220800, 951868800, 951955200, 952041600,
952128000, 952214400, 952300800, 952387200, 952473600, 952560000,
952646400, 952732800, 952819200, 952905600, 952992000, 953078400,
953164800, 953251200, 953337600, 953424000, 953510400, 953596800,
953683200, 953769600, 953856000, 953942400, 954028800, 954115200,
954201600, 954288000, 954374400, 954460800, 954547200, 954633600,
954720000, 954806400, 954892800, 954979200, 955065600, 955152000,
955238400, 955324800, 955411200, 955497600, 955584000, 955670400,
955756800, 955843200, 955929600, 956016000, 956102400, 956188800,
956275200, 956361600, 956448000, 956534400, 956620800, 956707200,
956793600, 956880000, 956966400, 957052800, 957139200, 957225600,
957312000, 957398400, 957484800, 957571200, 957657600, 957744000,
957830400, 957916800, 958003200, 958089600, 958176000, 958262400,
958348800, 958435200, 958521600, 958608000, 958694400, 958780800,
958867200, 958953600, 959040000, 959126400, 959212800, 959299200,
959385600, 959472000, 959558400, 959644800, 959731200, 959817600,
959904000, 959990400, 960076800, 960163200, 960249600, 960336000,
960422400, 960508800, 960595200, 960681600, 960768000, 960854400,
960940800, 961027200, 961113600, 961200000, 961286400, 961372800,
961459200, 961545600, 961632000, 961718400, 961804800, 961891200,
961977600, 962064000, 962150400, 962236800, 962323200, 962409600,
962496000, 962582400, 962668800, 962755200, 962841600, 962928000,
963014400, 963100800, 963187200, 963273600, 963360000, 963446400,
963532800, 963619200, 963705600, 963792000, 963878400, 963964800,
964051200, 964137600, 964224000, 964310400, 964396800, 964483200,
964569600, 964656000, 964742400, 964828800, 964915200, 965001600,
965088000, 965174400, 965260800, 965347200, 965433600, 965520000,
965606400, 965692800, 965779200, 965865600, 965952000, 966038400,
966124800, 966211200, 966297600, 966384000, 966470400, 966556800,
966643200, 966729600, 966816000, 966902400, 966988800, 967075200,
967161600, 967248000, 967334400, 967420800, 967507200, 967593600,
967680000, 967766400, 967852800, 967939200, 968025600, 968112000,
968198400, 968284800, 968371200, 968457600, 968544000, 968630400,
968716800, 968803200, 968889600, 968976000, 969062400, 969148800,
969235200, 969321600, 969408000, 969494400, 969580800, 969667200,
969753600, 969840000, 969926400, 970012800, 970099200, 970185600,
970272000, 970358400, 970444800, 970531200, 970617600, 970704000,
970790400, 970876800, 970963200, 971049600, 971136000, 971222400,
971308800, 971395200, 971481600, 971568000, 971654400, 971740800,
971827200, 971913600, 9.72e+08, 972086400, 972172800, 972259200,
972345600, 972432000, 972518400, 972604800, 972691200, 972777600,
972864000, 972950400, 973036800, 973123200, 973209600, 973296000,
973382400, 973468800, 973555200, 973641600, 973728000, 973814400,
973900800, 973987200, 974073600, 974160000, 974246400, 974332800,
974419200, 974505600, 974592000, 974678400, 974764800, 974851200,
974937600, 975024000, 975110400, 975196800, 975283200, 975369600,
975456000, 975542400, 975628800, 975715200, 975801600, 975888000,
975974400, 976060800, 976147200, 976233600, 976320000, 976406400,
976492800, 976579200, 976665600, 976752000, 976838400, 976924800,
977011200, 977097600, 977184000, 977270400, 977356800, 977443200,
977529600, 977616000, 977702400, 977788800, 977875200, 977961600,
978048000, 978134400), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
Birth = c(69, 52, 49, 65, 45, 44, 70, 49, 66, 42, 66, 43,
42, 75, 39, 60, 68, 59, 45, 32, 76, 41, 45, 53, 40, 51, 76,
33, 43, 53, 57, 65, 73, 78, 78, 32, 48, 43, 77, 67, 53, 49,
49, 63, 43, 34, 46, 54, 76, 74, 35, 75, 54, 59, 71, 46, 76,
44, 43, 49, 53, 50, 39, 50, 33, 41, 62, 38, 45, 39, 64, 51,
68, 72, 52, 40, 53, 65, 45, 38, 48, 69, 72, 68, 46, 50, 46,
66, 41, 45, 60, 72, 46, 47, 64, 49, 64, 65, 36, 74, 36, 71,
53, 49, 45, 70, 32, 56, 36, 37, 77, 73, 41, 43, 41, 69, 53,
42, 34, 63, 67, 76, 67, 71, 57, 46, 44, 71, 75, 34, 62, 48,
55, 70, 53, 37, 40, 34, 49, 34, 42, 42, 34, 53, 65, 56, 37,
55, 62, 54, 33, 32, 48, 34, 59, 53, 36, 46, 59, 77, 67, 58,
71, 57, 41, 67, 49, 77, 55, 71, 33, 34, 70, 69, 64, 73, 50,
56, 75, 37, 46, 44, 77, 71, 50, 54, 61, 77, 77, 73, 67, 46,
71, 54, 68, 47, 63, 48, 64, 53, 75, 68, 64, 46, 43, 64, 72,
74, 63, 72, 45, 50, 74, 70, 44, 68, 33, 67, 63, 72, 59, 61,
65, 47, 77, 65, 71, 52, 39, 72, 43, 56, 44, 52, 65, 64, 51,
75, 58, 37, 61, 33, 71, 53, 53, 72, 76, 57, 50, 40, 42, 43,
44, 41, 74, 52, 37, 75, 59, 44, 69, 72, 61, 68, 60, 68, 70,
40, 71, 72, 51, 75, 34, 39, 36, 40, 64, 37, 48, 43, 58, 63,
54, 63, 73, 49, 38, 65, 54, 52, 75, 38, 59, 63, 72, 41, 44,
66, 71, 62, 63, 66, 37, 70, 45, 65, 32, 71, 76, 60, 58, 45,
61, 61, 53, 76, 44, 64, 73, 42, 62, 33, 74, 66, 50, 47, 61,
33, 64, 59, 52, 77, 50, 32, 46, 60, 59, 57, 54, 50, 46, 34,
39, 49, 46, 34, 36, 56, 51, 74, 47, 58, 59, 49, 62, 73, 42,
67, 71, 47, 42, 33, 47, 40, 44)), row.names = c(NA, -365L
), class = c("tbl_df", "tbl", "data.frame"))

6

Great, thank you.

One approach seems to be to use the floor_date() function in lubridate to set all dates in a given month to yyyy.mm.01.

Calling your data dat1

library(lubridate)
dat1$month  <- floor_date(dat1$Date, unit = "month")

looks like it will work.

2 Likes
7

This works for me, thank you.

8

I believe that @jrkrideau should be the one given credit for the solution.

9

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.