Pivot Tables with Pandas-CodePudding

I have the following data saved as a pandas dataframe

Animal Day age Food  kg  
 1      1   3   17   0.1 
 1      1   3   22   0.7
 1      2   3   17   0.8
 2      2   7   15   0.1

With pivot I get the following:

output = df.pivot(["Animal", "Food"], "Day", "kg") \
           .add_prefix("Day") \
           .reset_index() \
           .rename_axis(None, axis=1)

>>> output
   Animal  Food  Day1  Day2
0       1    17   0.1   0.8
1       1    22   0.7   NaN
2       2    15   NaN   0.1

However I would like to have the age column (and other columns) still included. It could also be possible that for animal x the value age is not always the same, then it doesn't matter which age value is taken.

    Animal  Food Age Day1  Day2
0       1    17   3   0.1   0.8
1       1    22   3   0.7   NaN
2       2    15   7   NaN   0.1

How do I need to change the code above?

CodePudding user response：

Use pivot, add other columns to index:

>>> df.pivot(df.columns[~df.columns.isin(['Day', 'kg'])], 'Day', 'kg') \
      .add_prefix('Day').reset_index().rename_axis(columns=None)

   Animal  age  Food  Day1  Day2
0       1    3    17   0.1   0.8
1       1    3    22   0.7   NaN
2       2    7    15   NaN   0.1

CodePudding user response：

IIUC, what you want is to pivot the weight, but to aggregate the age.

To my knowledge, you need to do both operations separately. One with pivot, the other with groupby (here I used first for the example, but this could be anything), and join:

(df.pivot(index=["Animal", "Food"],
          columns="Day",
          values="kg",
         )
   .add_prefix('Day')
   .join(df.groupby(['Animal', 'Food'])['age'].first())
   .reset_index()
 )

I am adding a non ambiguous example (here the age of Animal 1 changes on Day2).

Input:

   Animal  Day  age  Food   kg
0       1    1    3    17  0.1
1       1    1    3    22  0.7
2       1    2    4    17  0.8
3       2    2    7    15  0.1

output:

   Animal  Food  Day1  Day2  age
0       1    17   0.1   0.8    3
1       1    22   0.7   NaN    3
2       2    15   NaN   0.1    7