| name | ID | gender |
|---|---|---|
| John | 123 | male |
| Scot | na | na |
| 124 | male | na |
| Jill | 231 | female |
I want to cut the missing values for "Scot" from the below and paste them instead of the "nan" values so the new dataframe will be thus:
| name | ID | gender |
|---|---|---|
| John | 123 | male |
| Scot | 124 | male |
| Jill | 231 | female |
I think you're looking for bfill.
Here's example: https://www.geeksforgeeks.org/python-pandas-series-bfill/
So this should do it:
df['ID'] = df['ID'].bfill()
df['gender'] = df['gender'].bfill()
or, if you don't need to be selective, you can run it on the entire dataframe:
df = df.bfill()
It might be easier to fix that, by changeing the way you originally load the data, because it seems you have a linebreak there. However you could do something like this:
Test data:
import pandas as pd
import numpy as np
df = pd.DataFrame({'name': {0: 'John', 1: 'Scot', 2: '124', 3: 'Jill'},
'ID': {0: '123', 1: np.nan, 2: 'male', 3: '231'},
'gender': {0: 'male', 1: np.nan, 2: np.nan, 3: 'female'}})
Code:
# find out which rows are valid (m) and which contain the offset data (m2)
m = df['ID'].isna()
m2 = m.shift(fill_value=False)
# create a separate dataframe, only containing the relevant row and columns for filling nan values
df2 = df[df.columns[:-1]][m2].copy()
# harmonize the index and column names so it fits the original dataframe
df2.columns = df.columns[1:]
df2.index = df2.index-1
# fill empty values by using the newly created dataframe values
df.fillna(df2)[~m2]
Output:
# name ID gender
# 0 John 123 male
# 1 Scot 124 male
# 3 Jill 231 female