Mapping columns from one dataframe to another to create a new column [duplicate]

This question already has an answer here:

Pandas Merging 101

1 answer

i have a dataframe

id  store    address

1    100        xyz

2    200        qwe

3    300        asd

4    400        zxc

5    500        bnm

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

edited Sep 5 '17 at 23:41

coldspeed

139k24153239

asked Sep 5 '17 at 7:51

Shubham

1,88341850

marked as duplicate by coldspeed pandas
Users with the pandas badge can single-handedly close pandas questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 16:05

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

This question already has an answer here:

Pandas Merging 101

1 answer

i have a dataframe

id  store    address

1    100        xyz

2    200        qwe

3    300        asd

4    400        zxc

5    500        bnm

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

edited Sep 5 '17 at 23:41

coldspeed

139k24153239

asked Sep 5 '17 at 7:51

Shubham

1,88341850

marked as duplicate by coldspeed pandas
Users with the pandas badge can single-handedly close pandas questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 16:05

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

This question already has an answer here:

Pandas Merging 101

1 answer

i have a dataframe

id  store    address

1    100        xyz

2    200        qwe

3    300        asd

4    400        zxc

5    500        bnm

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

edited Sep 5 '17 at 23:41

coldspeed

139k24153239

asked Sep 5 '17 at 7:51

Shubham

1,88341850

This question already has an answer here:

Pandas Merging 101

1 answer

i have a dataframe

id  store    address

1    100        xyz

2    200        qwe

3    300        asd

4    400        zxc

5    500        bnm

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

This question already has an answer here:

Pandas Merging 101

1 answer

python pandas dataframe mapping

edited Sep 5 '17 at 23:41

coldspeed

139k24153239

asked Sep 5 '17 at 7:51

Shubham

1,88341850

edited Sep 5 '17 at 23:41

coldspeed

139k24153239

asked Sep 5 '17 at 7:51

Shubham

1,88341850

edited Sep 5 '17 at 23:41

coldspeed

139k24153239

edited Sep 5 '17 at 23:41

coldspeed

139k24153239

edited Sep 5 '17 at 23:41

coldspeed

139k24153239

asked Sep 5 '17 at 7:51

Shubham

1,88341850

asked Sep 5 '17 at 7:51

Shubham

1,88341850

asked Sep 5 '17 at 7:51

Shubham

1,88341850

marked as duplicate by coldspeed pandas
Users with the pandas badge can single-handedly close pandas questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 16:05

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

marked as duplicate by coldspeed pandas
Users with the pandas badge can single-handedly close pandas questions as duplicates and reopen them as needed.

StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;

$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');

$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Jan 1 at 16:05

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add a comment |

2 Answers
2

active

oldest

votes

`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`

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

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

`df.replace`/`df.map`

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

edited Jan 1 at 16:07

answered Sep 5 '17 at 8:04

coldspeed

139k24153239

add a comment |

Use map or join:

df1['warehouse'] = df1['store'].map(df2.set_index('store_code')['warehouse'])

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

df1 = df1.join(df2.set_index('store_code'), on=['store']).drop('serialNo', 1)

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

edited Sep 5 '17 at 8:17

answered Sep 5 '17 at 7:55

jezrael

354k26318394

i'm getting this error, when running .map code in a similar dataset. Reindexing only valid with uniquely valued Index objects

– Shubham
Sep 5 '17 at 9:01

I think there is problem you have duplicates in store_code in df2. so need df1['store'].map(df2.drop_duplicates('store_code').set_index('store_code')['warehouse'])

– jezrael
Sep 5 '17 at 9:05

1

correct! Thanks :)

– Shubham
Sep 5 '17 at 9:14

add a comment |

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

`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`

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

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

`df.replace`/`df.map`

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

edited Jan 1 at 16:07

answered Sep 5 '17 at 8:04

coldspeed

139k24153239

add a comment |

`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`

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

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

`df.replace`/`df.map`

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

edited Jan 1 at 16:07

answered Sep 5 '17 at 8:04

coldspeed

139k24153239

add a comment |

`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`

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

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

`df.replace`/`df.map`

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

edited Jan 1 at 16:07

answered Sep 5 '17 at 8:04

coldspeed

139k24153239

`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`

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

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

`df.replace`/`df.map`

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

edited Jan 1 at 16:07

answered Sep 5 '17 at 8:04

coldspeed

139k24153239

edited Jan 1 at 16:07

answered Sep 5 '17 at 8:04

coldspeed

139k24153239

answered Sep 5 '17 at 8:04

coldspeed

139k24153239

answered Sep 5 '17 at 8:04

coldspeed

139k24153239

add a comment |

Use map or join:

df1['warehouse'] = df1['store'].map(df2.set_index('store_code')['warehouse'])

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

df1 = df1.join(df2.set_index('store_code'), on=['store']).drop('serialNo', 1)

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

edited Sep 5 '17 at 8:17

answered Sep 5 '17 at 7:55

jezrael

354k26318394

i'm getting this error, when running .map code in a similar dataset. Reindexing only valid with uniquely valued Index objects

– Shubham
Sep 5 '17 at 9:01

I think there is problem you have duplicates in store_code in df2. so need df1['store'].map(df2.drop_duplicates('store_code').set_index('store_code')['warehouse'])

– jezrael
Sep 5 '17 at 9:05

1

correct! Thanks :)

– Shubham
Sep 5 '17 at 9:14

add a comment |

Use map or join:

df1['warehouse'] = df1['store'].map(df2.set_index('store_code')['warehouse'])

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

df1 = df1.join(df2.set_index('store_code'), on=['store']).drop('serialNo', 1)

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

edited Sep 5 '17 at 8:17

answered Sep 5 '17 at 7:55

jezrael

354k26318394

i'm getting this error, when running .map code in a similar dataset. Reindexing only valid with uniquely valued Index objects

– Shubham
Sep 5 '17 at 9:01

I think there is problem you have duplicates in store_code in df2. so need df1['store'].map(df2.drop_duplicates('store_code').set_index('store_code')['warehouse'])

– jezrael
Sep 5 '17 at 9:05

1

correct! Thanks :)

– Shubham
Sep 5 '17 at 9:14

add a comment |

Use map or join:

df1['warehouse'] = df1['store'].map(df2.set_index('store_code')['warehouse'])

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

df1 = df1.join(df2.set_index('store_code'), on=['store']).drop('serialNo', 1)

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

edited Sep 5 '17 at 8:17

answered Sep 5 '17 at 7:55

jezrael

354k26318394

Use map or join:

df1['warehouse'] = df1['store'].map(df2.set_index('store_code')['warehouse'])

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

df1 = df1.join(df2.set_index('store_code'), on=['store']).drop('serialNo', 1)

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

edited Sep 5 '17 at 8:17

answered Sep 5 '17 at 7:55

jezrael

354k26318394

edited Sep 5 '17 at 8:17

answered Sep 5 '17 at 7:55

jezrael

354k26318394

answered Sep 5 '17 at 7:55

jezrael

354k26318394

answered Sep 5 '17 at 7:55

jezrael

354k26318394

i'm getting this error, when running .map code in a similar dataset. Reindexing only valid with uniquely valued Index objects

– Shubham
Sep 5 '17 at 9:01

I think there is problem you have duplicates in store_code in df2. so need df1['store'].map(df2.drop_duplicates('store_code').set_index('store_code')['warehouse'])

– jezrael
Sep 5 '17 at 9:05

1

correct! Thanks :)

– Shubham
Sep 5 '17 at 9:14

add a comment |

i'm getting this error, when running .map code in a similar dataset. Reindexing only valid with uniquely valued Index objects

– Shubham
Sep 5 '17 at 9:01

I think there is problem you have duplicates in store_code in df2. so need df1['store'].map(df2.drop_duplicates('store_code').set_index('store_code')['warehouse'])

– jezrael
Sep 5 '17 at 9:05

1

correct! Thanks :)

– Shubham
Sep 5 '17 at 9:14

i'm getting this error, when running .map code in a similar dataset. Reindexing only valid with uniquely valued Index objects

– Shubham
Sep 5 '17 at 9:01

I think there is problem you have duplicates in store_code in df2. so need df1['store'].map(df2.drop_duplicates('store_code').set_index('store_code')['warehouse'])

– jezrael
Sep 5 '17 at 9:05

correct! Thanks :)

– Shubham
Sep 5 '17 at 9:14

add a comment |

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ndtyjky