应用规则以在Python中转置数据框 [英] Apply rules to transpose dataframe in 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:

请注意，concept_id = 123表示快速"，而456表示随机".我在另一个csv文件中有此信息(密钥).我该如何链接所有这些?

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?

但是，我设法做到了:

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)

请注意，我所显示的是单个csv数据文件和单个包含concept_id的哈希/密钥文件.实时，我有30多个csv文件和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.

例如，该文件具有两个概念"Fasting"和"Random"，可以通过查看哈希文件将其替换为123,456.同样，其他数据文件可能包含病态"，健康"等术语，应替换为135,579等.

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 **

我希望所有这些值对于每个组应该是相同的数字(例如:1、1、1或10、10、10)，具体取决于该值出现的次数.我对吗?但是不知道为什么会有所不同.而且我的输入数据框没有NA

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

---

对于ConceptID列，您只需创建一个字典即可相应地map值

---

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

这篇关于应用规则以在Python中转置数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！