Dataframe - Create a column based on another with IF formulas

Question

I'm struggling with this rather complex calculated column.

The cumulative sum is watts of light.

It changes to 0 when the system resets it to a new day. So the 24hour day is sunrise to sunrise.

I want to use this fact to calculate a 'Date 2' that I can them summarize in the future to report average 24hr day temp, light etc.

For the First 0 Cumulative Sum of every Date, Date + 1 Day, Else the last row of Date 2.

I have been playing around with the following, assuming Advanced Date is a copy of Cumulative Sum:

for i in range(1, len(ClimateDF)):
    j = ClimateDF.columns.get_loc('AdvancedDate')

    if ClimateDF.iat[i, j] == 0 and ClimateDF.iat[i - 1, j] != 0:

        print(ClimateDF.iat[i, j])
       # ClimateDF.iat[i, 'AdvancedDate'] = 'New Day' #this doesn't work
        ClimateDF['AdvancedDate'].values[i] = 1

    else:

        print(ClimateDF.iat[i, j])
        #ClimateDF.iat[i, 'AdvancedDate'] = 'Not New Day' #this doesn't work
        ClimateDF['AdvancedDate'].values[i] = 2

This doesn't quite do what I want, but I thought I was close. However when I change:

ClimateDF['AdvancedDate'].values[i] = 1

to

ClimateDF['AdvancedDate'].values[i] = ClimateDF['Date'].values[i]

I get a:

TypeError: float() argument must be a string or a number, not 'datetime.date'

Am I on the right track? How do I get past this error? Is there a more efficient way I could be doing this?

Henry Yik · Accepted Answer · 2019-11-04 04:45:46Z

1

IIUC, you can first create a cumsum reflecting day change, and then calculate Date_2 by adding it to the first date:

s = (df["sum"].eq(0)&df["sum"].shift().ne(0)).cumsum()

df["Date_2"] = df["Datetime"][0]+pd.to_timedelta(s,unit="D") #base on first day to calculate offset for all days

edited Nov 4, 2019 at 4:45

answered Nov 4, 2019 at 4:28

Henry Yik

22.6k5 gold badges21 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Norris Over a year ago

This works - I just had to do (s-1,unit="D"). This condenses around 25 lines I had to 3! Could you explain how it is working? For example how does it deal with there being two zeros next to each other?

Chris Norris Over a year ago

I think I figured it out. If the sum == 0 AND the sum one row above == 0, it will return TRUE and cumulative sum these up. You then add this sum to the date in the very first row of the Datetime. Excellent!

Henry Yik Over a year ago

To be exact its sum ==0 and the sum one row above != 0, but you get the idea.

Collectives™ on Stack Overflow

Dataframe - Create a column based on another with IF formulas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related