CODEWITHSUNDEEP
pythonpandas
Yona
Yona

Reputation: 561

how to pivot a dataframe into another dataframe

Hopefully this question makes sense. I haven't been able to find something similar online.

I have a bunch of data in the following format:

Name  Field  Value
A     Field1 Value1
A     Field2 Value2
A     Field3 Value3
B     Field1 Value1
B     Field2 Value2
...

I would like to convert it into a DataFrame such as:

Name  Field1  Field2  Field3
A     Value1  Value2  Value3
B     Value1  Value2  Value3

If I do: df.pivot('Name', 'Field') I get a pivot table with the data in the "shape" I want but as a pivot object and I would simply prefer to have a dataframe. On my mind, pivots are there to deal with numbers, what I am doing is just converting a column into variables and I am sure there may be a much simpler way of doing this.

As some people ask for data:

In[224]: df = pd.DataFrame([['A','Field1', 'Value1'],['A', 'Field2', 'Value2'],['A', 'Field3', 'Value3'],['B','Field1', 'Value1'],['B', 'Field2', 'Value2'],['B', 'Field3', 'Value3'],['C','Field1', 'Value1'],['C', 'Field2', 'Value2'],['C', 'Field3', 'Value3']], columns=['Name', 'Field', 'Value'])
In[225]: df
Out[225]: 
  Name   Field   Value
0    A  Field1  Value1
1    A  Field2  Value2
2    A  Field3  Value3
3    B  Field1  Value1
4    B  Field2  Value2
5    B  Field3  Value3
6    C  Field1  Value1
7    C  Field2  Value2
8    C  Field3  Value3
In[226]: pv = df.pivot('Name', 'Field')
In[227]: pv
Out[227]: 
        Value                
Field  Field1  Field2  Field3
Name                         
A      Value1  Value2  Value3
B      Value1  Value2  Value3
C      Value1  Value2  Value3

What I would like is for pv to actually be a DataFrame such as:

Name   Field1  Field2  Field3                        
A      Value1  Value2  Value3
B      Value1  Value2  Value3
C      Value1  Value2  Value3

As always, thanks everyone for your help!

Upvotes: 1

Views: 358

Answers (1)

root
root

Reputation: 33803

The data itself in the format you want, it's just the index and columns that aren't in your desired format. So, all you need to do is edit the columns and reset the index:

pv = df.pivot('Name', 'Field')
pv.columns = [c[1] for c in pv.columns]
pv.reset_index(inplace=True)

Upvotes: 1

Related Questions