python - 如何迭代 df 中的列并将值与前一列进行比较并在 Python 中执行操作

Question

我尝试执行的操作类似于这条 mysql 删除语句:

   DELETE FROM ABCD WHERE val_2001>val_2000*1.5 OR val_2001>val_1999*POW(1.5,2);

column_names 从 val_2001 到 val_2017 不等。

表 ABCD 中的所有数据都转储到 csv 中并加载到 df 中。

如何遍历每一列并与前一列进行比较并执行删除？ (python 新手)

表格数据示例:

val_2000   val_2001        val_2002            val_2003100     112.058663384525    119.070787312921    117.033250060214100     118.300395256917    124.655238202362    128.723125524235100     109.333236619151    116.785836024946    117.390803371386100     120.954175930764    126.099776250454    124.491022271481100     107.776153227575    105.560100052722    108.07490649383100     151.596517146962    306.608812920781    124.610273175528

Note: there are columns which need not be iterated as well.

The sample output :

val_2000   val_2001        val_2002            val_2003100     112.058663384525    119.070787312921    117.033250060214100     118.300395256917    124.655238202362    128.723125524235100     109.333236619151    116.785836024946    117.390803371386100     120.954175930764    126.099776250454    124.491022271481100     107.776153227575    105.560100052722    108.07490649383100     NULL                   NULL             124.610273175528

EDIT : - Currently trying this way:

    df = pd.read_csv("singleDataFile.csv")
   for values in xrange(2000,2016):
        val2 = values+1
        df['val_'+str(val2)] = df['val_'+str(val2)].where((df['val_'+str(val2)]>df['val_'+str(values)]*1.5) |  (df['val_'+str(val2)]<df['val_'+str(values)]*0.75))

   print(df)

格式错误

Answer 1

这段代码创建了一个随机的 DataFrame，它与您的 DataFrame 非常相似。您的问题的关键组成部分之一似乎是遍历多个列，这是(通过 Pandas )。

构建数据框:

cols = [ 'val_{}'.format(c) for c in range(2000, 2018)]

d = {}
for c in cols:
    d[c] = np.random.rand(10) * 200 + 100

df = pd.DataFrame(d, columns = cols)

输出:

     val_2000    val_2001    val_2002    val_2003    val_2004    val_2005  \
0  138.795742  178.467087  131.461771  151.475698  217.449107  107.680520   
1  127.857106  217.484552  248.528498  155.661208  281.914679  211.313490   
2  278.366253  137.543827  167.605495  129.869768  272.923010  190.659691   
3  221.798435  206.622385  145.636888  236.499951  212.404028  122.954408   
4  122.994183  299.793792  171.987895  246.948802  290.938506  127.846811   
5  264.400326  203.226235  121.972832  137.858361  161.812761  270.464277   
6  156.253907  280.101596  138.100352  164.018757  121.044386  297.869079   
7  186.572007  146.406624  110.309996  270.895300  101.975819  229.314098   
8  195.470896  286.125937  251.778581  259.112738  207.539354  127.895095   
9  168.135585  261.295740  203.234246  279.825177  188.648541  197.145975   

核心代码:

df[(df.shift(axis = 1) > df * 1.5) | (df.shift(axis = 1) < df * 0.75)] = 'NULL'

输出:

     val_2000 val_2001    val_2002 val_2003 val_2004 val_2005   \
0  138.795742  178.467  131.461771  151.476     NULL  107.681 
1  127.857106     NULL  248.528498  155.661     NULL  211.313  
2  278.366253  137.544  167.605495   129.87     NULL   190.66  
3  221.798435  206.622  145.636888     NULL  212.404  122.954     
4  122.994183     NULL  171.987895     NULL  290.939  127.847  
5  264.400326  203.226  121.972832  137.858  161.813     NULL  
6  156.253907     NULL  138.100352  164.019  121.044     NULL  
7  186.572007  146.407  110.309996     NULL  101.976     NULL   
8  195.470896     NULL  251.778581  259.113  207.539  127.895     
9  168.135585     NULL  203.234246     NULL  188.649  197.146