CODEWITHSUNDEEP
pythonpandasdataframe
Rodrigo Brust
Rodrigo Brust

Reputation: 70

How to fill a new column after two for loops?

I have two dataframes. Each one of them has an id_right. I want to make a verification that if the id_right from the df_orders is in the df_events, a new column named open_orders returns 0 or 1 (False or True).

This is my df_events

id_left type id_right
4 a752c3dd-211b-32cf-263a-c95a600e2498 bias 35108
183 8e3ef49e-6c27-08bc-1c57-8c3db5d15d52 payload 35108
236 4cbef24a-a796-455c-9f23-9f6f32d47707 rack 17459

This is my df_orders

id_left priority id_right
0 2cc99d82-991f-47e3-bf52-381f6e3fae1a medium 17761
1 2ee76487-bbd4-46d1-a66c-89f22be2bfbd urgent 17958
2 89f22be2bfbd-47e3-9f6f32d47707 normal 35108

This is what I have already tried, but the outputs are always 0.

for i in df_orders['id_right']:

    for x in df_events['id_right']:
        if i == x:
            df_events['open_orders'] = 1
       
        elif i != x:
            df_events['open_orders'] = 0
        else:
             pass

I'm expecting this kind of output in the df_events:

id_left type id_right open_orders
4 a752c3dd-211b-32cf-263a-c95a600e2498 bias 35108 1
183 8e3ef49e-6c27-08bc-1c57-8c3db5d15d52 payload 35108 1
236 4cbef24a-a796-455c-9f23-9f6f32d47707 rack 17459 0

Anyone has any tips?

Upvotes: 0

Views: 163

Answers (2)

Henry Ecker
Henry Ecker

Reputation: 35696

Use isin to test id_right's presence in df_events['id_right] and convert to type int with astype.

import pandas as pd

df_orders = pd.DataFrame({
    'id_left': ['a752c3dd-211b-32cf-263a-c95a600e2498',
                '8e3ef49e-6c27-08bc-1c57-8c3db5d15d52',
                '4cbef24a-a796-455c-9f23-9f6f32d47707'],
    'type': ['bias', 'payload', 'rack'],
    'id_right': [35108, 35108, 17459]
})
df_events = pd.DataFrame({
    'id_left': ['2cc99d82-991f-47e3-bf52-381f6e3fae1a',
                '2ee76487-bbd4-46d1-a66c-89f22be2bfbd',
                '89f22be2bfbd-47e3-9f6f32d47707'],
    'type': ['medium', 'urgent', 'normal'],
    'id_right': [17761, 17958, 35108]
})

new_df = df_orders.copy()
new_df['open_orders'] = new_df['id_right'] \
    .isin(df_events['id_right']) \
    .astype(int)

# For Print
print(new_df)

Output:

id_left type id_right open_orders
0 a752c3dd-211b-32cf-263a-c95a600e2498 bias 35108 1
1 8e3ef49e-6c27-08bc-1c57-8c3db5d15d52 payload 35108 1
2 4cbef24a-a796-455c-9f23-9f6f32d47707 rack 17459 0

If modifying df_orders is okay, the new column can be added directly to the DataFrame:

df_orders['open_orders'] = df_orders['id_right'] \
    .isin(df_events['id_right']) \
    .astype(int)

Upvotes: 3

Jimmar
Jimmar

Reputation: 4459

I'd attempt to do this in a different way, I'm gonna rephrase what you need as

"produce a new dataframe that contains id_right and open_orders columns where open_orders would contain 1 if the respective id_right existed in both input dataframe, otherwise it'd be 0"

I'm assuming that only the first dataframe needs to be checked and that the order and index doesn't matter.

Steps

Now the steps to do this would be would be

  • For the second dataframe, add a column open_orders and fill it with 1
  • do a left join (using merge) on slices of the dataframe containing only the needed columns
  • fillna on open_orders with 0s

Code

df_orders["open_orders"] = 1
df_merged = df_events["id_right"].merge(df_orders[["id_right", "open_orders"]], how="left")
df_merged["open_orders"] = df_merged["open_orders"].fillna(0)

Upvotes: 1

Related Questions