Python-Calculation with pandas data frames

Question

I am new to python. I am trying to learn pandas with below example. I have two data frames below.

First one is,

CCP_DETAILS_SID BASE_LINE
    1            1235.89
    2            369.32
    3            9863.1

And Second one is,

CCP_DETAILS_SID PERIOD_SID  GROWTH
1                  601       0.1
1                  602       0.2
1                  603       0.3
2                  601       0.1
2                  602       0.2
2                  603       0.3
3                  601       0.1
3                  602       0.2
3                  603       0.3

by merging above two, I am trying to calculate a field called 'PROJECTION_SALES'. Formula and examples for the field i have listed below.

Projection_Sales=(Base_Line)*(1+Growth) and the merge or join condition between two data frames is CCP_DETAILS_SID.

Examples

    Projection_Sales(ccp_details_sid=1 and period_sid=601)=1235.89*(1+0.1)

    Projection_Sales(ccp_details_sid=1 and period_sid=602)=1235.89*(1+0.1)*(1+0.2)

    Projection_Sales(ccp_details_sid=1 and period_sid=603)=1235.89*(1+0.1)*(1+0.2)*(1+0.3)

    Projection_Sales(ccp_details_sid=2 and period_sid=601)=369.32*(1+0.1).

Same way of calculation applies to other rows in the data frames. And sample output i listed below.

CCP_DETAILS_SID PERIOD_SID  PROJECTION_SALES
1                 601        1359.479
1                 602        1631.3748
1                 603        2120.78724
2                 601        406.252
2                 602        487.5024
2                 603        633.75312
3                 601        10849.41
3                 602        13019.292
3                 603        16925.0796

I have tried some thing like below

pd.merge(first,second,how='inner',on='CCP_DETAILS_SID')

After this step i need to extend code with the use of cumprod. Because you can observe above examples are having cumulative product logic etc.

Can you people please suggest me a way to complete this calculation?.

MaxU - stand with Ukraine · Accepted Answer · 2018-03-16 11:21:00Z

Is that what you want?

In [145]: t = d1.merge(d2)

In [146]: (t.assign(x=t.assign(x=t.GROWTH+1)
                       .groupby('CCP_DETAILS_SID')['x']
                       .cumprod())
            .eval("Projection_Sales = BASE_LINE * x")
            .drop('x',1))
Out[146]:
   CCP_DETAILS_SID  BASE_LINE  PERIOD_SID  GROWTH  Projection_Sales
0                1    1235.89         601     0.1        1359.47900
1                1    1235.89         602     0.2        1631.37480
2                1    1235.89         603     0.3        2120.78724
3                2     369.32         601     0.1         406.25200
4                2     369.32         602     0.2         487.50240
5                2     369.32         603     0.3         633.75312
6                3    9863.10         601     0.1       10849.41000
7                3    9863.10         602     0.2       13019.29200
8                3    9863.10         603     0.3       16925.07960

Collectives™ on Stack Overflow

Python-Calculation with pandas data frames

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related