How to group grouped values by different date by a single value per hour

Question

How to group grouped values by different date by a single value per hour

Navigation

#1 by (1 votes)

0

Good, I need to show the number of calls that are made every hour in a whole month. So far I was able to do a resample of the CSV so that I stayed as follows:

                     Cantidad
   Fecha
2017-03-01 00:00:00      5
2017-03-01 01:00:00      1
      .
      .
2017-03-31 22:00:00      7
2017-03-31 23:00:00      2

The date would be a datetimeIndex and group all values in 1 hour intervals. What I need now is to be able to group all the rows by the hour, regardless of the day, that is to say that I stayed this way, for example:

                     Cantidad
   Fecha
  2017-03  00:00:00     600
  2017-03  01:00:00     200
  2017-03  02:00:00      30
      .
      .
  2017-03  22:00:00     500
  2017-03  23:00:00     150

Look in several posts and in the panda documentation but I could not see anything that could be useful or that would give me the expected result

python csv pandas

asked by Nico Messina 05.05.2017 в 01:25

source

1 answer

Error in Java, "Can not inherit from final class error" Improve performance when loading images with reactjs

score 1 · Answer 1

If I have not misunderstood you need to group for hours within each month.

To create a reproducible example let's assume that we have the following input csv called agrupar_horas.csv and similar to the example you show:

Date, Quantity
  2017-03-01 00: 00: 00,1
  2017-03-01 01: 00: 00,2
  2017-03-01 22: 00: 00,3
  2017-03-01 23: 00: 00,4
  2017-03-02 00: 00: 00,1
  2017-03-02 01: 00: 00,2
  2017-03-02 22: 00: 00,3
  2017-03-02 23: 00: 00,4
  2017-04-01 00: 00: 00,5
  2017-04-01 01: 00: 00,6
  2017-04-01 22: 00: 00.7
  2017-04-01 23: 00: 00,8
  2017-04-02 00: 00: 00,5
  2017-04-02 01: 00: 00,6
  2017-04-02 22: 00: 00.7
  2017-04-02 23: 00: 00,8

To do what you want if I have not confused the closest thing I can think of is using groupby :

import pandas as pd

df = pd.read_csv('agrupar_horas.csv', index_col=0, parse_dates=True)

periodo = df.index.to_period("M")
df2 = df.groupby([ periodo, df.index.time]).sum()

print(df2)

Exit:

                  Cantidad
Fecha                     
2017-03 00:00:00         2
        01:00:00         4
        22:00:00         6
        23:00:00         8
2017-04 00:00:00        10
        01:00:00        12
        22:00:00        14
        23:00:00        16