Why am I getting NANs when concatenating a Data Frame with a Series

Question

I have a Pandas Dataframe ('a') and a Series ('b') both with timeseries index (weekends excluded). I am trying to concatenate them.

Both of them start with the same time point and the only difference I see is that the series 'b' Index has 3 more days than the dataframe 'a'.

When I try to concatenate them, I am just getting NANs for all the series values in the resultant Dataframe ('c'). I am unable to fathom the reason. Appreciate inputs.

print(a.head(10))
print(a.tail(10))
print(a.info(10))

            DCOILWTICO
DATE                  
2022-01-31       89.16
2022-02-01       88.22
2022-02-02       88.16
2022-02-03       90.17
2022-02-04       92.27
2022-02-07       91.25
2022-02-08       89.32
2022-02-09       89.57
2022-02-10       89.83
2022-02-11       93.10
            DCOILWTICO
DATE                  
2022-03-29      104.25
2022-03-30      107.81
2022-03-31      100.53
2022-04-01       99.32
2022-04-04      103.29
2022-04-05      101.98
2022-04-06       96.39
2022-04-07       96.05
2022-04-08       98.35
2022-04-11       94.22
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 51 entries, 2022-01-31 to 2022-04-11
Data columns (total 1 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   DCOILWTICO  50 non-null     float64
dtypes: float64(1)
memory usage: 2.9 KB
None

print(type(b))
print(b.head(10))
print(b.tail(10))

<class 'pandas.core.series.Series'>
2022-01-31    51.40
2022-02-01    52.68
2022-02-02    53.25
2022-02-03    53.91
2022-02-04    55.10
2022-02-07    55.71
2022-02-08    54.65
2022-02-09    54.76
2022-02-10    54.52
2022-02-11    55.21
Name: close, dtype: float64
2022-04-01    55.72
2022-04-04    55.82
2022-04-05    54.91
2022-04-06    55.30
2022-04-07    55.42
2022-04-08    56.09
2022-04-11    55.32
2022-04-12    55.99
2022-04-13    57.04
2022-04-14    57.11
Name: close, dtype: float64

c = pd.concat([a,b], axis=1)
print(c.head(10))
print(c.tail(10))
print(c.info)

            DCOILWTICO  close
2022-01-31       89.16    NaN
2022-02-01       88.22    NaN
2022-02-02       88.16    NaN
2022-02-03       90.17    NaN
2022-02-04       92.27    NaN
2022-02-07       91.25    NaN
2022-02-08       89.32    NaN
2022-02-09       89.57    NaN
2022-02-10       89.83    NaN
2022-02-11       93.10    NaN
            DCOILWTICO  close
2022-04-01       99.32    NaN
2022-04-04      103.29    NaN
2022-04-05      101.98    NaN
2022-04-06       96.39    NaN
2022-04-07       96.05    NaN
2022-04-08       98.35    NaN
2022-04-11       94.22    NaN
2022-04-12         NaN    NaN
2022-04-13         NaN    NaN
2022-04-14         NaN    NaN
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 54 entries, 2022-01-31 to 2022-04-14
Freq: B
Data columns (total 2 columns):
 #   Column      Non-Null Count  Dtype  
---  ------      --------------  -----  
 0   DCOILWTICO  50 non-null     float64
 1   close       0 non-null      float64
dtypes: float64(2)
memory usage: 1.3 KB
None

Answer 1

The index of the Pandas series 'b' needs to be converted into datetime index. Below is the necessary step before the concat step.

b.index = pd.to_datetime(b.index)