CODEWITHSUNDEEP
pythonpandas
rakamakafo
rakamakafo

Reputation: 159

How do I multiply two columns in pandas with different indexes?

I am trying to multiply two columns in pandas dataframe and store it in the new column. I want to grab top 10 values by market cap (already sorted) on each day and multiply supply of 1/1/2017 by price of 1/2/2017. 

df['New.Cap'] = df['Circulating.Supply'].loc['2017-01-01'].head(10) * df['Price'].loc['2017-02-01'].head(10)
print(df['New.Cap'])

I keep getting Value Error: cannot re-index from a duplicate axis. I assume this is due to join/assign to a column when the index has duplicate values.

Data

Upvotes: 1

Views: 1471

Answers (1)

jezrael
jezrael

Reputation: 862751

I believe you need create MultiIndex and then multiple by 1d numpy array created by values:

Notice - Solution working only if all value of symbol are same with same order for both datetimes.

df = df.set_index('Symbo', append=True)

df['New.Cap'] = (df['Circulating.Supply'].loc['2017-01-01'] * 
                 df['Price'].loc['2017-02-01'].values)

If not sure, then need:

df = df.set_index('Symbo', append=True)

s = df['Circulating.Supply'].xs('2017-01-01') * df['Price'].xs('2017-02-01')
s.index = [np.repeat(pd.Timestamp('2017-01-01'), len(s)), s.index]
print (s)
2017-01-01  BTC      1.642702e+10
            DASH     8.258543e+07
            DGD               NaN
            DOGE     0.000000e+00
            ETC      1.259195e+08
            ETH      7.331980e+08
            FCT      2.608459e+07
            ICN      2.610000e+07
            LTC      2.285203e+08
            MAID     4.072972e+07
            REP      4.609000e+07
            STEEM    3.443731e+07
            WAVES    2.300000e+07
            XEM      0.000000e+00
            XMR      2.193754e+08
            XRP      3.633730e+08
            ZEC               NaN
dtype: float64

df['new'] = s
print (df)
                   Market.Cap    Price  Circulating.Supply           new
Date       Symbo                                                        
2017-01-01 BTC    16050414526   998.33        1.607734e+07  1.642702e+10
           ETH      715049311     8.17        8.749380e+07  7.331980e+08
           XRP      231408729     0.01        3.633730e+10  3.633730e+08
           LTC      221718486     4.51        4.914415e+07  2.285203e+08
           XMR      190983552    13.97        1.366825e+07  2.193754e+08
           ETC      122202804     1.40        8.744412e+07  1.259195e+08
           DASH      78524020    11.23        6.992839e+06  8.258543e+07
           REP       43994860     4.00        1.100000e+07  4.609000e+07
           MAID      43862003     0.10        4.525524e+08  4.072972e+07
           STEEM     36999610     0.16        2.295821e+08  3.443731e+07
           XEM       30962258     0.00        9.000000e+09  0.000000e+00
           ICN       26381302     0.30        8.700000e+07  2.610000e+07
           FCT       25392986     2.90        8.753219e+06  2.608459e+07
           DOGE      24501946     0.00        1.075440e+11  0.000000e+00
           WAVES     22132166     0.22        1.000000e+08  2.300000e+07
           DGD       18079172     9.04        2.000000e+06           NaN
2017-02-01 BTC    16429072864  1021.75        1.607930e+07           NaN
           ETH      733331862     8.38        8.752531e+07           NaN
           XRP      229312859     0.01        3.633730e+10           NaN
           LTC      228536428     4.65        4.915883e+07           NaN
           XMR      219509938    16.05        1.367528e+07           NaN
           ETC      125743676     1.44        8.747679e+07           NaN
           DASH      82595831    11.81        6.994957e+06           NaN
           REP       46101566     4.19        1.100000e+07           NaN
           MAID      42320210     0.09        4.525524e+08           NaN
           STEEM     35374447     0.15        2.296454e+08           NaN
           XEM       30396875     0.00        9.000000e+09           NaN
           ICN       26451766     0.30        8.700000e+07           NaN
           FCT       26119449     2.98        8.753219e+06           NaN
           DOGE      24343443     0.00        1.075570e+11           NaN
           WAVES     23267904     0.23        1.000000e+08           NaN
           ZEC       17773124    49.79        3.569940e+05           NaN

Upvotes: 2

Related Questions