Search code examples
joinmergeconcatenation

How to concat a list of series according to earliest and latest dates

Hi I have a list of series called final_1 of more than 2000 items, each with thousands of rows, which i hope to concat into an entire dataframe with the index of the dataframe having the earliest and latest of all date indexes. final_1 look like this

         Dates  Values
 0   2020-04-13   529.0
 1   2020-04-20   465.0
 2   2020-04-27   408.0
 3   2020-05-04   374.0
 4   2020-05-11   339.0
 ..         ...     ...
          Dates  Values
 0   2020-03-30    0.09
 1   2020-04-06    0.05
 2   2020-04-13    0.05
 3   2020-04-20    0.05
 4   2020-04-27    0.04
 .....

and the finished product would look like...

enter image description here

I've tried concating the list with pd.concat(final, axis=1, join='outer') all i got was this

enter image description here

which is not what i want...

help please?

Solution

  • I misunderstood your initial question. I have changed the data structure to be Pandas series with date indices.

    s1 = pd.Series({
    pd.Timestamp('2020-04-13'): 529.0,
    pd.Timestamp('2020-04-20'): 465.0,
    pd.Timestamp('2020-04-27'): 408.0,
    pd.Timestamp('2020-05-04'): 374.0,
    pd.Timestamp('2020-05-11'): 339.0
})

s2 = pd.Series({
    pd.Timestamp('2020-03-30'): 0.09,
    pd.Timestamp('2020-04-06'): 0.05,
    pd.Timestamp('2020-04-13'): 0.05,
    pd.Timestamp('2020-04-20'): 0.05,
    pd.Timestamp('2020-04-27'): 0.04
})

# Convert series to DataFrames so merge can happen
df1 = s1.reset_index()
df1.columns = ['Dates', 'Values1']

df2 = s2.reset_index()
df2.columns = ['Dates', 'Values2']

combined_df = pd.merge(df1, df2, on='Dates', how='outer')

combined_df = combined_df.sort_values('Dates').reset_index(drop=True)
combined_df = combined_df.fillna('NA')