itertuples, new column, datetime, pandas

[karlito]

Hi,

I'm trying to select a range in a dataframe every time x <= 20 and create a new dataframe with a column which is the sum of the selected rows (negative value include) of that range and keep the last date as index. Thanks for some hints and if I'm on the right way :)

something like this --->
(Date as index)

This is what I tried so far but doesn't work

Date         x             Date         sum
2019-01-01   100           2019-01-05   353
2019-01-02   120   --->    2019-01-10   804
2019-01-03    80           2019-01-15   650
2019-01-04    48           2019-01-20   428
2019-01-05     5           ...
2019-01-06   110           ...
2019-01-07   420
2019-01-08   140
2019-01-09   126
2019-01-10     8
2019-01-11    50
2019-01-12   160   
2019-01-13   280
2019-01-14   148
2019-01-15    12
2019-01-16   190
2019-01-17   120
2019-01-18    80
2019-01-19    48
2019-01-20   -10
...
...

#######
for date in df.index.to_series().dt.date.unique():
   for row in df.itertuples():
      for i in row:
         if i <= 20:
           new_df = pd.DataFrame(columns=df.keys())
           new_df.index.name = 'Date'
           new_df ['sum'] = df.sum(axis = 0)
         continue:

[ThomasL]

Hi, is date the index and in your sum example, the first sum should be 353 not 343?
In your example the sequence length is always 5, is that guaranteed?

[karlito]

Hi, is date the index and in your sum example, the first sum should be 353 not 343?
In your example the sequence length is always 5, is that guaranteed?

Yes Date is already set as index and yes it should be 353 sorry typo. NO the sequence is not guaranteed because it changed over time. Thks for pointing that.

[karlito]

Hi, is date the index and in your sum example, the first sum should be 353 not 343?
In your example the sequence length is always 5, is that guaranteed?

Hi,
I thought you asked because you wanted to help? :)

[ThomasL]

Hi, sorry, i wasn´t able to solve your problem in my spare time.
My pandas knowledge isn´t good enough.
Maybe have a look at Kevin Markhams youtube videos and/or his website.

[perfringo]

I thought you asked because you wanted to help? :)

Did you provide sample data in easy to use (copy-paste) format?

Did you provided enough information ('Date as index' - is it datetime.date or what?, do you want select or sum all values less than 20?, are all errors and typos in sample eliminated?).

If one makes helping as hard as possible then it significantly reduces possibilities that someone is ready to invest time and effort into it.

[karlito]

I thought you asked because you wanted to help? :)

Did you provide sample data in easy to use (copy-paste) format?

Did you provided enough information ('Date as index' - is it datetime.date or what?, do you want select or sum all values less than 20?, are all errors and typos in sample eliminated?).

If one makes helping as hard as possible then it significantly reduces possibilities that someone is ready to invest time and effort into it.

Oh sorry about that! I will modify the original post and then add additional infos.