Replace value of cell with value in cell below pandas

February 8, 2023

I have a dataframe:

df = pd.DataFrame([['Lukas Mai', 22, 'End of loan'],
                   ['Malik Tillman', 20, 'loan transfer'],
                   ['Abdou Diallo', 26, 'Loan fee:'],
                   ['', '', '€1.50m'],
                   ['Ilaix Moriba', 19, 'End of loan'],
                   ['Diogo Leite', 23, 'Loan fee:'],
                   ['', '', '500k'],
                  ], columns=['Player', 'Age', 'Loan'])

I want to replace the cells containing "Loan fee:" in the Loan column with the value in the cell below.

For example, the desired output would be:

df = pd.DataFrame([['Lukas Mai', 22, 'End of loan'],
                   ['Malik Tillman', 20, 'loan transfer'],
                   ['Abdou Diallo', 26, '€1.50m'],
                   ['', '', '€1.50m'],
                   ['Ilaix Moriba', 19, 'End of loan'],
                   ['Diogo Leite', 23, '€500k'],
                   ['', '', '€500k'],
                  ], columns=['Player', 'Age', 'Loan'])

I have looked for similar questions on stackoverflow but haven’t found any to answer this question.

Is this possible to do in pandas?

>Solution :

Use np.where with pd.Series.shift

import numpy as np


df['Loan'] = np.where(df['Loan'] == 'Loan fee:', df['Loan'].shift(-1), df['Loan'])

          Player Age           Loan
0      Lukas Mai  22    End of loan
1  Malik Tillman  20  loan transfer
2   Abdou Diallo  26         €1.50m
3                            €1.50m
4   Ilaix Moriba  19    End of loan
5    Diogo Leite  23           500k
6                              500k