How to fill empty cells selectively

Tong

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

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.

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

Thanks. It works!

Tong

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.

tgb417

-

CoreyS

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

Tong

thanks for encouraging me!