Pandas数据操作，根据同一列的其他行计算列值

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

Pandas data manipulation, calculating a column value based on other rows of the same column

问题

  1. 我希望在pandas数据框中进行如下数据操作：
  3. a = {'idx': range(8),
  4.      'col': [47, 33, 23, 33, 32, 31, 22, 5]}
  6. df = pd.DataFrame(a)
  7. print(df)
  9. idx    col
  10. 0    47
  11. 1    33
  12. 2    23
  13. 3    33
  14. 4    32
  15. 5    31
  16. 6    22
  17. 7     5
  18. dtype: int64
  20. 我的期望输出是：
  22. idx    col    desired
  23. 0    47         14
  24. 1    33         10
  25. 2    23        -10
  26. 3    33          1
  27. 4    32          1
  28. 5    31          9
  29. 6    22        17
  30. 7     5           5
  32. 计算如下所示。
英文:

I wish to do a data manipulation as follows in a pandas dataframe:

  1. a = {'idx': range(8),
  2.      'col': [47,33,23,33,32,31,22,5],
  3.      }
  5. df = pd.DataFrame(a)
  6. print(df)
  8. idx	col
  9. 0	47
  10. 1	33
  11. 2	23
  12. 3	33
  13. 4	32
  14. 5	31
  15. 6	22
  16. 7	5

My desired output is:

  1. idx	col	desired
  2. 0	47	14
  3. 1	33	10
  4. 2	23	-10
  5. 3	33	1
  6. 4	32	1
  7. 5	31	9
  8. 6	22	17
  9. 7	5	5

The calculation is as follows.

Pandas数据操作，根据同一列的其他行计算列值

答案1

得分: 3

  1. import numpy as np
  3. df['desired'] = -np.diff(df['col'], append=0)
英文:

Same solution as @mozway with numpy (which is faster):

  1. import numpy as np
  3. df['desired'] = -np.diff(df['col'], append=0)

Output:

  1. >>> df
  2.    idx  col  desired
  3. 0    0   47       14
  4. 1    1   33       10
  5. 2    2   23      -10
  6. 3    3   33        1
  7. 4    4   32        1
  8. 5    5   31        9
  9. 6    6   22       17
  10. 7    7    5        5

For 10k records:

  1. # @mozway
  2. >>> %timeit df['col'].diff(-1).fillna(df['col'])
  3. 281 µs ± 14 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
  5. # @GodIsOne
  6. >>> %timeit df['col'] - df['col'].shift(-1, fill_value=0)
  7. 144 µs ± 4.18 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)
  9. # @Corralien
  10. >>> %timeit (-np.diff(df['col'], append=0))
  11. 32.7 µs ± 951 ns per loop (mean ± std. dev. of 7 runs, 10000 loops each)

答案2

得分: 1

  1. IIUC，您需要一个反转的 [`diff`](https://pandas.pydata.org/docs/reference/api/pandas.Series.diff.html) 和 [`fillna`](https://pandas.pydata.org/docs/reference/api/pandas.Series.fillna.html)：

df['desired'] = df['col'].diff(-1).fillna(df['col'])

  1. 输出：

idx col desired
0 0 47 14.0
1 1 33 10.0
2 2 23 -10.0
3 3 33 1.0
4 4 32 1.0
5 5 31 9.0
6 6 22 17.0
7 7 5 5.0

  2. <details>
  3. <summary>英文:</summary>
  5. IIUC, you need a reversed [`diff`](https://pandas.pydata.org/docs/reference/api/pandas.Series.diff.html), and [`fillna`](https://pandas.pydata.org/docs/reference/api/pandas.Series.fillna.html):

df['desired'] = df['col'].diff(-1).fillna(df['col'])

  1. Output:

idx col desired
0 0 47 14.0
1 1 33 10.0
2 2 23 -10.0
3 3 33 1.0
4 4 32 1.0
5 5 31 9.0
6 6 22 17.0
7 7 5 5.0

  2. </details>
  6. # 答案3
  7. **得分**: 1
  9. a = {'idx': range(8), 'col': [47, 33, 23, 33, 32, 31, 22, 5]}
  11. df = pd.DataFrame(a)
  13. df['col'] - df['col'].shift(-1, fill_value=0)
  15. 0    14
  16. 1    10
  17. 2   -10
  18. 3     1
  19. 4     1
  20. 5     9
  21. 6    17
  22. 7     5
  23. Name: col, dtype: int64
  24. **************
  26. df['desired'] = df['col'] - df['col'].shift(-1, fill_value=0)
  28.    idx  col  desired
  29. 0    0   47       14
  30. 1    1   33       10
  31. 2    2   23      -10
  32. 3    3   33        1
  33. 4    4   32        1
  34. 5    5   31        9
  35. 6    6   22       17
  36. 7    7    5        5
  38. <details>
  39. <summary>英文:</summary>
  41.     a = {&#39;idx&#39;: range(8),
  42.          &#39;col&#39;: [47,33,23,33,32,31,22,5],
  43.          }
  44.     
  45.     df = pd.DataFrame(a)
  47.     df[&#39;col&#39;] - df[&#39;col&#39;].shift(-1, fill_value=0)
  49.     0    14
  50.     1    10
  51.     2   -10
  52.     3     1
  53.     4     1
  54.     5     9
  55.     6    17
  56.     7     5
  57.     Name: col, dtype: int64
  58. **************
  60.     df[&#39;desired&#39;] = df[&#39;col&#39;] - df[&#39;col&#39;].shift(-1, fill_value=0)
  61.     
  62.        idx  col  desired
  63.     0    0   47       14
  64.     1    1   33       10
  65.     2    2   23      -10
  66.     3    3   33        1
  67.     4    4   32        1
  68.     5    5   31        9
  69.     6    6   22       17
  70.     7    7    5        5
  74. </details>

huangapple
  • 本文由 发表于 2023年6月8日 15:07:16
  • 转载请务必保留本文链接：https://go.coder-hub.com/76429378.html
  • dataframe
  • pandas
  • python
  • python-3.x
匿名

发表评论

匿名网友

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

确定