cancel
Showing results for 
Search instead for 
Did you mean: 
Get Started Discussions
cancel
Showing results for 
Search instead for 
Did you mean: 

Calculation on a dataframe

Hareesh1980
New Contributor

Hi, I need to do following calculations on a dataframe. It should be done for each period and calculated value will be used for next period's calculation. Adding sample data and formula from excel here. Thanks in advance for your help.

Need to calculate NewCashFlow and NewAllocation for period 1 onwards.

Formula for NewCashFlow for period 1 is =(G5*F2)+E5+E2 and NewAllocation is =H5/SUM(H$5:H$7)

From Period 2 onwards NewCashFlow is =(G8*I5)+E8+H5 and NewAllocation is =H8/SUM(H$8:H$10)

Sample data is:

PeriodHorizonDateGroupCodeAssignmentCodeCashFlowAmtAllocationPercentUnassignedCashFlowAmtNewCashFlowNewAllocation
03/31/2023Group1Allocation 138316684.900.470.00  
03/31/2023Group1Allocation 22589294.650.030.00  
03/31/2023Group1Allocation 341209290.550.500.00  
14/30/2023Group1Allocation 1-3087.230.005846.8638316325.940.466620701
14/30/2023Group1Allocation 2-208.620.005846.862589270.400.031532438
14/30/2023Group1Allocation 3-3320.290.005846.8641208904.490.501846861
25/31/2023Group1Allocation 1-3104.730.00-11987.0038307627.820.466620701
25/31/2023Group1Allocation 2-209.810.00-11987.002588682.610.031532438
25/31/2023Group1Allocation 3-3339.110.00-11987.0041199549.740.501846861
36/30/2023Group1Allocation 1-3115.460.00-2954.6338303133.670.466620701
36/30/2023Group1Allocation 2-210.530.00-2954.632588378.910.031532438
36/30/2023Group1Allocation 3-3350.660.00-2954.6341194716.310.501846861
1 REPLY 1

Kaniz
Community Manager
Community Manager

Hi @Hareesh1980In general, to perform these calculations in a data frame in Spark, you would need to use the withColumn Function to create new columns ’NewCashFlow’ and ’NewAllocation’, and apply the necessary calculations using the existing columns.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.