Python Forum
Do Calculation between Rows based on Column values - Pandas Dataframe
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Do Calculation between Rows based on Column values - Pandas Dataframe
#1
Hello, I have a sharepoint list that I am importing in as a Pandas dataframe. The resulting code generates a df which looks something like below

                                        Task Name              % Complete  ...   Modified By   Version   Identifier
0    Equipment Intelligent Special Network System                    0.00  ...  Dominic Leow     1.0          1
1              Core Switch, 24SFP+8GE, Combo+4SFP                    0.00  ...  Dominic Leow     1.0        1.1
2                                         Level 1                    0.00  ...  Dominic Leow     1.0      1.1.1
3                                         Hacking                    0.30  ...  Dominic Leow     2.0    1.1.1.1
4                                      PVC Piping                    0.20  ...  Dominic Leow     2.0    1.1.1.2
5                                        Trunking                    0.45  ...  Dominic Leow     2.0    1.1.1.3
6                                         Cabling                    0.90  ...  Dominic Leow     2.0    1.1.1.4
7                                         Testing                    0.25  ...  Dominic Leow     2.0    1.1.1.5
8                                     Termination                    0.10  ...  Dominic Leow     2.0    1.1.1.6
9                                         Level 2                    0.00  ...  Dominic Leow     1.0      1.1.2
10                                        Hacking                    0.00  ...  Dominic Leow     1.0    1.1.2.1
11                                     PVC Piping                    0.00  ...  Dominic Leow     1.0    1.1.2.2
12                                       Trunking                    0.00  ...  Dominic Leow     1.0    1.1.2.3
13                                        Cabling                    0.00  ...  Dominic Leow     1.0    1.1.2.4
14                                        Testing                    0.00  ...  Dominic Leow     1.0    1.1.2.5
15                                    Termination                    0.00  ...  Dominic Leow     1.0    1.1.2.6
16                                        Level 3                    0.00  ...  Dominic Leow     1.0      1.1.3
The last column is the 'Identifier' field which basically has patterns based on the level of task. I need to calculate the % complete between two Identifiers that has a pattern (x.x.x - where x is a digit) and then update the total percentage task completed in the % complete column for the upper task having Identifier (x.x.x)

Below is the code i have come up with until now but I don't know where to go with this anymore - Please help, the field reports for my company is dependent on this.

from shareplum import Site
from shareplum import Office365
import pandas as pd
import re
pd.set_option('display.max_rows', None)

authcookie = Office365('https://speedmax.sharepoint.com', username='username', password='password').GetCookies()
site = Site('https://speedmax.sharepoint.com/sites/jdtstadium', authcookie=authcookie)
sp_list = site.List('joblist')
data = sp_list.GetListItems('All Tasks', rowlimit=5000)
df = pd.DataFrame(data)

stringMatch_mainTask = re.compile(r'^\d$')
stringMatch_bqItem = re.compile(r'^\d'+'.'+'\d$')
stringMatch_level = re.compile(r'^\d'+'.'+'\d'+'.'+'\d$')
stringMatch_job	= re.compile(r'^\d'+'.'+'\d'+'.'+'\d'+'.'+'\d$')

mainTaskdf = df[df['Identifier'].str.contains(stringMatch_mainTask)]
bqItemdf = df[df['Identifier'].str.contains(stringMatch_bqItem)]
leveldf = df[df['Identifier'].str.contains(stringMatch_level)]
jobdf = df[df['Identifier'].str.contains(stringMatch_job)]


for index, row in df.iterrows():
    countRows = jobdf.shape[0]
    visualRows = df
    print(df)
    break

# print(jobdf)
Reply


Messages In This Thread
Do Calculation between Rows based on Column values - Pandas Dataframe - by ahmedwaqas92 - Jan-28-2020, 07:06 AM

Possibly Related Threads…
Thread Author Replies Views Last Post
  Adding PD DataFrame column bsben 2 241 Mar-08-2024, 10:46 PM
Last Post: deanhystad
  Get an average of the unique values of a column with group by condition and assign it klllmmm 0 220 Feb-17-2024, 05:53 PM
Last Post: klllmmm
  Converting column of values into muliple columns of counts highland44 0 205 Feb-01-2024, 12:48 AM
Last Post: highland44
  Python Alteryx QS-Passing pandas dataframe column inside SQL query where condition sanky1990 0 689 Dec-04-2023, 09:48 PM
Last Post: sanky1990
  How is pandas modifying all rows in an assignment - python-newbie question markm74 1 652 Nov-28-2023, 10:36 PM
Last Post: deanhystad
  pandas : problem with conditional filling of a column Xigris 2 591 Jul-22-2023, 11:44 AM
Last Post: Xigris
  Question on pandas.dataframe merging two colums shomikc 4 782 Jun-29-2023, 11:30 AM
Last Post: snippsat
  Transposing a dataframe without creating NaN values doug2019 2 932 Mar-18-2023, 03:14 PM
Last Post: jefsummers
  Pandas AttributeError: 'DataFrame' object has no attribute 'concat' Sameer33 5 5,298 Feb-17-2023, 06:01 PM
Last Post: Sameer33
  Difference one column in a dataframe Scott 0 619 Feb-10-2023, 08:41 AM
Last Post: Scott

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020