No equals Date in Several time series and dataframe

[francis-fm]

Body

Hello, in the UBT index you have the date, and the series_without_irregularities_df you have the date as a column, so you want to have the same dates from UBT in series_without_irregularities_df and series_without_irregularities. I have tried many attempts but the dates differ from each other.

import pandas as pd
import altair as alt
from sklearn.linear_model import LinearRegression
import numpy as np
import pmdarima as pm

# 1. Load the database
UBT = pd.read_excel("Descomposicion serial v1.xlsx", sheet_name="Top Mount")

# Dictionary to convert month names in Spanish to numbers
months = {
    'enero': '01', 'febrero': '02', 'marzo': '03', 'abril': '04',
    'mayo': '05', 'junio': '06', 'julio': '07', 'agosto': '08',
    'septiembre': '09', 'octubre': '10', 'noviembre': '11', 'diciembre': '12'
}

# Function to convert the date to the desired format
def convert_date(date):
    day, month, year = date.split(' de ')
    month = months[month.lower()]
    return f'{year}-{month}-{day}'

# Convert the "Semana de Fecha Completa" column to date
UBT['Date'] = UBT['Semana de Fecha Completa'].apply(convert_date)
UBT['Date'] = pd.to_datetime(UBT['Date'], format='%Y-%m-%d')
UBT = UBT.set_index('Date')  # Set 'Date' as index

# Align UBT_ts with UBT
UBT_ts = UBT["UBT
[prueba fechas.xlsx](https://github.com/user-attachments/files/15587681/prueba.fechas.xlsx)
"].resample('W').mean()

# Reindex UBT_ts to ensure it includes all dates from UBT
UBT_ts = UBT_ts.reindex(UBT.index, method='ffill')

# Check if indices are equal
indices_equal = UBT_ts.index.equals(UBT.index)
print("Are the indices of UBT and UBT_ts equal?", indices_equal)

# Automatically fit an ARIMA model to the time series
arima_model = pm.auto_arima(UBT_ts.dropna(), seasonal=False, stepwise=True, trace=True)
irregular = pd.Series(arima_model.resid(), index=UBT_ts.dropna().index)  # Create a series with the residuals

# Get the series without the irregular component
series_without_irregularities = UBT_ts.loc[irregular.index] - irregular

# Reindex series_without_irregularities to ensure it includes all dates from UBT
series_without_irregularities = series_without_irregularities.reindex(UBT.index, method='ffill')

# Create a DataFrame series_without_irregularities_df
series_without_irregularities_df = pd.DataFrame({
    'Date': UBT.index,
    'series_without_irregularities': series_without_irregularities.values
})

# Check if the dates in series_without_irregularities_df are equal to those in UBT
indices_equal_series_without_irregularities_df = series_without_irregularities_df['Date'].equals(UBT.index)
print("Are the indices of UBT and series_without_irregularities_df equal?", indices_equal_series_without_irregularities_df)

# 3. Create a DataFrame with the original series and the series without irregularities
combined_df = pd.DataFrame({
    'Date': UBT.index,
    'UBT_ts': UBT_ts.values,
    'series_without_irregularities': series_without_irregularities_df['series_without_irregularities'].values
})

# Remove rows with NaN values in the 'series_without_irregularities' column
combined_df = combined_df.dropna(subset=['series_without_irregularities'])

# 4. Fit a linear model and 5. Print the trend equation
# Convert the dates to a numeric format
combined_df['Date_num'] = (combined_df['Date'] - pd.Timestamp('1970-01-01')) / pd.Timedelta(days=1)
trend_model = LinearRegression()
trend_model.fit(combined_df[['Date_num']], combined_df['series_without_irregularities'])
coefficients = trend_model.coef_, trend_model.intercept_
print(f"Trend equation: Value = {coefficients[1]} + {coefficients[0][0]} * t")

# 6. Calculate mean, standard deviation, and confidence limits
mean_val = np.mean(combined_df['series_without_irregularities'])
std_dev = np.std(combined_df['series_without_irregularities'])
upper_limit = mean_val + std_dev
lower_limit = mean_val - std_dev

# 7. Create a chart of the series without irregularities with confidence limits
chart1 = alt.Chart(combined_df.melt('Date', value_vars=['series_without_irregularities'], var_name='series', value_name='value')).mark_line(point=True).encode(
    x='Date:T',
    y='value:Q',
    color='series:N',
    tooltip=['Date', 'value', 'series']
).properties(
    title='Series without Irregularities with Confidence Limits'
).interactive()

# Add horizontal lines for the mean and confidence limits
rule1 = alt.Chart(pd.DataFrame({'y': [mean_val]})).mark_rule(color='red', strokeDash=[5, 5]).encode(
    y='y:Q',
    tooltip=[alt.Tooltip('y:Q', title='Mean')]
)
rule2 = alt.Chart(pd.DataFrame({'y': [upper_limit]})).mark_rule(color='blue', strokeDash=[5, 5]).encode(
    y='y:Q',
    tooltip=[alt.Tooltip('y:Q', title='Upper Limit')]
)
rule3 = alt.Chart(pd.DataFrame({'y': [lower_limit]})).mark_rule(color='blue', strokeDash=[5, 5]).encode(
    y='y:Q',
    tooltip=[alt.Tooltip('y:Q', title='Lower Limit')]
)

chart1 = chart1 + rule1 + rule2 + rule3
chart1.show()  # Show the chart

# 9. Create chart of original and series without irregularities curves
chart2 = alt.Chart(combined_df).mark_line(point=True).encode(
    x='Date:T',
    y=alt.Y('UBT_ts:Q', title='Value'),
    color=alt.value('blue'),
    tooltip=['Date', 'UBT_ts']
).properties(
    title='Original Curve (UBT_ts)'
)

chart3 = alt.Chart(combined_df).mark_line(point=True).encode(
    x='Date:T',
    y=alt.Y('series_without_irregularities:Q', title='Value'),
    color=alt.value('orange'),
    tooltip=['Date', 'series_without_irregularities']
).properties(
    title='Curve without Irregularities'
)

combined_chart = alt.layer(chart2, chart3).resolve_scale(y='independent')
combined_chart.show()  # Show the chart

Guidelines

• I have read and understood this category's guidelines before making this post.
  prueba fechas.xlsx

[rohispace90]

Hello dear;

It seems the primary issue is ensuring that the indices of 'series_without_irregularities' and 'series_without_irregularities_df' align correctly with 'UBT.index'. Let's break down the steps and ensure we properly align the indices and create the DataFrame correctly.
Here's a modified version of your code that ensures proper alignment of the dates:

import pandas as pd
import altair as alt
from sklearn.linear_model import LinearRegression
import numpy as np
import pmdarima as pm

# 1. Load the database
UBT = pd.read_excel("prueba fechas.xlsx", sheet_name="Top Mount")

# Dictionary to convert month names in Spanish to numbers
months = {
    'enero': '01', 'febrero': '02', 'marzo': '03', 'abril': '04',
    'mayo': '05', 'junio': '06', 'julio': '07', 'agosto': '08',
    'septiembre': '09', 'octubre': '10', 'noviembre': '11', 'diciembre': '12'
}

# Function to convert the date to the desired format
def convert_date(date):
    day, month, year = date.split(' de ')
    month = months[month.lower()]
    return f'{year}-{month}-{day}'

# Convert the "Semana de Fecha Completa" column to date
UBT['Date'] = UBT['Semana de Fecha Completa'].apply(convert_date)
UBT['Date'] = pd.to_datetime(UBT['Date'], format='%Y-%m-%d')
UBT = UBT.set_index('Date')  # Set 'Date' as index

# Align UBT_ts with UBT
UBT_ts = UBT["UBT"].resample('W').mean()

# Reindex UBT_ts to ensure it includes all dates from UBT
UBT_ts = UBT_ts.reindex(UBT.index, method='ffill')

# Check if indices are equal
indices_equal = UBT_ts.index.equals(UBT.index)
print("Are the indices of UBT and UBT_ts equal?", indices_equal)

# Automatically fit an ARIMA model to the time series
arima_model = pm.auto_arima(UBT_ts.dropna(), seasonal=False, stepwise=True, trace=True)
irregular = pd.Series(arima_model.resid(), index=UBT_ts.dropna().index)  # Create a series with the residuals

# Get the series without the irregular component
series_without_irregularities = UBT_ts.loc[irregular.index] - irregular

# Reindex series_without_irregularities to ensure it includes all dates from UBT
series_without_irregularities = series_without_irregularities.reindex(UBT.index, method='ffill')

# Create a DataFrame series_without_irregularities_df
series_without_irregularities_df = pd.DataFrame({
    'Date': UBT.index,
    'series_without_irregularities': series_without_irregularities.values
})

# Check if the dates in series_without_irregularities_df are equal to those in UBT
indices_equal_series_without_irregularities_df = series_without_irregularities_df['Date'].equals(UBT.index)
print("Are the indices of UBT and series_without_irregularities_df equal?", indices_equal_series_without_irregularities_df)

# 3. Create a DataFrame with the original series and the series without irregularities
combined_df = pd.DataFrame({
    'Date': UBT.index,
    'UBT_ts': UBT_ts.values,
    'series_without_irregularities': series_without_irregularities.values
})

# Remove rows with NaN values in the 'series_without_irregularities' column
combined_df = combined_df.dropna(subset=['series_without_irregularities'])

# 4. Fit a linear model and 5. Print the trend equation
# Convert the dates to a numeric format
combined_df['Date_num'] = (combined_df['Date'] - pd.Timestamp('1970-01-01')) / pd.Timedelta(days=1)
trend_model = LinearRegression()
trend_model.fit(combined_df[['Date_num']], combined_df['series_without_irregularities'])
coefficients = trend_model.coef_, trend_model.intercept_
print(f"Trend equation: Value = {coefficients[1]} + {coefficients[0][0]} * t")

# 6. Calculate mean, standard deviation, and confidence limits
mean_val = np.mean(combined_df['series_without_irregularities'])
std_dev = np.std(combined_df['series_without_irregularities'])
upper_limit = mean_val + std_dev
lower_limit = mean_val - std_dev

# 7. Create a chart of the series without irregularities with confidence limits
chart1 = alt.Chart(combined_df.melt('Date', value_vars=['series_without_irregularities'], var_name='series', value_name='value')).mark_line(point=True).encode(
    x='Date:T',
    y='value:Q',
    color='series:N',
    tooltip=['Date', 'value', 'series']
).properties(
    title='Series without Irregularities with Confidence Limits'
).interactive()

# Add horizontal lines for the mean and confidence limits
rule1 = alt.Chart(pd.DataFrame({'y': [mean_val]})).mark_rule(color='red', strokeDash=[5, 5]).encode(
    y='y:Q',
    tooltip=[alt.Tooltip('y:Q', title='Mean')]
)
rule2 = alt.Chart(pd.DataFrame({'y': [upper_limit]})).mark_rule(color='blue', strokeDash=[5, 5]).encode(
    y='y:Q',
    tooltip=[alt.Tooltip('y:Q', title='Upper Limit')]
)
rule3 = alt.Chart(pd.DataFrame({'y': [lower_limit]})).mark_rule(color='blue', strokeDash=[5, 5]).encode(
    y='y:Q',
    tooltip=[alt.Tooltip('y:Q', title='Lower Limit')]
)

chart1 = chart1 + rule1 + rule2 + rule3
chart1.show()  # Show the chart

# 9. Create chart of original and series without irregularities curves
chart2 = alt.Chart(combined_df).mark_line(point=True).encode(
    x='Date:T',
    y=alt.Y('UBT_ts:Q', title='Value'),
    color=alt.value('blue'),
    tooltip=['Date', 'UBT_ts']
).properties(
    title='Original Curve (UBT_ts)'
)

chart3 = alt.Chart(combined_df).mark_line(point=True).encode(
    x='Date:T',
    y=alt.Y('series_without_irregularities:Q', title='Value'),
    color=alt.value('orange'),
    tooltip=['Date', 'series_without_irregularities']
).properties(
    title='Curve without Irregularities'
)

combined_chart = alt.layer(chart2, chart3).resolve_scale(y='independent')
combined_chart.show()  # Show the chart

Key adjustments made:
1- Ensured 'UBT_ts' and 'series_without_irregularities' are reindexed to 'UBT.index' with forward fill ('ffill').
2- Created the DataFrame 'series_without_irregularities_df' directly from 'series_without_irregularities.values'.
3- Confirmed the indices alignment in the DataFrame creation process and during comparisons.
These changes should ensure that the dates align properly across all DataFrames and series.

Be lucky.