基于配对合并数据框

huangapple go评论62阅读模式
英文:

Merging dataframes based on pairs

问题

我有一个数据框,看起来像这样:

df = pd.DataFrame({'col_1': ['1', '2', '3', '4'],
                   'col_2': ['a:b,c:d', ':v', 'w:,x:y', 'a:g,h:b,j:']
                   })

col_2的数据类型是字符串,所以我们必须进行字符串操作/正则表达式处理。

我还有另一个数据框,它包含了col_2中键值对的映射。它看起来像这样:

df1 = pd.DataFrame({'col_1': ['a', 'c', '', 'w', 'x', 'a', 'h', 'j','t'],
                    'col_2': ['b', 'd', 'v', '', 'y', 'g', 'b', '', 'g'],
                    'col_3': ['aw', 'rt', 'er', 'aa', 'ey', 'wk', 'oo', 'ri', 'ty'],
                    'col_4': ['rt', 'yu', 'gq', 'tr', 'ui', 'pi', 'pw', 'pp', 'uu']
                   })

基本上,a:b 被翻译为 aw:rt,这意味着你不能只通过 ab 来获取 awrt

我想要获取与col_2中的键值对对应的col_4中的所有值,所以我希望我的输出是:

pd.DataFrame({'col_1': ['1', '2', '3', '4'],
                   'col_2': ['a:b,c:d', ':v', 'w:,x:y', 'a:g,h:b,j:'],
                   'col_3': ['rt,yu', 'gq', 'tr,ui','pi,pw,pp' ]
                   })

我可以使用以下代码将键值对提取为不同的列:

df[['c1', 'c2']] = df['col_2'].str.extract(r'^([^:,]*):([^:,]*)&')

因此,我可以将所有键值对提取为列,然后进行合并,但这似乎是一种冗长的方法。有没有其他优化的方式?

英文:

I have a dataframe that looks like this:

df = pd.DataFrame({'col_1': ['1', '2', '3', '4'],
                   'col_2': ['a:b,c:d', ':v', 'w:,x:y', 'a:g,h:b,j:']
                   })

The datatype of col_2 is a string, so we must do string manipulation/regex.

I also have another dataframe that has a mapping between key-value pair from col_2. It looks like this:

df1 = pd.DataFrame({'col_1': ['a', 'c', '', 'w', 'x', 'a', 'h', 'j','t'],
                    'col_2': ['b', 'd', 'v', '','y', 'g', 'b', '', 'g'],
                    'col_3': ['aw', 'rt', 'er', 'aa', 'ey', 'wk', 'oo', 'ri', 'ty'],
                    'col_4': ['rt', 'yu', 'gq', 'tr', 'ui', 'pi', 'pw', 'pp', 'uu']
                   })

basically a:b translated to aw:rt, which means you can't reach aw and rt without both a and b,

I want to get all the values from col_4 corresponding to the key-value pairs in col_2, so i want my output to be

pd.DataFrame({'col_1': ['1', '2', '3', '4'],
                   'col_2': ['a:b,c:d', ':v', 'w:,x:y', 'a:g,h:b,j:'],
                   'col_3': ['rt,yu', 'gq', 'tr,ui','pi,pw,pp' ]
                   })

I am able to extract key, value pair as different columns using

df[['c1', 'c2']] = df['col_2'].str.extract(r'^([^:,]*):([^:,]*)')

so I can extract all the key-value pairs as columns and then do merge, but it looks like a lengthy route, Any other optimised way?

答案1

得分: 2

我会在这里使用基本的pandas方法。拆分并展开col_2以获得单独的配对,创建从配对到col_4的映射,然后将其映射以替换值。

pairs = df['col_2'].str.split(',').explode()
mapping = df1['col_4'].set_axis(df1['col_1'] + ':' + df1['col_2'])
df['col_3'] = pairs.map(mapping).groupby(level=0).agg(','.join)

基于配对合并数据框

英文:

I would use the basic pandas methods here. Split and explode col_2 to get the individual pairs, create a mapping from pairs to col_4 and just map it to replace the values.

pairs = df['col_2'].str.split(',').explode()
mapping = df1['col_4'].set_axis(df1['col_1'] + ':' + df1['col_2'])
df['col_3'] = pairs.map(mapping).groupby(level=0).agg(','.join)

基于配对合并数据框

huangapple
  • 本文由 发表于 2023年2月24日 03:27:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/75549463.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定