pandas使用相对位置遍历行(YTD计算示例)

Question

我有以下数据帧：

​

​

我正在尝试遍历所有行，并根据所讨论的月份计算每个单元格的YTD性能。我的思路是查找索引级别对应的月份ID，然后倒数那么多个单元格，以获得YTD性能。

例如:查看列RU30INTR (11720.86)下的第一个值，我将查找月份ID并将11个单元格下移到对应于12/31/2019年的单元格。(t/t-11)-1将提供YTD性能。当然，相对位置会随着每一行的变化而变化，但应该保持YTD性能始终与月份ID相对应。前一年的12个月将作为锚，然后在一年完成后移动到下一年。

我还使用pct_change计算了1、3、5、7和10年的绩效，因为这是一个固定的相对位置。这一个更棘手，因为它是一个移动的目标。

我研究过iterrows()、iteritem()、itertuples、iloc等，但似乎在理解如何迭代整个dataframe时遇到了困难。如果有任何帮助，我将不胜感激。很高兴在需要的地方澄清。

谢谢

​

​

df字典如下

{'Dates': {731: Timestamp('2020-11-30 00:00:00'),
     730: Timestamp('2020-10-30 00:00:00'),
    729: Timestamp('2020-09-30 00:00:00'),
    728: Timestamp('2020-08-31 00:00:00'),
    727: Timestamp('2020-07-31 00:00:00'),
    726: Timestamp('2020-06-30 00:00:00'),
    725: Timestamp('2020-05-29 00:00:00'),
    724: Timestamp('2020-04-30 00:00:00'),
  723: Timestamp('2020-03-31 00:00:00'),
  722: Timestamp('2020-02-28 00:00:00'),
  721: Timestamp('2020-01-31 00:00:00'),
  720: Timestamp('2019-12-31 00:00:00'),
  719: Timestamp('2019-11-29 00:00:00'),
  718: Timestamp('2019-10-31 00:00:00'),
  717: Timestamp('2019-09-30 00:00:00'),
  716: Timestamp('2019-08-30 00:00:00'),
  715: Timestamp('2019-07-31 00:00:00'),
  714: Timestamp('2019-06-28 00:00:00'),
  713: Timestamp('2019-05-31 00:00:00'),
  712: Timestamp('2019-04-30 00:00:00'),
  711: Timestamp('2019-03-29 00:00:00'),
  710: Timestamp('2019-02-28 00:00:00'),
  709: Timestamp('2019-01-31 00:00:00'),
  708: Timestamp('2018-12-31 00:00:00')},
 'Month ID': {731: 11,
  730: 10,
  729: 9,
  728: 8,
  727: 7,
  726: 6,
  725: 5,
  724: 4,
  723: 3,
  722: 2,
  721: 1,
  720: 12,
  719: 11,
  718: 10,
  717: 9,
  716: 8,
  715: 7,
  714: 6,
  713: 5,
  712: 4,
  711: 3,
  710: 2,
  709: 1,
  708: 12},
 'RU30INTR Index': {731: 11720.86,
  730: 10449.63,
  729: 10680.14,
  728: 11083.71,
  727: 10335.0,
  726: 9779.68,
  725: 9561.09,
  724: 9075.74,
  723: 8014.32,
  722: 9292.15,
  721: 10120.78,
  720: 10131.84,
  719: 9847.51,
  718: 9486.89,
  717: 9286.97,
  716: 9126.79,
  715: 9316.72,
  714: 9180.25,
  713: 8577.78,
  712: 9171.27,
  711: 8819.1,
  710: 8692.18,
  709: 8396.87,
  708: 7733.11},
 'RU10INTR Index': {731: 12022.82,
  730: 10756.2,
  729: 11021.99,
  728: 11440.1,
  727: 10657.84,
  726: 10068.29,
  725: 9850.456,
  724: 9356.779,
  723: 8264.663,
  722: 9523.141,
  721: 10370.6,
  720: 10359.42,
  719: 10068.65,
  718: 9701.994,
  717: 9500.642,
  716: 9338.769,
  715: 9513.004,
  714: 9367.528,
  713: 8753.029,
  712: 9348.772,
  711: 8985.885,
  710: 8832.122,
  709: 8542.874,
  708: 7882.31},
 'RU10VATR Index': {731: 1782.662,
  730: 1571.3,
  729: 1592.226,
  728: 1632.322,
  727: 1567.505,
  726: 1507.893,
  725: 1517.969,
  724: 1467.674,
  723: 1319.35,
  722: 1591.293,
  721: 1761.93,
  720: 1800.668,
  719: 1752.459,
  718: 1699.904,
  717: 1676.471,
  716: 1618.719,
  715: 1667.76,
  714: 1654.049,
  713: 1543.267,
  712: 1649.313,
  711: 1592.814,
  710: 1582.751,
  709: 1533.745,
  708: 1422.988},
 'RU10GRTR Index': {731: 2316.922,
  730: 2101.723,
  729: 2175.61,
  728: 2283.027,
  727: 2069.473,
  726: 1921.625,
  725: 1841.44,
  724: 1725.594,
  723: 1503.168,
  722: 1667.177,
  721: 1789.012,
  720: 1749.893,
  719: 1698.643,
  718: 1626.487,
  717: 1581.891,
  716: 1581.695,
  715: 1593.902,
  714: 1558.705,
  713: 1458.538,
  712: 1556.892,
  711: 1489.592,
  710: 1448.367,
  709: 1398.335,
  708: 1283.01}}

Answer 1

这是一个可能对你有效的解决方案。

import pandas as pd

df = pd.read_csv('test2.csv')

vCol1 = 'RU30INTR'
vCol2 = 'RU10INTR'
vCol3 = 'RU10VATR'
vCol4 = 'RU10GRTR'
newRows = []
tempRow = None
last_val_is_twelve = False

for row in df.iterrows():
    vals = row[1]

    if tempRow is None:
        tempRow = vals

    elif vals['Month ID'] == 12:
        newRows.append(tempRow)
        last_val_is_twelve = True
        tempRow = vals

    else:
        if last_val_is_twelve:
            last_val_is_twelve = False

        tempRow[vCol1] = tempRow[vCol1] + vals[vCol1]
        tempRow[vCol2] = tempRow[vCol2] + vals[vCol2]
        tempRow[vCol3] = tempRow[vCol3] + vals[vCol3]
        tempRow[vCol4] = tempRow[vCol4] + vals[vCol4]

#append the last tempRow from the iteration to newRows if it hasnt already
if not last_val_is_twelve:
     newRows.append(tempRow)

newData = [[val for val in row[[vCol1, vCol2, vCol3, vCol4]]] for row in newRows]
newDf = pd.DataFrame(newData, columns=[x for x in newRows[0].keys()[3:]])