在pandas计算中出现错误。

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

Getting error in the calculation in pandas

问题

I am getting calculation errors while writing the groupby function with aggregate function in a loop. But, outside the loop everything is okay. Getting the results correctly...!

  1. import pandas as pd
  2. import numpy as np
  4. # Example DataFrame
  5. df = pd.DataFrame({
  6.     'GroupA': ['A', 'A', 'B', 'B', 'B', 'C'],
  7.     'GroupB': ['X', 'Y', 'Z', 'X', 'Y', 'X'],
  8.     'POP': [10, 20, 30, 40, 50, 60],
  9.     'LF': [1, 2, 3, 4, 5, 6],
  10.     'WRK': [100, 200, 300, 400, 500, 600]
  11. })
  13. groupby_cols = [[], ['GroupA'], ['GroupB'], ['GroupA', 'GroupB']]
  15. def test(df, gby):
  16.     # Perform groupby and aggregation
  17.     groupby_columns = groupby_cols[gby]
  18.     w2 = df.groupby(groupby_columns).agg(
  19.         pophat=('POP', lambda x: np.sum(x * df['CMULT'])),
  20.         lfhat=('LF', lambda x: np.sum(x * df['CMULT'])),
  21.         wrkhat=('WRK', lambda x: np.sum(x * df['CMULT']))
  22.     ).reset_index()
  24.     # Calculate CMULT column based on the current groupby configuration
  25.     if len(groupby_columns) == 1:
  26.         w2['CMULT'] = w2[groupby_columns[0]].map({'A': 0.5, 'B': 0.3, 'C': 0.2})
  27.     else:
  28.         w2['CMULT'] = w2['GroupA'].map({'A': 0.5, 'B': 0.3, 'C': 0.2})
  30.     print(w2, groupby_columns)
  32. for i in range(len(groupby_cols)):
  33.     if i == 0:
  34.         df['CMULT'] = df['GroupA'].map({'A': 0.5, 'B': 0.3, 'C': 0.2})
  35.         df['POP'] = pd.to_numeric(df['POP']) * df['CMULT']
  36.         df['LF'] = pd.to_numeric(df['LF']) * df['CMULT']
  37.         df['WRK'] = pd.to_numeric(df['WRK']) * df['CMULT']
  38.         df['no_sam'] = df.shape[0]
  40.         agg_dict = {'POP': 'sum', 'LF': 'sum', 'WRK': 'sum', 'no_sam': 'count'}
  41.         # Group the data by the current groupby configuration and calculate the aggregates
  42.         w2 = df.agg(agg_dict).to_frame().T
  44.         print(w2, groupby_cols[i])
  46.     else:
  47.         test(df, i)

This is the code, in which I am getting calculation errors. The results are -

  1. POP   LF    WRK  no_sam
  2. 0  63.0  6.3  630.0     6.0 []
  3.   GroupA  pophat  lfhat  wrkhat  CMULT
  4. 0      A     7.5   0.75    75.0    0.5
  5. 1      B    10.8   1.08   108.0    0.3
  6. 2      C     2.4   0.24    24.0    0.2 ['GroupA']
  7.   GroupB  pophat  lfhat  wrkhat  CMULT
  8. 0      X     8.5   0.85    85.0    NaN
  9. 1      Y     9.5   0.95    95.0    NaN
  10. 2      Z     2.7   0.27    27.0    NaN ['GroupB']
  11.   GroupA GroupB  pophat  lfhat  wrkhat  CMULT
  12. 0      A      X     2.5   0.25    25.0    0.5
  13. 1      A      Y     5.0   0.50    50.0    0.5
  14. 2      B      X     3.6   0.36    36.0    0.3
  15. 3      B      Y     4.5   0.45    45.0    0.3
  16. 4      B      Z     2.7   0.27    27.0    0.3
  17. 5      C      X     2.4   0.24    24.0    0.2 ['GroupA', 'GroupB']

But, outside the loop the results are - (You can verify it by changing the index of groupby_cols[NNNNNNNNN])

  1. import pandas as pd
  2. import numpy as np
  4. # Example DataFrame
  5. df = pd.DataFrame({
  6.     'GroupA': ['A', 'A', 'B', 'B', 'B', 'C'],
  7.     'GroupB': ['X', 'Y', 'Z', 'X', 'Y', 'X'],
  8.     'POP': [10, 20, 30, 40, 50, 60],
  9.     'LF': [1, 2, 3, 4, 5, 6],
  10.     'WRK': [100, 200, 300, 400, 500, 600]
  11. })
  13. groupby_cols = [[], ['GroupA'], ['GroupB'], ['GroupA', 'GroupB']]
  15. df['CMULT'] = df.groupby(groupby_cols[i])['GroupA'].transform(lambda x: x.map({'A': 0.5, 'B': 0.3, 'C': 0.2}))
  17. # Perform groupby and aggregation based on the current groupby configuration
  19. w2 = df.groupby(groupby_cols[3]).agg(
  20.     pophat=('POP', lambda x: np.sum(x * df['CMULT'])),
  21.     lfhat=('LF', lambda x: np.sum(x * df['CMULT'])),
  22.     wrkhat=('WRK', lambda x: np.sum(x * df['CMULT']))
  23. ).reset_index()
  25. print(w2)

The results outside the loop are -

  1.      POP   LF    WRK  no_sam
  2. 0  63.0  6.3  630.0     6.0
  3.   GroupA  pophat  lfhat  wrkhat
  4. 0      A    15.0    1.5   150.0
  5. 1      B    36.0    3.6   360.0
  6. 2      C    12.0    1.2   120.0
  7.   GroupB  pophat  lfhat  wrkhat
  8. 0      X    29.0    2.9   290.0
  9. 1      Y    25.0    2.5   250.0
  10. 2      Z     9.0
  12. <details>
  13. <summary>英文:</summary>
  15. I am getting calculation errors while writing the groupby function with aggregate function in a loop.But, outside the loop everything is okay. Getting the results correctly...!
  17.     import pandas as pd
  18.     import numpy as np
  19.     
  20.     # Example DataFrame
  21.     df = pd.DataFrame({
  22.         &#39;GroupA&#39;: [&#39;A&#39;, &#39;A&#39;, &#39;B&#39;, &#39;B&#39;, &#39;B&#39;, &#39;C&#39;],
  23.         &#39;GroupB&#39;: [&#39;X&#39;, &#39;Y&#39;, &#39;Z&#39;, &#39;X&#39;, &#39;Y&#39;, &#39;X&#39;],
  24.         &#39;POP&#39;: [10, 20, 30, 40, 50, 60],
  25.         &#39;LF&#39;: [1, 2, 3, 4, 5, 6],
  26.         &#39;WRK&#39;: [100, 200, 300, 400, 500, 600]
  27.     })
  28.     
  29.     groupby_cols = [[], [&#39;GroupA&#39;], [&#39;GroupB&#39;], [&#39;GroupA&#39;, &#39;GroupB&#39;]]
  30.     
  31.     def test(df, gby):
  32.         # Perform groupby and aggregation
  33.         groupby_columns = groupby_cols[gby]
  34.         w2 = df.groupby(groupby_columns).agg(
  35.             pophat=(&#39;POP&#39;, lambda x: np.sum(x * df[&#39;CMULT&#39;])),
  36.             lfhat=(&#39;LF&#39;, lambda x: np.sum(x * df[&#39;CMULT&#39;])),
  37.             wrkhat=(&#39;WRK&#39;, lambda x: np.sum(x * df[&#39;CMULT&#39;]))
  38.         ).reset_index()
  39.     
  40.         # Calculate CMULT column based on the current groupby configuration
  41.         if len(groupby_columns) == 1:
  42.             w2[&#39;CMULT&#39;] = w2[groupby_columns[0]].map({&#39;A&#39;: 0.5, &#39;B&#39;: 0.3, &#39;C&#39;: 0.2})
  43.         else:
  44.             w2[&#39;CMULT&#39;] = w2[&#39;GroupA&#39;].map({&#39;A&#39;: 0.5, &#39;B&#39;: 0.3, &#39;C&#39;: 0.2})
  45.     
  46.     
  47.         print(w2, groupby_columns)
  48.     
  49.     for i in range(len(groupby_cols)):
  50.         if i == 0:
  51.             df[&#39;CMULT&#39;] = df[&#39;GroupA&#39;].map({&#39;A&#39;: 0.5, &#39;B&#39;: 0.3, &#39;C&#39;: 0.2})
  52.             df[&#39;POP&#39;] = pd.to_numeric(df[&#39;POP&#39;]) * df[&#39;CMULT&#39;]
  53.             df[&#39;LF&#39;] = pd.to_numeric(df[&#39;LF&#39;]) * df[&#39;CMULT&#39;]
  54.             df[&#39;WRK&#39;] = pd.to_numeric(df[&#39;WRK&#39;]) * df[&#39;CMULT&#39;]
  55.             df[&#39;no_sam&#39;] = df.shape[0]
  56.     
  57.             agg_dict = {&#39;POP&#39;: &#39;sum&#39;, &#39;LF&#39;: &#39;sum&#39;, &#39;WRK&#39;: &#39;sum&#39;, &#39;no_sam&#39;: &#39;count&#39;}
  58.             # Group the data by the current groupby configuration and calculate the aggregates
  59.             w2 = df.agg(agg_dict).to_frame().T
  60.     
  61.             print(w2, groupby_cols[i])
  62.     
  63.         else:
  64.             test(df, i)
  67. This is the code , in which I am getting calculation errors. The results are -     
  69.     POP   LF    WRK  no_sam
  70.     0  63.0  6.3  630.0     6.0 []
  71.       GroupA  pophat  lfhat  wrkhat  CMULT
  72.     0      A     7.5   0.75    75.0    0.5
  73.     1      B    10.8   1.08   108.0    0.3
  74.     2      C     2.4   0.24    24.0    0.2 [&#39;GroupA&#39;]
  75.       GroupB  pophat  lfhat  wrkhat  CMULT
  76.     0      X     8.5   0.85    85.0    NaN
  77.     1      Y     9.5   0.95    95.0    NaN
  78.     2      Z     2.7   0.27    27.0    NaN [&#39;GroupB&#39;]
  79.       GroupA GroupB  pophat  lfhat  wrkhat  CMULT
  80.     0      A      X     2.5   0.25    25.0    0.5
  81.     1      A      Y     5.0   0.50    50.0    0.5
  82.     2      B      X     3.6   0.36    36.0    0.3
  83.     3      B      Y     4.5   0.45    45.0    0.3
  84.     4      B      Z     2.7   0.27    27.0    0.3
  85.     5      C      X     2.4   0.24    24.0    0.2 [&#39;GroupA&#39;, &#39;GroupB&#39;]
  87. But, outside the loop the results are - (You can verify it by changing the index of groupby_cols[NNNNNNNNN])
  89.     import pandas as pd
  90.     import numpy as np
  91.     
  92.     # Example DataFrame
  93.     df = pd.DataFrame({
  94.         &#39;GroupA&#39;: [&#39;A&#39;, &#39;A&#39;, &#39;B&#39;, &#39;B&#39;, &#39;B&#39;, &#39;C&#39;],
  95.         &#39;GroupB&#39;: [&#39;X&#39;, &#39;Y&#39;, &#39;Z&#39;, &#39;X&#39;, &#39;Y&#39;, &#39;X&#39;],
  96.         &#39;POP&#39;: [10, 20, 30, 40, 50, 60],
  97.         &#39;LF&#39;: [1, 2, 3, 4, 5, 6],
  98.         &#39;WRK&#39;: [100, 200, 300, 400, 500, 600]
  99.     })
  100.     
  101.     groupby_cols = [[], [&#39;GroupA&#39;], [&#39;GroupB&#39;], [&#39;GroupA&#39;, &#39;GroupB&#39;]]
  102.     
  103.     df[&#39;CMULT&#39;] = df.groupby(groupby_cols[i])[&#39;GroupA&#39;].transform(lambda x: x.map({&#39;A&#39;: 0.5, &#39;B&#39;: 0.3, &#39;C&#39;: 0.2}))
  104.     
  105.     # Perform groupby and aggregation based on the current groupby configuration
  107.     w2 = df.groupby(groupby_cols[3]).agg(
  108.         pophat=(&#39;POP&#39;, lambda x: np.sum(x * df[&#39;CMULT&#39;])),
  109.         lfhat=(&#39;LF&#39;, lambda x: np.sum(x * df[&#39;CMULT&#39;])),
  110.         wrkhat=(&#39;WRK&#39;, lambda x: np.sum(x * df[&#39;CMULT&#39;]))
  111.     ).reset_index()
  112.     
  113.     print(w2)
  115.              POP       LF       WRK         no_sam
  116.      0       63.0      6.3      630.0       6.0 
  117.     
  118.       GroupA  pophat  lfhat  wrkhat
  119.     0      A    15.0    1.5   150.0
  120.     1      B    36.0    3.6   360.0
  121.     2      C    12.0    1.2   120.0
  122.     
  123.       GroupB  pophat  lfhat  wrkhat
  124.     0      X    29.0    2.9   290.0
  125.     1      Y    25.0    2.5   250.0
  126.     2      Z     9.0    0.9    90.0
  127.     
  128.       GroupA GroupB  pophat  lfhat  wrkhat
  129.     0      A      X     5.0    0.5    50.0
  130.     1      A      Y    10.0    1.0   100.0
  131.     2      B      X    12.0    1.2   120.0
  132.     3      B      Y    15.0    1.5   150.0
  133.     4      B      Z     9.0    0.9    90.0
  134.     5      C      X    12.0    1.2   120.0
  136. So, am I not understanding the groupby and aggregation properly because It is not working in the loop or The functions work differently in the loop. I have doubt about it, how can It be possible???
  138. </details>
  141. # 答案1
  142. **得分**: 1
  144. Here are the translated parts of the code you provided:
  146. ```python
  147. IIUC multiple columns before loop and then aggregate `sum` only:
  149. groupby_cols = [[], ['GroupA'], ['GroupB'], ['GroupA', 'GroupB']]
  151. def test(df, gby):
  152.     groupby_columns = groupby_cols[gby]
  153.     w2 = df.groupby(groupby_columns).agg(
  154.         pophat=('POP', 'sum'),
  155.         lfhat=('LF', 'sum'),
  156.         wrkhat=('WRK', 'sum')
  157.     ).reset_index()
  159.     if len(groupby_columns) == 1:
  160.         w2['CMULT'] = w2[groupby_columns[0]].map({'A': 0.5, 'B': 0.3, 'C': 0.2})
  161.     else:
  162.         w2['CMULT'] = w2['GroupA'].map({'A': 0.5, 'B': 0.3, 'C': 0.2})
  164.     print(w2, groupby_columns)
  166. df['CMULT'] = df['GroupA'].map({'A': 0.5, 'B': 0.3, 'C': 0.2})
  167. df['POP'] = pd.to_numeric(df['POP']) * df['CMULT']
  168. df['LF'] = pd.to_numeric(df['LF']) * df['CMULT']
  169. df['WRK'] = pd.to_numeric(df['WRK']) * df['CMULT']
  170. df['no_sam'] = df.shape[0]
  172. for i in range(len(groupby_cols)):
  173.     if i == 0:
  174.         agg_dict = {'POP': 'sum', 'LF': 'sum', 'WRK': 'sum', 'no_sam': 'count'}
  175.         w2 = df.agg(agg_dict).to_frame().T
  177.         print(w2, groupby_cols[i])
  178.     else:
  179.         test(df, i)

Please note that I've only translated the code, and there are no additional comments or explanations.

英文:

IIUC multiple columns before loop and then aggregate sum only:

  1. groupby_cols = [[], [&#39;GroupA&#39;], [&#39;GroupB&#39;], [&#39;GroupA&#39;, &#39;GroupB&#39;]]
  2. def test(df, gby):
  3. # print (df)
  4. # Perform groupby and aggregation
  5. groupby_columns = groupby_cols[gby]
  6. w2 = df.groupby(groupby_columns).agg(
  7. pophat=(&#39;POP&#39;, &#39;sum&#39;),
  8. lfhat=(&#39;LF&#39;, &#39;sum&#39;),
  9. wrkhat=(&#39;WRK&#39;, &#39;sum&#39;)
  10. ).reset_index()
  11. # print (w2)
  12. # Calculate CMULT column based on the current groupby configuration
  13. if len(groupby_columns) == 1:
  14. w2[&#39;CMULT&#39;] = w2[groupby_columns[0]].map({&#39;A&#39;: 0.5, &#39;B&#39;: 0.3, &#39;C&#39;: 0.2})
  15. else:
  16. w2[&#39;CMULT&#39;] = w2[&#39;GroupA&#39;].map({&#39;A&#39;: 0.5, &#39;B&#39;: 0.3, &#39;C&#39;: 0.2})
  17. print(w2, groupby_columns)
  1. df[&#39;CMULT&#39;] = df[&#39;GroupA&#39;].map({&#39;A&#39;: 0.5, &#39;B&#39;: 0.3, &#39;C&#39;: 0.2})
  2. df[&#39;POP&#39;] = pd.to_numeric(df[&#39;POP&#39;]) * df[&#39;CMULT&#39;]
  3. df[&#39;LF&#39;] = pd.to_numeric(df[&#39;LF&#39;]) * df[&#39;CMULT&#39;]
  4. df[&#39;WRK&#39;] = pd.to_numeric(df[&#39;WRK&#39;]) * df[&#39;CMULT&#39;]
  5. df[&#39;no_sam&#39;] = df.shape[0]
  6. for i in range(len(groupby_cols)):
  7. if i == 0:
  8. agg_dict = {&#39;POP&#39;: &#39;sum&#39;, &#39;LF&#39;: &#39;sum&#39;, &#39;WRK&#39;: &#39;sum&#39;, &#39;no_sam&#39;: &#39;count&#39;}
  9. # Group the data by the current groupby configuration and calculate the aggregates
  10. w2 = df.agg(agg_dict).to_frame().T
  11. print(w2, groupby_cols[i])
  12. else:
  13. test(df, i)

huangapple
  • 本文由 发表于 2023年6月1日 13:33:55
  • 转载请务必保留本文链接：https://go.coder-hub.com/76378911.html
  • dataframe
  • numpy
  • pandas
  • python-3.x
匿名

发表评论

匿名网友

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

确定