How to fill empty cells selectively

Solved!
Tong
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. 

 

IDsubIDC%
581100
582 
591120
592 
601 
602 
611125
612 
0 Kudos
1 Solution
tgb417

@Tong 

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

View solution in original post

7 Replies
tgb417

@Tong 

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
Tong
Level 3
Author

Thanks. It works! 

tgb417

-

--Tom
0 Kudos
Ignacio_Toledo

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

import dataiku
import pandas as pd

dataset = 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!

Tong
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. 

CoreyS
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

A reply answered your question? Mark as โ€˜Accepted Solutionโ€™ to help others like you!
Tong
Level 3
Author

thanks for encouraging me!