CODEWITHSUNDEEP
pythonpandasdataframe
Chris Norris
Chris Norris

Reputation: 197

Dataframe - Create a column based on another with IF formulas

I'm struggling with this rather complex calculated column.

pandas

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?

Upvotes: 0

Views: 38

Answers (1)

Henry Yik
Henry Yik

Reputation: 22513

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

Upvotes: 1

Related Questions