ValueError: Can only compare identically-labeled Series objects in pandas
i have two dataframe like this.
df1
MainId,Time,info1,info2
100,2018-07-12 08:05:00,a,b
100,2018-07-12 08:07:00,x,y
101,2018-07-14 16:00,c,d
100,2018-07-14 19:30:00,d,e
104,2018-07-14 03:30:00,g,h
and
df2
Id,MainId,startTime,endTime,value
1,100,2018-07-12 08:00:00,2018-07-12 08:10:00,1001
2,150,2018-07-14 10:05:00,2018-07-14 17:05:00,1002
3,101,2018-07-12 0:05:00,2018-07-12 19:05:00,1003
4,100,2018-07-12 08:05:00,2018-07-12 08:15:00,1004
df2 is main dataframe and df1 is subdataframe. I would like to check starttime and endtime of df2 with the time in df1 with respective to MainId. If df1.Time isin df2(start and endtime) with respective to MainId, then i want to include info1 and info2 column of df1 to df2. If there are no values, then I would like to enter just nan.
I want my output like this
Id,MainId,info1,info2,value
1,100,a,b,1001
1,100,x,y,1001
2,150,nan,nan,1002
3,101,nan,nan,1003
4,100,a,b,1004
4,100,x,y,1004
Here I have two same Id(In Id1) and MainId in output because they have different info1 and info2 and I want to include that one too.
This is what I am doing in pandas
df2['info1'] = np.where((df2['MainId'] == df1['MainId'])& (df1['Time'].isin([df2['startTime'], df2['endTime']])),df1['info1'], np.nan)
but it is throwing an error
ValueError: Can only compare identically-labeled Series objects
How Can i Fix this error ? Is there a better way ?
python pandas dataframe
asked Nov 12 at 13:10
user3280146
7112722
add a comment |
i have two dataframe like this.
df1
MainId,Time,info1,info2
100,2018-07-12 08:05:00,a,b
100,2018-07-12 08:07:00,x,y
101,2018-07-14 16:00,c,d
100,2018-07-14 19:30:00,d,e
104,2018-07-14 03:30:00,g,h
and
df2
Id,MainId,startTime,endTime,value
1,100,2018-07-12 08:00:00,2018-07-12 08:10:00,1001
2,150,2018-07-14 10:05:00,2018-07-14 17:05:00,1002
3,101,2018-07-12 0:05:00,2018-07-12 19:05:00,1003
4,100,2018-07-12 08:05:00,2018-07-12 08:15:00,1004
df2 is main dataframe and df1 is subdataframe. I would like to check starttime and endtime of df2 with the time in df1 with respective to MainId. If df1.Time isin df2(start and endtime) with respective to MainId, then i want to include info1 and info2 column of df1 to df2. If there are no values, then I would like to enter just nan.
I want my output like this
Id,MainId,info1,info2,value
1,100,a,b,1001
1,100,x,y,1001
2,150,nan,nan,1002
3,101,nan,nan,1003
4,100,a,b,1004
4,100,x,y,1004
Here I have two same Id(In Id1) and MainId in output because they have different info1 and info2 and I want to include that one too.
This is what I am doing in pandas
df2['info1'] = np.where((df2['MainId'] == df1['MainId'])& (df1['Time'].isin([df2['startTime'], df2['endTime']])),df1['info1'], np.nan)
but it is throwing an error
ValueError: Can only compare identically-labeled Series objects
How Can i Fix this error ? Is there a better way ?
python pandas dataframe
asked Nov 12 at 13:10
user3280146
7112722
1
You should mergeon=MainIdand then use a boolean mask afterwards to find where the time is between. It's very similar to this answer, though in this case you just merge based onMainId
– ALollz
Nov 12 at 14:43
@ALollz I did trying first withmergebut problem I found is that, after merge, while selecting the data when the Time isin (start and end), it is possible that some of the original data fromdf2were missing. I need to return all the data fromdf2( expected output from question)
– user3280146
Nov 12 at 18:22
add a comment |
i have two dataframe like this.
df1
MainId,Time,info1,info2
100,2018-07-12 08:05:00,a,b
100,2018-07-12 08:07:00,x,y
101,2018-07-14 16:00,c,d
100,2018-07-14 19:30:00,d,e
104,2018-07-14 03:30:00,g,h
and
df2
Id,MainId,startTime,endTime,value
1,100,2018-07-12 08:00:00,2018-07-12 08:10:00,1001
2,150,2018-07-14 10:05:00,2018-07-14 17:05:00,1002
3,101,2018-07-12 0:05:00,2018-07-12 19:05:00,1003
4,100,2018-07-12 08:05:00,2018-07-12 08:15:00,1004
df2 is main dataframe and df1 is subdataframe. I would like to check starttime and endtime of df2 with the time in df1 with respective to MainId. If df1.Time isin df2(start and endtime) with respective to MainId, then i want to include info1 and info2 column of df1 to df2. If there are no values, then I would like to enter just nan.
I want my output like this
Id,MainId,info1,info2,value
1,100,a,b,1001
1,100,x,y,1001
2,150,nan,nan,1002
3,101,nan,nan,1003
4,100,a,b,1004
4,100,x,y,1004
Here I have two same Id(In Id1) and MainId in output because they have different info1 and info2 and I want to include that one too.
This is what I am doing in pandas
df2['info1'] = np.where((df2['MainId'] == df1['MainId'])& (df1['Time'].isin([df2['startTime'], df2['endTime']])),df1['info1'], np.nan)
but it is throwing an error
ValueError: Can only compare identically-labeled Series objects
How Can i Fix this error ? Is there a better way ?
python pandas dataframe
asked Nov 12 at 13:10
user3280146
7112722
i have two dataframe like this.
df1
MainId,Time,info1,info2
100,2018-07-12 08:05:00,a,b
100,2018-07-12 08:07:00,x,y
101,2018-07-14 16:00,c,d
100,2018-07-14 19:30:00,d,e
104,2018-07-14 03:30:00,g,h
and
df2
Id,MainId,startTime,endTime,value
1,100,2018-07-12 08:00:00,2018-07-12 08:10:00,1001
2,150,2018-07-14 10:05:00,2018-07-14 17:05:00,1002
3,101,2018-07-12 0:05:00,2018-07-12 19:05:00,1003
4,100,2018-07-12 08:05:00,2018-07-12 08:15:00,1004
df2 is main dataframe and df1 is subdataframe. I would like to check starttime and endtime of df2 with the time in df1 with respective to MainId. If df1.Time isin df2(start and endtime) with respective to MainId, then i want to include info1 and info2 column of df1 to df2. If there are no values, then I would like to enter just nan.
I want my output like this
Id,MainId,info1,info2,value
1,100,a,b,1001
1,100,x,y,1001
2,150,nan,nan,1002
3,101,nan,nan,1003
4,100,a,b,1004
4,100,x,y,1004
Here I have two same Id(In Id1) and MainId in output because they have different info1 and info2 and I want to include that one too.
This is what I am doing in pandas
df2['info1'] = np.where((df2['MainId'] == df1['MainId'])& (df1['Time'].isin([df2['startTime'], df2['endTime']])),df1['info1'], np.nan)
but it is throwing an error
ValueError: Can only compare identically-labeled Series objects
How Can i Fix this error ? Is there a better way ?
python pandas dataframe
python pandas dataframe
asked Nov 12 at 13:10
user3280146
7112722
asked Nov 12 at 13:10
user3280146
7112722
edited Nov 13 at 11:05
asked Nov 12 at 13:10
user3280146
7112722
asked Nov 12 at 13:10
user3280146
7112722
asked Nov 12 at 13:10
user3280146
7112722
7112722
1
You should mergeon=MainIdand then use a boolean mask afterwards to find where the time is between. It's very similar to this answer, though in this case you just merge based onMainId
– ALollz
Nov 12 at 14:43
@ALollz I did trying first withmergebut problem I found is that, after merge, while selecting the data when the Time isin (start and end), it is possible that some of the original data fromdf2were missing. I need to return all the data fromdf2( expected output from question)
– user3280146
Nov 12 at 18:22
add a comment |
1
You should mergeon=MainIdand then use a boolean mask afterwards to find where the time is between. It's very similar to this answer, though in this case you just merge based onMainId
– ALollz
Nov 12 at 14:43
@ALollz I did trying first withmergebut problem I found is that, after merge, while selecting the data when the Time isin (start and end), it is possible that some of the original data fromdf2were missing. I need to return all the data fromdf2( expected output from question)
– user3280146
Nov 12 at 18:22
1
1
You should merge
on=MainId and then use a boolean mask afterwards to find where the time is between. It's very similar to this answer, though in this case you just merge based on MainId– ALollz
Nov 12 at 14:43
You should merge
on=MainId and then use a boolean mask afterwards to find where the time is between. It's very similar to this answer, though in this case you just merge based on MainId– ALollz
Nov 12 at 14:43
@ALollz I did trying first with
merge but problem I found is that, after merge, while selecting the data when the Time isin (start and end), it is possible that some of the original data from df2 were missing. I need to return all the data from df2 ( expected output from question)– user3280146
Nov 12 at 18:22
@ALollz I did trying first with
merge but problem I found is that, after merge, while selecting the data when the Time isin (start and end), it is possible that some of the original data from df2 were missing. I need to return all the data from df2 ( expected output from question)– user3280146
Nov 12 at 18:22
add a comment |
1 Answer
1
active
oldest
votes
df1 and df2 have diferente Index (you can check this by inspecting df1.index and df2.index. Hence, when you do df2['MainId'] == df1['MainId'], you have 2 series objects that are not comparable.
Try using a left join, something like:
df3 = df2.join(df1.set_index('MainId'), on='MainId'))
should give you the dataframe you want. You can then use it to execute your comparisons.
answered Nov 12 at 13:16
Daniel Severo
580612
I am not sure, if merging dataframe would be a good solution in this case.
– user3280146
Nov 12 at 13:37
Why not? You will need to do that some way or another.
– Daniel Severo
Nov 26 at 17:45
add a comment |
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
});
}
});
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%2f53262894%2fvalueerror-can-only-compare-identically-labeled-series-objects-in-pandas%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
df1 and df2 have diferente Index (you can check this by inspecting df1.index and df2.index. Hence, when you do df2['MainId'] == df1['MainId'], you have 2 series objects that are not comparable.
Try using a left join, something like:
df3 = df2.join(df1.set_index('MainId'), on='MainId'))
should give you the dataframe you want. You can then use it to execute your comparisons.
answered Nov 12 at 13:16
Daniel Severo
580612
I am not sure, if merging dataframe would be a good solution in this case.
– user3280146
Nov 12 at 13:37
Why not? You will need to do that some way or another.
– Daniel Severo
Nov 26 at 17:45
add a comment |
df1 and df2 have diferente Index (you can check this by inspecting df1.index and df2.index. Hence, when you do df2['MainId'] == df1['MainId'], you have 2 series objects that are not comparable.
Try using a left join, something like:
df3 = df2.join(df1.set_index('MainId'), on='MainId'))
should give you the dataframe you want. You can then use it to execute your comparisons.
answered Nov 12 at 13:16
Daniel Severo
580612
I am not sure, if merging dataframe would be a good solution in this case.
– user3280146
Nov 12 at 13:37
Why not? You will need to do that some way or another.
– Daniel Severo
Nov 26 at 17:45
add a comment |
df1 and df2 have diferente Index (you can check this by inspecting df1.index and df2.index. Hence, when you do df2['MainId'] == df1['MainId'], you have 2 series objects that are not comparable.
Try using a left join, something like:
df3 = df2.join(df1.set_index('MainId'), on='MainId'))
should give you the dataframe you want. You can then use it to execute your comparisons.
answered Nov 12 at 13:16
Daniel Severo
580612
df1 and df2 have diferente Index (you can check this by inspecting df1.index and df2.index. Hence, when you do df2['MainId'] == df1['MainId'], you have 2 series objects that are not comparable.
Try using a left join, something like:
df3 = df2.join(df1.set_index('MainId'), on='MainId'))
should give you the dataframe you want. You can then use it to execute your comparisons.
answered Nov 12 at 13:16
Daniel Severo
580612
answered Nov 12 at 13:16
Daniel Severo
580612
answered Nov 12 at 13:16
Daniel Severo
580612
answered Nov 12 at 13:16
Daniel Severo
580612
580612
I am not sure, if merging dataframe would be a good solution in this case.
– user3280146
Nov 12 at 13:37
Why not? You will need to do that some way or another.
– Daniel Severo
Nov 26 at 17:45
add a comment |
I am not sure, if merging dataframe would be a good solution in this case.
– user3280146
Nov 12 at 13:37
Why not? You will need to do that some way or another.
– Daniel Severo
Nov 26 at 17:45
I am not sure, if merging dataframe would be a good solution in this case.
– user3280146
Nov 12 at 13:37
I am not sure, if merging dataframe would be a good solution in this case.
– user3280146
Nov 12 at 13:37
Why not? You will need to do that some way or another.
– Daniel Severo
Nov 26 at 17:45
Why not? You will need to do that some way or another.
– Daniel Severo
Nov 26 at 17:45
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53262894%2fvalueerror-can-only-compare-identically-labeled-series-objects-in-pandas%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
1
You should merge
on=MainIdand then use a boolean mask afterwards to find where the time is between. It's very similar to this answer, though in this case you just merge based onMainId– ALollz
Nov 12 at 14:43
@ALollz I did trying first with
mergebut problem I found is that, after merge, while selecting the data when the Time isin (start and end), it is possible that some of the original data fromdf2were missing. I need to return all the data fromdf2( expected output from question)– user3280146
Nov 12 at 18:22