blocks|key|900767|text|我不是正则表达式专家，最后一个是最难处理的，但下面是工作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|900768|In+[402]:

df['code']+=+df['product'].str.split('-').str[1:].str.join('-').str.findall(r'[A-Z]%2B').str.join('-').map(df1.set_index('color')['code'])
df
Out[402]:
++++++++++++++++product+++code
index+++++++++++++++++++++++++
1++++++++++++a208-BLACK++X1001
2+++++++++++a2008-WHITE++X7005
3++++++x307-PEARL-WHITE++X7055
4+++++++++aa-b307-WHITE++X7005|code-block|syntax|javascript|900769|基本上，我在-上拆分产品代码，并将所有元素放在第一个破折号的右侧。|offset|length|style|CODE|900770|剩下的就是：|900771|In+[403]:

df['product'].str.split('-').str[1:]
Out[403]:
index
1+++++++++++++++[BLACK]
2+++++++++++++++[WHITE]
3++++++++[PEARL,+WHITE]
4+++++++++[b307,+WHITE]
Name:+product,+dtype:+object|900772|然后我把破折号放回去，使用正则表达式只查找大写字母字符，这将处理最后一个字符，再次重新加入。|900773|最后一位是在设置颜色列的索引后，在另一个df上调用map，这将在df中执行颜色值的查找并返回相应的代码。|900774|正则表达式不是万无一失的，但它适用于您的数据集。|900775|编辑|BOLD|900776|我现在意识到我们不需要那么多的连接：|900777|In+[409]:

df['code']+=+df['product'].str.findall(r'[A-Z]%2B').str.join('-').map(df1.set_index('color')['code'])
df
Out[409]:
++++++++++++++++product+++code
index+++++++++++++++++++++++++
1++++++++++++a208-BLACK++X1001
2+++++++++++a2008-WHITE++X7005
3++++++x307-PEARL-WHITE++X7055
4+++++++++aa-b307-WHITE++X7005|900778|计时|900779|In+[414]:


%25%25timeit+
import+re
df['color']+=+df['product'].apply(lambda+x:+re.sub('%5E[%5EALPHA:]*-(.*)',+'\\1',+x))

pd.merge(df,+df1,+on='color')
1+loops,+best+of+3:+4.09+ms+per+loop
In+[416]:

%25%25timeit
df['code']+=+df['product'].str.findall(r'[A-Z]%2B').str.join('-').map(df1.set_index('color')['code'])

100+loops,+best+of+3:+1.63+ms+per+loop|900780|str方法比使用lambda快2倍以上，这并不奇怪，因为像调用map一样，str方法是矢量化的。|900781|已更新计时|900782|In+[7]:

%25%25timeit
df1['color']+=+df1['product'].str.extract(r'-([A-Z-]%2B)$')
pd.merge(df1,+df2)
100+loops,+best+of+3:+4.51+ms+per+loop
In+[9]:

%25%25timeit
df1['code']+=+df1['product'].str.findall(r'[A-Z]%2B').str.join('-').map(df2.set_index('color')['code'])
100+loops,+best+of+3:+3.87+ms+per+loop
In+[10]:

%25%25timeit+
import+re
df1['color']+=+df1['product'].apply(lambda+x:+re.sub('%5E[%5EALPHA:]*-(.*)',+'\\1',+x))

pd.merge(df1,+df2,+on='color')
100+loops,+best+of+3:+4.79+ms+per+loop|900783|所以@unutbu的回答比@beaveau上校的略快一些，但在这里使用map更快。|900784|事实上，如果我们将@unutbu的regex+str方法与map结合起来，我们会比我原来的方法更快：|900785|In+[12]:

%25%25timeit
df1['product'].str.extract(r'-([A-Z-]%2B)$').map(df2.set_index('color')['code'])
100+loops,+best+of+3:+2.17+ms+per+loop|900786|因此，在这里使用map几乎比合并快2倍|900787|entityMap^0|0|0|6|1|0|0|0|0|0|0|0|2|0|0|0|0|2|0|0|V|3|11|3|0|0|5|0|0|0|N|3|0|0|8|3|0^^$0|@$1|2|3|4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1O|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1P|8|@$I|1Q|J|1R|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|1S|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1T|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1U|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1V|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1W|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1X|8|@$I|1Y|J|1Z|K|Y]]|9|@]|A|$]]|$1|Z|3|10|5|6|7|20|8|@]|9|@]|A|$]]|$1|11|3|12|5|D|7|21|8|@]|9|@]|A|$E|F]]|$1|13|3|14|5|6|7|22|8|@$I|23|J|24|K|Y]]|9|@]|A|$]]|$1|15|3|16|5|D|7|25|8|@]|9|@]|A|$E|F]]|$1|17|3|18|5|6|7|26|8|@$I|27|J|28|K|L]|$I|29|J|2A|K|L]]|9|@]|A|$]]|$1|19|3|1A|5|6|7|2B|8|@$I|2C|J|2D|K|Y]]|9|@]|A|$]]|$1|1B|3|1C|5|D|7|2E|8|@]|9|@]|A|$E|F]]|$1|1D|3|1E|5|6|7|2F|8|@]|9|@]|A|$]]|$1|1F|3|1G|5|6|7|2G|8|@$I|2H|J|2I|K|L]]|9|@]|A|$]]|$1|1H|3|1I|5|D|7|2J|8|@]|9|@]|A|$E|F]]|$1|1J|3|1K|5|6|7|2K|8|@$I|2L|J|2M|K|L]]|9|@]|A|$]]|$1|1L|3|-4|5|6|7|2N|8|@]|9|@]|A|$]]]|1M|$]]

I'm not a regex expert, the last one was the trickiest one to handle but the following works:

<pre><code>In [402]:

df['code'] = df['product'].str.split('-').str[1:].str.join('-').str.findall(r'[A-Z]+').str.join('-').map(df1.set_index('color')['code'])
df
Out[402]:
 product code
index 
1 a208-BLACK X1001
2 a2008-WHITE X7005
3 x307-PEARL-WHITE X7055
4 aa-b307-WHITE X7005
</code></pre>

Basically I split the product code on <code>-</code> and take all the elements to the right of the first dash.

This leaves this:

<pre><code>In [403]:

df['product'].str.split('-').str[1:]
Out[403]:
index
1 [BLACK]
2 [WHITE]
3 [PEARL, WHITE]
4 [b307, WHITE]
Name: product, dtype: object
</code></pre>

I then put the dash back, use a regex to find only uppercase alpha characters, this deals with the last one, rejoin again.

The last bit is to call map on this on the other df after setting the index on the color column, this will perform a lookup on the color value in df and return the corresponding code.

The regex isn't foolproof but it works for your dataset.

EDIT

I now realise we don't need that many joins:

<pre><code>In [409]:

df['code'] = df['product'].str.findall(r'[A-Z]+').str.join('-').map(df1.set_index('color')['code'])
df
Out[409]:
 product code
index 
1 a208-BLACK X1001
2 a2008-WHITE X7005
3 x307-PEARL-WHITE X7055
4 aa-b307-WHITE X7005
</code></pre>

Timings

<pre><code>In [414]:


%%timeit 
import re
df['color'] = df['product'].apply(lambda x: re.sub('^[^ALPHA:]*-(.*)', '\\1', x))

pd.merge(df, df1, on='color')
1 loops, best of 3: 4.09 ms per loop
In [416]:

%%timeit
df['code'] = df['product'].str.findall(r'[A-Z]+').str.join('-').map(df1.set_index('color')['code'])

100 loops, best of 3: 1.63 ms per loop
</code></pre>

The str method is over 2X faster than using the lambda, this may not be so surprising as the <code>str</code> methods are vectorised as is calling <code>map</code>.

Updated timings

<pre><code>In [7]:

%%timeit
df1['color'] = df1['product'].str.extract(r'-([A-Z-]+)$')
pd.merge(df1, df2)
100 loops, best of 3: 4.51 ms per loop
In [9]:

%%timeit
df1['code'] = df1['product'].str.findall(r'[A-Z]+').str.join('-').map(df2.set_index('color')['code'])
100 loops, best of 3: 3.87 ms per loop
In [10]:

%%timeit 
import re
df1['color'] = df1['product'].apply(lambda x: re.sub('^[^ALPHA:]*-(.*)', '\\1', x))

pd.merge(df1, df2, on='color')
100 loops, best of 3: 4.79 ms per loop
</code></pre>

So @unutbu's answer is marginally faster than @colonel beaveau's but using map here is faster still.

In fact if we combine @unutbu's regex <code>str</code> method with map we get faster than my original method:

<pre><code>In [12]:

%%timeit
df1['product'].str.extract(r'-([A-Z-]+)$').map(df2.set_index('color')['code'])
100 loops, best of 3: 2.17 ms per loop
</code></pre>

So using <code>map</code> here is nearly 2X faster than merging

blocks|key|900798|text|一些简明的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|900799|import+pandas+as+pd

df1['color']+=+df1['product'].apply(lambda+x:+re.sub('%5E[%5EALPHA:]*-(.*)',+'\\1',+x))

pd.merge(df1,+df2,+on='color')

#++++++++++++product++++++++color+++code
#0++++++++a208-BLACK++++++++BLACK++X1001
#1+++++++a2008-WHITE++++++++WHITE++X7005
#2++x307-PEARL-WHITE++PEARL-WHITE++X7055
#3+++++aa-b307-WHITE++++++++WHITE++X7005|code-block|syntax|javascript|900800|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Some concise solution:

<pre><code>import pandas as pd

df1['color'] = df1['product'].apply(lambda x: re.sub('^[^ALPHA:]*-(.*)', '\\1', x))

pd.merge(df1, df2, on='color')

# product color code
#0 a208-BLACK BLACK X1001
#1 a2008-WHITE WHITE X7005
#2 x307-PEARL-WHITE PEARL-WHITE X7055
#3 aa-b307-WHITE WHITE X7005
</code></pre>

blocks|key|900828|text|您可以使用带有正则表达式模式r'-([A-Z-]%2B)$'的vectorized+string+method+str.extract来查找颜色。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|900829|df1['color']+=+df1['product'].str.findall(r'-([A-Z-]%2B)$').str[0]|code-block|syntax|javascript|900830|然后，pd.merge(df1,+df2)将在公共列(在本例中为color列)上合并：|900831|result+=+pd.merge(df1,+df2)|900832|900833|例如,|900834|import+io
import+pandas+as+pd

df1+=+'''\
index+product
1+++++a208-BLACK+
2+++++a2008-WHITE
3+++++x307-PEARL-WHITE
4+++++aa-b307-WHITE'''
df1+=+pd.read_table(io.BytesIO(df1),+sep='\s%2B',+index_col=0)

df2+=+'''\
index+color+++++++code
1+++++BLACK+++++++X1001
2+++++WHITE+++++++X7005
3+++++PEARL-WHITE+X7055'''
df2+=+pd.read_table(io.BytesIO(df2),+sep='\s%2B',+index_col=0)
df1['color']+=+df1['product'].str.extract(r'-([A-Z-]%2B)$')
print(pd.merge(df1,+df2))|900835|收益率|900836|++++++++++++product++++++++color+++code
0++++++++a208-BLACK++++++++BLACK++X1001
1+++++++a2008-WHITE++++++++WHITE++X7005
2+++++aa-b307-WHITE++++++++WHITE++X7005
3++x307-PEARL-WHITE++PEARL-WHITE++X7055|900837|900838|正则表达式模式r'-([A-Z-]%2B)$'意味着|900839|-++++++++++++++#+match+a+literal+hyphen
(++++++++++++++#+followed+by+a+group+
+[A-Z-]%2B+++++++#+of+1-or-more+capital+letters+or+hyphens
)++++++++++++++#+end+of+group+
$++++++++++++++#+followed+by+end+of+line|900840|entityMap|0|LINK|mutability|MUTABLE|url|http://pandas.pydata.org/pandas-docs/stable/text.html#text-string-methods|1|http://pandas.pydata.org/pandas-docs/dev/generated/pandas.core.strings.StringMethods.extract.html^0|E|E|1I|B|T|O|0|1I|B|1|0|0|3|I|W|5|0|0|0|0|0|0|0|0|7|E|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@$9|1H|A|1I|1|1J]|$9|1K|A|1L|1|1M]]|E|$]]|$1|F|3|G|5|H|7|1N|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1O|8|@$9|1P|A|1Q|B|C]|$9|1R|A|1S|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|1T|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|1U|8|@]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1V|8|@]|D|@]|E|$]]|$1|R|3|S|5|H|7|1W|8|@]|D|@]|E|$I|J]]|$1|T|3|U|5|6|7|1X|8|@]|D|@]|E|$]]|$1|V|3|W|5|H|7|1Y|8|@]|D|@]|E|$I|J]]|$1|X|3|-4|5|6|7|1Z|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|20|8|@$9|21|A|22|B|C]]|D|@]|E|$]]|$1|10|3|11|5|H|7|23|8|@]|D|@]|E|$I|J]]|$1|12|3|-4|5|6|7|24|8|@]|D|@]|E|$]]]|13|$14|$5|15|16|17|E|$18|19]]|1A|$5|15|16|17|E|$18|1B]]]]

You could use the <a href="http://pandas.pydata.org/pandas-docs/stable/text.html#text-string-methods" rel="nofollow">vectorized string method</a>, <a href="http://pandas.pydata.org/pandas-docs/dev/generated/pandas.core.strings.StringMethods.extract.html" rel="nofollow"><code>str.extract</code></a>, with the regex pattern <code>r'-([A-Z-]+)$'</code> to find the colors. 

<pre><code>df1['color'] = df1['product'].str.findall(r'-([A-Z-]+)$').str[0]
</code></pre>

Then <code>pd.merge(df1, df2)</code> will merge on the common column(s) which in this case is the <code>color</code> column:

<pre><code>result = pd.merge(df1, df2)
</code></pre>

<hr>

For example,

<pre><code>import io
import pandas as pd

df1 = '''\
index product
1 a208-BLACK 
2 a2008-WHITE
3 x307-PEARL-WHITE
4 aa-b307-WHITE'''
df1 = pd.read_table(io.BytesIO(df1), sep='\s+', index_col=0)

df2 = '''\
index color code
1 BLACK X1001
2 WHITE X7005
3 PEARL-WHITE X7055'''
df2 = pd.read_table(io.BytesIO(df2), sep='\s+', index_col=0)
df1['color'] = df1['product'].str.extract(r'-([A-Z-]+)$')
print(pd.merge(df1, df2))
</code></pre>

yields

<pre><code> product color code
0 a208-BLACK BLACK X1001
1 a2008-WHITE WHITE X7005
2 aa-b307-WHITE WHITE X7005
3 x307-PEARL-WHITE PEARL-WHITE X7055
</code></pre>

<hr>

The regex pattern <code>r'-([A-Z-]+)$'</code> means

<pre><code>- # match a literal hyphen
( # followed by a group 
 [A-Z-]+ # of 1-or-more capital letters or hyphens
) # end of group 
$ # followed by end of line
</code></pre>

i want to find the best way to merge 2 dataframes on key1.str.endswith(key2), an example is sometimes better than words: 

<pre><code> i want to merge df1 and df2 on product.str.endswith(color)

 df1:
 index product
 1 a208-BLACK 
 2 a2008-WHITE
 3 x307-PEARL-WHITE
 4 aa-b307-WHITE

 df2:
 index color code
 1 BLACK X1001
 2 WHITE X7005
 3 PEARL-WHITE X7055
</code></pre>

to get:

<pre><code> df:
 index product code
 1 a208-BLACK X1001
 2 a2008-WHITE X7005
 3 x307-PEARL-WHITE X7055
 4 aa-b307-WHITE X7005
</code></pre>

Any idea ?

Pandas : how to merge 2 dataframes on key1.str.endswith(key2)

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我想找到在key1.str.endswith(key2)上合并两个数据帧的最佳方法，示例有时比文字更好： i want to merge df1 and df2  on product.str.endswith(color) df1:    index product    1     a208-BLACK     2...

问Pandas :如何在key1.str.endswith上合并2个数据帧(Key2)
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Pandas :如何在key1.str.endswith上合并2个数据帧(Key2)EN