Pandas и SQL по-разному суммируют суммы в долларах ⇐ Python

[Anonymous]

Я пытаюсь воссоздать SQL-запрос, который я написал с использованием Python и Pandas. Я не очень хорошо знаком с SQL, поэтому может быть что-то очевидное, что я упускаю. Я просто пытаюсь суммировать строки, где «вопросом» является «Сумма в долларах», а затем группировать по поддоменам. Но я продолжаю получать совершенно разные результаты. Я использую MySql.
Запросы SQL: (они оба приводят к одному и тому же результату ниже)
SELECT subdomain,
SUM(CAST(REPLACE(answer, '$', '') AS DECIMAL(10, 2))) AS amounts
FROM (
SELECT subdomain, answer, entry_date
FROM assessments AS a
WHERE question = 'Dollar Amount'
AND answer IS NOT NULL
AND answer != ''
AND DATE(entry_date) BETWEEN '2024-07-01' AND '2024-09-30'
AND entry_date = (
SELECT MAX(entry_date)
FROM assessments
WHERE form_submission_id = a.form_submission_id
)
) AS latest_entries
GROUP BY subdomain
ORDER BY amounts DESC;

SELECT subdomain, SUM(CAST(REPLACE(answer, '$', '') AS DECIMAL(10, 2))) AS amounts FROM assessments
WHERE form_name LIKE '%-support-services-fund%'
AND question = 'Dollar Amount'
AND answer IS NOT NULL
AND answer != ''
AND DATE(entry_date) BETWEEN '2024-07-01' AND '2024-09-30'
GROUP BY subdomain
ORDER BY amounts DESC;

Первые 5 строк вывода:
subdomain 1,82263.89
subdomain 2,74560.26
subdomain 3,28501.65
subdomain 4,8764.40
subdomain 5,8493.30

Скрипт Python
from sqlalchemy import create_engine
import pandas as pd

def create_db_engine():
# Database connection details
user =
password =
host =
port = "3306"
database =

# Local SSL file paths
ssl_args = {
"ssl": {
"ca": ,
"cert": ,
"key": ,
}
}

# Create engine with SSL connection
engine = create_engine(
f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}",
connect_args=ssl_args
)

return engine

engine = create_db_engine()

table_df = pd.read_sql_table(
"assessments",
con=engine,
columns=[ # a list of all column names as strings (I'm redacting this)
],)

ssf = table_df[table_df['form_name'].str.contains('support-services-fund')]

date_1 = '2024-07-01'
date_2 = '2024-09-30'
date_restricted_ssf = ssf[ssf['entry_date'].between(date_1, date_2)]

# Filter strictly for rows where 'question' matches 'Dollar Amount' exactly
ssf_filtered = date_restricted_ssf[date_restricted_ssf['question'] == 'Dollar Amount']

# Convert answer values to numeric, ensuring only relevant rows are processed
ssf_filtered['amounts'] = pd.to_numeric(ssf_filtered['answer'].str.replace('$', '', regex=True), errors='coerce')

# Group by 'subdomain' and sum the amounts
grouped_df = ssf_filtered.groupby('subdomain', as_index=False)['amounts'].sum()

# Sort the results by 'amounts' in descending order
result_df = grouped_df.sort_values(by='amounts', ascending=False)

# Display the results
print(result_df)


Первые 5 строк вывода Python:
subdomain amounts
1 subdomain 1 66734.00
6 subdomain 2 61436.02
8 subdomain 3 28501.65
3 subdomain 5 8053.67
5 subdomain 6 5739.55


Подробнее здесь: https://stackoverflow.com/questions/791 ... ifferently