pandas matching database with string keeping index of database

user_11341120 0
Viewed 77

I have a database with strings and the index as below.

df0
idx name_id_code string_line_0
0 0.01 A
1 0.5 B
2 77.6 C
3 29.8 D
4 56.2 E
5 88.1000005 F
6 66.4000008 G
7 2.1 H
8 99 I
9 550.9999999 J


df1
idx string_line_1
0 A
1 F
2 J
3 G
4 D

Now, I want to match the df1 with df0, taking the values where df1 = df 0 but, keeping the index of df0 true as below

df_result name_id_code string_line_0
0 0.01 A
5 88.1000005 F
9 550.9999999 J
6 66.4000008 G
3 29.8 D

I tried with my code but it didnt work for string and only matching index

c = df0['name_id_code'] + ' (' + df0['string_line_0'].astype(str) + ')'
out = df1[df2['string_line_1'].isin(s)]

I also tried to keep simple just last column match like

c = df0['string_line_0'].astype(str) + ')'
out = df1[df1['string_line_1'].isin(s)]

but blank output.

pandas python
2 Answers

Because is filtered df0 DataFrame then is index values not changed if use Series.isin by df1['string_line_1', only order of columns is like in original df0:

out = df0[df0['string_line_0'].isin(df1['string_line_1'])]
print (out)
     name_id_code string_line_0
idx                            
0        0.010000             A
3       29.800000             D
5       88.100001             F
6       66.400001             G
9      551.000000             J

Or if use DataFrame.merge then for avoid lost df0.index is necessary add DataFrame.reset_index:

out = (df1.rename(columns={'string_line_1':'string_line_0'})
          .merge(df0.reset_index(), on='string_line_0'))
print (out)
  string_line_0  idx  name_id_code
0             A    0      0.010000
1             F    5     88.100001
2             J    9    551.000000
3             G    6     66.400001
4             D    3     29.800000

Similar solution, only same values in string_line_0 and string_line_1 columns:

out = (df1.merge(df0.reset_index(), left_on='string_line_1', right_on='string_line_0'))
print (out)
  string_line_1  idx  name_id_code string_line_0
0             A    0      0.010000             A
1             F    5     88.100001             F
2             J    9    551.000000             J
3             G    6     66.400001             G
4             D    3     29.800000             D
user_29010020

You can do:

out = df0.loc[(df0["string_line_0"].isin(df1["string_line_1"]))].copy()
out["string_line_0"] = pd.Categorical(out["string_line_0"], categories=df1["string_line_1"].unique())
out.sort_values(by=["string_line_0"], inplace=True)

The first line filters df0 to just the rows where string_line_0 is in the string_line_1 column of df1.

The second line converts string_line_0 in the output df to a Categorical feature, which is then custom sorted by the order of the values in df1

user_62865400
Related