Pandas: substitute NA's with shadow values obtained from contemporary non missing values in other column -


suppose have following prices, asset x:

2004              nan 2005              nan 2006    246313.490770 2007    245557.678822 2008              nan 2009              nan 2010    246221.051892

the values of x missing of years.

and have price comparable asset, asset y (asset y never has nan prices):

2004    249008.038563 2005    248558.125114 2006    249644.313926 2007    249381.243924 2008    248739.371177 2009    249514.093672 2010    251120.103454

for every nan in price of asset x (top matrix), replace nan last non nan value of asset x multiplied growth rate of y (botom matrix) in intermediate years.

so example substitution value in 2009 be: 

245557.678822/249381.243924*249514.093672

(value 2007 of x times growth rate of y between 2007 , 2009)

the values 2004 , 2005 remain nan's though.

i can code using ugly nested loops wondering if there elegant 2 liners in pandas (i have dataframe several columns x.1, x.2,... scattered missing values , column y no missing values)

i think need first divide div both columns, forward fill nan ffill , last multiplicate mul:

print (x)                col1 2004            nan 2005            nan 2006  246313.490770 2007  245557.678822 2008            nan 2009            nan 2010  246221.051892  print (y)                col1 2004  249008.038563 2005  248558.125114 2006  249644.313926 2007  249381.243924 2008  248739.371177 2009  249514.093672 2010  251120.103454 
print (x.col1.div(y.col1)) 2004         nan 2005         nan 2006    0.986658 2007    0.984668 2008         nan 2009         nan 2010    0.980491 name: col1, dtype: float64  print (x.col1.div(y.col1).ffill()) 2004         nan 2005         nan 2006    0.986658 2007    0.984668 2008    0.984668 2009    0.984668 2010    0.980491 name: col1, dtype: float64  x['new'] = (x.col1.div(y.col1).ffill().mul(y.col1))  print (x)                col1            new 2004            nan            nan 2005            nan            nan 2006  246313.490770  246313.490770 2007  245557.678822  245557.678822 2008            nan  244925.647401 2009            nan  245688.491690 2010  246221.051892  246221.051892  print (245557.678822/249381.243924*249514.093672) 245688.4916900315

Comments

Post a Comment

Popular posts from this blog

php - How to add and update images or image url in Volusion using Volusion API -

actually have store importing products store product data insert , update image or image url not insert , update. this xml code name datapro.txt. <products_joined> <productcode>3710_012t</productcode> <vendor_partno>eah5450silent/di/1gd3(lp)</vendor_partno> <productname>test product ta</productname> <hideproduct>n</hideproduct> <stockstatus>20</stockstatus> <lastmodified>1/5/2016 10:25:00 am</lastmodified> <lastmodby>2</lastmodby> <productweight>0.9</productweight> <productprice>100</productprice> <productmanufacturer>asus tek</productmanufacturer> <vendor_price>32.69</vendor_price> <numproductssharingstock>0</numproductssharingstock> <categoryids>107</categoryids> <producturl>http://tebkq.mvlce.servertrust.com/productdetails.asp?productcode=3710_012t</producturl...
Read more

Laravel mail error `Swift_TransportException in StreamBuffer.php line 269: Connection could not be established with host smtp.gmail.com [ #0]` -

i developing website contact-us page guest users can send me email using gmail . anytime try send mail using contact-us form, error swift_transportexception in streambuffer.php line 269: connection not established host smtp.gmail.com [ #0] this mail.php return [ 'driver' => env('mail_driver', 'smtp'), 'host' => env('mail_host', 'smtp.gmail.com'), 'port' => 587, 'from' => ['address' => '', 'name' => ''], 'encryption' => env('mail_encryption', 'tls'), 'username' => env('mail_username'), 'password' => env(mail_password), 'sendmail' => '/usr/sbin/sendmail -bs', 'pretend' => env('mail_pretend', false), ]; this .env mail_driver=smtp mail_host=smtp.gmail.com mail_port=587 mail_username=afecjaped@gmail.com mail_password=********...
Read more

c# SetCompatibleTextRenderingDefault must be called before the first -

i tried search exception couldn't find solution on case i'am using code below invoke .net application : assembly assem = assembly.load(data); methodinfo method = assem.entrypoint; var o = activator.createinstance(method.declaringtype); method.invoke(o, null); the application invoked has form , in entrypoint of application : [stathread] static void main() { application.enablevisualstyles(); application.setcompatibletextrenderingdefault(false); //exception application.run(new form1()); } setcompatibletextrenderingdefault must called before first iwin32window object created in application. edit : assembly = assembly.load(data); methodinfo method = a.gettype().getmethod("start"); var o = activator.createinstance(method.declaringtype); method.invoke(o, null); you should create new method skips initialization...
Read more