在熊猫中合并两个没有列名的数据(熊猫是新的)

Question

短解释

如果数据中有重复的列名，请确保在读取文件时重命名一列。

如果您的数据中有NaN等，请删除这些。

然后使用下面正确的答案进行合并。

可能是个很简单的问题。

我使用pandas.read_csv()阅读了两个数据集。

我的数据在两个独立的csv中。

使用以下代码：

        import mibian
        import pandas as pd


        underlying = pd.read_csv("txt1.csv", names=['dt1','price']);

        options = pd.read_csv("txt2.txt", names=['dt2','ticker','maturity','strike','cP','px','strike','yield','rF','T','rlzd10']);

        merged = underlying.merge(options, left_on='dt1', right_on='dt2');

我的两个数据头看起来如下：

>>> underlying.head();

          0         1
0  20040326  3.579987
1  20040329  3.690494
2  20040330  3.755247
3  20040331  3.719373
4  20040401  3.728671

和

>>> options.head();

         0     1         2     3     4      5     6   7      8         9                10

0  20130628  SVXY  20130817  32.5  call  39.22  32.5   0  0.005  0.136986   0.411224

因此，我在这两个数据集上的列0是我想要合并的键，我希望保留两个结果集中的所有数据。

我该怎么做呢？我在网上找到的所有例子都需要键，但我的结果中没有这一点。

但是，在联接时，我得到以下错误：

                            Traceback (most recent call last):
                              File "<stdin>", line 1, in <module>
                              File "/Applications/Spyder.app/Contents/Resources/lib/python2.7/spyderlib/widgets/externalshell/sitecustomize.py", line 540, in runfile
                                execfile(filename, namespace)
                              File "/Users/jasonmellone/.spyder2/.temp.py", line 12, in <module>
                                merged = underlying.merge(options, left_on='dt1', right_on='dt2',how='outer');
                              File "/Library/Python/2.7/site-packages/pandas-0.13.0-py2.7-macosx-10.9-intel.egg/pandas/core/frame.py", line 3723, in merge
                                suffixes=suffixes, copy=copy)
                              File "/Library/Python/2.7/site-packages/pandas-0.13.0-py2.7-macosx-10.9-intel.egg/pandas/tools/merge.py", line 40, in merge
                                return op.get_result()
                              File "/Library/Python/2.7/site-packages/pandas-0.13.0-py2.7-macosx-10.9-intel.egg/pandas/tools/merge.py", line 197, in get_result
                                result_data = join_op.get_result()
                              File "/Library/Python/2.7/site-packages/pandas-0.13.0-py2.7-macosx-10.9-intel.egg/pandas/tools/merge.py", line 722, in get_result
                                return BlockManager(result_blocks, self.result_axes)
                              File "/Library/Python/2.7/site-packages/pandas-0.13.0-py2.7-macosx-10.9-intel.egg/pandas/core/internals.py", line 1954, in __init__
                                self._set_ref_locs(do_refs=True)
                              File "/Library/Python/2.7/site-packages/pandas-0.13.0-py2.7-macosx-10.9-intel.egg/pandas/core/internals.py", line 2091, in _set_ref_locs
                                'have _ref_locs set' % (block, labels))
                            AssertionError: Cannot create BlockManager._ref_locs because block [IntBlock: [dt1], 1 x 372145, dtype: int64] with duplicate items [Index([u'dt1', u'price', u'dt2', u'ticker', u'maturity', u'strike', u'cP', u'px', u'strike', u'yield', u'rF', u'T', u'rlzd10'], dtype='object')] does not have _ref_locs set

我搜索了我的数据集，没有副本。

谢谢!

Answer 1

您仍然应该能够在列上合并：

merged = underlying.merge(options, left_on='0', right_on='0')

这将执行内部合并，因此只有两个数据集的交集，即列0中的值在这两个数据集中都存在，如果您想要所有值，那么指定outer。

merged = underlying.merge(options, left_on='0', right_on='0', how='outer')

In [10]:  

merged = underlying.merge(options, left_on='0', right_on='0', how='outer')

merged

Out[10]:

          0       1_x   1_y         2     3     4      5     6   7      8  \
0  20040326  3.579987   NaN       NaN   NaN   NaN    NaN   NaN NaN    NaN   
1  20040329  3.690494   NaN       NaN   NaN   NaN    NaN   NaN NaN    NaN   
2  20040330  3.755247   NaN       NaN   NaN   NaN    NaN   NaN NaN    NaN   
3  20040331  3.719373   NaN       NaN   NaN   NaN    NaN   NaN NaN    NaN   
4  20040401  3.728671   NaN       NaN   NaN   NaN    NaN   NaN NaN    NaN   
5  20130628       NaN  SVXY  20130817  32.5  call  39.22  32.5   0  0.005   

          9        10  
0       NaN       NaN  
1       NaN       NaN  
2       NaN       NaN  
3       NaN       NaN  
4       NaN       NaN  
5  0.136986  0.411224  

[6 rows x 12 columns]

您必须重命名或移动上面与1_x和1_y冲突的列。

最好把这些列重新命名为一些有意义的东西。读取csv时，可以传递列名列表：

df = pd.read_csv('data.csv', names=['Id', 'Price'])

Answer 2

类似的问题让我想到了这条线索。我最后出了一个关键错误。修正是将单引号从left_on='0'移到left_on=0。

merged = underlying.merge(options, left_on='0', right_on='0')
merged = underlying.merge(options, left_on=0, right_on=0)

Answer 3

如果您想使用相同的列进行合并(在您的情况下是这样的)，您可以简单地使用on=0，其中0表示两个数据格式中的第一列。

import pandas as pd
merged = underlying.merge(options, on=0, how='outer')
# or
merged = pd.merge(underlying, options, on=0, how='outer')

如果索引列在两种数据格式中都不同，那么可以使用left_on和right_on项。

# here 0 is the index column for df1 and 2 is the index column for df2
pd.merge(df1, df2, left_on=0, right_on=2, how='outer')