3 Data Frames And 3 Rules In Operation To Insert Data Into Another Dataframe - No Common Columns - Big Data
I have 3 different data-frames which can be generated using the code given below data_file= pd.DataFrame({'person_id':[1,2,3],'gender': ['Male','Female','Not disclosed'],'ethnicity
Solution 1:
After chat and remove duplicates data is possible use:
s = hash_file.set_index('VARIABLE')['concept_id']
df1 = map_file.melt().dropna(subset=['value'])
df1[['valueid','valuestring']] = df1.pop('value').str.extract('(\d+)\.(.+)')
df1['valuestring'] = df1['valuestring'].str.strip()
columns = ['studyid','obsid','valuenum','valuestring','valueid']
obs = data_file.melt('studyid', value_name='valuestring').sort_values('studyid')
#merge by 2 columns variable, valuestring
obs = (obs.merge(df1, on=['variable','valuestring'], how='left')
.rename(columns={'valueid':'valuenum'}))
obs['obsid'] = obs['variable'].map(s)
obs['valueid'] = obs['valuestring'].map(s)
#map by only one column variable
s1 = df1.drop_duplicates('variable').set_index('variable')['valueid']
obs['valuenum_new'] = obs['variable'].map(s1)
obs = obs.reindex(columns + ['valuenum_new'], axis=1)
print (obs)
#compare number of non missing rows
print (len(obs.dropna(subset=['valuenum'])))
print (len(obs.dropna(subset=['valuenum_new'])))
Solution 2:
Here is an alterternative approach using DataFrame.melt
and Series.map
:
# Solution for pandas V 0.24.0 +
columns = ['person_id','obsid','valuenum','valuestring','valueid']
# Create map Series
hash_map = hash_file.set_index('keys')['values']
value_map = map_file.stack().str.split('\.\s?', expand=True).set_index(1, append=True).droplevel(0)[0]
# Melt and add mapped columns
obs = data_file.melt(id_vars=['person_id'], value_name='valuestring')
obs['obsid'] = obs.variable.map(hash_map)
obs['valueid'] = obs.valuestring.map(hash_map).astype('Int64')
obs['valuenum'] = obs[['variable', 'valuestring']].apply(tuple, axis=1).map(value_map)
# Reindex and sort for desired output
obs.reindex(columns=columns).sort_values('person_id')
[out]
person_id obsid valuenum valuestring valueid
0 1 21 1 Male 127
3 1 22 1 Chinese 141
6 1 23 1 Single 129
9 1 24 1 Yes 125
1 2 21 2 Female 128
4 2 22 2 Indian 142
7 2 23 2 Married 130
10 2 24 2 No 126
2 3 21 3 Not disclosed NaN
5 3 22 3 European 0
8 3 23 3 Widowed 131
11 3 24 2 No 126
Post a Comment for "3 Data Frames And 3 Rules In Operation To Insert Data Into Another Dataframe - No Common Columns - Big Data"