How to divide the value of pandas columns by the other column

bigbug picture bigbug · Apr 10, 2013 · Viewed 43.8k times · Source

I have a dataframe:

>>> dt
                   COL000   COL001   QT
STK_ID  RPT_Date                       
STK000  20120331   2.6151   2.1467    1
        20120630   4.0589   2.3442    2
        20120930   4.4547   3.9204    3
        20121231   4.1360   3.8559    4
STK001  20120331  -0.2178   0.9184    1
        20120630  -1.9639   0.7900    2
        20120930  -2.9147   1.0189    3
        20121231  -2.5648   2.3743    4
STK002  20120331  -0.6426   0.9543    1
        20120630  -0.3575   1.6085    2
        20120930  -2.3549   0.7174    3
        20121231  -3.4860   1.6324    4

And I want the columns values divided by 'QT' column, somewhat like this:

dt =  dt/dt.QT     # pandas does not accept this syntax

The desired output is:

STK_ID  RPT_Date        COL000       COL001  QT
STK000  20120331   2.615110188  2.146655745   1
        20120630   2.029447265  1.172093561   1
        20120930   1.484909881  1.306795608   1
        20121231   1.034008443  0.963970609   1
STK001  20120331  -0.217808111  0.918355842   1
        20120630  -0.981974837  0.394977675   1
        20120930  -0.97157148   0.339633733   1
        20121231  -0.641203355  0.593569537   1
STK002  20120331  -0.642567516  0.954323016   1
        20120630  -0.178759288  0.804230898   1
        20120930  -0.784982521  0.239117442   1
        20121231  -0.871501505  0.408094317   1

How to do that?

Answer

waitingkuo picture waitingkuo · Apr 10, 2013

The / operator for dv seems equal to div with default axis "columns". Set the axis to "index", then it'll work.

df = df.div(df.QT, axis='index')

Another tricky way is to transpose it first, divide it, and then transpose back:

df = (df.T / df.QT).T