How to adjust value every hour every week in pandas-CodePudding

In the pandas dataframe I need to shift all the values in every seventh row (every Saturday) by one place, so that all the 10s line up vertically.

So 2020-11-07 should go from

10  1   10  3   10  9   10  7   10  3   10  5   10  5   10  10

10  10  1   10  3   10  9   10  7   10  3   10  5   10  5   10

And likewise for 2020-11-14, 2020-11-21 etc.

CodePudding user response：

For every 7th row in the dataframe, use shift method to move everything right by one place and then concatenate the last value and everything from the shifted row except the first one (which will be null)

for i in range(len(df)):
    if i % 6 == 0:
        df.iloc[i, :] = [df.iloc[i, -1]]   df.iloc[i, :].shift(1).tolist()[1:]

If needed, this solution can be generalized to shift every kth row by r places

for i in range(len(df)):
    if i % (k-1) == 0:
        df.iloc[i, :] = df.iloc[i, -r:].tolist()   df.iloc[i, :].shift(r).tolist()[r:]

EDIT:

You can achieve this also without using the shift method

For every 7th row 1 place:

for i in range(len(df)):
    if i % 6 == 0:
        df.iloc[i, :] = [df.iloc[i, -1]]   df.iloc[i, :-1].tolist()

For the general case of kth row r places:

for i in range(len(df)):
    if i % (k-1) == 0:
        df.iloc[i, :] = df.iloc[i, -r:].tolist()   df.iloc[i, :-r].tolist()

CodePudding user response：

Do you want something like this ?

import pandas as pd
import numpy as np

# Build a little exemple (with only 2 columns)
rng = pd.date_range('2021-11-01', periods=15, freq='D')
df = pd.DataFrame({ 'Date': rng, '1': range(15), '2':range(15) })
df["2"] = df["2"] * 100

# Algo :
# creat a new column with the max hour 1
df["3"] = np.NaN

for i in [2,1]:
    # Increment hour every sunday (day 6, because starting at 0) 
     df[str(i 1)] = df[str(i)].where(df["Date"].dt.weekday == 6, df[str(i 1)])

    # Last hour become first hour every sunday
    df[str(1)] = df[str(3)].where(df["Date"].dt.weekday == 6, df[str(1)])
#EndFor

# Keep only first columns
df = df[["Date", "1", "2"]]

Result :

         Date       1     2
0  2021-11-01     0.0     0
1  2021-11-02     1.0   100
2  2021-11-03     2.0   200
3  2021-11-04     3.0   300
4  2021-11-05     4.0   400
5  2021-11-06     5.0   500
6  2021-11-07   600.0     6
7  2021-11-08     7.0   700
8  2021-11-09     8.0   800
9  2021-11-10     9.0   900
10 2021-11-11    10.0  1000
11 2021-11-12    11.0  1100
12 2021-11-13    12.0  1200
13 2021-11-14  1300.0    13
14 2021-11-15    14.0  1400

This is not the best way to get the result, but this is one.