python把dataframe转置,应用规则在Python中转置数据框

I have a dataframe like shown below:

df = pd.DataFrame({'person_id' :[1,1,1,2,2,2,2,2,2],'level_1': ['L1FR','L1Date','L1value','L1FR','L1Date','L1value','L2FR','L2Date','L2value'], 'val3':['Fasting','11/4/2005',1.33,'Random','18/1/2007',4.63,'Fasting','18/1/2017',8.63]})

But I would like to have my output dataframe as shown below:

Please note that concept_id = 123 indicates "Fasting" and 456 represents "Random". I have this information (keys) in another csv file. How do I link all this?

However, I managed to get till here:

d1 = s1[s1['level_1'].str.contains('Date')]

d2 = s1[~s1['level_1'].str.contains('Date')]

d1['g'] = d1.groupby('person_id').cumcount()

d2['g'] = d2.groupby('person_id').cumcount()

d3 = pd.merge(d1,d2,on=["person_id",'g'],how='left').drop(['g','level_1_x','level_1_y'], axis=1)

Please note that what I have shown is for a single csv data file and single hash/key file which contains concept_ids. In real time, I have more than 30 csv files and 1 hash/key file.

So the hash file remains the same, but data file changes frequently.

For example, this file had two concepts "Fasting" and "Random" , which could be replaced as 123,456 by looking at the hash file. Similarly other data file might contain terms like "Sick", "Healthy" which should be replaced as 135,579 etc.

But the data format remains the same. Can you help me achieve this?

** Update screenshot for output **

** Mismatch in group by cum count **

I am expecting all these values should be of same numbers for each group (ex: 1,1,1 or 10,10,10) based on the number of occurrence the value is. Am I right? But no idea why it is different. Moreover my input dataframe has no NA's

解决方案

You're looking for a pivot here. You'll need a little extra work though to specify the columns and index of the pivoted dataframe. Here's one way:

g = df.level_1.str[2:]

ix = g.groupby(g).cumcount()

out = (df.pivot_table(values = 'val3',

columns= g,

index = g.groupby(g).cumcount(),

aggfunc='first'))

out['person_id'] = df.loc[ix.groupby(ix).idxmax(), 'person_id'].values

print(out)

level_1 Date FR value person_id

0 11/4/2005 Fasting 1.33 1

1 18/1/2007 Random 4.63 2

2 18/1/2017 Fasting 8.63 2

For the ConceptID column, you can just create a dictionary to map the values accordingly