Python Shift values in one column based on Nan or boolean in other column

user_12820692 0
Viewed 203

Thank you so much for your help! It is great to have access to such a great community :) edited

I'm desperately trying to solve this for two days now, so I really hope to get some help from you all.

I have a table that looks like this:

ColumnA ColumnB ColumnC
This 1
Das 2
is 3
ist 4
a 5
ein 6
not_translated 7
text Text 8
, 9
10
, 11
and und 12
so 13
so 14
. 15
. 16

I now want that every translation in ColumnB is shifted one line up when there is nothing written in Column A (so that "This" and "Das" is in the same row), but stays in the same row when there is text in ColumnA (as in "text" and "Text"). So the result I want to achieve is:

ColumnA ColumnB ColumnC
This Das 1
is ist 3
a ein 5
not_translated 7
text Text 8
, , 9
and und 12
so so 13
. . 15

What I've tried so far is to use a boolean, which hasn't worked out:

df1['bool'] = (df1['ColumnA'].isnull())
if df1['bool']:
    df1['ColumnB'].shift(periods=-1)
else:
    df1['ColumnB']

and also by using the np.where:

df1["ColumnB"] = np.where((df1["ColumnA"].isnull()), df1["ColumnB"].shift(periods=-1), df1["ColumnB"])

It would be perfect to shift up the translation in ColumnB to the next row of ColumnA with text (this was my original try, but unfortunately it didn't work out). Thank you very much for your help and efforts so far!

pandas nan boolean numpy python
3 Answers

You are using ColumnA as the subject of your logical statement. There is an easier approach to this:

  1. If ColumnB is null, use the row below it. If not, keep its original value.
  2. If ColumnA is 'not_translated', keep null in ColumnB.
  3. Drop rows where ColumnA is null.

In code:

# If B is null, take next value. Otherwise, keep value.
df['ColumnB_new'] = np.where(df['ColumnB'].notna(), df['ColumnB'], df['ColumnB'].shift(-1))

# If A is 'not_tranlsated', leave null
df['ColumnB_new'] = np.where(df['ColumnA'].eq('not_translated'), np.nan, df['ColumnB_new'])

# Result
df = df.loc[df['ColumnA'].notna(), ['ColumnA','ColumnB_new']]
print(df)

          ColumnA ColumnB_new
0            This         Das
2              is         ist
4               a         ein
6  not_translated         NaN
7            text        text
8               .           .
user_97958170

Here is one approach you could try:

Code:

df = df.dropna(axis=0, subset=['ColumnA', 'ColumnB'], how='all').reset_index(drop=True)

idxs=df[df['ColumnA'].isna() & df['ColumnB'].notna()].index.tolist()

for i in idxs:
    df['ColumnB'].iloc[i-1] = df['ColumnB'].iloc[i]
    df['ColumnB'].iloc[i] = np.NaN

df = df.dropna(axis=0, subset=['ColumnA', 'ColumnB'], how='all').reset_index(drop=True)
print(df)

Input:

           ColumnA ColumnB  ColumnC
0             This     NaN        1
1              NaN     Das        2
2               is     NaN        3
3              NaN     ist        4
4                a     NaN        5
5              NaN     ein        6
6   not_translated     NaN        7
7             text    Text        8
8                ,     NaN        9
9              NaN     NaN       10
10             NaN       ,       11
11             and     und       12
12              so     NaN       13
13             NaN      so       14
14               .     NaN       15
15             NaN       .       16

Output:

          ColumnA ColumnB  ColumnC
0            This     Das        1
1              is     ist        3
2               a     ein        5
3  not_translated     NaN        7
4            text    Text        8
5               ,       ,        9
6             and     und       12
7              so      so       13
8               .       .       15

Explanation:

  1. Find indices where ColumnA is Nan but ColumnB is not
  2. For each of those indices replace the Nan value in the previous row of ColumnB with that in current row
  3. Make the value of the current row of ColumnB Nan
  4. Call dropna to drop unnecessary rows
user_153478050

Edited according to change of requirement:

An approach without using looping:

df = df.dropna(subset=['ColumnA', 'ColumnB'], how='all').copy()
m = df['ColumnB'].shift(-1).notna() & df['ColumnA'].shift(-1).isna() & df['ColumnB'].isna()
df.loc[m, 'ColumnB'] = df['ColumnB'].shift(-1)
df = df.dropna(subset=['ColumnA'])

Data input:

           ColumnA ColumnB  ColumnC
0             This     NaN        1
1              NaN     Das        2
2               is     NaN        3
3              NaN     ist        4
4                a     NaN        5
5              NaN     ein        6
6   not_translated     NaN        7
7             text    Text        8
8                ,     NaN        9
9              NaN     NaN       10
10             NaN       ,       11
11             and     und       12
12              so     NaN       13
13             NaN      so       14
14               .     NaN       15
15             NaN       .       16

Result:

           ColumnA ColumnB  ColumnC
0             This     Das        1
2               is     ist        3
4                a     ein        5
6   not_translated     NaN        7
7             text    Text        8
8                ,       ,        9
11             and     und       12
12              so      so       13
14               .       .       15
user_150706970
Related