# How to fill empty cells selectively

Solved!
Level 3
###### How to fill empty cells selectively

Hi,

I have a data set like this (see below). I want to fill the cells in column (C%). If the ID is the same, the C% value is the same. I can not use ' fill cells with previous value' because some IDs do not have values. Can I create somethis like this: if the ID is the same, the C% is the same for 1 and 2 (sub ID)? Thanks in advane if anyone can help me with this.

 ID subID C% 58 1 100 58 2 59 1 120 59 2 60 1 60 2 61 1 125 61 2
1 Solution

A quick thought you might be able to do this with two visual recipes.

1. Create a dataset from your data that is grouped by ID and contains the MAX or Min of C% for each of your ids.

now you have two data sets your original dataset, and a new dataset that has your ids and your C%.  Hopefully this second dataset will have no missing values for C% for any of the IDs.

2. Then Use a Join recipe to connect the two datasets  on ID.  Use a left join with your complete dataset that is missing the values as your first data set, and the second dataset will be this new dataset that you have just created.

hope that helps.

--Tom
7 Replies

A quick thought you might be able to do this with two visual recipes.

1. Create a dataset from your data that is grouped by ID and contains the MAX or Min of C% for each of your ids.

now you have two data sets your original dataset, and a new dataset that has your ids and your C%.  Hopefully this second dataset will have no missing values for C% for any of the IDs.

2. Then Use a Join recipe to connect the two datasets  on ID.  Use a left join with your complete dataset that is missing the values as your first data set, and the second dataset will be this new dataset that you have just created.

hope that helps.

--Tom
Level 3
Author

Thanks. It works!

-

--Tom

Just as a code alternative to @tgb417 solution, using a python recipe (and provided the name of your dataset is 'mydataset';

`import dataikuimport pandas as pddataset = dataiku.Dataset("mydataset")df =dataset.get_dataframe()val_map = df.drop_duplicates(subset='ID').set_index('ID')df['C%'] = df.apply(    lambda x: val_map.loc[x['ID'], 'C%'] if pd.isnull(x['C%'])     else x['C%'], axis=1)## Add dataiku code to write into a Dataset, e.g.:dataset_result = dataiku.Dataset("myresult")dataset_resiult.write_with_schema(df)`

With your given example, the C% will remain empty for ID 60, as you don't have any reference value in that case.

Cheers!

Level 3
Author

Thanks for writing me the code. But unfortunately I am not familiar with Python. I just started using DSS since one week. I need to learn in time. But anyway thanks a lot, I might use it for other case in the future.

Dataiker Alumni

Hey @Tong welcome to Dataiku and thanks for using DSS. This is only if you haven't started using it already, but since you are brand new to DSS I would also recommend the Dataiku Academy and the Learning Paths

That way in no time you'll be an expert and even be answering other users questions on here ๐

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base