CODEWITHSUNDEEP
pythonpandas
cwohlfart
cwohlfart

Reputation: 177

Calculate number ofdays until first value appears in pandas dataframe

I have the follosing dataset:

import pandas as pd
from datetime import datetime
import numpy as np

date_rng = pd.date_range(start='2020-07-01', end='2020-07-10', freq='d')
l1 = [np.nan, np.nan, 3, np.nan, np.nan, 4, np.nan, np.nan, 5, np.nan]
l2 = [np.nan, np.nan, np.nan, np.nan, np.nan, 4, np.nan, np.nan, 1, 3]

df = pd.DataFrame({
    'date':date_rng,
    'value':l1,
    'group':'a'
})
df2 = pd.DataFrame({
    'date':date_rng,
    'value':l2,
    'group':'b'
})

df = df.append(df2, ignore_index=True)
df

I would like to count the days until the first value appears for each group. I was able to find the date with the following code, but would get the number of days for each group.

# first valid valuefor each column
df.set_index(["date"]).groupby('group')['value'].apply(pd.Series.first_valid_index)

EDIT: This would be the expected outcome:

columns = ["group", "number_of_days"]

df_features = pd.DataFrame([["a", 3],
                            ["b", 6],],
                  columns=columns)
df_features

Upvotes: 1

Views: 49

Answers (1)

jezrael
jezrael

Reputation: 863056

Use GroupBy.first for first days per groups, subtract by Series.sub, convert to days by Series.dt.days, add 1 and convert to 2 column DataFrame:

s1 = df.groupby('group')['date'].first()
s2 = df.set_index(["date"]).groupby('group')['value'].apply(pd.Series.first_valid_index)

df = s2.sub(s1).dt.days.add(1).reset_index(name='number_of_days')
print (df)
  group  number_of_days
0     a               3
1     b               6

Upvotes: 2

Related Questions