问题stmt:根据不同表[INC]中的条件[OP_DIRECTIVE='D']从表[MDF]中删除列。两个表都有相同的列。
我正在寻找Python中的SQL等效查询，以删除一个相同的记录，即使有多个记录符合Delete条件
我在SQL中也写过同样的内容[工作]：-解决方法：将不匹配的行复制到工作表，然后截断原始表并替换为工作表的内容。识别不匹配行的一种方法是用一个唯一的数字标记一组重复行中的每一个输入行，类似于以下内容：
INSERT work_table SELECT MI.col1, MI.col2, ...
FROM
(SELECT M.*,
ROW_NUMBER() OVER (PARTITION BY <join cols> ORDER BY <some col(s)>) AS ROWNUM
FROM MORTALITY M) MI
LEFT JOIN
(SELECT I.*,
ROW_NUMBER() OVER (PARTITION BY <join cols> ORDER BY <some col(s)>) AS ROWNUM
FROM INC_MORTALITY I
WHERE OP_DIRECTIVE='D') INC
ON MI.join_col1 = INC.join_col1
AND MI.join_col2 = INC.join_col2
... all the columns except for 'OP_DIRECTIVE'
AND MI.ROWNUM = INC.ROWNUM
WHERE INC.ROWNUM IS NULL /* "anti-join" keeps only unmatched rows */
;
DELETE FROM MORTALITY;
INSERT MORTALITY SELECT * FROM work_table;
我尝试过的：
import os
import time
import pandas as pd
filePath = '/Users/test_files'
timestr = time.strftime("%Y-%m-%d-%-H%M%S")
fileName = ‘left_join' + timestr + '.txt'
if os.path.exists(filePath):
MDF = pd.read_csv(“mdf.txt", sep='|')
INC = pd.read_csv(“inc.txt”, sep='|')
result = MDF.merge(
INC_D,
on=['data_source','dd_imp_flag','dob','dod','death_verification','gender_probability','gender','token_1','token_2','token_4','token_5','token_7','token_16','token_key'],
how = 'left',
suffixes=('', '_delme'))
cols = result.columns.difference(MDF.columns)
result = result.loc[result[cols].isnull().all(axis=1), MDF.columns.tolist()]
result.to_csv(os.path.join(filePath, fileName), sep="|", index=False) # remove header=None if header is needed
print("Data export successful.")
else:
print("File path does not exist.")
但这删除了所有匹配"D"作为指示符的记录，很明显我这里缺少ROW_NUMBER，所以我想知道如何在python 中实现它
MDF以前
data_source|op_directive|dd_imp_flag|dod|dob|death_verification|gender_probability|gender|token_1|token_2|token_4|token_5|token_7|token_16|token_key
OBIT^SSA|A|1|1931-12-06|1978-03-31|5|0.6735|M|3i5HbesGaxZKeHTAzQkeDskr3YTEyMhcm2zpOQUexog=|UqskLHepjFVSIYGTlpsezOi30eTDh4VrX9H87ynifX6=|6E8hQBwm9Ylwszv6LJwyGN1TF18y8hRubFHe4pLwE03=|SoU4pSpEFZhtUROME0rFwlqnRDb5gfHlcCnlTZLuPQv=|yc499QG3ItyqRtqr8bKFtZ4WRaOBwAZzP5Pmd1ChTUF=|zJBxzxwqZVY66finpsmtRfuzBqeQ2N0FhMGyWmoxB07=|cigna-datavant_TOKEN_ENCRYPTION_KEY
OBIT^SSA|A|1|1931-12-06|1978-03-31|5|0.6735|M|3i5HbesGaxZKeHTAzQkeDskr3YTEyMhcm2zpOQUexog=|UqskLHepjFVSIYGTlpsezOi30eTDh4VrX9H87ynifX6=|6E8hQBwm9Ylwszv6LJwyGN1TF18y8hRubFHe4pLwE03=|SoU4pSpEFZhtUROME0rFwlqnRDb5gfHlcCnlTZLuPQv=|yc499QG3ItyqRtqr8bKFtZ4WRaOBwAZzP5Pmd1ChTUF=|zJBxzxwqZVY66finpsmtRfuzBqeQ2N0FhMGyWmoxB07=|cigna-datavant_TOKEN_ENCRYPTION_KEY
SSA|A|0|1940-12-01|1859-09-01|3|1.0|F|Vznnb7W7VcSvM6bdKbDLyKXcv/UK9FYxfQEWSf7WU1s=|2ye4lajQ4v2lzl5P0sJnUExn8uMMjjWw3vInwUFjx50=|geZFT7Ea5O8rwGwJi17dL9EggYY+ahpfEv5hqz8f/K4=|cT8lopT3v+qvNykrv5N0/hNQdVzEBWt0wz8V01L197Q=|fSkPNkTewOiC+o7ahtH/6YvOx6MJ2Tr36gHyZYBFiNU=|cyusBFir8H19NvWjBYSriCIivL2KVqzFtJkSWSciYFM=|cigna-datavant_TOKEN_ENCRYPTION_KEY
SSA|A|0|1940-12-01|1859-10-01|3|0.0|F|4pxtVDIKcDdiSZqgMNlI5rILQCmm0RhgScJ2E84+BwI=|KyNwahEN6lCvxGBxAOXjYO/QM0Z0QcfI7kPtcEITS4s=|wzyHav4A370qgBk8wPn2AaJyMHMtdFJDCTFhLog9wkI=|hohND7ZFlO9ug14Vei2ESXNy9eqYT47DbiI9J2v+ljQ=|8Plp87L0cC6gdlVbaE0YYzSoe46oIbR/YccdfFGtgd8=|Sb6pUg1X7R7nJONwRrMbWYZ8rMi2TRSkriYHawx2vNE=|cigna-datavant_TOKEN_ENCRYPTION_KEY
SSA|A|0|1940-12-01|1859-10-01|3|0.0|F|4pxtVDIKcDdiSZqgMNlI5rILQCmm0RhgScJ2E84+BwI=|KyNwahEN6lCvxGBxAOXjYO/QM0Z0QcfI7kPtcEITS4s=|wzyHav4A370qgBk8wPn2AaJyMHMtdFJDCTFhLog9wkI=|hohND7ZFlO9ug14Vei2ESXNy9eqYT47DbiI9J2v+ljQ=|8Plp87L0cC6gdlVbaE0YYzSoe46oIbR/YccdfFGtgd8=|Sb6pUg1X7R7nJONwRrMbWYZ8rMi2TRSkriYHawx2vNE=|cigna-datavant_TOKEN_ENCRYPTION_KEY
SSA|A|0|1940-12-01|1859-10-01|3|0.0|F|4pxtVDIKcDdiSZqgMNlI5rILQCmm0RhgScJ2E84+BwI=|KyNwahEN6lCvxGBxAOXjYO/QM0Z0QcfI7kPtcEITS4s=|wzyHav4A370qgBk8wPn2AaJyMHMtdFJDCTFhLog9wkI=|hohND7ZFlO9ug14Vei2ESXNy9eqYT47DbiI9J2v+ljQ=|8Plp87L0cC6gdlVbaE0YYzSoe46oIbR/YccdfFGtgd8=|Sb6pUg1X7R7nJONwRrMbWYZ8rMi2TRSkriYHawx2vNE=|cigna-datavant_TOKEN_ENCRYPTION_KEY
INC
data_source|op_directive|dd_imp_flag|dod|dob|death_verification|gender_probability|gender|token_1|token_2|token_4|token_5|token_7|token_16|token_key
OBIT^SSA|D|1|1931-12-06|1978-03-31|5|0.6735|M|3i5HbesGaxZKeHTAzQkeDskr3YTEyMhcm2zpOQUexog=|UqskLHepjFVSIYGTlpsezOi30eTDh4VrX9H87ynifX6=|6E8hQBwm9Ylwszv6LJwyGN1TF18y8hRubFHe4pLwE03=|SoU4pSpEFZhtUROME0rFwlqnRDb5gfHlcCnlTZLuPQv=|yc499QG3ItyqRtqr8bKFtZ4WRaOBwAZzP5Pmd1ChTUF=|zJBxzxwqZVY66finpsmtRfuzBqeQ2N0FhMGyWmoxB07=|cigna-datavant_TOKEN_ENCRYPTION_KEY
SSA|D|0|1940-12-01|1859-09-01|3|1.0|F|Vznnb7W7VcSvM6bdKbDLyKXcv/UK9FYxfQEWSf7WU1s=|2ye4lajQ4v2lzl5P0sJnUExn8uMMjjWw3vInwUFjx50=|geZFT7Ea5O8rwGwJi17dL9EggYY+ahpfEv5hqz8f/K4=|cT8lopT3v+qvNykrv5N0/hNQdVzEBWt0wz8V01L197Q=|fSkPNkTewOiC+o7ahtH/6YvOx6MJ2Tr36gHyZYBFiNU=|cyusBFir8H19NvWjBYSriCIivL2KVqzFtJkSWSciYFM=|cigna-datavant_TOKEN_ENCRYPTION_KEY
SSA|D|0|1940-12-01|1859-10-01|3|0.0|F|4pxtVDIKcDdiSZqgMNlI5rILQCmm0RhgScJ2E84+BwI=|KyNwahEN6lCvxGBxAOXjYO/QM0Z0QcfI7kPtcEITS4s=|wzyHav4A370qgBk8wPn2AaJyMHMtdFJDCTFhLog9wkI=|hohND7ZFlO9ug14Vei2ESXNy9eqYT47DbiI9J2v+ljQ=|8Plp87L0cC6gdlVbaE0YYzSoe46oIbR/YccdfFGtgd8=|Sb6pUg1X7R7nJONwRrMbWYZ8rMi2TRSkriYHawx2vNE=|cigna-datavant_TOKEN_ENCRYPTION_KEY
更新预期输出后的MDF
data_source|op_directive|dd_imp_flag|dod|dob|death_verification|gender_probability|gender|token_1|token_2|token_4|token_5|token_7|token_16|token_key
OBIT^SSA|A|1|1931-12-06|1978-03-31|5|0.6735|M|3i5HbesGaxZKeHTAzQkeDskr3YTEyMhcm2zpOQUexog=|UqskLHepjFVSIYGTlpsezOi30eTDh4VrX9H87ynifX6=|6E8hQBwm9Ylwszv6LJwyGN1TF18y8hRubFHe4pLwE03=|SoU4pSpEFZhtUROME0rFwlqnRDb5gfHlcCnlTZLuPQv=|yc499QG3ItyqRtqr8bKFtZ4WRaOBwAZzP5Pmd1ChTUF=|zJBxzxwqZVY66finpsmtRfuzBqeQ2N0FhMGyWmoxB07=|cigna-datavant_TOKEN_ENCRYPTION_KEY
SSA|A|0|1940-12-01|1859-10-01|3|0.0|F|4pxtVDIKcDdiSZqgMNlI5rILQCmm0RhgScJ2E84+BwI=|KyNwahEN6lCvxGBxAOXjYO/QM0Z0QcfI7kPtcEITS4s=|wzyHav4A370qgBk8wPn2AaJyMHMtdFJDCTFhLog9wkI=|hohND7ZFlO9ug14Vei2ESXNy9eqYT47DbiI9J2v+ljQ=|8Plp87L0cC6gdlVbaE0YYzSoe46oIbR/YccdfFGtgd8=|Sb6pUg1X7R7nJONwRrMbWYZ8rMi2TRSkriYHawx2vNE=|cigna-datavant_TOKEN_ENCRYPTION_KEY
SSA|A|0|1940-12-01|1859-10-01|3|0.0|F|4pxtVDIKcDdiSZqgMNlI5rILQCmm0RhgScJ2E84+BwI=|KyNwahEN6lCvxGBxAOXjYO/QM0Z0QcfI7kPtcEITS4s=|wzyHav4A370qgBk8wPn2AaJyMHMtdFJDCTFhLog9wkI=|hohND7ZFlO9ug14Vei2ESXNy9eqYT47DbiI9J2v+ljQ=|8Plp87L0cC6gdlVbaE0YYzSoe46oIbR/YccdfFGtgd8=|Sb6pUg1X7R7nJONwRrMbWYZ8rMi2TRSkriYHawx2vNE=|cigna-datavant_TOKEN_ENCRYPTION_KEY
在评论和句子中进行解释"警告：请确保您只删除或标记为已删除一条记录，即使有多条历史记录与此新删除记录完全匹配">，我相信实现结果的一个简单方法是使用duplicated，将所有重复的行标记为True，开始第二个重复的
result.loc[result[cols].isnull().all(axis=1)
|result.duplicated(subset=MDF.columns, keep='first'), # add this condition
MDF.columns.tolist()]