python - 合并索引包含一个索引的数据帧(但不相同)

例如，df1有shape(533, 2176)，df2有shapeElkford (5901003) DM 01010，如(743, 12)，df2有shape5901003，如(533, 2176+12)，df1的索引括号中的数字将与df2匹配。形状显示有些指标根本不匹配。现在我想要一个shape的数据集，即在增加列的同时保留匹配的行。
加载数据

import pandas as pd

from tabulate import tabulate

if __name__ == '__main__':
    # Read data
    census_subdivision_profile = pd.read_excel('../data/census_subdivision_profile.xlsx', sheetname='Data',
                                               index_col='Geography', encoding='utf-8').T
    print(tabulate(census_subdivision_profile.head(), headers="keys", index_col='CNSSSBDVSN', tablefmt='psql'))
    print(census_subdivision_profile.shape)

    census_subdivision_count = pd.read_csv('../data/augmented/census_subdivision.csv', encoding='utf-8')
    print(tabulate(census_subdivision_count.head(), headers='keys', tablefmt='psql'))
    print(census_subdivision_count.shape)

使用第一个答案，我有个错误：

Traceback (most recent call last):
  File "/Users/Chu/Documents/dssg/ongoing/economy_vs_tourism.py", line 26, in <module>
    census_subdivision_profile.index = census_subdivision_profile.index.map(extract_id)
  File "/anaconda/lib/python2.7/site-packages/pandas/core/indexes/base.py", line 2727, in map
    mapped_values = self._arrmap(self.values, mapper)
  File "pandas/_libs/algos_common_helper.pxi", line 1212, in pandas._libs.algos.arrmap_object (pandas/_libs/algos.c:31954)
  File "/Users/Chu/Documents/dssg/ongoing/economy_vs_tourism.py", line 10, in extract_id
    return int(m.group(0)[1:-1])
ValueError: invalid literal for int() with base 10: 'Part 1) (5917054'

只是因为

Index([u'Canada (01)   20000',
       u'British Columbia / Colombie-Britannique (59)   21010',
       u'East Kootenay (5901)   01010', u'Elkford (5901003) DM 01010',
       u'Sparwood (5901006) DM 01010', u'Fernie (5901012) CY 01010',
       u'East Kootenay A (5901017) RDA 02020',
       u'East Kootenay B (5901019) RDA 01020', u'Cranbrook (5901022) CY 01011',
       u'Kimberley (5901028) CY 01010',

另一个是

Int64Index([5931813, 5941833, 5949832, 5919012, 5923033, 5924836, 5941016,
            5955040, 5923809, 5941801,

数据框太大了抱歉我不能放在这里

最佳答案

文件1.csv：

,col_1,col_2
5901001,a,-1
5901002,b,-2
5901003,c,-3
5901004,d,-4
5901005,e,-5
5901006,f,-6
5901007,g,-7
5901008,h,-8
5901009,i,-9
5901010,k,-10

这里df1.shape = (10, 2)。
文件2.csv：

,col_3
Elkford (Part 1) (5901003) DM 01010,1
Ahia (5901004) DM 01010,2
Canada (01)   20000,4
Fork (5901005) DM 01010,3
England (34)   20000,4

这里df2.shape = (3, 1)。
运行此脚本：

import re

import pandas as pd
import numpy as np


def extract_id(s):
    m = re.search('\((\d{7})\)', s)
    if m:
        return int(m.group(1))


df1 = pd.read_csv('file1.csv', index_col=0)
df2 = pd.read_csv('file2.csv', index_col=0)


indexes = df2.index.map(extract_id)
mask = ~np.isnan(indexes)
# filter incorrect row (without id)
df2 = df2[mask]
# convert index
df2.index = indexes[mask]

df = pd.concat([df1, df2], axis=1)

print(df)

输出：

        col_1  col_2  col_3
5901001     a     -1    NaN
5901002     b     -2    NaN
5901003     c     -3    1.0
5901004     d     -4    2.0
5901005     e     -5    3.0
5901006     f     -6    NaN
5901007     g     -7    NaN
5901008     h     -8    NaN
5901009     i     -9    NaN
5901010     k    -10    NaN

此处df.shape = (10, 2 + 1)

关于python - 合并索引包含一个索引的数据帧(但不相同)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44754881/

Shape

python - 合并索引包含一个索引的数据帧(但不相同)