合并两个或多个表，使用Python检查唯一值。

huangapple
go评论126阅读模式
英文:

Merge two or more tables checking the unique value using python

问题

以下是翻译好的部分：

  1. 我是Python的初学者。
  2. 我必须检查2或3个表格，看看是否有一些数据缺失。
  3. 这些数据是`pandas.core.frame.DataFrame`。
  4. 我有这些表格：
  6.     start	end	val	accn	fy	fp	form	filed	frame
  7.     0	2016-01-01	2016-12-31	90272000000	0001652044-19-000004	2018	FY	10-K	2019-02-05	CY2016
  8.     6	2017-01-01	2017-12-31	110855000000	0001652044-19-000004	2018	FY	10-K	2019-02-05	NaN
  9.     7	2017-01-01	2017-12-31	110855000000	0001652044-20-000008	2019	FY	10-K	2020-02-04	CY2017
  10.     18	2018-01-01	2018-12-31	136819000000	0001652044-19-000004	2018	FY	10-K	2019-02-05	NaN
  11.     19	2018-01-01	2018-12-31	136819000000	0001652044-20-000008	2019	FY	10-K	2020-02-04	CY2018
  12.     30	2019-01-01	2019-12-31	161857000000	0001652044-20-000008	2019	FY	10-K	2020-02-04	CY2019
  13.     41	2020-01-01	2020-12-31	182527000000	0001652044-23-000016	2022	FY	10-K	2023-02-03	CY2020
  14.     52	2021-01-01	2021-12-31	257637000000	0001652044-23-000016	2022	FY	10-K	2023-02-03	CY2021
  15.     61	2022-01-01	2022-12-31	282836000000	0001652044-23-000016	2022	FY	10-K	2023-02-03	CY2022
  17. 和
  19.     start	end	val	accn	fy	fp	form	filed	frame
  20.     0	2006-12-31	2007-12-29	39474000000	0001193125-10-036385	2009	FY	10-K	2010-02-22	CY2007
  21.     4	2007-12-30	2008-12-27	43251000000	0001193125-11-040427	2010	FY	10-K	2011-02-18	NaN
  22.     5	2007-12-30	2008-12-27	43251000000	0001193125-10-036385	2009	FY	10-K	2010-02-22	NaN
  23.     13	2008-12-28	2009-12-26	43232000000	0001193125-12-081822	2011	FY	10-K	2012-02-27	CY2009
  24.     15	2008-12-28	2009-12-26	43232000000	0001193125-11-040427	2010	FY	10-K	2011-02-18	NaN
  25.     16	2008-12-28	2009-12-26	43232000000	0001193125-10-036385	2009	FY	10-K	2010-02-22	NaN
  26.     27	2009-12-27	2010-12-25	57838000000	0001445305-13-000278	2012	FY	10-K	2013-02-21	CY2010
  27.     28	2009-12-27	2010-12-25	57838000000	0001193125-12-081822	2011	FY	10-K	2012-02-27	NaN
  28.     30	2009-12-27	2010-12-25	57838000000	0001193125-11-040427	2010	FY	10-K	2011-02-18	NaN
  29.     41	2010-12-26	2011-12-31	66504000000	0001445305-13-000278	2012	FY	10-K	2013-02-21	NaN
  30.     42	2010-12-26	2011-12-31	66504000000	0001193125-12-081822	2011	FY	10-K	2012-02-27	NaN
  31.     43	2010-12-26	2011-12-31	66504000000	0000077476-14-000007	2013	FY	10-K	2014-02-14	CY2011
  32.     54	2012-01-01	2012-12-29	65492000000	0001445305-13-000278	2012	FY	10-K	2013-02-21	NaN
  33.     56	2012-01-01	2012-12-29	65492000000	0000077476-15-000012	2014	FY	10-K	2015-02-12	NaN
  34.     57	2012-01-01	2012-12-29	65492000000	0000077476-14-000007	2013	FY	10-K	2014-02-14	NaN
  35.     68	2012-12-30	2013-12-28	66415000000	0000077476-16-000066	2015	FY	10-K	2016-02-11	CY2013
  36.     70	2012-12-30	2013-12-28	66415000000	0000077476-15-000012	2014	FY	10-K	2015-02-12	NaN
  37.     71	2012-12-30	2013-12-28	66415000000	0000077476-14-000007	2013	FY	10-K	2014-02-14	NaN
  38.     82	2013-12-29	2014-12-27	66683000000	0000077476-17-000010	2016	FY	10-K	2017-02-15	CY2014
  39.     83	2013-12-29	2014
  41. <details>
  42. <summary>英文:</summary>
  44. I am a beginner with Python.
  45. I have to check 2 or 3 tables and see if some data is missing.
  46. The data are `pandas.core.frame.DataFrame`.
  47. I have these tables:
  49.     start	end	val	accn	fy	fp	form	filed	frame
  50.     0	2016-01-01	2016-12-31	90272000000	0001652044-19-000004	2018	FY	10-K	2019-02-05	CY2016
  51.     6	2017-01-01	2017-12-31	110855000000	0001652044-19-000004	2018	FY	10-K	2019-02-05	NaN
  52.     7	2017-01-01	2017-12-31	110855000000	0001652044-20-000008	2019	FY	10-K	2020-02-04	CY2017
  53.     18	2018-01-01	2018-12-31	136819000000	0001652044-19-000004	2018	FY	10-K	2019-02-05	NaN
  54.     19	2018-01-01	2018-12-31	136819000000	0001652044-20-000008	2019	FY	10-K	2020-02-04	CY2018
  55.     30	2019-01-01	2019-12-31	161857000000	0001652044-20-000008	2019	FY	10-K	2020-02-04	CY2019
  56.     41	2020-01-01	2020-12-31	182527000000	0001652044-23-000016	2022	FY	10-K	2023-02-03	CY2020
  57.     52	2021-01-01	2021-12-31	257637000000	0001652044-23-000016	2022	FY	10-K	2023-02-03	CY2021
  58.     61	2022-01-01	2022-12-31	282836000000	0001652044-23-000016	2022	FY	10-K	2023-02-03	CY2022
  60. and 
  62.     start	end	val	accn	fy	fp	form	filed	frame
  63.     0	2006-12-31	2007-12-29	39474000000	0001193125-10-036385	2009	FY	10-K	2010-02-22	CY2007
  64.     4	2007-12-30	2008-12-27	43251000000	0001193125-11-040427	2010	FY	10-K	2011-02-18	NaN
  65.     5	2007-12-30	2008-12-27	43251000000	0001193125-10-036385	2009	FY	10-K	2010-02-22	NaN
  66.     13	2008-12-28	2009-12-26	43232000000	0001193125-12-081822	2011	FY	10-K	2012-02-27	CY2009
  67.     15	2008-12-28	2009-12-26	43232000000	0001193125-11-040427	2010	FY	10-K	2011-02-18	NaN
  68.     16	2008-12-28	2009-12-26	43232000000	0001193125-10-036385	2009	FY	10-K	2010-02-22	NaN
  69.     27	2009-12-27	2010-12-25	57838000000	0001445305-13-000278	2012	FY	10-K	2013-02-21	CY2010
  70.     28	2009-12-27	2010-12-25	57838000000	0001193125-12-081822	2011	FY	10-K	2012-02-27	NaN
  71.     30	2009-12-27	2010-12-25	57838000000	0001193125-11-040427	2010	FY	10-K	2011-02-18	NaN
  72.     41	2010-12-26	2011-12-31	66504000000	0001445305-13-000278	2012	FY	10-K	2013-02-21	NaN
  73.     42	2010-12-26	2011-12-31	66504000000	0001193125-12-081822	2011	FY	10-K	2012-02-27	NaN
  74.     43	2010-12-26	2011-12-31	66504000000	0000077476-14-000007	2013	FY	10-K	2014-02-14	CY2011
  75.     54	2012-01-01	2012-12-29	65492000000	0001445305-13-000278	2012	FY	10-K	2013-02-21	NaN
  76.     56	2012-01-01	2012-12-29	65492000000	0000077476-15-000012	2014	FY	10-K	2015-02-12	NaN
  77.     57	2012-01-01	2012-12-29	65492000000	0000077476-14-000007	2013	FY	10-K	2014-02-14	NaN
  78.     68	2012-12-30	2013-12-28	66415000000	0000077476-16-000066	2015	FY	10-K	2016-02-11	CY2013
  79.     70	2012-12-30	2013-12-28	66415000000	0000077476-15-000012	2014	FY	10-K	2015-02-12	NaN
  80.     71	2012-12-30	2013-12-28	66415000000	0000077476-14-000007	2013	FY	10-K	2014-02-14	NaN
  81.     82	2013-12-29	2014-12-27	66683000000	0000077476-17-000010	2016	FY	10-K	2017-02-15	CY2014
  82.     83	2013-12-29	2014-12-27	66683000000	0000077476-16-000066	2015	FY	10-K	2016-02-11	NaN
  83.     85	2013-12-29	2014-12-27	66683000000	0000077476-15-000012	2014	FY	10-K	2015-02-12	NaN
  84.     96	2014-12-28	2015-12-26	63056000000	0000077476-18-000012	2017	FY	10-K	2018-02-13	CY2015
  85.     97	2014-12-28	2015-12-26	63056000000	0000077476-17-000010	2016	FY	10-K	2017-02-15	NaN
  86.     98	2014-12-28	2015-12-26	63056000000	0000077476-16-000066	2015	FY	10-K	2016-02-11	NaN
  87.     109	2015-12-27	2016-12-31	62799000000	0000077476-18-000012	2017	FY	10-K	2018-02-13	CY2016
  88.     110	2015-12-27	2016-12-31	62799000000	0000077476-17-000010	2016	FY	10-K	2017-02-15	NaN
  89.     117	2017-01-01	2017-12-30	63525000000	0000077476-18-000012	2017	FY	10-K	2018-02-13	CY2017
  91. So the target is to check and merge the data from all years.
  92. So basically I have to check the 2 tables and create a new table with all CY Years.
  93. For example the first table start from 2016, but the second from 2007. So I need to create a table with `val` and `frame` merged and unique values. The table basically should have all years found from an API.
  95. Example result:
  97.     39474000000 2007
  98.     43232000000  2009
  99.     57838000000 2010
  100.     ...
  101.     110855000000     2017
  102.     etc etc
  103. There is a thing I have to check if both tables contain data for `the same year`(example `2017`), then I have to check which `val` is bigger and insert this in the new table.
  104. How can  I do an iter for loo year by year and check the val value between the two tables?
  107. </details>
  110. # 答案1
  111. **得分**: 1
  113. 如果您想忽略`frame`列中包含`NaN`的行，您可以在应用`max`之前，简单地连接您过滤后的数据框并按`frame`进行分组：
  115. ```python
  116. df1 = df1.dropna(subset='frame')[['val', 'frame']]
  117. df2 = df2.dropna(subset='frame')[['val', 'frame']]
  119. pd.concat([
  120.     df1[~df1['frame'].str.contains('Q')],
  121.     df2[~df2['frame'].str.contains('Q')]
  122. ]).groupby('frame').max()

输出：

  1.                  val
  2. frame
  3. CY2007   39474000000
  4. CY2009   43232000000
  5. CY2010   57838000000
  6. CY2011   66504000000
  7. CY2013   66415000000
  8. CY2014   66683000000
  9. CY2015   63056000000
  10. CY2016   90272000000
  11. CY2017  110855000000
  12. CY2018  136819000000
  13. CY2019  161857000000
  14. CY2020  182527000000
  15. CY2021  257637000000
  16. CY2022  282836000000

编辑：不包括frame单元格中包含'Q'的行。

英文:

If you want to ignore rows where frame is NaN, you can simply concat your filtered dfs and group by frame before applying max:

  1. df1 = df1.dropna(subset=&#39;frame&#39;)[[&#39;val&#39;, &#39;frame&#39;]]
  2. df2 = df2.dropna(subset=&#39;frame&#39;)[[&#39;val&#39;, &#39;frame&#39;]]
  4. pd.concat([
  5.     df1[~df1[&#39;frame&#39;].str.contains(&#39;Q&#39;)], 
  6.     df2[~df2[&#39;frame&#39;].str.contains(&#39;Q&#39;)]
  7. ]).groupby(&#39;frame&#39;).max()

Output:

  1.                  val
  2. frame
  3. CY2007   39474000000
  4. CY2009   43232000000
  5. CY2010   57838000000
  6. CY2011   66504000000
  7. CY2013   66415000000
  8. CY2014   66683000000
  9. CY2015   63056000000
  10. CY2016   90272000000
  11. CY2017  110855000000
  12. CY2018  136819000000
  13. CY2019  161857000000
  14. CY2020  182527000000
  15. CY2021  257637000000
  16. CY2022  282836000000

Edit: excluding frame cells containing &#39;Q&#39;.

huangapple
  • 本文由 发表于 2023年8月10日 20:22:33
  • 转载请务必保留本文链接：https://go.coder-hub.com/76875706.html
  • for-loop
  • pandas
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定