Warm tip: This article is reproduced from stackoverflow.com, please click
pandas python python-3.x

Creating columns in df with day and hour of week based on value

发布于 2020-03-27 15:41:15

I am trying to create 2 columns based of a column that contains numerical values.

  Value 
    0
    4
    10
    24
    null
    49

Expected Output:
  Value           Day      Hour
    0           Sunday   12:00am
    4           Sunday   4:00am
    10          Sunday   10:00am
    24          Monday   12:00am
    null        No Day    No Time
    49          Tuesday   1:00am
    Continued.....

Code I am trying out:

    value = df.value.unique()
    Sunday_Starting_Point = pd.to_datetime('Sunday 2015') 
    (Sunday_Starting_Point + pd.to_timedelta(Value, 'h')).dt.strftime('%A %I:%M%P')

Thanks for looking!

Questioner
Chris90
Viewed
23
分享
jezrael 2020-01-31 16:44

I think unique values are not necessary, you can use 2 times dt.strftime for 2 columns with replace with NaT values:

Sunday_Starting_Point = pd.to_datetime('Sunday 2015') 
x = pd.to_numeric(df.Value, errors='coerce')
s = Sunday_Starting_Point + pd.to_timedelta(x, unit='h')
df['Day'] = s.dt.strftime('%A').replace('NaT','No Day')
df['Hour'] = s.dt.strftime('%I:%M%p').replace('NaT','No Time')
print (df)
   Value      Day     Hour
0    0.0   Sunday  12:00AM
1    4.0   Sunday  04:00AM
2   10.0   Sunday  10:00AM
3   24.0   Monday  12:00AM
4    NaN   No Day  No Time
5   49.0  Tuesday  01:00AM
Chris90 2020-01-31 16:38:05

Thanks, when I try I receive value error of ValueError: unit abbreviation w/o a number @jezrael

jezrael 2020-01-31 16:40:23

@Chris90 - It seems some non numeric values, can you check if working s = Sunday_Starting_Point + pd.to_timedelta(df.Value, unit='h', errors='coerce') instead s = Sunday_Starting_Point + pd.to_timedelta(df.Value, unit='h')

Chris90 2020-01-31 16:43:32

Thanks, it runs, but it gives same value of Sunday for Day and 12:00AM for Hour for every row

jezrael 2020-01-31 16:44:50

@Chris90 - It seems non numeric values, how working x = pd.to_numeric(df.Value, errors='coerce') s = Sunday_Starting_Point + pd.to_timedelta(x, unit='h') ?

Chris90 2020-01-31 16:45:31

Yeah this seems to populate and checks with what I am getting, thanks for helping to verify!

Related issues

1
How to use python cut method to create bins, accept one parameter and return appropriate bin?
2
Create a dictionary from a list of lists with certain criteria
3
selecting columns based on row value, Python, Pandas
4
plotting count of zeros and ones in a dataframe
5
BeautifulSoup find.all() web scraping returns empty
6
python function. output a keys list from a dictionary if the key is todays date
7
Best way to perform multiple amount of Pandas lookups between two DataFrames
8
How to get the number of columns and the width of each column in a Pandas pivot table?
9
Display a column when a desired value is missing while grouping in Pandas dataframe
10
Python hide ticks but show tick labels