python 将两个 Dataframe 与两个条件列合并

cunj1qz1  于 2023-02-02  发布在  Python
关注(0)|答案(2)|浏览(144)

我有两个表（A和B），我想从表B中相同ID的同一财政年度的最新data_date中获取每个ID在每个财政年度中的最新data_date的值。我在第三个表中有结果。我正在尝试用python编写此代码
表A

  1. ID          data_date   fiscal_year
  2. A           2016-03-31  2016          
  3. A           2016-03-31  2016      
  4. A           2018-09-31  2018            
  5. B           2017-06-30  2017         
  6. B           2017-09-30  2017     
  7. B           2018-06-30  2018     
  8. C           2013-03-31  2013

表B

  1. ID          data_date   Value
  2. A           2015-12-31     1   
  3. A           2016-12-31     4  
  4. A           2018-03-30     85           
  5. B           2015-12-31     7  
  6. B           2016-12-31     14   
  7. B           2017-12-31     12 
  8. C           2013-03-30     45 
  9. C           2013-12-31     9  
  10. C           2014-12-31     64 
  11. C           2015-12-31     25

结果

  1. ID          data_date   fiscal_year Value
  2. A           2016-03-31  2016          4
  3. A           2016-03-31  2016          4
  4. A           2018-09-31  2018          85 [85 is the value of the latest date of B in 2018
  5. B           2017-06-30  2017          NA [It is not the latest data date in 2017 for B]
  6. B           2017-09-30  2017          12
  7. B           2018-06-30  2018          NA [There is no value for B in 2018]  
  8. C           2013-03-31  2013          9
python

2条答案

按热度按时间
bnlyeluc

bnlyeluc1#

IIUC，使用带有forward * 方向 * 的merge_asof。
假设（df1）和（df2）是您的两个表/ Dataframe ，您可以使用以下命令：

  1. df1["data_date"] = pd.to_datetime(df1["data_date"])
  2. df2["data_date"] = pd.to_datetime(df2["data_date"])
  3. df1 = df_A.sort_values(by="data_date")
  4. df2 = df_B.sort_values(by="data_date")
  5. ​
  6. out = (pd.merge_asof(df1, df2, on="data_date", by="ID",
  7.                     allow_exact_matches=False, direction="forward")
  8.            .sort_values(by="ID", ignore_index=True))

输出：

  1. print(out)
  2. ​
  3.   ID  data_date  fiscal_year  Value
  4. 0  A 2016-03-31         2016    4.0
  5. 1  A 2016-03-31         2016    4.0
  6. 2  A 2018-09-30         2018    NaN
  7. 3  B 2017-06-30         2017   12.0
  8. 4  B 2017-09-30         2017   12.0
  9. 5  B 2018-06-30         2018    NaN
  10. 6  C 2013-03-31         2013    9.0
展开查看全部
赞(0）分享  回复(0）举报  2023-02-02
7xzttuei

7xzttuei2#

要获得相同的输出

1.如果数据使用字典

  1. dataDictA = [
  2. #ID          data_date   fiscal_year
  3. {"ID": "A", "data_date": "2016-03-31", "fiscal_year": 2016},
  4. {"ID": "A", "data_date": "2016-03-31", "fiscal_year": 2016},
  5. {"ID": "A", "data_date": "2018-09-31", "fiscal_year": 2018},
  6. {"ID": "B", "data_date": "2017-06-30", "fiscal_year": 2017},
  7. {"ID": "B", "data_date": "2017-09-30", "fiscal_year": 2017},
  8. {"ID": "B", "data_date": "2018-06-30", "fiscal_year": 2018},
  9. {"ID": "C", "data_date": "2013-03-31", "fiscal_year": 2013},
  10. ]
  12. dataDictB = [
  13. #ID          data_date   Value
  14. {"ID": "A", "data_date": "2015-12-31", "Value": 1},
  15. {"ID": "A", "data_date": "2016-12-31", "Value": 4},
  16. {"ID": "A", "data_date": "2018-03-30", "Value": 85},
  17. {"ID": "B", "data_date": "2015-12-31", "Value": 7},
  18. {"ID": "B", "data_date": "2016-12-31", "Value": 14},
  19. {"ID": "B", "data_date": "2017-12-31", "Value": 12},
  20. {"ID": "C", "data_date": "2013-03-30", "Value": 45},
  21. {"ID": "C", "data_date": "2013-12-31", "Value": 9},
  22. {"ID": "C", "data_date": "2014-12-31", "Value": 64},
  23. {"ID": "C", "data_date": "2015-12-31", "Value": 25},
  24. ]
  26. def mergeDict(a,b):
  27.     res = []
  28.     for listA in a:
  29.         year = listA["fiscal_year"]# or listA["data_date"].split("-")[0]
  30.         tick = dateTick(listA["data_date"])
  32.         latest = 0
  33.         latestVal = "NA"
  34.         
  35.         #is it the latest date?
  36.         previous = False
  37.         for listA2 in a:
  38.             if listA["ID"]==listA2["ID"]:#same ID
  39.                 if listA2["fiscal_year"] == year:#same year
  40.                     if tick<dateTick(listA2["data_date"]):#not the latest
  41.                         previous = True
  42.                         break
  43.         if previous:
  44.             res.append(listA)
  45.             res[-1]["Value"] = "NA"
  46.             res[-1]["Reason"] = f"-not the lastest data in {year} for {listA2['ID']}"
  47.             continue#skip if not the latest
  48.         
  49.         #check latest in dataB
  50.         for listB in b:
  51.             if listA["ID"]==listB["ID"]:#same ID
  52.                 if int(listB["data_date"].split("-")[0]) == year:#same year
  53.                     tickB = dateTick(listB["data_date"])
  54.                     if latest<tick:#get the latest value (compared in seconds)
  55.                         latest = tickB
  56.                         latestVal = listB["Value"]
  57.         
  58.         res.append(listA)
  59.         res[-1]["Value"] = latestVal
  60.         if latestVal == "NA":
  61.             res[-1]["Reason"] = f"-no value for {listA['ID']} in {year}"
  62.     return res
  64. for v in mergeDict(dataDictA,dataDictB):
  65.     print(v)

输出：

  1. {'ID': 'A', 'data_date': '2016-03-31', 'fiscal_year': 2016, 'Value': 4}
  2. {'ID': 'A', 'data_date': '2016-03-31', 'fiscal_year': 2016, 'Value': 4}
  3. {'ID': 'A', 'data_date': '2018-09-31', 'fiscal_year': 2018, 'Value': 85}
  4. {'ID': 'B', 'data_date': '2017-06-30', 'fiscal_year': 2017, 'Value': 'NA', 'Reason': '-not the lastest data in 2017 for B'}
  5. {'ID': 'B', 'data_date': '2017-09-30', 'fiscal_year': 2017, 'Value': 12}
  6. {'ID': 'B', 'data_date': '2018-06-30', 'fiscal_year': 2018, 'Value': 'NA', 'Reason': '-no value for B in 2018'}
  7. {'ID': 'C', 'data_date': '2013-03-31', 'fiscal_year': 2013, 'Value': 9}

2.如果数据使用数组

  1. dataA = [
  2. #ID          data_date   fiscal_year
  3. ["A", "2016-03-31", 2016],
  4. ["A", "2016-03-31", 2016],
  5. ["A", "2018-09-31", 2018],
  6. ["B", "2017-06-30", 2017],
  7. ["B", "2017-09-30", 2017],
  8. ["B", "2018-06-30", 2018],
  9. ["C", "2013-03-31", 2013],
  10. ]
  12. dataB = [
  13. #ID          data_date   Value
  14. ["A", "2015-12-31", 1],
  15. ["A", "2016-12-31", 4],
  16. ["A", "2018-03-30", 85],
  17. ["B", "2015-12-31", 7],
  18. ["B", "2016-12-31", 14],
  19. ["B", "2017-12-31", 12],
  20. ["C", "2013-03-30", 45],
  21. ["C", "2013-12-31", 9],
  22. ["C", "2014-12-31", 64],
  23. ["C", "2015-12-31", 25],
  24. ]
  26. #date to seconds
  27. def dateTick(date):
  28.     tick = date.split("-")
  29.     return int(tick[0])*31536000+int(tick[1])*2628000+int(tick[2])*86400
  31. def merge(a,b):
  32.     res = []
  33.     for listA in a:
  34.         year = listA[2]# or listA[1].split("-")[0]
  35.         tick = dateTick(listA[1])
  37.         latest = 0
  38.         latestVal = ["NA",f"-no value for {listA[0]} in {year}"]
  39.         
  40.         #is it the latest date?
  41.         previous = False
  42.         for listA2 in a:
  43.             if listA[0]==listA2[0]:#same ID
  44.                 if listA2[2] == year:#same year
  45.                     if tick<dateTick(listA2[1]):#not the latest
  46.                         previous = True
  47.                         break
  48.         if previous:
  49.             res.append(listA+["NA",f"-not the lastest data in {year} for {listA2[0]}"])
  50.             continue#skip if not the latest
  51.         
  52.         #check latest in dataB
  53.         for listB in b:
  54.             if listA[0]==listB[0]:#same ID
  55.                 if int(listB[1].split("-")[0]) == year:#same year
  56.                     tickB = dateTick(listB[1])
  57.                     if latest<tick:#get the latest value (compared in seconds)
  58.                         latest = tickB
  59.                         latestVal = [listB[2]]
  60.         
  61.         res.append(listA+latestVal)
  62.     return res
  64. for v in merge(dataA,dataB):
  65.     print(v)

输出：

  1. ['A', '2016-03-31', 2016, 4]
  2. ['A', '2016-03-31', 2016, 4]
  3. ['A', '2018-09-31', 2018, 85]
  4. ['B', '2017-06-30', 2017, 'NA', '-not the lastest data in 2017 for B']
  5. ['B', '2017-09-30', 2017, 12]
  6. ['B', '2018-06-30', 2018, 'NA', '-no value for B in 2018']
  7. ['C', '2013-03-31', 2013, 9]
展开查看全部
赞(0）分享  回复(0）举报  2023-02-02
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com