基于annum列非空行创建Pandas DataFrame并计算增长率

需求 #

• 从名为div的Pandas DataFrame中，筛选出annum列（第3列）不为空的行，生成新DataFrame
• 在新DataFrame中新增growth列，计算规则为：当前行annum值 ÷ 下一行annum值 - 1

原始div数据结构 #

Date  C_dividends  annum
0   04 Nov 2022         0.51    NaN
1   29 Jul 2022         0.51    NaN
2   29 Apr 2022         0.51    NaN
3   04 Feb 2022         0.51   2.04
4   29 Oct 2021         0.51    NaN
5   30 Jul 2021         0.51    NaN
6   30 Apr 2021         0.51    NaN
7   29 Jan 2021         0.51   2.04
8   30 Oct 2020         0.51    NaN
9   31 Jul 2020         0.51    NaN
10  01 May 2020         0.51    NaN
11  31 Jan 2020         0.51   2.04
12  01 Nov 2019         0.51    NaN
13  02 Aug 2019         0.51    NaN
14  03 May 2019         0.45    NaN
15  01 Feb 2019         0.45   1.92
16  02 Nov 2018         0.45    NaN
17  03 Aug 2018         0.45    NaN
18  04 May 2018         0.32    NaN
19  02 Feb 2018         0.32   1.54
20  03 Nov 2017         0.32    NaN
21  03 Aug 2017         0.32    NaN
22  27 Apr 2017         0.16    NaN
23  02 Feb 2017         0.16   0.96
24  03 Nov 2016         0.16    NaN
25  28 Jul 2016         0.16    NaN
26  28 Apr 2016         0.05    NaN
27  28 Jan 2016         0.05   0.42
28  29 Oct 2015         0.05    NaN
29  30 Jul 2015         0.05    NaN
30  07 May 2015         0.05    NaN
31  29 Jan 2015         0.01   0.16
32  30 Oct 2014         0.01    NaN
33  31 Jul 2014         0.01    NaN
34  01 May 2014         0.01    NaN
35  30 Jan 2014         0.01   0.04
36  31 Oct 2013         0.01    NaN
37  01 Aug 2013         0.01    NaN
38  02 May 2013         0.01    NaN
39  31 Jan 2013         0.01   0.04

用户现有代码 #

def g_rate():
  div= dh()
  df= pd.DataFrame()

  for count in range(0,len(div)):
    if count == 0:
        df[count] = [div.iloc[0, 2]]

    if count >0:
        if count <10:
            row = count*3+count+3
            df[count] = [div.iloc[row, 2]]

  print(df)

当前输出 #

Enter ticker: C
    0     1     2     3     4     5     6     7     8     9
0 NaN  2.04  2.04  1.92  1.54  0.96  0.42  0.16  0.04  0.04

期望输出 #

Year  C_dividends  annum  growth
0  2022         0.51   2.04   0          #annum row 0/annum row 1
1  2021         0.51   2.04   0          #annum row 1/annum row 2
2  2020         0.51   2.04   0.0625     #annum row 2/annum row 3 and so on
3  2019         0.45   1.92   0.246753
4  2018         0.32   1.54   0.604167
5  2017         0.16   0.96   1.285714
6  2016         0.05   0.42   1.625
7  2015         0.01   0.16   3
8  2014         0.01   0.04   0
9  2013         0.01   0.04   NaN #or 0, as it is the last row

正确实现代码 #

import pandas as pd

def g_rate():
    # 获取原始数据
    div = dh()
    
    # 步骤1：筛选annum列非空的行，重置索引
    df = div.dropna(subset=['annum']).reset_index(drop=True)
    
    # 步骤2：从Date列提取年份，生成Year列
    df['Year'] = pd.to_datetime(df['Date']).dt.year
    
    # 步骤3：计算growth列：当前annum / 下一行annum -1，最后一行设为NaN或0
    df['growth'] = df['annum'] / df['annum'].shift(-1) - 1
    # 如果需要把最后一行的NaN替换为0，取消下面注释
    # df['growth'].fillna(0, inplace=True)
    
    # 步骤4：调整列顺序为期望的顺序
    df = df[['Year', 'C_dividends', 'annum', 'growth']]
    
    print(df)

代码说明 #

1. 筛选有效行：使用dropna(subset=['annum'])直接过滤掉annum为空的行，比手动循环更高效简洁
2. 提取年份：通过pd.to_datetime将字符串日期转为时间格式，再提取年份字段
3. 计算增长率：利用Pandas的shift(-1)方法获取下一行的annum值，批量计算无需循环
4. 列顺序调整：重新排列列的顺序，匹配期望输出的结构

内容的提问来源于stack exchange，提问作者user14894283