将列从一个数据框映射到另一个以创建新列 [英] Mapping columns from one dataframe to another to create a new column

问题描述

我有一个数据框

id  store    address
1    100        xyz
2    200        qwe
3    300        asd
4    400        zxc
5    500        bnm

我还有另一个数据框df2

i have another dataframe df2

serialNo    store_code  warehouse
    1          300         Land
    2          500         Sea
    3          100         Land
    4          200         Sea
    5          400         Land

我希望我的最终数据框看起来像这样:

I want my final dataframe to look like:

id  store    address  warehouse
1    100        xyz     Land
2    200        qwe     Sea
3    300        asd     Land
4    400        zxc     Land
5    500        bnm     Sea

即从一个数据框映射到另一个创建新列

i.e map from one dataframe onto another creating new column

推荐答案

df.merge

out = (df1.merge(df2, left_on='store', right_on='store_code')
          .reindex(columns=['id', 'store', 'address', 'warehouse']))
print(out)

   id  store address warehouse
0   1    100     xyz      Land
1   2    200     qwe       Sea
2   3    300     asd      Land
3   4    400     zxc      Land
4   5    500     bnm       Sea

---

pd.concat + df.sort_values

---

pd.concat + df.sort_values

u = df1.sort_values('store')
v = df2.sort_values('store_code')[['warehouse']].reset_index(drop=1)
out = pd.concat([u, v], 1)

print(out)

   id  store address warehouse
0   1    100     xyz      Land
1   2    200     qwe       Sea
2   3    300     asd      Land
3   4    400     zxc      Land
4   5    500     bnm       Sea

如果您的数据框已经在store上进行了排序，则第一次排序调用是多余的，在这种情况下，您可以将其删除.

The first sort call is redundant assuming your dataframe is already sorted on store, in which case you may remove it.

s = df1.store.replace(df2.set_index('store_code')['warehouse'])
print(s) 
0    Land
1     Sea
2    Land
3    Land
4     Sea

df1['warehouse'] = s
print(df1)

   id  store address warehouse
0   1    100     xyz      Land
1   2    200     qwe       Sea
2   3    300     asd      Land
3   4    400     zxc      Land
4   5    500     bnm       Sea

或者，显式创建一个映射.如果您以后要使用它，则可以使用.

Alternatively, create a mapping explicitly. This works if you want to use it later.

mapping = dict(df2[['store_code', 'warehouse']].values)
df1['warehouse'] = df1.store.map(mapping)
print(df1)

   id  store address warehouse
0   1    100     xyz      Land
1   2    200     qwe       Sea
2   3    300     asd      Land
3   4    400     zxc      Land
4   5    500     bnm       Sea

这篇关于将列从一个数据框映射到另一个以创建新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！