-1

Being working with a KPIs dataframe where I've a set of KPIs for different element and each KPI having a value hourly, e.g below.

The CSV text used to create the dataframe cand be found here : https://textsaver.flap.tv/lists/4ta4

Main Dataframe :

Main Dataframe

filtering by an element :

One element example

Filtering by another element :

Second element example

What I want to do actually is to have the Daily Occurence for each element on it's own, I've tried with the Resampling functions but the result is global to the whole Dataframe and not Cell column wise.

Result when using **Resample" function :

Resample function result

While below is the desired result :

Desired result

Any suggestions ?

DarKing
  • 25
  • 6
  • 1
    please provide a **minimal** example of input/output as **text** (to be able to copy/paste) – mozway May 17 '22 at 15:08
  • You can find the CSV used to create the input dataframe with this link : https://textsaver.flap.tv/lists/4ta4 – DarKing May 17 '22 at 15:17
  • Unfortunately, it is neither minimal nor guaranteed to be available on the long term – mozway May 17 '22 at 15:18
  • Would you pelase tell me how to do so then ? as the text file is really long and would make the post less clear – DarKing May 17 '22 at 15:25
  • Thats precisely the point, craft a dummy example that is only a few lines long and is sufficient to explain what you want to do – mozway May 17 '22 at 15:29
  • Please have a look at [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for more direction on creating a data sample – G. Anderson May 17 '22 at 20:52

1 Answers1

0

IUUC, you can try groupby date and Cell column then compute group sizes.

df['Time'] = pd.to_datetime(df['Time'])
out = df.groupby([df['Time'].dt.date, 'Cell']).size().reset_index(name='Count')

print(out)

          Time        Cell  Count
0   2022-05-08   LAL0005_H      7
1   2022-05-08  LAL0005_H2     11
2   2022-05-08  LAL0005_I2      1
3   2022-05-08  LAL0005_Y2      4
4   2022-05-09   LAL0005_H      2
5   2022-05-09  LAL0005_H2      7
6   2022-05-09  LAL0005_Y2      1
7   2022-05-10   LAL0005_H      1
8   2022-05-10  LAL0005_H2      9
9   2022-05-11   LAL0005_H      1
10  2022-05-11  LAL0005_H2      8
11  2022-05-12   LAL0005_H      2
12  2022-05-12  LAL0005_H2      7
13  2022-05-13   LAL0005_H      4
14  2022-05-13  LAL0005_H2     10
15  2022-05-14   LAL0005_H      8
16  2022-05-14  LAL0005_H2     12
Ynjxsjmh
  • 28,441
  • 6
  • 34
  • 52
  • Thank you it works, but I've a question. How did you get to that result (code) ? I'm pretty new to pandas and I'm struggling as I can't use my logical/algorithmical thinking, from the little time working with it, I found myself googling things and methods and even with the full correct answer I still don't see any algorithmic reasoning pattern to get to that result. If you have any advices I will be grateful. – DarKing May 18 '22 at 10:20
  • @DarKing Honestly I didn't get what you want with *What I want to do actually is to have the Daily Occurence for each element on it's own*. But your result pic is clear enough that you take `Time` and `Cell` as a group. That's why I use `.groupby([df['Time'].dt.date, 'Cell'])`. `GroupBy.size` is common api to compute the group size. – Ynjxsjmh May 18 '22 at 10:53