1

struggling with this one using a pandas dataframe, I have data as follows: 

Animal     Nationality   Type     Value
Leopard    India         Age      4.0
Elephant   India         Age      12.0
Leopard    India         Speed    30.0
Elephant   India         Speed    3.0
Leopard    China         Age      5.0
Leopard    China         Speed    27.0

I want to effectively merge the data into a dataframe as follows:

Animal     Nationality    Age      Speed
Leopard    India          4.0      30.0
Elephant   India          12.0     3.0 
Leopard    China          5.0      27.0

I have looked up semi-similar problems, but have had no joy, I think due to the nature of changing the size of the dataframe, any help would be much appreciated.

Thanks in advance.

ColmMeMaybe
  • 11
  • 2

1 Answers1

3

Use DataFrame.pivot_table or DataFrame.pivot:

new_df=df.pivot_table(index=['Animal','Nationality'],columns='Type',values='Value')
print(new_df)


Type                   Age  Speed
Animal   Nationality             
Elephant India        12.0    3.0
Leopard  China         5.0   27.0
         India         4.0   30.0

new_df.reset_index(inplace=True)
print(new_df)


Type    Animal Nationality   Age  Speed
0     Elephant       India  12.0    3.0
1      Leopard       China   5.0   27.0
2      Leopard       India   4.0   30.0
ansev
  • 30,322
  • 5
  • 17
  • 31
  • Hi, Thank you for the response, I also realised I need to include multi-indexing, which is an issue, and have updated the question, would you be able to look at this and update your response? Much appreciated. – ColmMeMaybe Oct 11 '19 at 01:20
  • I update the solution. Please accept it if it is correct – ansev Oct 11 '19 at 10:04
  • Unfortunately this hasn't resolved it for me. I have tried this using pivot_table and got the error: "No numeric types to aggregate" - I guess because my values are strings. I also tried using pivot in python and got "Length of values passed is 6, index implies 2" – ColmMeMaybe Oct 11 '19 at 12:19
  • `new_df=df.pivot_table(index=['Animal','Nationality'],columns='Type',values='Value', aggfunc=''.join)` – ansev Aug 07 '20 at 11:50