Python Forum
Creating column in python based on the other colums value count
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Creating column in python based on the other colums value count
#1
Photo 
Hello everyone!

Can someone help me, what code should I use to create a new column which will display cumulatively the number of preceding rows in which "0" appeared in the column NumEvents. The counting should reset and begin from 0 again every time a new record in the column USERS_PK1 appears.
from:

[Image: upRoh.png]

to smth like this:
[Image: ysqSC.png]

Thanks in advance!
Reply
#2
Hope this helps

import pandas as pd
import numpy as np


df1 = pd.DataFrame(data = {'USERS_PK1':[550395,550395,550395,550395,550395,931192,931192,931192,931192,931192],
                             'ACADEMIC_WEEK':[2,3,4,5,6,2,3,4,5,6],
                             'NumEvents':[0,14,9,0,1,0,0,3,2,0],})


df1['Zero'] = (((df1['USERS_PK1']==df1['USERS_PK1'].shift(1)) | (pd.isnull(df1['USERS_PK1'].shift(1)))) & (df1['NumEvents']==0)).cumsum()
Output:
USERS_PK1 ACADEMIC_WEEK NumEvents Zero 0 550395 2 0 1 1 550395 3 14 1 2 550395 4 9 1 3 550395 5 0 2 4 550395 6 1 2 5 931192 2 0 2 6 931192 3 0 3 7 931192 4 3 3 8 931192 5 2 3 9 931192 6 0 4
Bartek635 likes this post
Reply
#3
(Apr-14-2021, 04:30 PM)klllmmm Wrote: Hope this helps

import pandas as pd
import numpy as np


df1 = pd.DataFrame(data = {'USERS_PK1':[550395,550395,550395,550395,550395,931192,931192,931192,931192,931192],
                             'ACADEMIC_WEEK':[2,3,4,5,6,2,3,4,5,6],
                             'NumEvents':[0,14,9,0,1,0,0,3,2,0],})


df1['Zero'] = (((df1['USERS_PK1']==df1['USERS_PK1'].shift(1)) | (pd.isnull(df1['USERS_PK1'].shift(1)))) & (df1['NumEvents']==0)).cumsum()
Output:
USERS_PK1 ACADEMIC_WEEK NumEvents Zero 0 550395 2 0 1 1 550395 3 14 1 2 550395 4 9 1 3 550395 5 0 2 4 550395 6 1 2 5 931192 2 0 2 6 931192 3 0 3 7 931192 4 3 3 8 931192 5 2 3 9 931192 6 0 4
It works! Great, thank you a lot!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Question on pandas.dataframe merging two colums shomikc 4 783 Jun-29-2023, 11:30 AM
Last Post: snippsat
  create new column based on condition arvin 12 2,134 Dec-13-2022, 04:53 PM
Last Post: jefsummers
  Row Count and coloumn count Yegor123 4 1,268 Oct-18-2022, 03:52 AM
Last Post: Yegor123
  How to assign a value to pandas dataframe column rows based on a condition klllmmm 0 799 Sep-08-2022, 06:32 AM
Last Post: klllmmm
  Reshaping a single column in to multiple column using Python sahar 7 1,969 Jun-20-2022, 12:35 PM
Last Post: deanhystad
  df column mean and count of unique SriRajesh 0 1,093 May-07-2022, 08:19 AM
Last Post: SriRajesh
  Creating a numpy array from specific values of a spreadsheet column JulianZ 0 1,078 Apr-19-2022, 07:36 AM
Last Post: JulianZ
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,576 Mar-30-2022, 11:05 PM
Last Post: Pedroski55
  NaN when creating a new column... Menthix 2 1,135 Mar-15-2022, 08:40 AM
Last Post: Menthix
  pandas pivot table: How to find count for each group in Index and Column JaneTan 0 3,229 Oct-23-2021, 04:35 AM
Last Post: JaneTan

Forum Jump:

User Panel Messages

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