Pandas Dataframe filter results with Merge. Encoding Decoding Issues

Question

I read tons of posts from our database for the last few days. with my limited skills in python and pandas and numpy, i am not sure if i found the answers that I desire. so would you please take a look at my situation, and see what i can do with it. and I am sorry about the chinese characters in the searching results.

I am currently writing some quant analysis for personal use. I retrieved a csv file via tushare-pro, which is a 3825-rows dataframe.

df1 = pd.DataFrame(pd.read_csv(stock_stats_ts.csv))
data1 = np.array(df1.loc[:,:])

returns

[[300826 'N测绘' '建筑工程' ... 41.16 16.85 40171.0]
 [2770 '科迪乳业' '乳制品' ... 21.05 4.38 47133.0]
 [2503 '搜于特' '服饰' ... 8.6 3.08 65664.0]
 ...
 [2260 '*ST德奥' '家用电器' ... 23.08 3.03 24704.0]
 [995 '*ST皇台' '白酒' ... 68.05 -35.24 10275.0]
 [939 '*ST凯迪' '新型电力' ... 10.79 -74.92 79373.0]]

and then I narrow it down to things i desire, such as code/name/esp/pb/roe

df2 = df1.loc[:,['code','name','esp','pb','npr']]
data2 = np.array(df1.loc[:,:])

returns

[[300826 'N测绘' 1.08 2.79 16.85]
 [2770 '科迪乳业' 0.03 2.13 4.38]
 [2503 '搜于特' 0.098 2.17 3.08]
 ...
 [2260 '*ST德奥' 0.034 0.0 3.03]
 [995 '*ST皇台' -0.079 0.0 -35.24]
 [939 '*ST凯迪' -0.362 0.0 -74.92]]

and I also have a list of stock names which i desire from previous session

df3 = pd.DataFrame(pd.read_csv(candidates.csv))
data3 = np.array(df3.loc[:,['candidates']])

returns

[['维维股份']
 ['ST正源']
 ['美克家居']
 ['*ST金山']
 ['大有能源']
 ['好当家']
 ['贵州茅台']
 ['通策医疗']
 ['杭州解百']
 ['耀皮玻璃']
 ['梅花生物']
 ['金牌厨柜']
 ['继峰股份']
 ['胜利股份']
 ['渝 开 发']
 ['云南白药']
 ['中原环保']
 ['兴蓉环境']
 ['华闻集团']
 ['粤 水 电']
 ['濮耐股份']
 ['*ST东南']
 ['洪涛股份']
 ['达实智能']
 ['千红制药']
 ['闽发铝业']
 ['史丹利']
 ['加加食品']
 ['张家港行']
 ['国联水产']]

What i am sure of is that my candidates are in df2[name] columns for sure, and then, with what lines of codes so that I can filter my df2 based on the results i have from df3?

Thanks to chief @Rexhil Regmi and @nimrodm, my question worked perfectly with pd.merge. However, all those chinese characters in are encoding in 'gbk' which is unreadable with MS Excel. Any hints to change them into 'utf8'?

user_4967825 · Answer

You can use a merge for this problem of yours.

new_df = df3.merge(df1, left_on='candidate', right_on='name', how='left')

This should give you what you are looking for.

user_23388 · Answer

The best solution is to join the two tables (similar to SQL JOIN). Pandas merge performs an inner join meaning you select only rows that have a matching entry in the candidates data frame.

So, for example result.csv is

name,value
first,10
second,20
third,30

And selected.csv is

candidates
first
third

Read both of these as DataFrames (no need to convert to a numpy array):

data = pd.read_csv('result.csv')
selected = pd.read_csv('selected.csv')

And join the two (the how parameter is optional since inner is the default value for merge)

data.merge(selected, how='inner', left_on='name',right_on='candidates')

    name  value candidates
0  first     10      first
1  third     30      third

This joins the two DataFrames looking for rows where the value of data[j,'name'] == data[k, 'candidates']

Another option

Another approach is to directly select lines where name (in my example) is in a given list:

data[data['name'].isin(selected['candidates'])]    

    name  value 
0  first     10
2  third     30

This is probably inefficient unless, perhaps, the candidates list is very short.

Pandas Dataframe filter results with Merge. Encoding Decoding Issues

2 Answers

Another option