Joining two datasets by county and date

[rxak]

Hey experts!

I have two datasets, one containing data for weather types (Rain,Fog, Storm etc.) from 2016-2019 in California, and the second dataset containing wildfires in California from 2016-2019. In the second dataset (wildfires) there are 835 unique records, meaning that there were 835 wildfires in California from 2016-2019.

The weather dataset is much larger than this, since this shows the different weather activities during a day (it could be both foggy and rainy for one day, hence there are multiple duplicated dates)

df1 

Type	Date	Counties
Rain	2016-01-02	Santa Cruz
Rain	2016-01-02	Santa Cruz
Rain	2016-01-02	Santa Cruz
Rain	2016-01-04	Santa Cruz
.....
Fog	2019-02-26	Yuba

Contains 200,000+ records

df2

Date*	County	Lat	Lon	UniqueId
2016-07-22	Monterey	36.45994	-121.89938	977ac7ce
2016-06-23	Kern	35.6115	-118.45628	91816ff6
....
2019-06-28	Nevada	39.40972222	-121.00055556	b38c0563

Contains 835 unique records

*Date here is when the first fire was observed (i.e., Started)

My question is, how would you guys join these two datasets to visualize what kind of weather there was during a wildfire?

What I have done until now: 

- Joined Left and Right by Date, and Counties on Left and Right

The issue with this: This gives me duplicated values (because of the duplicated dates in weather dataset), so it now looks like there are 1208 records, or 1208 wildfires (which is not correct) -> if I just take the unique values of the UniqueId column, then it only shows about 200 records

- Joined only on counties (Left and Right)

The issue with this: This obviously doesn't take the different dates into account, why it just

I hope my question isn't too broadly asked, and that you guys can come up with a "better" solution that could showcase the weather during a wildfire more precisely 

[gautiergodard]

Hey @rxak 

I modified your sample data to make the example work but see attached for one way to join the two data sets in a way that doesn't cause a row explosion or loss of data.

Hope this helps!

[rxak]

Hey @gautiergodard 

This is definitely a better solution than what I came up with, but this still give a misleading number of wildfires in California from 2016-2019

By applying your solution to the dataset, I come up with only 293 records (meaning there have only been 293 wildfires, which is not correct)

I appreciate the help, and will most likely use this if there are no better options than this :)

[gautiergodard]

Hey @rxak 

Hard to diagnose the problem without the full dataset, but if you are getting a lower number of records I presume some of them may be getting dropped during your join?

Perhaps there are certain days or counties missing from the weather data set? 

If this solution ends up working for you - please accept the post as a solution !

[rxak]

@gautiergodard 

Correct, there are a few counties that are missing from the Weather dataset

If you would like to investigate further, I have attached both .csv files :)

[gautiergodard]

@rxak I get the same 293 result. I suggest trying to obtain a more complete weather dataset.

[rxak]

Have tried, and that dataset was the closest I could find that have been filtered via counties 

Thanks once again for your time!