How to change column values in dataframe in python?

Question

I have created a dataframe in python,

import pandas as pd
d = {'col1': ["day1", "7:00", "8:00","9:00", "10:00", "11:00",
               "day2", "7:00", "8:00","9:00", "10:00", "11:00",
               "day3", "7:00", "8:00","9:00", "10:00", "11:00"],
      'col2': [0, 4.1, 3, 3.5, 45.1, 16.9,
               0, 6.5, 4, 9.8, 33.9, 19.8,
               0, 6.9, 2.5, 7, 81.1, 13.8]}
df = pd.DataFrame(data=d)
print(df)
  col1  col2
0    day1   0.0
1    7:00   4.1
2    8:00   3.0
3    9:00   3.5
4   10:00  45.1
5   11:00  16.9
6    day2   0.0
7    7:00   6.5
8    8:00   4.0
9    9:00   9.8
10  10:00  33.9
11  11:00  19.8
12   day3   0.0
13   7:00   6.9
14   8:00   2.5
15   9:00   7.0
16  10:00  81.1
17  11:00  13.8

I want to change all those timeline data in col1 into days, for example

 col1  col2
    0    day1   0.0
    1    day1   4.1
    2    day1   3.0
    3    day1   3.5
    4    day1  45.1
    5    day1  16.9
    6    day2   0.0
    7    day2   6.5
    8    day2   4.0
    9    day2   9.8
    10   day2  33.9
    11   day2  19.8
    12   day3   0.0
    13   day3   6.9
    14   day3   2.5
    15   day3   7.0
    16   day3  81.1
    17   day3  13.8

It is just a sample data set. So I hope to have a little general answer to solve this problem. Like if we have 1000 days data set..

This is a marked improvement in your question quality. Well done, keep it up. — cs95
– cs95, Commented Jan 9, 2018 at 22:19

BENY · Accepted Answer · 2018-01-09 22:19:21Z

3

df.col1=df.col1.where(df.col1.str.isalnum()).ffill()
df
Out[242]: 
    col1  col2
0   day1   0.0
1   day1   4.1
2   day1   3.0
3   day1   3.5
4   day1  45.1
5   day1  16.9
6   day2   0.0
7   day2   6.5
8   day2   4.0
9   day2   9.8
10  day2  33.9
11  day2  19.8
12  day3   0.0
13  day3   6.9
14  day3   2.5
15  day3   7.0
16  day3  81.1
17  day3  13.8

answered Jan 9, 2018 at 22:19

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

cs95 Over a year ago

Or df.col1 = df.col1.where(df.col1.str.startswith('day')).ffill().

BENY Over a year ago

@cᴏʟᴅsᴘᴇᴇᴅ Yes ; -) Nice alternative !!

Deena · Accepted Answer · 2018-01-09 22:15:07Z

3

Try discarding the timestamps and forward-filling:

# Remove timestamps 
discard_mask = ~df.col1.str.startswith('day')
df.loc[discard_mask, "col1"] = np.nan

# Forward fill
df.ffill()

#     col1  col2
# 0   day1   0.0
# 1   day1   4.1
# 2   day1   3.0
# 3   day1   3.5
# 4   day1  45.1
# 5   day1  16.9
# 6   day2   0.0
# 7   day2   6.5
# 8   day2   4.0
# 9   day2   9.8
# 10  day2  33.9
# 11  day2  19.8
# 12  day3   0.0
# 13  day3   6.9
# 14  day3   2.5
# 15  day3   7.0
# 16  day3  81.1
# 17  day3  13.8

answered Jan 9, 2018 at 22:15

Deena

6,2236 gold badges36 silver badges41 bronze badges

3 Comments

APorter1031 Over a year ago

What does ~df do?

Deena Over a year ago

@APorter1031 negates the mask (ie: not operator)

aiven Over a year ago

negate: ~[true, true, false] = [false, false, true]

Collectives™ on Stack Overflow

How to change column values in dataframe in python?

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related