Count by date and time

Asked 1 weeks ago, Updated 1 weeks ago, 1 views

There is a document that shows the time by date. I want to convert it to hourly data by date! Help me.

I want to write an if sentence, but I have no idea.

python mysql

2022-09-20 08:43

2 Answers

There is MySQL in the tag, so I will upload the MySQL version.

If you approach it with IF, there will be no answer because this problem can only be solved by finding a rule that can be applied consistently for all data.

-- Assume that the tbl1 table has an id, created_at column.
SELECT
    concat(
        s.d, ':00~', -- 4. For example, if s.d === 2022-07-22 12:00?
        DATE_FORMAT (DATE_ADD (concat(s.d, ':00:00'), INTERVAL 1 HOUR), '%H:00') -- 5. You need to obtain a date that adds 1 hour to it, convert it to the form "13:00", and paste it after s.d.
    ) AS 'Wanted Outcome',
    s.c AS 'count'
FROM (
    SELECT
        DATE_FORMAT(t.created_at, '%Y-%m-%d%H') ASd, --3. Select values grouped for output and further formatting.
        count (t.id) ASc -- 2. Aggregates.
    FROM tbl1 t
    GROUP BY DATE_FORMAT (t.created_at, '%Y-%m-%d %H') --1. Group as of the start of the year-month-day.
) s;


2022-09-20 08:43

>>> import pandas as pd
>>> from io import StringIO
>>> dat = ''''Time
2021-01-31 21:45:03
2021-01-31 20:41:27
2021-01-31 20:20:55
2021-01-31 20:01:59
2021-01-31 19:45:03
2021-01-31 19:12:49
2021-01-31 18:40:24
2021-01-31 18:22:34'''
>>> df = pd.read_csv(StringIO(dat))


>>> df
                    Time
0  2021-01-31 21:45:03
1  2021-01-31 20:41:27
2  2021-01-31 20:20:55
3  2021-01-31 20:01:59
4  2021-01-31 19:45:03
5  2021-01-31 19:12:49
6  2021-01-31 18:40:24
7  2021-01-31 18:22:34


>>> df.resample ("H", on="Time").count()
Traceback (most recent call last):
  File "<pyshell#22>", line 1, in <module>
    df.resample ("H", on="time").count()
  File "C:\PROGRAMS\Python3864\lib\site-packages\pandas\core\frame.py", line 10350, in resample
    return super().resample(
  File "C:\PROGRAMS\Python3864\lib\site-packages\pandas\core\generic.py", line 8126, in resample
    return get_resampler(
  File "C:\PROGRAMS\Python3864\lib\site-packages\pandas\core\resample.py", line 1382, in get_resampler
    return tg._get_resampler(obj, kind=kind)
  File "C:\PROGRAMS\Python3864\lib\site-packages\pandas\core\resample.py", line 1558, in _get_resampler
    raise TypeError(
TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'Index'


>>> df["Time"] = pd.to_datetime (df["Time"])
>>> df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8 entries, 0 to 7
Data columns (total 1 columns):
 #   #   Column  Non-Null Count  Dtype         
---  ------  --------------  -----         
 0 hours 8 non-null datetime64[ns]
dtypes: datetime64[ns](1)
memory usage: 192.0 bytes


>>> df.resample ("H", on="Time").count()
                     Time
Time                     
2021-01-31 18:00:00   2
2021-01-31 19:00:00   2
2021-01-31 20:00:00   3
2021-01-31 21:00:00   1


2022-09-20 08:43

If you have any answers or tips


© 2022 pinfo. All rights reserved.