How to update dataframe based on dependent value in pandas?

user_11022079 0
Viewed 71

I have to update a dataframe based on a dependency value. How can this be done?

For example, input dataframe df:

id      dependency
10
20       30
30       40
40
50       10
60       20

Here we have: 20 -> 30 and 30 -> 40. So the final result will be 20 -> 40 and 30 -> 40.

In the same way, 60 -> 20 -> 30 -> 40 so final result will be 60 -> 40.

Final result:

id      dependency   final_dependency
10
20       30            40
30       40            40
40
50       10            10
60       20            40
pandas networkx dataframe python-3.x python
3 Answers

You can use networkx to do this. First, create a graph with the nodes that have a dependency:

df_edges = df.dropna(subset=['dependency'])
G = nx.from_pandas_edgelist(df_edges, create_using=nx.DiGraph, source='dependency', target='id')

Now, we can find the root ancestor for each node and add that as a new column:

def find_root(G, node):
    ancestors = list(nx.ancestors(G, node))
    if len(ancestors) > 0:
        root = find_root(G, ancestors[0])
    else:
        root = node
    return root

df['final_dependency'] = df['id'].apply(lambda x: find_root(G, x))
df['final_dependency'] = np.where(df['final_dependency'] == df['id'], np.nan, df['final_dependency'])

Resulting dataframe:

   id  dependency  final_dependency
0  10         NaN               NaN
1  20        30.0              40.0
2  30        40.0              40.0
3  40         NaN               NaN
4  50        10.0              10.0
5  60        20.0              40.0
user_75795470

One way is to create a custom function:

s = df[df["dependency"].notnull()].set_index("id")["dependency"].to_dict()

def func(val):
    if not s.get(val):
        return None
    while s.get(val):
        val = s.get(val)
    return val

df["final"] = df["id"].apply(func)

print (df)

   id  dependency  final
0  10         NaN    NaN
1  20        30.0   40.0
2  30        40.0   40.0
3  40         NaN    NaN
4  50        10.0   10.0
5  60        20.0   40.0
user_92844230

You already have a few answers. iterrows() is a bit expensive solution but wanted you to have this as well.

import pandas as pd

raw_data = {'id': [i for i in range (10,61,10)],
            'dep':[None,30,40,None,10,20]}
df = pd.DataFrame(raw_data)

df['final_dep'] = df.dep

for i,r in df.iterrows():

    if pd.notnull(r.dep):
        x = df.loc[df['id'] == r.dep, 'dep'].values[0]
        if pd.notnull(x):
            df.iloc[i,df.columns.get_loc('final_dep')] = x
        else:
            df.iloc[i,df.columns.get_loc('final_dep')] = r.dep

print (df)

Output of this will be:

   id   dep final_dep
0  10   NaN       NaN
1  20  30.0        40
2  30  40.0        40
3  40   NaN       NaN
4  50  10.0        10
5  60  20.0        30
user_138739800
Related