CODEWITHSUNDEEP
pythonpandasdataframe
Pie-ton
Pie-ton

Reputation: 562

How to extract day and hour from a column in a DataFrame?

I have a data frame with one column "Date," with values like "10/9/2019 23:59". I want to create two columns: one containing the day ("10/9/2019") and another containing hour ("23").

here is my code:

import pandas as pd
df = pd.read_csv('date.csv', names = ['Date'],
                 low_memory=False, encoding = 'utf-8-sig', header = 0)

cdf = pd.DataFrame(columns =['day' , 'hour'])
i = 0

for index, line in df.iterrows():
    day = datetime.strptime(str(df["Date"]), '%m/%d/%Y %H:%M').date()
    cdf.at[i , 'day'] = day
    hour = datetime.strptime(str(df["Date"]), '%m/%d/%Y %H:%M').hour
    cdf.at[i , 'hour'] = hour  
    i +=1

but I get this error:

ValueError                                Traceback (most recent call last)
<ipython-input-26-0550c19671cf> in <module>
      3 
      4 for line in dd:
----> 5     day = datetime.strptime(str(df["Date"]), '%m/%d/%Y %H:%M').date()
      6     cdf.at[i , 'day'] = day
      7     hour = datetime.strptime(str(df["Date"]), '%m/%d/%Y %H:%M').hour

~\AppData\Local\Continuum\anaconda3\lib\_strptime.py in _strptime_datetime(cls, data_string, format)
    575     """Return a class cls instance based on the input string and the
    576     format string."""
--> 577     tt, fraction, gmtoff_fraction = _strptime(data_string, format)
    578     tzname, gmtoff = tt[-2:]
    579     args = tt[:6] + (fraction,)

~\AppData\Local\Continuum\anaconda3\lib\_strptime.py in _strptime(data_string, format)
    357     if not found:
    358         raise ValueError("time data %r does not match format %r" %
--> 359                          (data_string, format))
    360     if len(data_string) != found.end():
    361         raise ValueError("unconverted data remains: %s" %

ValueError: time data '0        10/9/2019 23:59\n1        10/9/2019 23:59\n2        10/9/2019 23:59\n3        10/9/2019 23:59\n4        10/9/2019 23:59\n5        10/9/2019 23:59\n6        10/9/2019 23:59\n7        10/9/2019 23:59\n8        10/9/2019 23:59\n9        10/9/2019 23:59\n10       10/9/2019 23:59\n11       10/9/2019 23:59\n12       10/9/2019 23:59\n13       10/9/2019 23:59\n14       10/9/2019 23:59\n15       10/9/2019 23:59\n16       10/9/2019 23:58\n17       10/9/2019 23:58\n18       10/9/2019 23:58\n19       10/9/2019 23:58\n20       10/9/2019 23:58\n21       10/9/2019 23:58\n22       10/9/2019 23:58\n23       10/9/2019 23:58\n24       10/9/2019 23:58\n25       10/9/2019 23:58\n26       10/9/2019 23:58\n27       10/9/2019 23:58\n28       10/9/2019 23:58\n29       10/9/2019 23:58\n              ...       \n38584     10/7/2019 2:57\n38585     10/7/2019 2:43\n38586     10/7/2019 2:35\n38587     10/7/2019 2:33\n38588     10/7/2019 2:29\n38589     10/7/2019 2:22\n38590     10/7/2019 2:16\n38591     10/7/2019 2:01\n38592     10/7/2019 1:54\n38593     10/7/2019 1:52\n38594     10/7/2019 1:45\n38595     10/7/2019 1:42\n38596     10/7/2019 1:35\n38597     10/7/2019 1:30\n38598     10/7/2019 1:23\n38599     10/7/2019 1:23\n38600     10/7/2019 1:20\n38601     10/7/2019 1:15\n38602     10/7/2019 1:14\n38603     10/7/2019 1:13\n38604     10/7/2019 1:11\n38605     10/7/2019 1:05\n38606     10/7/2019 0:52\n38607     10/7/2019 0:42\n38608     10/7/2019 0:29\n38609     10/7/2019 0:19\n38610     10/7/2019 0:17\n38611     10/7/2019 0:14\n38612     10/7/2019 0:06\n38613     10/7/2019 0:02\nName: Date, Length: 38614, dtype: object' does not match format '%m/%d/%Y %H:%M'

Here's a link to my data.

Upvotes: 1

Views: 147

Answers (2)

pissall
pissall

Reputation: 7399

You can easily do it in this way:

Example frame:

                     Date      vals
0      2000-01-01 00:00:00  0.000000
1      2000-01-01 00:01:00  0.000031
2      2000-01-01 00:02:00  0.000063
3      2000-01-01 00:03:00  0.000094
4      2000-01-01 00:04:00  0.000126

Please note, Date column is in datetime64[ns].

You can convert your date column using df["Date"] = pd.to_datetime(df["Date"]) if dates is string or object or any dtype which is not datetime

df["day"] = df["Date"].dt.date
df["hour"] = df["Date"].dt.hour

Upvotes: 2

89f3a1c
89f3a1c

Reputation: 1488

On read_csv(), specify parse_dates='Date'.

With that, you can then obtain the columns by doing:

df['day'] = df['Date'].dt.date
df['hour'] = df['Date'].dt.hour

EDIT1:

Realized you don't have header on input file. So instead of specifying parse_dates on read_csv(), you could convert the date column to datetime by:

df['Date'] = pd.to_datetime(df['Date'])

Hope this helps!

Upvotes: 2

Related Questions