pandas基于Index加入两个数据帧

时间:2021-08-17 22:57:36

I am trying to join two dataframes based on Index values of dataframes however, my result produces NaN and followed by values with index values repeated.

我试图基于数据帧的索引值加入两个数据帧但是,我的结果产生NaN,然后​​是重复索引值的值。

print st

results in (stored in data frame series and with only index as follows)

结果(存储在数据框系列中,只有索引如下)

index
1499054400000
1499140800000
1499227200000
1499313600000
1499400000000

Another dataframe is as follows

另一个数据帧如下

print dtest2

This results in (same index as above)

这导致(与上述索引相同)

                1
index   
1499227200000   33.48
1499400000000   35.71

I am trying to get the following when i merge two dataframes

我合并两个数据帧时试图得到以下内容

Result desired

结果需要

index            1
1499054400000    0.0
1499140800000    0.0
1499227200000    33.48
1499313600000    0.0
1499400000000    35.71

However when i concatenate as follows

但是,当我连接如下

pd.concat([st,dtest2],ignore_index=False)

I get the following

我得到以下内容

                1
index   
1499054400000   NaN
1499140800000   NaN
1499227200000   NaN
1499313600000   NaN
1499400000000   NaN
1499227200000   33.48
1499400000000   35.71

3 个解决方案

#1


5  

IIUC... use pd.DataFrame.reindex

IIUC ...使用pd.DataFrame.reindex

dtest2.reindex(st.index, fill_value=0)

                   1
index               
1499054400000   0.00
1499140800000   0.00
1499227200000  33.48
1499313600000   0.00
1499400000000  35.71

However, you may have a hidden problem in that the types are not the same. You can ensure that all indices are the same by using astype to cast them.

但是,您可能会遇到一个隐藏的问题,即类型不同。通过使用astype来强制转换,可以确保所有索引都相同。

dtest2.index = dtest2.index.astype(str)
st.index = st.index.astype(str)

dtest2.reindex(st.index, fill_value=0)

                   1
index               
1499054400000   0.00
1499140800000   0.00
1499227200000  33.48
1499313600000   0.00
1499400000000  35.71

Or

要么

dtest2.index = dtest2.index.astype(int)
st.index = st.index.astype(int)

dtest2.reindex(st.index, fill_value=0)

                   1
index               
1499054400000   0.00
1499140800000   0.00
1499227200000  33.48
1499313600000   0.00
1499400000000  35.71

#2


1  

use the merge function instead of concat

使用merge函数而不是concat

pd.merge(st,dtest2,how='outer',left_index=True,right_index=True).fillna(0)

#3


1  

You can try following:

您可以尝试以下方法:

result = st.join(dtest2, how='outer').fillna(0)
print(result)

Output:

输出:

                   1
  index               
  1499054400000   0.00
  1499140800000   0.00
  1499227200000  33.48
  1499313600000   0.00
  1499400000000  35.71

Or, left join would also give same result:

或者,左连接也会给出相同的结果:

result = st.join(dtest2, how='left').fillna(0)

#1


5  

IIUC... use pd.DataFrame.reindex

IIUC ...使用pd.DataFrame.reindex

dtest2.reindex(st.index, fill_value=0)

                   1
index               
1499054400000   0.00
1499140800000   0.00
1499227200000  33.48
1499313600000   0.00
1499400000000  35.71

However, you may have a hidden problem in that the types are not the same. You can ensure that all indices are the same by using astype to cast them.

但是,您可能会遇到一个隐藏的问题,即类型不同。通过使用astype来强制转换,可以确保所有索引都相同。

dtest2.index = dtest2.index.astype(str)
st.index = st.index.astype(str)

dtest2.reindex(st.index, fill_value=0)

                   1
index               
1499054400000   0.00
1499140800000   0.00
1499227200000  33.48
1499313600000   0.00
1499400000000  35.71

Or

要么

dtest2.index = dtest2.index.astype(int)
st.index = st.index.astype(int)

dtest2.reindex(st.index, fill_value=0)

                   1
index               
1499054400000   0.00
1499140800000   0.00
1499227200000  33.48
1499313600000   0.00
1499400000000  35.71

#2


1  

use the merge function instead of concat

使用merge函数而不是concat

pd.merge(st,dtest2,how='outer',left_index=True,right_index=True).fillna(0)

#3


1  

You can try following:

您可以尝试以下方法:

result = st.join(dtest2, how='outer').fillna(0)
print(result)

Output:

输出:

                   1
  index               
  1499054400000   0.00
  1499140800000   0.00
  1499227200000  33.48
  1499313600000   0.00
  1499400000000  35.71

Or, left join would also give same result:

或者,左连接也会给出相同的结果:

result = st.join(dtest2, how='left').fillna(0)