How to get the common index of two pandas dataframes?
up vote
4
down vote
favorite
I have two pandas DataFrames df1 and df2 and I want to transform them in order that they keep values only for the index that are common to the 2 dataframes.
df1
values 1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
01/12/2000 0.012749
04/12/2000 0.113892
df2
values 2
24/11/2000 -0.004808
27/11/2000 -0.001812
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
become
df1
value 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2
value 2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
python pandas dataframe
add a comment |
up vote
4
down vote
favorite
I have two pandas DataFrames df1 and df2 and I want to transform them in order that they keep values only for the index that are common to the 2 dataframes.
df1
values 1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
01/12/2000 0.012749
04/12/2000 0.113892
df2
values 2
24/11/2000 -0.004808
27/11/2000 -0.001812
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
become
df1
value 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2
value 2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
python pandas dataframe
add a comment |
up vote
4
down vote
favorite
up vote
4
down vote
favorite
I have two pandas DataFrames df1 and df2 and I want to transform them in order that they keep values only for the index that are common to the 2 dataframes.
df1
values 1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
01/12/2000 0.012749
04/12/2000 0.113892
df2
values 2
24/11/2000 -0.004808
27/11/2000 -0.001812
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
become
df1
value 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2
value 2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
python pandas dataframe
I have two pandas DataFrames df1 and df2 and I want to transform them in order that they keep values only for the index that are common to the 2 dataframes.
df1
values 1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
01/12/2000 0.012749
04/12/2000 0.113892
df2
values 2
24/11/2000 -0.004808
27/11/2000 -0.001812
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
become
df1
value 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2
value 2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
python pandas dataframe
python pandas dataframe
asked Jan 9 at 14:54
astudentofmaths
370120
370120
add a comment |
add a comment |
7 Answers
7
active
oldest
votes
up vote
7
down vote
You can use Index.intersection + DataFrame.loc:
idx = df1.index.intersection(df2.index)
print (idx)
Index(['28/11/2000', '29/11/2000', '30/11/2000'], dtype='object')
Alternative solution with numpy.intersect1d:
idx = np.intersect1d(df1.index, df2.index)
print (idx)
['28/11/2000' '29/11/2000' '30/11/2000']
df1 = df1.loc[idx]
print (df1)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2 = df1.loc[idx]
print (df2)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
add a comment |
up vote
5
down vote
In [352]: common = df1.index.intersection(df2.index)
In [353]: df1.loc[common]
Out[353]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [354]: df2.loc[common]
Out[354]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
4
down vote
And, using isin. intersection might be faster though.
In [286]: df1.loc[df1.index.isin(df2.index)]
Out[286]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [287]: df2.loc[df2.index.isin(df1.index)]
Out[287]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
3
down vote
reindex + dropna
df1.reindex(df2.index).dropna()
Out[21]:
values1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2.reindex(df1.index).dropna()
Out[22]:
values2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
1
down vote
Have you tried something like
df1 = df1.loc[[x for x in df1.index if x in df2.index]]
df2 = df2.loc[[x for x in df2.index if x in df1.index]]
add a comment |
up vote
0
down vote
You can pd.merge them with an intermediary DataFrame created with the indexes of the other DataFrame:
df2_indexes = pd.DataFrame(index=df2.index)
df1 = pd.merge(df1, df2_indexes, left_index=True, right_index=True)
df1_indexes = pd.DataFrame(index=df1.index)
df2 = pd.merge(df2, df1_indexes, left_index=True, right_index=True)
or you can use pd.eval:
df2_indexes = df2.index.values
df1 = df1[eval("df1.index in df2_indexes"]
df1_indexes = df1.index.values
df2 = df2[eval("df2.index in df1_indexes"]
add a comment |
up vote
0
down vote
The index object has some set-like properties so you simply can take the intersection as follows:
df1 = df1.reindex[ df1.index & df2.index ]
This retains the order of the first dataframe in the intersection, df.
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%2f48170867%2fhow-to-get-the-common-index-of-two-pandas-dataframes%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
7 Answers
7
active
oldest
votes
7 Answers
7
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
7
down vote
You can use Index.intersection + DataFrame.loc:
idx = df1.index.intersection(df2.index)
print (idx)
Index(['28/11/2000', '29/11/2000', '30/11/2000'], dtype='object')
Alternative solution with numpy.intersect1d:
idx = np.intersect1d(df1.index, df2.index)
print (idx)
['28/11/2000' '29/11/2000' '30/11/2000']
df1 = df1.loc[idx]
print (df1)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2 = df1.loc[idx]
print (df2)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
add a comment |
up vote
7
down vote
You can use Index.intersection + DataFrame.loc:
idx = df1.index.intersection(df2.index)
print (idx)
Index(['28/11/2000', '29/11/2000', '30/11/2000'], dtype='object')
Alternative solution with numpy.intersect1d:
idx = np.intersect1d(df1.index, df2.index)
print (idx)
['28/11/2000' '29/11/2000' '30/11/2000']
df1 = df1.loc[idx]
print (df1)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2 = df1.loc[idx]
print (df2)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
add a comment |
up vote
7
down vote
up vote
7
down vote
You can use Index.intersection + DataFrame.loc:
idx = df1.index.intersection(df2.index)
print (idx)
Index(['28/11/2000', '29/11/2000', '30/11/2000'], dtype='object')
Alternative solution with numpy.intersect1d:
idx = np.intersect1d(df1.index, df2.index)
print (idx)
['28/11/2000' '29/11/2000' '30/11/2000']
df1 = df1.loc[idx]
print (df1)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2 = df1.loc[idx]
print (df2)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
You can use Index.intersection + DataFrame.loc:
idx = df1.index.intersection(df2.index)
print (idx)
Index(['28/11/2000', '29/11/2000', '30/11/2000'], dtype='object')
Alternative solution with numpy.intersect1d:
idx = np.intersect1d(df1.index, df2.index)
print (idx)
['28/11/2000' '29/11/2000' '30/11/2000']
df1 = df1.loc[idx]
print (df1)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2 = df1.loc[idx]
print (df2)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
answered Jan 9 at 14:57
jezrael
317k22257336
317k22257336
add a comment |
add a comment |
up vote
5
down vote
In [352]: common = df1.index.intersection(df2.index)
In [353]: df1.loc[common]
Out[353]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [354]: df2.loc[common]
Out[354]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
5
down vote
In [352]: common = df1.index.intersection(df2.index)
In [353]: df1.loc[common]
Out[353]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [354]: df2.loc[common]
Out[354]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
5
down vote
up vote
5
down vote
In [352]: common = df1.index.intersection(df2.index)
In [353]: df1.loc[common]
Out[353]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [354]: df2.loc[common]
Out[354]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
In [352]: common = df1.index.intersection(df2.index)
In [353]: df1.loc[common]
Out[353]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [354]: df2.loc[common]
Out[354]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
answered Jan 9 at 14:58
MaxU
119k11107165
119k11107165
add a comment |
add a comment |
up vote
4
down vote
And, using isin. intersection might be faster though.
In [286]: df1.loc[df1.index.isin(df2.index)]
Out[286]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [287]: df2.loc[df2.index.isin(df1.index)]
Out[287]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
4
down vote
And, using isin. intersection might be faster though.
In [286]: df1.loc[df1.index.isin(df2.index)]
Out[286]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [287]: df2.loc[df2.index.isin(df1.index)]
Out[287]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
4
down vote
up vote
4
down vote
And, using isin. intersection might be faster though.
In [286]: df1.loc[df1.index.isin(df2.index)]
Out[286]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [287]: df2.loc[df2.index.isin(df1.index)]
Out[287]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
And, using isin. intersection might be faster though.
In [286]: df1.loc[df1.index.isin(df2.index)]
Out[286]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [287]: df2.loc[df2.index.isin(df1.index)]
Out[287]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
answered Jan 9 at 15:07
Zero
38.2k86388
38.2k86388
add a comment |
add a comment |
up vote
3
down vote
reindex + dropna
df1.reindex(df2.index).dropna()
Out[21]:
values1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2.reindex(df1.index).dropna()
Out[22]:
values2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
3
down vote
reindex + dropna
df1.reindex(df2.index).dropna()
Out[21]:
values1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2.reindex(df1.index).dropna()
Out[22]:
values2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
add a comment |
up vote
3
down vote
up vote
3
down vote
reindex + dropna
df1.reindex(df2.index).dropna()
Out[21]:
values1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2.reindex(df1.index).dropna()
Out[22]:
values2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
reindex + dropna
df1.reindex(df2.index).dropna()
Out[21]:
values1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2.reindex(df1.index).dropna()
Out[22]:
values2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
answered Jan 9 at 15:16
W-B
99k73162
99k73162
add a comment |
add a comment |
up vote
1
down vote
Have you tried something like
df1 = df1.loc[[x for x in df1.index if x in df2.index]]
df2 = df2.loc[[x for x in df2.index if x in df1.index]]
add a comment |
up vote
1
down vote
Have you tried something like
df1 = df1.loc[[x for x in df1.index if x in df2.index]]
df2 = df2.loc[[x for x in df2.index if x in df1.index]]
add a comment |
up vote
1
down vote
up vote
1
down vote
Have you tried something like
df1 = df1.loc[[x for x in df1.index if x in df2.index]]
df2 = df2.loc[[x for x in df2.index if x in df1.index]]
Have you tried something like
df1 = df1.loc[[x for x in df1.index if x in df2.index]]
df2 = df2.loc[[x for x in df2.index if x in df1.index]]
answered Jan 9 at 14:57
LangeHaare
820720
820720
add a comment |
add a comment |
up vote
0
down vote
You can pd.merge them with an intermediary DataFrame created with the indexes of the other DataFrame:
df2_indexes = pd.DataFrame(index=df2.index)
df1 = pd.merge(df1, df2_indexes, left_index=True, right_index=True)
df1_indexes = pd.DataFrame(index=df1.index)
df2 = pd.merge(df2, df1_indexes, left_index=True, right_index=True)
or you can use pd.eval:
df2_indexes = df2.index.values
df1 = df1[eval("df1.index in df2_indexes"]
df1_indexes = df1.index.values
df2 = df2[eval("df2.index in df1_indexes"]
add a comment |
up vote
0
down vote
You can pd.merge them with an intermediary DataFrame created with the indexes of the other DataFrame:
df2_indexes = pd.DataFrame(index=df2.index)
df1 = pd.merge(df1, df2_indexes, left_index=True, right_index=True)
df1_indexes = pd.DataFrame(index=df1.index)
df2 = pd.merge(df2, df1_indexes, left_index=True, right_index=True)
or you can use pd.eval:
df2_indexes = df2.index.values
df1 = df1[eval("df1.index in df2_indexes"]
df1_indexes = df1.index.values
df2 = df2[eval("df2.index in df1_indexes"]
add a comment |
up vote
0
down vote
up vote
0
down vote
You can pd.merge them with an intermediary DataFrame created with the indexes of the other DataFrame:
df2_indexes = pd.DataFrame(index=df2.index)
df1 = pd.merge(df1, df2_indexes, left_index=True, right_index=True)
df1_indexes = pd.DataFrame(index=df1.index)
df2 = pd.merge(df2, df1_indexes, left_index=True, right_index=True)
or you can use pd.eval:
df2_indexes = df2.index.values
df1 = df1[eval("df1.index in df2_indexes"]
df1_indexes = df1.index.values
df2 = df2[eval("df2.index in df1_indexes"]
You can pd.merge them with an intermediary DataFrame created with the indexes of the other DataFrame:
df2_indexes = pd.DataFrame(index=df2.index)
df1 = pd.merge(df1, df2_indexes, left_index=True, right_index=True)
df1_indexes = pd.DataFrame(index=df1.index)
df2 = pd.merge(df2, df1_indexes, left_index=True, right_index=True)
or you can use pd.eval:
df2_indexes = df2.index.values
df1 = df1[eval("df1.index in df2_indexes"]
df1_indexes = df1.index.values
df2 = df2[eval("df2.index in df1_indexes"]
edited Nov 25 at 17:33
answered Nov 22 at 16:24
starostise
11
11
add a comment |
add a comment |
up vote
0
down vote
The index object has some set-like properties so you simply can take the intersection as follows:
df1 = df1.reindex[ df1.index & df2.index ]
This retains the order of the first dataframe in the intersection, df.
add a comment |
up vote
0
down vote
The index object has some set-like properties so you simply can take the intersection as follows:
df1 = df1.reindex[ df1.index & df2.index ]
This retains the order of the first dataframe in the intersection, df.
add a comment |
up vote
0
down vote
up vote
0
down vote
The index object has some set-like properties so you simply can take the intersection as follows:
df1 = df1.reindex[ df1.index & df2.index ]
This retains the order of the first dataframe in the intersection, df.
The index object has some set-like properties so you simply can take the intersection as follows:
df1 = df1.reindex[ df1.index & df2.index ]
This retains the order of the first dataframe in the intersection, df.
answered Dec 8 at 18:51
iamjli
212
212
add a comment |
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%2f48170867%2fhow-to-get-the-common-index-of-two-pandas-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