Dataframe selection using values from another dataframe

Question

I have this dataframe:

studyarea_pcdn 



    POSTCODE    Number of meters
0   NE4 9UP     41
1   NE4 9UN     32
2   NE4 9UL     29
3   NE4 9EN     27
4   NE4 9DY     25
5   NE4 9ED     22
6   NE4 9EL     18
7   NE4 9EE     13
8   NE4 9UJ     11
9   NE4 9DX     6
10  NE4 9EA     4

Then I have this other:

sa_gas_pcd_2018[['POSTCODE', 'Number of meters']] 

        POSTCODE    Number of meters
628318  NE4 9UP     41
628315  NE4 9UJ     36
628317  NE4 9UN     32
628199  NE4 9DY     29
628201  NE4 9EE     28
628316  NE4 9UL     28
628205  NE4 9EN     26
628200  NE4 9ED     20
628198  NE4 9DX     19
628204  NE4 9EL     17

I would like to select from sa_gas_pcd_2018 those rows that have a matching postcode to studyarea_pcdn ['POSTCODE'] and have number of meters +- 2 when compared to studyarea_pcdn['Number of meters']

The correct answer should be:

        POSTCODE    Number of meters
628318  NE4 9UP     41
628317  NE4 9UN     32
628316  NE4 9UL     28
628205  NE4 9EN     26
628200  NE4 9ED     20
628204  NE4 9EL     17

I have tried quite a few things but I am really stuck as I am not sure how to frame this issue. Happy if anyone has any pointers.

Chris · Accepted Answer · 2022-09-13 13:56:58Z

This is really just an inner merge, then a .loc filter on the absolute value of the differences.

df = pd.DataFrame({'POSTCODE': ['NE4 9UP',
  'NE4 9UN',
  'NE4 9UL',
  'NE4 9EN',
  'NE4 9DY',
  'NE4 9ED',
  'NE4 9EL',
  'NE4 9EE',
  'NE4 9UJ',
  'NE4 9DX',
  'NE4 9EA'],
 'Number of meters': [41, 32, 29, 27, 25, 22, 18, 13, 11, 6, 4]})

df2 = pd.DataFrame({'POSTCODE': ['NE4 9UP',
  'NE4 9UJ',
  'NE4 9UN',
  'NE4 9DY',
  'NE4 9EE',
  'NE4 9UL',
  'NE4 9EN',
  'NE4 9ED',
  'NE4 9DX',
  'NE4 9EL'],
 'Number of meters': [41, 36, 32, 29, 28, 28, 26, 20, 19, 17]})

df.rename(columns={'Number of meters':'m'}, inplace=True)

df = df.merge(df2, on='POSTCODE')

df.loc[abs((df['Number of meters']-df['m']))<=2].drop(columns='m')

Output

 POSTCODE  Number of meters
0  NE4 9UP                41
1  NE4 9UN                32
2  NE4 9UL                28
3  NE4 9EN                26
5  NE4 9ED                20
6  NE4 9EL                17

Collectives™ on Stack Overflow

Dataframe selection using values from another dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related