Shift Values in Single Column of Dataframe Up

Shift column in pandas dataframe up by one?

In [44]: df['gdp'] = df['gdp'].shift(-1)

In [45]: df
Out[45]: 
   y  gdp  cap
0  1    3    5
1  2    7    9
2  8    4    2
3  3    7    7
4  6  NaN    7

In [46]: df[:-1]                                                                                                                                                                                                                                                                                                               
Out[46]: 
   y  gdp  cap
0  1    3    5
1  2    7    9
2  8    4    2
3  3    7    7

How to shift a column in Pandas DataFrame

In [18]: a
Out[18]: 
   x1  x2
0   0   5
1   1   6
2   2   7
3   3   8
4   4   9

In [19]: a['x2'] = a.x2.shift(1)

In [20]: a
Out[20]: 
   x1  x2
0   0 NaN
1   1   5
2   2   6
3   3   7
4   4   8

How do I shift all elements in a row in a python dataframe over by one column?

If I understood your request correctly, you can exploit the shift and update methods like this:

# df

    one     two     ten
a   1.0     1.0     1.0
b   2.0     2.0     NaN
c   3.0     3.0     NaN
d   4.0     4.0     4.0

df.update(
          df.loc[df.iloc[:, -1].isna(), :].shift(axis=1).replace(np.nan, 'na')
)
df.replace('na', np.nan, inplace=True)
print(df)

# Output
    one     two     ten
a   1.0     1.0     1.0
b   NaN     2.0     2.0
c   NaN     3.0     3.0
d   4.0     4.0     4.0

Let's break it down:

#### Step1: Filter NaN in the last column

df.loc[df.iloc[:, -1].isna(), :]

# Ouput 
    one     two     ten
b   2.0     2.0     NaN
c   3.0     3.0     NaN


#### Step2: Shift the rows to the right

df.loc[df.iloc[:, -1].isna(), :].shift(axis=1)

# Output
    one     two     ten
b   NaN     2.0     2.0
c   NaN     3.0     3.0


#### Step3: Replace NaN with "na"
# This is because the update function doens't replace values with NaN

df.loc[df.iloc[:, -1].isna(), :].shift(axis=1).replace(np.nan, 'na')

# Output
    one     two     ten
b   na      2.0     2.0
c   na      3.0     3.0


#### Step4: Update the orginal dataframe with this new one (in place method)

df.update(df.loc[df.iloc[:, -1].isna(), :].shift(axis=1).replace(np.nan, 'na'))

# Output
    one     two     ten
a   1.0     1.0     1.0
b   na      2.0     2.0
c   na      3.0     3.0
d   4.0     4.0     4.0

#### Step5: Replace back the original NaN

df.replace('na', np.nan, inplace=True)

Shift values in single column of dataframe up

Your problem simplifies to:

• Drop the first n elements in a vector
• Pad n values of NA at the end

You can do this with a simple function:

shift <- function(x, n){
  c(x[-(seq(n))], rep(NA, n))
}

example$z <- shift(example$z, 2)

The result:

example
  x y  z
1 1 1  3
2 2 2  4
3 3 3  5
4 4 4  6
5 5 5  7
6 6 6  8
7 7 7 NA
8 8 8 NA

Shift only one column in DataFrame

Do,

df['col2'] = df['col2'].shift(5)

You will get the following output

   col1  col2
0     A   NaN
1     B   NaN
2     C   NaN
3     D   NaN
4     E   NaN
5     F   5.0
6     G   3.0
7     H   8.0
8     I   2.0
9     J   9.0
10    K   9.0
11    L   4.0
12    M   9.0

But from the updated Q, I can see that the col1 should be extended as well. So do this instead,

df['col2'].index = df['col2'].index + 5
pd.concat([df['col1'], df['col2']], axis=1)

   col1  col2
0     A   NaN
1     B   NaN
2     C   NaN
3     D   NaN
4     E   NaN
5     F   5.0
6     G   3.0
7     H   8.0
8     I   2.0
9     J   9.0
10    K   9.0
11    L   4.0
12    M   9.0
13  NaN   3.0
14  NaN   5.0
15  NaN   7.0
16  NaN   3.0
17  NaN   7.0

Shift several parts inside one dataframe in pandas with condition

Try:

df["My Shift"] = df.groupby("B")["A"].shift()
print(df)

Prints:

     A    B  Correctly Shifted  Incorrectly Shifted  My Shift
0  100  aaa                NaN                  NaN       NaN
1  110  aaa              100.0                100.0     100.0
2  120  aaa              110.0                110.0     110.0
3  100  bbb                NaN                120.0       NaN
4  110  bbb              100.0                100.0     100.0
5  120  bbb              110.0                110.0     110.0
6  100  ccc                NaN                120.0       NaN
7  110  ccc              100.0                100.0     100.0
8  120  ccc              110.0                110.0     110.0

How to shift rows up in Pandas Dataframe based on specific column

The way I'd do this IIUC is to filter out the values in ColB which are not 0, and fill the column with these values according to the length of the obtained valid values:

m = df.loc[~df.ColB.eq(0), 'ColB'].values
df['ColB'] = float('nan')
df.loc[:m.size-1, 'ColB'] = m

print(df)

 ColA  ColB    ColC
0    A   1.0   First
1    B   2.0  Second
2    C   NaN   Third

Shift values in single column of dataframe UP with lag specified in another column

Using dplyr::lead

library(dplyr)

example %>% 
  group_by(shift) %>% 
  mutate(price = lead(price, unique(shift)))

or using data.table::shift

library(data.table)

setDT(example)[, price := shift(.(price), type = "lead", n = shift), .(shift)][]

giving

#>   country price shift
#> 1 IT        200     0
#> 2 IT        150     0
#> 3 GR        480     1
#> 4 GR        590     1
#> 5 GR         NA     1
#> 6 TR        438     2
#> 7 TR        555     2
#> 8 TR         NA     2
#> 9 TR         NA     2

Shift values in single column with lag in R

We can use lead in dplyr

library(dplyr)
df %>%  group_by(Code) %>%  mutate(Price = lead(Price, 2))

#  Code   Week Price
#  <fct> <int> <int>
# 1 A1      1     8
# 2 A1      2    NA
# 3 A1      3    NA
# 4 A2      1     6
# 5 A2      2     6
# 6 A2      3    NA
# 7 A2      4    NA
# 8 A3      1    20
# 9 A3      2    20
#10 A3      3    20
#11 A3      4    NA
#12 A3      5    NA

Or shift in data.table

library(data.table)
setDT(df)[, Price := shift(Price, 2, type = "lead"), Code]

There isn't a ready function available to do this in base but we can use tail and append NA values.

df$Price <- with(df,ave(Price, Code, FUN = function(x) c(tail(x, -2), rep(NA, 2))))

---

Related Topics