How to reshape data in Pandas DataFrame

Question

I am working on a project where i need to reshape the data in particular Format. I have transformed successfully in Excel but how we can transformed this Data with pandas because in Excel we have the limitation of rows.

Territory ID    Patient Id  Doctor Id   Clinic Count    Attribute          Value
43                172        6021          Clinic1      Service Date      06/22/2017 
43                172        6021          Clinic1      Product               A
43                172        6021          Clinic1      Qty                   1
43                172        6021          Clinic1      Amount                80.63
43                172        6021          Clinic1      Age                   1
43                172        6021          Clinic1      Days Last Clinic      0
43                187        5658          Clinic2      Service Date       06/30/2017 
43                187        5658          Clinic2      Product               B
43                187        5658          Clinic2      Qty                   2
43                187        5658          Clinic2      Amount                52.48
43                187        5658          Clinic2      Age                   9y1m
43                187        5658          Clinic2      Days Last Clinic      23

This has to be reshape in this format:

TerritoryID PatientId   DoctorId    Clinic_Count    Service_Date    Product    Qty    Amount   Age    Days Last Clinic   
    
43            172        6021          Clinic1      06/22/2017       A           1     80.63    1          0
43            187        5658          Clinic2      06/30/2017       B           2     52.48    9y1m       23

Try this `pd.pivot_table(df,index = ['Territory ID', 'Patient Id', 'Doctor Id', 'Clinic Count'], values='Value' , columns=['Attribute'],aggfunc='first').reset_index()` — XXavier, Jul 16 '20 at 14:57
@XXavier It is giving syntax error. Can you please try with your own — Muhammad Ahmed, Jul 16 '20 at 15:27
Can you post the error here. The code above works fine for me — XXavier, Jul 16 '20 at 15:35

score 0 · Answer 1 · 2020-07-16T15:50:21.333

0

Try this:

import pandas as pd
df = pd.DataFrame({...}) # Your DataFrame

New_Data = {x:[] for x in list(df.columns)+list(df['Attribute'])}

for x in list(df.columns):
    for j, i in enumerate(list(df[x])):
        if not list(df['Clinic Count'])[j] in New_Data['Clinic Count']:
            New_Data[x].append(i)

New_Data.pop('Attribute')
New_Data.pop('Value')

for x, i in zip(list(df['Attribute']), list(df['Value'])):
    New_Data[x].append(i)

edited Jul 16 '20 at 15:50

answered Jul 16 '20 at 14:39

I just share sample data of 2 clinic Count but in reality it is in thousands. So this will not be a good approach of manually giving clinic Count in pd.DataFrame. Hope this make sense. – Muhammad Ahmed Jul 16 '20 at 15:31
It can do thousands, I just forgot to remove the test ```DataFrame```. – Jul 16 '20 at 15:51
Can you click the check because it worked? – Jul 16 '20 at 19:15
There it is getting an error – Muhammad Ahmed Jul 21 '20 at 06:44

How to reshape data in Pandas DataFrame

1 Answers1