CODEWITHSUNDEEP
pythonpandasdataframe
Андрей Севостьянов
Андрей Севостьянов

Reputation: 159

Set df's column value based on several conditions from another df

I want to set the value to the dataframe based on values from another dataframe

Example:

df1 

A   |  B  |  C  |               
100   20.1        
100   21.3
100   22.0
100   23.6
100   24.0
100   25.8

df2 

A   |  B  |  D

100   20     AC1
100   22     AC2 
100   23     AC3
100   25     AC4
100   29     AC5
200   20     AC1
200   34     AC2
200   37     AC3

I want df1['C'] to have something like

AC1
AC1
AC2
AC3
AC3
AC4

I.e df1['C'] = df2['D'].where((df2['A'] == df1['A']) & (df2['B'] < df1['B']))

Upvotes: 1

Views: 44

Answers (1)

Sebastien D
Sebastien D

Reputation: 4482

You could pd.merge and ffill to fill missing values:

df1['C'] = pd.merge(df1, df2, how='left', on = ['A', 'B']).fillna(method='ffill')['D']

Output

+---+-----+----+-----+
|   |  A  | B  |  C  |
+---+-----+----+-----+
| 0 | 100 | 20 | AC1 |
| 1 | 100 | 21 | AC1 |
| 2 | 100 | 22 | AC2 |
| 3 | 100 | 23 | AC3 |
| 4 | 100 | 24 | AC3 |
| 5 | 100 | 25 | AC4 |
+---+-----+----+-----+

EDIT : explanation

First we merge df1 and df2 on A and B columns:

pd.merge(df1, df2, how='left', on = ['A', 'B'])
#output
+---+-----+----+-----+
|   |  A  | B  |  C  |
+---+-----+----+-----+
| 0 | 100 | 20 | AC1 |
| 1 | 100 | 21 | AC1 |
| 2 | 100 | 22 | AC2 |
| 3 | 100 | 23 | AC3 |
| 4 | 100 | 24 | AC3 |
| 5 | 100 | 25 | AC4 |
+---+-----+----+-----+

To fill missing values, we take leverage of the ffill method (see docs):

method : {‘backfill’, ‘bfill’, ‘pad’, ‘ffill’, None}, default None Method to use for filling holes in reindexed Series pad / ffill: propagate last valid observation forward to next valid backfill / bfill: use NEXT valid observation to fill gap

pd.merge(df1, df2, how='left', on = ['A', 'B']).fillna(method='ffill')
#output : missing values are filled as expected 
+---+-----+----+------+-----+
|   |  A  | B  |  C   |  D  |
+---+-----+----+------+-----+
| 0 | 100 | 20 | None | AC1 |
| 1 | 100 | 21 | None | AC1 |
| 2 | 100 | 22 | None | AC2 |
| 3 | 100 | 23 | None | AC3 |
| 4 | 100 | 24 | None | AC3 |
| 5 | 100 | 25 | None | AC4 |
+---+-----+----+------+-----+

df1['C'] is just the D column of the merged and filled dataframe, which is what we wanted

df1['C'] = pd.merge(df1, df2, how='left', on = ['A', 'B']).fillna(method='ffill')['D']

Upvotes: 3

Related Questions