Menu
I have a simple question to ask. I couldn't find a solution on SO.
I have a column vector of dates and I would like to see whether months are unique or not. I tried to use unique but I may have used it in the wrong way.
An example:
Date = structure(c(11690, 11725, 11753, 11781, 11809, 11844, 11872,
11900, 11942, 11970, 11998, 12026, 12061, 12089, 12117, 12145,
12180, 12208, 12243, 12264, 12265, 12299, 12327, 12362, 12390,
12425, 12453, 12481, 12509, 12544, 12572, 12600, 12635, 12663,
12698, 12726, 12754, 12796, 12817, 12845, 12880, 12907, 12936,
12971, 12999, 13027, 13062, 13090, 13118, 13160, 13181, 13209,
13244, 13272, 13307, 13335, 13363, 13392, 13426, 13454, 13489,
13524, 13552, 13580, 13615, 13643, 13670, 13699, 13727, 13762,
13790, 13825, 13853, 13888, 13916, 13944, 13979, 14007, 14035,
14063, 14098, 14126, 14154, 14160, 14189, 14217, 14259, 14280,
14308, 14336, 14371, 14399, 14427, 14462, 14490, 14525, 14553,
14581, 14623, 14644, 14672, 14707, 14735, 14770, 14798, 14826,
14854, 14889, 14917, 14945, 14987, 15008, 15036, 15071, 15099,
15134, 15162, 15190, 15225, 15253, 15281, 15316, 15351, 15379,
15407, 15434, 15463, 15497, 15526, 15554, 15589, 15617, 15652,
15680, 15715, 15743, 15771, 15799, 15827, 15862, 15890, 15918,
15953, 15980, 16016, 16044, 16079, 16107, 16135, 16163, 16198,
16226, 16254, 16289, 16317, 16345, 16380, 16408, 16457, 16467,
16499, 16540, 16556, 16589, 16632, 16648, 16681, 16730, 16740,
16772, 16821, 16832, 16870, 16912, 16922, 16954, 17003, 17014,
17052, 17094, 17106, 17143, 17185, 17198, 17234, 17283, 17287,
17325, 17367, 17379, 17416, 17465, 17471, 17514, 17556, 17563,
17598, 17647, 17652, 17696, 17738, 17744, 17787, 17829, 17836,
17878, 17920, 17928, 17962, 17996, 18017, 18053, 18102, 18109,
18151, 18193, 18201, 18242), class = "Date")
In this column vector I would like to see whether there are two observations in the same month (there are 2 for "2003-07" and "2008-10"). I can I check it with one line of command?
Thanks!
337
Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option. A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.
To count unique dates ("trading days" in the example) you can use the UNIQUE function with the COUNT function, or a formula based on the COUNTIF function. In the example shown, the formula in cell G8 is:
Count the unique numeric values that appear only once from a list, you should combine the SUM, IF, ISNUMBER and COUNTIF functions to create the formula, the generic syntax is: Array formula, should press Ctrl + Shift + Enter keys together. range: The data column where you want to count unique values from.
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. Unique distinct values are all values but duplicates are merged into one value.
Your query will return unique DATES, as you've specified. If you want to get just unique MONTHS/YEARS, you should either modify you dates in a way, that each date becomes the first day of the month, or you should just go with some string representation like 'YYYY-MM'.
In base R, we can format the Date to get only year and month, use table to count their occurrences, Filter to select only those month-year which occur more than once.
names(Filter(function(x) x > 1, table(format(Date, "%Y-%m"))))
#[1] "2003-07" "2008-10"
Same logic using zoo::as.yearmon.
names(Filter(function(x) x > 1, table(zoo::as.yearmon(Date))))
#[1] "Jul 2003" "Oct 2008"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
