Merging dataframes on dates

General Usage
1

I’m trying to clean a data set and I am stuck at the last step.

The main dataframe, that I call maindf, will contain all the data and has 3000 rows and 200 columns, like this

Capture
Capture793×147 2.4 KB

Each row of maindf will be filled with a time series.

Each time series, called tempdf, is a data frame with one row:
Capture2
I would like that each observation in tempdf automatically goes into the proper part of maindf. I would then iterate by sending the first time series in the first row of maindf etc.
The problem is that each series has a different number of observations, and there may be holes in the dates, so I do not know how to match them properly.

The data set is small so I am more interested in a simple solution than an efficient one. I tried all my knowledge of dataframes and joins but this is beyond me.

2

Would definitely suggest you do things the opposite way that you are now. Time series almost always works best as a column vector.

In this case I’d create individual dataframes for each series, then use something like leftjoin on date (perhaps outerjoin or rightjoin instead). You could first compute the union of all date columns to get the complete set of dates and use that as your base dataframe to initialize the join.

4 Likes
3

+1 on this. stack and vcat.

4

Worked perfectly. Instead of creating an empty dataframe, I just created a dataframe column with the dates and added all the time series looping leftjoin. Easy solutions are always the best. Thank you very much.
(I think this is a record, 5 minutes from answer to exact question.)

1 Like