找到你要的答案

Q:Add a calculated result with multiple columns to Pandas DataFrame with MultiIndex columns

Q:添加计算结果与多个栏目熊猫数据框指标列

I have a DataFrame like so:

In [10]: df.head()
Out[10]: 
      sand              silt              clay             rho_b  ...      n  \
        5     25    60    5     25    60    5     25    60    5   ...     60   
STID                                                              ...          
ACME  73.0  60.3  52.5  19.7  23.9  25.9   7.2  15.7  21.5  1.27  ...   1.32   
ADAX  61.1  51.1  47.6  22.0  25.4  24.6  16.9  23.5  27.8  1.01  ...   1.25   
ALTU  23.8  17.8  14.3  40.0  45.2  40.9  36.2  37.0  44.8  1.57  ...   1.18   
ALV2  33.3  21.2  19.8  31.4  29.7  29.8  35.3  49.1  50.5  1.66  ...   1.20   
ANT2  55.6  57.5  47.7  34.9  31.1  26.8   9.4  11.3  25.5  1.49  ...   1.29  

So for every STID (e.g. ACME, ADAX, ALTU), there's some property (e.g. sand, silt, clay) defined at three depths (5, 25, 60).

This structure makes it really easy to do per-depth calculations at each STID, e.g.:

In [12]: (df['sand'] + df['silt']).head()
Out[12]: 
        5     25    60
STID                  
ACME  92.7  84.2  78.4
ADAX  83.1  76.5  72.2
ALTU  63.8  63.0  55.2
ALV2  64.7  50.9  49.6
ANT2  90.5  88.6  74.5

How can I neatly incorporate a calculated result back in to the DataFrame? For example, if I wanted to call the result of the above calculation 'notclay':

In [13]: df['notclay'] = df['sand'] + df['silt']
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-13-a30bd9ba99c3> in <module>()
----> 1 df['notclay'] = df['sand'] + df['silt']

<snip>

ValueError: Wrong number of items passed 3, placement implies 1

Three columns are expected to be defined for each column in the result, not just the one 'notclay' column.

I do have a solution using strict assignments, but I'm not very satisfied with it:

In [21]: df[[('notclay', 5), ('notclay', 25), ('notclay', 60)]] = df['sand'] + df['silt']

In [22]: df['notclay'].head()
Out[22]: 
        5     25    60
STID                  
ACME  92.7  84.2  78.4
ADAX  83.1  76.5  72.2
ALTU  63.8  63.0  55.2
ALV2  64.7  50.9  49.6
ANT2  90.5  88.6  74.5

I have many other calculations to do similar to this one, and using a strict assignment every time seems tedious. I'm guessing there's a better/"right" way to do this. I think add a field in pandas dataframe with MultiIndex columns might contain the answer, but I don't very well understand the solutions (or even what a Panel is and if it can help me).

Edit: Something I tried that doesn't work, prepending a category using concat:

In [36]: concat([df['sand'] + df['silt']], axis=1, keys=['notclay']).head()
Out[36]: 
     notclay            
          5     25    60
STID                    
ACME    92.7  84.2  78.4
ADAX    83.1  76.5  72.2
ALTU    63.8  63.0  55.2
ALV2    64.7  50.9  49.6
ANT2    90.5  88.6  74.5

In [37]: df['notclay'] = concat([df['sand'] + df['silt']], axis=1, keys=['notclay'])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)

<snip>

ValueError: Wrong number of items passed 3, placement implies 1

Same ValueError raised as above.

我有一个数据框,像这样:

In [10]: df.head()
Out[10]: 
      sand              silt              clay             rho_b  ...      n  \
        5     25    60    5     25    60    5     25    60    5   ...     60   
STID                                                              ...          
ACME  73.0  60.3  52.5  19.7  23.9  25.9   7.2  15.7  21.5  1.27  ...   1.32   
ADAX  61.1  51.1  47.6  22.0  25.4  24.6  16.9  23.5  27.8  1.01  ...   1.25   
ALTU  23.8  17.8  14.3  40.0  45.2  40.9  36.2  37.0  44.8  1.57  ...   1.18   
ALV2  33.3  21.2  19.8  31.4  29.7  29.8  35.3  49.1  50.5  1.66  ...   1.20   
ANT2  55.6  57.5  47.7  34.9  31.1  26.8   9.4  11.3  25.5  1.49  ...   1.29  

所以每一个interface(例如极致,该公司,展布,有产权)(如砂、粉砂、粘土)三深度定义(5,25,60)。

这种结构使得它很容易做,每个深度计算在每一个interface,例如:

In [12]: (df['sand'] + df['silt']).head()
Out[12]: 
        5     25    60
STID                  
ACME  92.7  84.2  78.4
ADAX  83.1  76.5  72.2
ALTU  63.8  63.0  55.2
ALV2  64.7  50.9  49.6
ANT2  90.5  88.6  74.5

我怎么能巧妙地将计算结果返回到帧?例如,如果我想打电话给上述计算结果的notclay:

In [13]: df['notclay'] = df['sand'] + df['silt']
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-13-a30bd9ba99c3> in <module>()
----> 1 df['notclay'] = df['sand'] + df['silt']

<snip>

ValueError: Wrong number of items passed 3, placement implies 1

三列,预计结果在每一列的定义,不只是一个“notclay”专栏。

我有一个解决方案,使用严格的任务,但我不是很满意:

In [21]: df[[('notclay', 5), ('notclay', 25), ('notclay', 60)]] = df['sand'] + df['silt']

In [22]: df['notclay'].head()
Out[22]: 
        5     25    60
STID                  
ACME  92.7  84.2  78.4
ADAX  83.1  76.5  72.2
ALTU  63.8  63.0  55.2
ALV2  64.7  50.9  49.6
ANT2  90.5  88.6  74.5

我有许多其他的计算做类似这一个,并且使用严格的分配每一次似乎乏味。我猜有一个更好的/“正确”的方式来做到这一点。我想添加一个字段在数据框列可能包含多项大熊猫的回答,但是我非常不理解的解决方案(或是面板如果它能帮助我)。

编辑:我想不工作,把一类使用concat:

In [36]: concat([df['sand'] + df['silt']], axis=1, keys=['notclay']).head()
Out[36]: 
     notclay            
          5     25    60
STID                    
ACME    92.7  84.2  78.4
ADAX    83.1  76.5  72.2
ALTU    63.8  63.0  55.2
ALV2    64.7  50.9  49.6
ANT2    90.5  88.6  74.5

In [37]: df['notclay'] = concat([df['sand'] + df['silt']], axis=1, keys=['notclay'])
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)

<snip>

ValueError: Wrong number of items passed 3, placement implies 1

同时提出上述ValueError。

answer1: 回答1:

Depending on your taste, this may be a nicer way to do it still using concat:

In [53]: df
Out[53]: 
       blah                           foo                    
          1         2         3         1         2         3
a  0.351045  0.044654  0.855627  0.839725  0.675183  0.325324
b  0.610374  0.394499  0.924708  0.924303  0.404475  0.885368
c  0.116418  0.487866  0.190669  0.283535  0.862869  0.346477
d  0.771014  0.204143  0.143449  0.848520  0.887373  0.220083
e  0.103268  0.306820  0.277125  0.627272  0.631019  0.386406

In [54]: newdf
Out[54]: 
          1         2         3
a  0.433377  0.806679  0.976298
b  0.593683  0.217415  0.086565
c  0.716244  0.908777  0.180252
d  0.031942  0.074283  0.745019
e  0.651517  0.393569  0.861616

In [56]: newdf.columns=pd.MultiIndex.from_product([['bar'], newdf.columns])

In [57]: pd.concat([df, newdf], axis=1)
Out[57]: 
       blah                           foo                           bar  \
          1         2         3         1         2         3         1   
a  0.351045  0.044654  0.855627  0.839725  0.675183  0.325324  0.433377   
b  0.610374  0.394499  0.924708  0.924303  0.404475  0.885368  0.593683   
c  0.116418  0.487866  0.190669  0.283535  0.862869  0.346477  0.716244   
d  0.771014  0.204143  0.143449  0.848520  0.887373  0.220083  0.031942   
e  0.103268  0.306820  0.277125  0.627272  0.631019  0.386406  0.651517   


          2         3  
a  0.806679  0.976298  
b  0.217415  0.086565  
c  0.908777  0.180252  
d  0.074283  0.745019  
e  0.393569  0.861616 

In order to store this into the original dataframe, you can simply assign to it in the last line:

In [58]: df = pd.concat([df, newdf], axis=1)

根据你的口味,这可以做一个更好的方式仍然使用concat:

In [53]: df
Out[53]: 
       blah                           foo                    
          1         2         3         1         2         3
a  0.351045  0.044654  0.855627  0.839725  0.675183  0.325324
b  0.610374  0.394499  0.924708  0.924303  0.404475  0.885368
c  0.116418  0.487866  0.190669  0.283535  0.862869  0.346477
d  0.771014  0.204143  0.143449  0.848520  0.887373  0.220083
e  0.103268  0.306820  0.277125  0.627272  0.631019  0.386406

In [54]: newdf
Out[54]: 
          1         2         3
a  0.433377  0.806679  0.976298
b  0.593683  0.217415  0.086565
c  0.716244  0.908777  0.180252
d  0.031942  0.074283  0.745019
e  0.651517  0.393569  0.861616

In [56]: newdf.columns=pd.MultiIndex.from_product([['bar'], newdf.columns])

In [57]: pd.concat([df, newdf], axis=1)
Out[57]: 
       blah                           foo                           bar  \
          1         2         3         1         2         3         1   
a  0.351045  0.044654  0.855627  0.839725  0.675183  0.325324  0.433377   
b  0.610374  0.394499  0.924708  0.924303  0.404475  0.885368  0.593683   
c  0.116418  0.487866  0.190669  0.283535  0.862869  0.346477  0.716244   
d  0.771014  0.204143  0.143449  0.848520  0.887373  0.220083  0.031942   
e  0.103268  0.306820  0.277125  0.627272  0.631019  0.386406  0.651517   


          2         3  
a  0.806679  0.976298  
b  0.217415  0.086565  
c  0.908777  0.180252  
d  0.074283  0.745019  
e  0.393569  0.861616 

为了存储为原始数据框,你可以简单地给它在最后一行:

In [58]: df = pd.concat([df, newdf], axis=1)
python  pandas  multi-index