Change index in a pandas dataframe and add additional time column

Question

I have a pandas dataframe that currently has no specifiy index (thus when printing an automatic index is created which beginns with 0). Now I would like to have a "timeslot" index that beginns with 1 and an additional "time of the day" column in the dataframe. Here you can see a screenshot of how theoutput csv should look like. Can you tell me how to do this?

I didn't downvote but please can you update your post with the input dataframe as plain text (just the left dataframe)? — Corralien
– Corralien, Commented Nov 29, 2021 at 15:38

Corralien · Accepted Answer · 2021-11-29 15:55:15Z

1

Try with pd.date_range:

df['time of day'] = pd.date_range('1970-1-1', periods=len(df), freq='H') \
                      .strftime('%H:%M')

Setup:

df = pd.DataFrame(np.random.randint(1, 50, (30, 2)), columns=['Column 1', 'Column 2'])
df.insert(0, 'time of day', pd.date_range('1970-1-1', periods=len(df), freq='H').strftime('%H:%M'))
df.index.name = 'timeslot'
df.index += 1
print(df)

# Output:
         time of day  Column 1  Column 2
timeslot                                
1              00:00        43        33
2              01:00        20        11
3              02:00        40        10
4              03:00        19        28
5              04:00        10        27
6              05:00        27        10
7              06:00         1        10
8              07:00        33        36
9              08:00        32         2
10             09:00        23        32
11             10:00         1        17
12             11:00        48        42
13             12:00        21         3
14             13:00        48        28
15             14:00        41        46
16             15:00        48        43
17             16:00        47         6
18             17:00        33        21
19             18:00        38        19
20             19:00        17        40
21             20:00         8        24
22             21:00        28        22
23             22:00         2        13
24             23:00        24         3
25             00:00         4         1
26             01:00         8         9
27             02:00        19        36
28             03:00        30        36
29             04:00        43        39
30             05:00        43         3

edited Nov 29, 2021 at 15:55

answered Nov 29, 2021 at 15:42

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

PeterBe Over a year ago

Thanks for your comment Corralien. Actually I have 2 remarks: 1) How can I shift the new created dataframe onto the second position (should be the second column) 2) How can I give the first column a name?

Corralien Over a year ago

@PeterBe. I updated my answer. Can you check my demo, please? Is it what you expect?

PeterBe Over a year ago

Thanks a lot for your tremendous help. I upvoted and accepted your answer. Just out of curiosity: Why are you specifying 1970-1-1 as the date and not the current date for example?

mozway Over a year ago

Seems overkill to use datetime just to convert an int to string :p

Corralien Over a year ago

@PeterBe. The date doesn't matter because we only keep the time.

|

mozway · Accepted Answer · 2021-11-29 16:45:48Z

1

Assuming your dataframe is df:

df['time of day'] = df.index.astype(str).str.rjust(2, '0')+':00'
df.index += 1

output: No output as no text input was provided

if there are more than 24 rows:

df['time of day'] = (df.index%24).astype(str).str.rjust(2, '0')+':00'
df.index += 1

edited Nov 29, 2021 at 16:45

answered Nov 29, 2021 at 15:41

mozway

267k13 gold badges56 silver badges106 bronze badges

Collectives™ on Stack Overflow

Change index in a pandas dataframe and add additional time column

2 Answers 2

7 Comments

if there are more than 24 rows:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

if there are more than 24 rows:

Comments

Your Answer

Sign up or log in

Post as a guest

Related