python最简单索引,使用可能的最简单索引在python pandas中转置一列

I have the following data (data_current):

import pandas as pd

import numpy as np

data_current=pd.DataFrame({'medicine':['green tea','fried tomatoes','meditation','meditation'],'disease':['acne','hypertension', 'cancer','lupus']})

data_current

What I would like to do is to transpose one of the columns, so that instead of having multiple rows with same medicine and different diseases I have one row for each medicine with several columns for diseases. It is also important to keep index as simple as possible, i.e. 0,1,2... i.e. I don't want to assign 'medicines' as index column because I will merge it on some other key.

So, I need to get data_needed

data_needed=pd.DataFrame({'medicine':['green tea','fried tomatoes','meditation'],'disease_1':['acne','hypertension','cancer'], 'disease_2':['np.nan','np.nan','lupus']})

data_needed

解决方案

Here's one to achieve the output

Firstly, groupby on medicine and get the disease as list

In [368]: md = (data_current.groupby('medicine')

.apply(lambda x: x['disease'].tolist())

.reset_index())

In [369]: md

Out[369]:

medicine 0

0 fried tomatoes [hypertension]

1 green tea [acne]

2 meditation [cancer, lupus]

Then convert the lists in column to separate columns

In [370]: dval = pd.DataFrame(md[0].tolist(), )

In [371]: dval

Out[371]:

0 1

0 hypertension None

1 acne None

2 cancer lupus

Now, you can concat -- md with dval

In [372]: md = md.drop(0, axis=1)

In [373]: data_final = pd.concat([md, dval], axis=1)

And, rename the columns as you want.

In [374]: data_final.columns = ['medicine', 'disease_1', 'disease_2']

In [375]: data_final

Out[375]:

medicine disease_1 disease_2

0 fried tomatoes hypertension None

1 green tea acne None

2 meditation cancer lupus