# Faster groupwise joins to complete implicitly missing rows

I have a grouped `DataFrame` with dates and values but only observe dates in which a value was observed. I’d like to create the rows that are implicitly missing and have them as missing e.g. for interpolation later. I came up with the following code but was wondering if there is a faster way to do this?

``````df = DataFrame(
g = ['a','a', 'b', 'b', 'c', 'c', 'c'],
date = [Date(2021,1,1), Date(2021,1,2), Date(2021,1,2), Date(2021,1,4), Date(2021,1,1),Date(2021,1,3) ,Date(2021,1,7)],
v = rand(7)
)
alldates = DataFrame(date = minimum(df.date):Day(1):maximum(df.date))
gdf = groupby(df, :g)
combdf = DataFrame()
for g in gdf
gout = leftjoin(alldates, g, on = :date)
gout.g .= g.g
disallowmissing!(gout, :g)
append!(combdf, gout, cols = :union)
end
``````

Not sure if faster, but I think this is clearer:

``````julia> combdf2 = rename!(DataFrame(Iterators.product(alldates.date, unique(df.g))), [:date, :g])
julia> combdf2 = leftjoin(combdf2, df, on = [:date, :g])

julia> isequal(combdf, combdf2)
true
``````
2 Likes

Thanks! It is not only clearer but also twice as fast in a quick benchmark!

1 Like
{"ft_lang_label":"__label__en","ft_lang_prob":0.83086395,"math_prob":0.99018687,"size":744,"snap":"2022-05-2022-21","text_gpt3_token_len":253,"char_repetition_ratio":0.1527027,"word_repetition_ratio":0.0,"special_character_ratio":0.375,"punctuation_ratio":0.26804122,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.990981,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-05-27T05:17:06Z\",\"WARC-Record-ID\":\"<urn:uuid:81ab4af3-2f05-4481-b9f1-6792b8b67c34>\",\"Content-Length\":\"25011\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:42f26db8-9e7c-4c56-a0e2-80289308aba6>\",\"WARC-Concurrent-To\":\"<urn:uuid:37c82f90-162c-403b-bee3-f918366ecb58>\",\"WARC-IP-Address\":\"64.71.144.205\",\"WARC-Target-URI\":\"https://discourse.julialang.org/t/faster-groupwise-joins-to-complete-implicitly-missing-rows/59414\",\"WARC-Payload-Digest\":\"sha1:NJFE5TBSQ3KXD3H46CXVCZCNH32MBM3Z\",\"WARC-Block-Digest\":\"sha1:45FADWVCEYVUX5LKNWPUMY65BAERTNPJ\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-21/CC-MAIN-2022-21_segments_1652662636717.74_warc_CC-MAIN-20220527050925-20220527080925-00045.warc.gz\"}"}