Filling empty "B column" cells with last known value if rows have same "A column" value

user_9134545 0
Viewed 253

Im really new to Python and Datascience.

I have a large Dataset(with 100K+ rows), in this dataset i have two columns A and B. A is a Datetime column and B is string.

Column B has some NaN values, i want to fill those NaN values with latest known B column value, given the condition that my empty B column row and already filled B column row are in the same day, month and year (Column A).

Lemme explain my Self:

Let's say that's my input:

df=pd.DataFrame({'A': ["2019-03-13 08:12:23", "2019-03-13 07:10:18", "2019-03-20 08:12:23", "2019-03-13 08:12:23", "2019-03-15 10:35:53", "2019-03-20 11:12:23"], 'B': ["B1", "B0", "B13", np.nan, "B10", "B12"]})
                     A    B
0  2019-03-13 08:12:23   B1
1  2019-03-13 07:10:18   B0
2  2019-03-20 08:12:23  B13
3  2019-03-13 08:12:23  NaN
4  2019-03-15 10:35:53  B10
5  2019-03-20 11:12:23  B12

I want to fill the NaN value with B1(B value that occurs the same day and has the biggest time given the condition that this "Biggest time" isn't ahead of the actual A column value).

So my output should look like this:

                     A    B
0  2019-03-13 08:12:23   B1
1  2019-03-13 07:10:18   B0
2  2019-03-20 08:12:23  B13
3  2019-03-13 08:12:23   B1
4  2019-03-15 10:35:53  B10
5  2019-03-20 11:12:23  B12

I tried to achieve this with no success, the best i could do is making NaN Value to B13 using this :

df['B']=df['B'].replace({'B': {0: np.nan}}).ffill()

Can you please guys tell me what's the fatest and most economic way to achieve this?

data-science pandas dataset python
3 Answers

Use groupby+ffill

as_date = pd.to_datetime(df.A)
s = np.argsort(as_date)

df['B'] = df.B.loc[s].groupby(as_date.loc[s].dt.date).ffill().loc[df.index]

                     A    B
0  2019-03-13 08:12:23   B1
1  2019-03-13 07:10:18   B0
2  2019-03-20 08:12:23  B13
3  2019-03-13 08:12:23   B1
4  2019-03-15 10:35:53  B10
5  2019-03-20 11:12:23  B12
user_25356110

Try groupby().idxmax():

max_val = df.loc[df['A'].groupby(df['A'].dt.date).transform('idxmax'),'B'].values

df['B'] = np.where(df['B'].isna(), max_val, df['B'])

Output:

                    A    B
0 2019-03-13 08:12:23   B1
1 2019-03-13 07:10:18   B0
2 2019-03-20 08:12:23  B13
3 2019-03-13 08:12:23   B1
4 2019-03-15 10:35:53  B10
5 2019-03-20 11:12:23  B12
user_42384080

Your task can be performed with the following one-liner:

df.B = df.sort_values('A').groupby(pd.to_datetime(df.A).dt.date).B.ffill()
user_73884770
Related