3
$\begingroup$

I have the pandas df in the following format (with NoOFDays being EndDate - StartDate)

------------------------------------------
|Item | StartDate | EndDate    | NoOFDays|
------------------------------------------
| A |  01-Jan-2018| 04-Jan-2018|    4    |
| A |  07-Jan-2018| 08-Jan-2018|    2    |
| B |  03-Jan-2018| 05-Jan-2018|    3    |
| A |  03-Jan-2018| 05-Jan-2018|    3    |
------------------------------------------

And wanted to get the count of each day grouped by item

A 01-Jan-2018 1
A 02-Jan-2018 1
A 03-Jan-2018 2
A 04-Jan-2018 2
A 05-Jan-2018 1
A 06-Jan-2018 0
A 07-Jan-2018 1
A 08-Jan-2018 1
B 01-Jan-2018 0
B 02-Jan-2018 0
B 03-Jan-2018 1
B 04-Jan-2018 1
B 05-Jan-2018 1
B 06-Jan-2018 0
B 07-Jan-2018 0
B 08-Jan-2018 0
Improve this question
$\endgroup$

2 Answers 2

Reset to default
4
$\begingroup$

Try this. It is a bit ugly, as I couldn't manage to get a solution without iterating through one of the dataframes. It could be done by iterating over the original data, or the new df with the list of all dates.

I had a similar problem, any cleaner ways of doing this would be appreciated.

import pandas as pd

df = pd.DataFrame(data={'Item':['A','A','B','A'], 
                        'StartDate':['2018-01-01','2018-01-07', '2018-01-03','2018-01-03'],
                        'EndDate': ['2018-01-04','2018-01-08','2018-01-05','2018-01-05']})
df['StartDate'] = pd.to_datetime(df['StartDate'])
df['EndDate'] = pd.to_datetime(df['EndDate'])


index = pd.MultiIndex.from_product([['A','B'],pd.date_range(start = df.StartDate.min(), end = df.EndDate.max())], names=['item', 'date'])
new_df = pd.DataFrame(index = index, data = {'counts':0})

dates_list = new_df.index.get_level_values(1)
items_list = new_df.index.get_level_values(0)
for row in df.itertuples():
  new_df.counts[(items_list == row.Item) & (dates_list>=row.StartDate) & (dates_list<= row.EndDate)] += 1

print(new_df)

Which gives this. You can remove the multi-index later if needed

                 counts
item date              
A    2018-01-01       1
     2018-01-02       1
     2018-01-03       2
     2018-01-04       2
     2018-01-05       1
     2018-01-06       0
     2018-01-07       1
     2018-01-08       1
B    2018-01-01       0
     2018-01-02       0
     2018-01-03       1
     2018-01-04       1
     2018-01-05       1
     2018-01-06       0
     2018-01-07       0
     2018-01-08       0
Improve this answer
$\endgroup$
0
$\begingroup$

What you are looking for is groupby with multiple columns. Based on this example your pandas groupby would look like this :

df.groupby(['Item', 'StartDate']).agg(['count'])

Improve this answer
$\endgroup$
3
  • $\begingroup$ But Nischal , the dataset over here isn't straight forward to apply groupby. Meaning , the first row has the following implicit dates - 01 Jan , 02 Jan , 03 Jan , 04 Jan) second record has the dates - 07 Jan , 08-Jan and so on... I understand I need to use date functionality to get all the dates in each row and then group it. But unsure of how to do this using pandas utilities. $\endgroup$
    – Maddy
    Commented May 23, 2018 at 8:46
  • $\begingroup$ @Maddy Can you add the sample exactly how your dataset is like, then we can help.. $\endgroup$
    – Aditya
    Commented May 23, 2018 at 8:55
  • $\begingroup$ Try this df.gropuby('date col').datecol.count() $\endgroup$
    – Aditya
    Commented May 23, 2018 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.