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