Combine data containing indexes that contain different (but not the same)
For example, df1 is shaped (533, 2176)
, indices such as Elkford (5901003) DM 01010
df2 are shaped (743, 12)
, indices such as 5901003
; the number in the df1 indices bracket will match the number in df2. And as the form showed some indices do not match at all. And now I want a dataset that has a shape (533, 2176+12)
i.e. Keeping matching rows while growing columns.
Loading data
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)
Using the first answer, I got the error:
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'
Just because
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',
and the other is
Int64Index([5931813, 5941833, 5949832, 5919012, 5923033, 5924836, 5941016, 5955040, 5923809, 5941801,
The dataframe is too big, sorry I can't get it here.
source to share
file1.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
Here df1.shape = (10, 2)
.
file2.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
Here df2.shape = (3, 1)
.
Run this script:
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)
Output:
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
Here df.shape = (10, 2 + 1)
source to share