0

Is it possible to reform a new dataframe without using loops?

I've a dataframe:

import pandas as pd
df = pd.DataFrame( [[ 0,  4.0,  0,  0,  3.0,  0,  0,  5.0,  2.0,   0,   0,   0],
                     [ 0,  2.0,  3.0,  4.0,  0,  6.0,  7.0,  8.0,  0,  10.0,  11.0,  12.0],
                      [11.0,  0,  9.0,  8.0,  7.0,  0,  0,  2.0,  4.0,   0,   8.0,   8.0],
                      [1.0 , 0,  3.0,  5.0,  0,  7.0,  9.0,  0,  0,  12.0,  11.0,  10.0]],
                      columns=['JAN',  'FEB',  'MAR',  'APR',  'MAY',  'JUN',  'JUL',  'AUG',  'SEP',   'OCT',   'NOV',   'DEC']
)
idx= pd.Index(['j1','j2','j3','j4'])
df = df.set_index(idx)

a lookup table:

lookup =  pd.DataFrame( [[ 1,      1,       0,       1.0],
                        [ 1,      0,       2,       0.5],
                      [ 1,      2 ,      0 ,      0.5],
                      [ 0,      0,       0,       0]],
                      columns=['move_flag',  'forward',  'backward',  'portions']
)
idx= pd.Index(['j1','j2','j3','j4'])
lookup = lookup.set_index(idx)

move_flag =1 means data can move forward or backward by some portions of its data;

=0 means no movement/ stay the same

example: j2 (backward for 2 months with 0.5), for Jan nothing move, for Feb 2*0.5 move to Jan

For Mar, 3*0.5 move to Feb and Jan, and so on

Same as j3 (forward for 2 months with 0.5), for Jan 11*0.5 move to Feb and Mar....

And until Dec, and sum up all forward or backward the values with its original value

The detail as: enter image description here

The new dataframe should be as:

result = pd.DataFrame( [[ 4.0,  4.0,  0.0,  0.0,  0.0,  0.0,  5.0,  7.0,  2.0,   0,   0,   0],
                     [ 2.5, 5.5,  5.0,  7.0,  6.5,  13.5,  11.0,  13.0,  10.5,  21.5,  17.0,  12.0],
                      [11.0,  5.5, 14.5,  12.5,  15.5,  7.5,  3.5,  2.0,  5.0,   3.0,   10.0,   12.0],
                      [1.0 , 0.0,  3.0,  5.0,  0.0,  7.0,  9.0,  0.0,  0.0,  12.0,  11.0,  10.0]],
                      columns=['JAN',  'FEB',  'MAR',  'APR',  'MAY',  'JUN',  'JUL',  'AUG',  'SEP',   'OCT',   'NOV',   'DEC']
)
idx= pd.Index(['j1','j2','j3','j4'])
result = result.set_index(idx)
simon leung
  • 61
  • 3
  • 1
    Refrain from showing your dataframe as an image. Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. – itprorh66 Nov 02 '22 at 23:08
  • 1
    No, there's certainly no built-in function to do specific processing like that. You will need to process it row-by-row. – Tim Roberts Nov 02 '22 at 23:32

0 Answers0