How to create new column in dataframe based on condition from other dataframe?

Question

df1 = pd.DataFrame({"DEPTH":[0.5, 1, 1.5, 2, 2.5],
                     "POROSITY":[10, 22, 15, 30, 20],
                     "WELL":"well 1"})

df2 = pd.DataFrame({"Well":"well 1",
                    "Marker":["Fm 1","Fm 2"],
                    "Depth":[0.7, 1.7]})

Hello everyone. I have two dataframes and I would like to create a new column on df1, for example: df1["FORMATIONS"], with information from df2["Marker"] values based on depth limits from df2["Depth"] and df1["DEPTH"].

So, for example, if df2["Depth"] = 1.7, then all samples in df1 with df1["DEPTH"] > 1.7 should be labelled as "Fm 2" in this new column df1["FORMATIONS"].

And the final dataframe df1 should look like this:

DEPTH   POROSITY   WELL   FORMATIONS
0.5     10        well 1     nan
1       22        well 1     Fm 1
1.5     15        well 1     Fm 1
2       30        well 1     Fm 2
2.5     20        well 1     Fm 2

Anyone could help me?

AngeloAD · Accepted Answer · 2022-01-19 20:34:08Z

2

What you're doing here is transforming continuous data into categorical data. There are many ways to do this with pandas, but one of the better known ways is using pandas.cut.

When specifying the bins argument, you need to add float(inf) to the end of the list, to represent that the last bin goes to infinity.

df1["FORMATIONS"] = pd.cut(df1.DEPTH, list(df2.Depth) + [float('inf')], labels=df2.Marker)

df1 will now be:

edited Jan 19, 2022 at 20:34

answered Jan 19, 2022 at 20:27

AngeloAD

1013 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mozway · Accepted Answer · 2022-01-19 19:57:09Z

1

Use pandas.merge_asof:

NB. the columns used for the merge need to be sorted first

pd.merge_asof(df1,
              df2[['Marker', 'Depth']].rename(columns={'Marker': 'Formations'}),
              left_on='DEPTH', right_on='Depth')

output:

   DEPTH  POROSITY    WELL Formations  Depth
0    0.5        10  well 1        NaN    NaN
1    1.0        22  well 1       Fm 1    0.7
2    1.5        15  well 1       Fm 1    0.7
3    2.0        30  well 1       Fm 2    1.7
4    2.5        20  well 1       Fm 2    1.7

answered Jan 19, 2022 at 19:57

mozway

267k13 gold badges56 silver badges106 bronze badges

Collectives™ on Stack Overflow

How to create new column in dataframe based on condition from other dataframe?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related