How to combine two time dependent datasets?

Question

I'm very new to data science so please be gentle.

I have a dataset that contains record of occurrence of fire for the past 35 years (+-700.000 rows). Each date and time can have more than one occurrence as two fires can happen at the same time in different locations. It's features are approximately as follows:

Date and Time | Borough | County | Area Burned | Type | Cause | etc.

I'm planning to combine this with a new dataset that I'm in the process of constructing. This new dataset would have the following features:

Date and Time | Latitude | Longitude | Av. Temperature | Av. Wind Speed | Av, Humidity | etc.

My idea is to try to predict the likelihood of a fire occurring and it's potential severity from a given set of atmospheric parameters. But I'm a bit unsure what would be the best way to combine them.

If I merge on dates, I'd have multiple repeated rows with fires occurring at the same time in different places, would that be the best way? The problem I see is that the locations where there was no fire would not be represented, so I'd have to add a bunch of blank rows for all locations where there was no fire to balance the set.

Any ideas on the best strategy for this?

Answer 1

If I merge on dates, I'd have multiple repeated rows with fires occurring at the same time in different places, would that be the best way?

Probably not, since you don't want to lose the location information. You should probably find a way to map the latitude/longitude to borough/county between the two datasets, so that you obtain a semantically consistent dataset (list of fires by both date/time and location).

The problem I see is that the locations where there was no fire would not be represented, so I'd have to add a bunch of blank rows for all locations where there was no fire to balance the set.

This depends what you plan to do with your data, but given how your first dataset is currently structured (list of fires) it doesn't make a lot of sense to add locations where there was no fire. You might want to create a dataset which lists for every place and every time whether there was a fire or not, for example.

Answer 2

It's important to remember you can always save objects (like dicts or json) into individual cells in Pandas. Especially if you're not sure of how you'd to analyze at the moment.

The Google Analytics Customer Revenue Prediction data uses a lot of JSON

You can see how people analyze the data on the Notebooks section too https://www.kaggle.com/c/ga-customer-revenue-prediction/notebooks

Alternatively, it's possibly fine to have multiple repeated rows, depending on your analysis. For example, you can then pivot/groupby/agg to "deduplicate" your data.