I have a DataFrame like this:
| start_day | end_day | value |
|---|---|---|
| 1 | 4 | 1 |
| 1 | 6 | 2 |
| 1 | 8 | 3 |
I now want to generate a new DataFrame, where for each day (1-8) the value is determined by taking the value of the row where day is in [start_day, end_day] and the duration (end_day - start_day) is minimal. Eg:
| day | value |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
| 6 | 2 |
| 7 | 3 |
| 8 | 3 |
Is there a way to do this without iterating through all the days and computing the value?
First create new columns filled by ranges, then use DataFrame.explode and last aggregate min:
df['day'] = df.apply(lambda x: range(x['start_day'], x['end_day']+1), axis=1)
df = df.explode('day').groupby('day', as_index=False)['value'].min()
print (df)
day value
0 1 1
1 2 1
2 3 1
3 4 1
4 5 2
5 6 2
6 7 3
7 8 3
EDIT:
df['duration'] = df.end_day - df.start_day
df['day'] = df.apply(lambda x: range(x['start_day'], x['end_day']+1), axis=1)
df1 = df.explode('day').reset_index(drop=True)
df = df1.loc[df1.groupby('day').duration.idxmin()]
print (df)
start_day end_day value duration day
0 1 4 1 3 1
1 1 4 1 3 2
2 1 4 1 3 3
3 1 4 1 3 4
8 1 6 2 5 5
9 1 6 2 5 6
16 1 8 3 7 7
17 1 8 3 7 8