CODEWITHSUNDEEP
pythonpandasdataframe
Mainland
Mainland

Reputation: 4554

Python comparing columns of two dataframes and producing index of matching rows

I have two dataframes 

ref_df = 
   condition          color
0     normal              g
1    onesoil         silver
2    sixsoil              k
3     crack1           pink
4     crack2         tomato
5    crack3a     lightcoral
6    crack3b      indianred
7     crack4      orangered
8    intcon1      turquoise
9    intcon2  lightseagreen
10  modcont1        hotpink
11  modcont2       deeppink

test_df = 
index
intcon1     71.046122
intcon2     70.925799
modcont1    70.061561
crack2      71.484572
crack3a     71.703785
crack3b     71.352460
crack4      72.214675

I want to compare test_df with ref_df and produce results of ref_df. In the above case, I want to compare index of test_df with the ref_df['condition'] and I want to produce results of ref_df['color'] for matching rows. I would like to achieve everything in one line code. My present code

 color_df = expdf['color'].loc[expdf['condition'].isin(faultdf.index)]

Above code simply reproduced whole column of expdf['color']. My present output: 

0                 g
1            silver
2                 k
4            tomato
5        lightcoral
6         indianred
7         orangered
8         turquoise
9     lightseagreen
10          hotpink
11         deeppink

Expected output

8    turquoise
9    lightseagreen
10   hotpink
4    tomato
5    lightcoral
6    indianred
7     orangered

How to achieve above from one-liner code.

Upvotes: 1

Views: 35

Answers (1)

U13-Forward
U13-Forward

Reputation: 71560

Try using:

color_df = expdf.loc[expdf['condition'].isin(faultdf.index), 'color']

You almost got it, 'color' needs to be at the end.

Edit:

For ordering the dataframe:

color_df = expdf[expdf['condition'].isin(faultdf.index)].set_index('condition').reindex(fault_df.index).reset_index()

Upvotes: 3

Related Questions