Take next group value and assign it to the current group element

user_6660373 0
Viewed 57

My sample df is like this:

                    hr  temp1
hour        
1900-01-01 00:00:00 0   19
1900-01-01 00:15:00 0   19
1900-01-01 00:30:00 0   19
1900-01-01 00:45:00 0   19
1900-01-01 01:00:00 1   18
1900-01-01 01:15:00 1   18
1900-01-01 01:30:00 1   18
1900-01-01 01:45:00 1   18
1900-01-01 02:00:00 2   18
1900-01-01 02:15:00 2   18
1900-01-01 02:30:00 2   18
1900-01-01 02:45:00 2   18
1900-01-01 03:00:00 3   17

expected output(after transformation):

                    hr  temp1 temp
hour        
1900-01-01 00:00:00 0   19    19 # as it is (current elem)
1900-01-01 00:15:00 0   19    18 # next group(hr=1) element
1900-01-01 00:30:00 0   19    18 # next group(hr=1) element
1900-01-01 00:45:00 0   19    18 # next group(hr=1) element
1900-01-01 01:00:00 1   18    18 # as it is (current elem)
1900-01-01 01:15:00 1   18    18 # next group(hr=2) element
1900-01-01 01:30:00 1   18    18 # next group(hr=2) element
1900-01-01 01:45:00 1   18    18 # next group(hr=2) element
1900-01-01 02:00:00 2   18    18 # as it is (current elem)
1900-01-01 02:15:00 2   18    17 # next group(hr=3) element
1900-01-01 02:30:00 2   18    17 # next group(hr=3) element
1900-01-01 02:45:00 2   18    17 # next group(hr=3) element
1900-01-01 03:00:00 3   17    17 # as it is (current elem)

So basically I want to put the next group value to the current group. keeping the first value of the row be same.

Since I have done resampling by 15min so there are repeated values for each hr group.

I ain't able to solve it.

Edit:

One solution is to do the shifting and assign value before resampling. because when I will pad I will have the next group element values. I can change the every group 1st value to be as row['temp'] something like this.

this is the data before resampling:

                    temp1   hr
hour        
1900-01-01 00:00:00 19      0
1900-01-01 01:00:00 18      1
1900-01-01 02:00:00 18      2
1900-01-01 03:00:00 17      3

Edit2:

Before resampling:

df[['temp1']].shift(-1).fillna(df.iloc[df.shape[0]-1]['temp1']).resample('900s').pad()

and then do the 1st group 1st element assignment.

but this looks too complex.

pandas python
2 Answers

Edit

If your data are starting like this, as you show in your edit:

                     temp
1900-01-01 00:00:00    19
1900-01-01 01:00:00    18
1900-01-01 02:00:00    18
1900-01-01 03:00:00    17

You can just resample and backfill:

>>> df.resample('15T').bfill()

                     temp
1900-01-01 00:00:00    19
1900-01-01 00:15:00    18
1900-01-01 00:30:00    18
1900-01-01 00:45:00    18
1900-01-01 01:00:00    18
1900-01-01 01:15:00    18
1900-01-01 01:30:00    18
1900-01-01 01:45:00    18
1900-01-01 02:00:00    18
1900-01-01 02:15:00    17
1900-01-01 02:30:00    17
1900-01-01 02:45:00    17
1900-01-01 03:00:00    17

Otherwise, here is my original answer.

Use diff to get when the hr changes. Initiate the new temp column with the temp1 values where the hr changes, and then backfill:

starts = df['hr'].diff() != 0
df['temp'] = df['temp1'][starts]
df['temp'] = df['temp'].bfill()

Data I used:

import pandas as pd

dr = pd.date_range('01-01-1900 00:00:00', '01-01-1900 03:00:00', freq='15T')
hr = [0,0,0,0,1,1,1,1,2,2,2,2,3]
temp = [19,19,19,19,18,18,18,18,18,18,18,18,17]
df = pd.DataFrame({'hr':hr, 'temp1':temp}, index=dr)

Result:

>>> df

                     hr  temp1  temp
1900-01-01 00:00:00   0     19  19.0
1900-01-01 00:15:00   0     19  18.0
1900-01-01 00:30:00   0     19  18.0
1900-01-01 00:45:00   0     19  18.0
1900-01-01 01:00:00   1     18  18.0
1900-01-01 01:15:00   1     18  18.0
1900-01-01 01:30:00   1     18  18.0
1900-01-01 01:45:00   1     18  18.0
1900-01-01 02:00:00   2     18  18.0
1900-01-01 02:15:00   2     18  17.0
1900-01-01 02:30:00   2     18  17.0
1900-01-01 02:45:00   2     18  17.0
1900-01-01 03:00:00   3     17  17.0
user_133869790

Use Groupby.cumcount and replace all rows with np.nan except first row in each group. Then simply df.bfill:

In [1848]: import numpy as np

In [1849]: ix = df[df.groupby('hr').cumcount() > 0].index    
In [1852]: df.loc[ix, 'temp1'] = np.nan

In [1855]: df = df.bfill()

In [1856]: df
Out[1856]: 
    hr  temp1
0    0   19.0
1    0   18.0
2    0   18.0
3    0   18.0
4    1   18.0
5    1   18.0
6    1   18.0
7    1   18.0
8    2   18.0
9    2   17.0
10   2   17.0
11   2   17.0
12   3   17.0
user_58208140
Related