Improve multiple merges of dataframes
0
I have the following dataframes:
print(inventory_df)
dt_op Prod_1 Prod_2 ... Prod_n
10/09/18 0 0 0
11/09/18 0 0 0
12/09/18 0 0 0
...
print(final_inspect)
dt_op Prod_1
10/09/18 10
11/09/18 2
12/09/18 5
print(updated_df)
dt_op Prod_1 Prod_2 ... Prod_n
10/09/18 10 0 0
11/09/18 2 0 0
12/09/18 5 0 0
...
I am trying to update the dataframe "inventory_df", with the values contained in "final_inspect", in order to get "updated_df" with:
final_inspect = pd.DataFrame(data = {'dt_op': inspect["dt_op"] , j: inventory})
final_inspect_1 = pd.DataFrame(data = {'dt_op':inventory_df.dt_op })
final_inspect_1 = final_inspect_1.merge(final_inspect, on = "dt_op", how = "left").fillna(0)
inventory_df = inventory_df.merge(final_inspect_1, on = ["dt_op", j], how = "outer").fillna(0)
inventory_df = inventory_df.drop_duplicates(subset=None, keep='first', inplace=False)
The solution is cumbersome, but the update function from pandas does not seem to work ( inventory_df.update(final_inspect) ).
How can I improve this solution, in order to run it on several (3000) items?
N.B. The size of updated_df has to be the same as inventory_df, and nrows of final_inspect are less than nrows of inventory_df.
python pandas
asked Nov 20 '18 at 10:14
Alessandro CeccarelliAlessandro Ceccarelli
269211
|
show 3 more comments
0
I have the following dataframes:
print(inventory_df)
dt_op Prod_1 Prod_2 ... Prod_n
10/09/18 0 0 0
11/09/18 0 0 0
12/09/18 0 0 0
...
print(final_inspect)
dt_op Prod_1
10/09/18 10
11/09/18 2
12/09/18 5
print(updated_df)
dt_op Prod_1 Prod_2 ... Prod_n
10/09/18 10 0 0
11/09/18 2 0 0
12/09/18 5 0 0
...
I am trying to update the dataframe "inventory_df", with the values contained in "final_inspect", in order to get "updated_df" with:
final_inspect = pd.DataFrame(data = {'dt_op': inspect["dt_op"] , j: inventory})
final_inspect_1 = pd.DataFrame(data = {'dt_op':inventory_df.dt_op })
final_inspect_1 = final_inspect_1.merge(final_inspect, on = "dt_op", how = "left").fillna(0)
inventory_df = inventory_df.merge(final_inspect_1, on = ["dt_op", j], how = "outer").fillna(0)
inventory_df = inventory_df.drop_duplicates(subset=None, keep='first', inplace=False)
The solution is cumbersome, but the update function from pandas does not seem to work ( inventory_df.update(final_inspect) ).
How can I improve this solution, in order to run it on several (3000) items?
N.B. The size of updated_df has to be the same as inventory_df, and nrows of final_inspect are less than nrows of inventory_df.
python pandas
asked Nov 20 '18 at 10:14
Alessandro CeccarelliAlessandro Ceccarelli
269211
Can you further explain what does not work withupdate()? For me, it works with the data you provided. The only thingupdate()may do is to change the dtype from int to float (which is possibly related to this known issue. However, you could fix this by usinginventory_df.update(final_inspect).astype(int).
– normanius
Nov 20 '18 at 11:51
running the code you suggester raises: AttributeError: 'NoneType' object has no attribute 'astype' @normanius
– Alessandro Ceccarelli
Nov 20 '18 at 16:50
My bad,update()operates in-place and returns None. Sorry. I meant the following:inventory_df.update(final_inspect); inventory_df = inventory_df.astype(int). Obviously, the latter works only if the dataframe consists of values that can be converted to int.
– normanius
Nov 20 '18 at 17:17
What exactly does not work withupdate()?
– normanius
Nov 20 '18 at 17:18
It does not update the first dataframe, leaving zeros. Unfortunately, I have a datetime object that, if converted to int, becomes a long set of numbers. Furthermore, even trying to convert only the product column to int update does not work.
– Alessandro Ceccarelli
Nov 20 '18 at 17:33
|
show 3 more comments
0
0
0
I have the following dataframes:
print(inventory_df)
dt_op Prod_1 Prod_2 ... Prod_n
10/09/18 0 0 0
11/09/18 0 0 0
12/09/18 0 0 0
...
print(final_inspect)
dt_op Prod_1
10/09/18 10
11/09/18 2
12/09/18 5
print(updated_df)
dt_op Prod_1 Prod_2 ... Prod_n
10/09/18 10 0 0
11/09/18 2 0 0
12/09/18 5 0 0
...
I am trying to update the dataframe "inventory_df", with the values contained in "final_inspect", in order to get "updated_df" with:
final_inspect = pd.DataFrame(data = {'dt_op': inspect["dt_op"] , j: inventory})
final_inspect_1 = pd.DataFrame(data = {'dt_op':inventory_df.dt_op })
final_inspect_1 = final_inspect_1.merge(final_inspect, on = "dt_op", how = "left").fillna(0)
inventory_df = inventory_df.merge(final_inspect_1, on = ["dt_op", j], how = "outer").fillna(0)
inventory_df = inventory_df.drop_duplicates(subset=None, keep='first', inplace=False)
The solution is cumbersome, but the update function from pandas does not seem to work ( inventory_df.update(final_inspect) ).
How can I improve this solution, in order to run it on several (3000) items?
N.B. The size of updated_df has to be the same as inventory_df, and nrows of final_inspect are less than nrows of inventory_df.
python pandas
asked Nov 20 '18 at 10:14
Alessandro CeccarelliAlessandro Ceccarelli
269211
I have the following dataframes:
print(inventory_df)
dt_op Prod_1 Prod_2 ... Prod_n
10/09/18 0 0 0
11/09/18 0 0 0
12/09/18 0 0 0
...
print(final_inspect)
dt_op Prod_1
10/09/18 10
11/09/18 2
12/09/18 5
print(updated_df)
dt_op Prod_1 Prod_2 ... Prod_n
10/09/18 10 0 0
11/09/18 2 0 0
12/09/18 5 0 0
...
I am trying to update the dataframe "inventory_df", with the values contained in "final_inspect", in order to get "updated_df" with:
final_inspect = pd.DataFrame(data = {'dt_op': inspect["dt_op"] , j: inventory})
final_inspect_1 = pd.DataFrame(data = {'dt_op':inventory_df.dt_op })
final_inspect_1 = final_inspect_1.merge(final_inspect, on = "dt_op", how = "left").fillna(0)
inventory_df = inventory_df.merge(final_inspect_1, on = ["dt_op", j], how = "outer").fillna(0)
inventory_df = inventory_df.drop_duplicates(subset=None, keep='first', inplace=False)
The solution is cumbersome, but the update function from pandas does not seem to work ( inventory_df.update(final_inspect) ).
How can I improve this solution, in order to run it on several (3000) items?
N.B. The size of updated_df has to be the same as inventory_df, and nrows of final_inspect are less than nrows of inventory_df.
python pandas
python pandas
asked Nov 20 '18 at 10:14
Alessandro CeccarelliAlessandro Ceccarelli
269211
asked Nov 20 '18 at 10:14
Alessandro CeccarelliAlessandro Ceccarelli
269211
edited Nov 20 '18 at 10:29
Alessandro Ceccarelli
asked Nov 20 '18 at 10:14
Alessandro CeccarelliAlessandro Ceccarelli
269211
asked Nov 20 '18 at 10:14
Alessandro CeccarelliAlessandro Ceccarelli
269211
asked Nov 20 '18 at 10:14
Alessandro CeccarelliAlessandro Ceccarelli
269211
269211
Can you further explain what does not work withupdate()? For me, it works with the data you provided. The only thingupdate()may do is to change the dtype from int to float (which is possibly related to this known issue. However, you could fix this by usinginventory_df.update(final_inspect).astype(int).
– normanius
Nov 20 '18 at 11:51
running the code you suggester raises: AttributeError: 'NoneType' object has no attribute 'astype' @normanius
– Alessandro Ceccarelli
Nov 20 '18 at 16:50
My bad,update()operates in-place and returns None. Sorry. I meant the following:inventory_df.update(final_inspect); inventory_df = inventory_df.astype(int). Obviously, the latter works only if the dataframe consists of values that can be converted to int.
– normanius
Nov 20 '18 at 17:17
What exactly does not work withupdate()?
– normanius
Nov 20 '18 at 17:18
It does not update the first dataframe, leaving zeros. Unfortunately, I have a datetime object that, if converted to int, becomes a long set of numbers. Furthermore, even trying to convert only the product column to int update does not work.
– Alessandro Ceccarelli
Nov 20 '18 at 17:33
|
show 3 more comments
Can you further explain what does not work withupdate()? For me, it works with the data you provided. The only thingupdate()may do is to change the dtype from int to float (which is possibly related to this known issue. However, you could fix this by usinginventory_df.update(final_inspect).astype(int).
– normanius
Nov 20 '18 at 11:51
running the code you suggester raises: AttributeError: 'NoneType' object has no attribute 'astype' @normanius
– Alessandro Ceccarelli
Nov 20 '18 at 16:50
My bad,update()operates in-place and returns None. Sorry. I meant the following:inventory_df.update(final_inspect); inventory_df = inventory_df.astype(int). Obviously, the latter works only if the dataframe consists of values that can be converted to int.
– normanius
Nov 20 '18 at 17:17
What exactly does not work withupdate()?
– normanius
Nov 20 '18 at 17:18
It does not update the first dataframe, leaving zeros. Unfortunately, I have a datetime object that, if converted to int, becomes a long set of numbers. Furthermore, even trying to convert only the product column to int update does not work.
– Alessandro Ceccarelli
Nov 20 '18 at 17:33
Can you further explain what does not work with
update()? For me, it works with the data you provided. The only thing update() may do is to change the dtype from int to float (which is possibly related to this known issue. However, you could fix this by using inventory_df.update(final_inspect).astype(int).– normanius
Nov 20 '18 at 11:51
Can you further explain what does not work with
update()? For me, it works with the data you provided. The only thing update() may do is to change the dtype from int to float (which is possibly related to this known issue. However, you could fix this by using inventory_df.update(final_inspect).astype(int).– normanius
Nov 20 '18 at 11:51
running the code you suggester raises: AttributeError: 'NoneType' object has no attribute 'astype' @normanius
– Alessandro Ceccarelli
Nov 20 '18 at 16:50
running the code you suggester raises: AttributeError: 'NoneType' object has no attribute 'astype' @normanius
– Alessandro Ceccarelli
Nov 20 '18 at 16:50
My bad,
update() operates in-place and returns None. Sorry. I meant the following: inventory_df.update(final_inspect); inventory_df = inventory_df.astype(int). Obviously, the latter works only if the dataframe consists of values that can be converted to int.– normanius
Nov 20 '18 at 17:17
My bad,
update() operates in-place and returns None. Sorry. I meant the following: inventory_df.update(final_inspect); inventory_df = inventory_df.astype(int). Obviously, the latter works only if the dataframe consists of values that can be converted to int.– normanius
Nov 20 '18 at 17:17
What exactly does not work with
update()?– normanius
Nov 20 '18 at 17:18
What exactly does not work with
update()?– normanius
Nov 20 '18 at 17:18
It does not update the first dataframe, leaving zeros. Unfortunately, I have a datetime object that, if converted to int, becomes a long set of numbers. Furthermore, even trying to convert only the product column to int update does not work.
– Alessandro Ceccarelli
Nov 20 '18 at 17:33
It does not update the first dataframe, leaving zeros. Unfortunately, I have a datetime object that, if converted to int, becomes a long set of numbers. Furthermore, even trying to convert only the product column to int update does not work.
– Alessandro Ceccarelli
Nov 20 '18 at 17:33
|
show 3 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53390721%2fimprove-multiple-merges-of-dataframes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53390721%2fimprove-multiple-merges-of-dataframes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you further explain what does not work with
update()? For me, it works with the data you provided. The only thingupdate()may do is to change the dtype from int to float (which is possibly related to this known issue. However, you could fix this by usinginventory_df.update(final_inspect).astype(int).– normanius
Nov 20 '18 at 11:51
running the code you suggester raises: AttributeError: 'NoneType' object has no attribute 'astype' @normanius
– Alessandro Ceccarelli
Nov 20 '18 at 16:50
My bad,
update()operates in-place and returns None. Sorry. I meant the following:inventory_df.update(final_inspect); inventory_df = inventory_df.astype(int). Obviously, the latter works only if the dataframe consists of values that can be converted to int.– normanius
Nov 20 '18 at 17:17
What exactly does not work with
update()?– normanius
Nov 20 '18 at 17:18
It does not update the first dataframe, leaving zeros. Unfortunately, I have a datetime object that, if converted to int, becomes a long set of numbers. Furthermore, even trying to convert only the product column to int update does not work.
– Alessandro Ceccarelli
Nov 20 '18 at 17:33