Here is the DataFrame when I am doing Python using pandas.
import pandas as pd
import numpy as np
items = ['Apple','Cherry','Banana','Apple','Banana']
place = ['UK','','China','USA','China']
quality = ['Bad','Bad','Good','Bad','Good']
date = ['Jan-1','Jan-2','Jan-3','','Jan-1']
df = pd.DataFrame({"Item":items, "Place":place, "Quality":quality, "Date":date})
print(df)
| Item | Place | Quality | Date |
|---|---|---|---|
| Apple | UK | Bad | Jan-1 |
| Cherry | Bad | Jan-2 | |
| Banana | China | Good | Jan-3 |
| Apple | USA | Bad | |
| Banana | Chian | Good | Jan-1 |
What I require is:
- If column
Placeis null and columnDateis not null, then transfer the value fromDatetoPlace; - If column
Placeis not null and columnDateis null, then no transferring happens; - If column
Placeis and columnDateis are both null or not null, then no transferring happens, either.
So I tried:
df.Place=np.where(df.Date!="",df.Date,df.Place)
but this can not be satisfied with what I require. The output should be like this:
| Item | Place | Quality | Date |
|---|---|---|---|
| Apple | UK | Bad | Jan-1 |
| Cherry | Jan-2 | Bad | Jan-2 |
| Banana | China | Good | Jan-3 |
| Apple | USA | Bad | |
| Banana | Chian | Good | Jan-1 |
Can someone help me with this solution? Thanks
>Solution :
The conditions of np.where seems to be incomplete:
df = df.replace('',np.nan)
df['Place'] = np.where(df['Place'].isna() & df['Date'].notna(), df['Date'], df['Place'])
or without replacing '' with NaN, just write the complete condition:
df.Place = np.where((df.Date!="") & (df.Place==""), df.Date, df.Place)
Output:
Item Place Quality Date
0 Apple UK Bad Jan-1
1 Cherry Jan-2 Bad Jan-2
2 Banana China Good Jan-3
3 Apple USA Bad NaN
4 Banana China Good Jan-1