0

I'm trying to use multiple np.where to solve my equation problem for my 2 datasets below:

import pandas as pd
df_A1 = pd.DataFrame([[46, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0],[47, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]], columns=['RTMB','RTMM',1,2,3,4,5,6,7,8,9,10])

Dataset 1 df_A1

RTMB RTMM 1 2 3 4 5 6 7 8 9 10
46 4 0 0 0 0 0 0 0 0 0 0
47 2 0 0 0 0 0 0 0 0 0 0 
import pandas as pd
df_Pokok = pd.DataFrame([[6852110,9228999,10497987,11941460,0,0],[34794991,18556525,21108047,24010403,0,0]], columns=[0,1,2,3,4,5])

Dataset 2 df_Pokok

0 1 2 3 4 5
6852110 9228999 10497987 11941460 0 0
34794991 18556525 21108047 24010403 0 0

I'm trying to repeat Nth times condition at the last np.where (i just put 0, 0 in code below there because I'm confused and lost in my own thought) in the dataframe based on respective month in dataset 1 (column name from 1-10th, the column name actually goes longer to Nth number)

to provide more context and explanation of the Dataset and what I'm trying to achieve:

  • 0-5 in Dataset 2 is a Year unit, so the last condition must be taken value of each column (after failing to satisfy both np.where(col <= df_VD['RTMM']), divide by 12 and enter the respective month in Dataset 1
  • Dataset 1 RTM_B & RTMM is a month unit
  • Dataset 2 column 0 is only used if the column name in 1-10 below the RTMM value
  • For the remaining iterations will continue to fill the month column in Dataset 1 as long as the month column is not more than RTM_B

here is what im trying to do so far:

for col in df_A1.iloc[:, 5:]:
    df_A1[col] = np.where(int(col) > df_VD['RTM_B'], 0, np.where(int(col) <= (df_VD['RTMM']), df_VD['POKVER'], np.where(((((df_VD['RTM_B']).astype(int) - (int(col)-1))%12) == 0) ,0 ,0)))

and this is the result what im desired for :

RTMB RTMM 1 2 3 4 5 6 7 8 9 10
46 4 685,211 685,211 685,211 685,211 769083 769083 769083 769083 769083
47 2 34,794,991 34,794,991 1,546,377 1,546,377 1,546,377 1,546,377 1,546,377 1,546,377 1,546,377 1,546,377

the 769083 is value from 'df_Pokok' column 2 divided by 12 and placed to each month respectively for 12 times, and after it continue to the next column of 'df_Pokok' column 3 and repeat the process untill RTMB is finished

Please kindly let me know if im not explain the problem clear enough, thank you so much

Edo
  • 13
  • 4
  • 1
    Could you provide a [minimal example](https://stackoverflow.com/help/minimal-reproducible-example), and the desired output? Preferably also, a [dataframe constructor](https://stackoverflow.com/q/20109391/14627505) – Vladimir Fokow Aug 24 '22 at 17:45
  • @VladimirFokow Thank you for your response sir, additional information has been added, hopefully, it can explain the problem better – Edo Aug 25 '22 at 01:45
  • To understand you problem you have to study it a lot. Maybe you could simplify it as much as possible? Remove unnecessary columns, simplify numbers (like 1, 2, 3 instead of thousands). Emphasis on **minimal** example - the most minimal that is possible. – Vladimir Fokow Aug 26 '22 at 17:40

0 Answers0