0

I need to reconfigure a df that entries based on Location and year but with multiple entries. I need them all grouped by Location and year so I can process it.

Location Year Type Value
1 1 A 1.3
1 1 B 2.1
1 1 C 3.6
2 1 A 1.7
2 1 B 2.8
2 1 C 0.9
...

into

Location Year ValueA ValueB ValueC
1 1 1.3 2.1 3.6
2 1 1.7 2.8 0.9

Joseph Fife
  • 3
  • 2
  • 1. Here is how to make a good post JosephFife (your post is not a good one) https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples 2. Here is a good StackOverflow question on groupby that should help you: https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum – David Erickson Mar 31 '20 at 23:01
  • Should the year in the second row of the second table be 2, not 1? – Oliver.R Mar 31 '20 at 23:01
  • Thanks, first time posting, I think I clarified it now – Joseph Fife Mar 31 '20 at 23:06
  • Thanks @JosephFife for making the question much better. I provided an answer. Hope it helps. here is another good post about the topic: https://stackoverflow.com/questions/30960338/pandas-difference-between-pivot-and-pivot-table-why-is-only-pivot-table-workin – David Erickson Mar 31 '20 at 23:24

1 Answers1

0

I would personally use a pivot_table as that's what I'm more comfortable with, but I think there are multiple ways to do this. Here is another useful post on pivot tables vs. groupby. Hope it helps Pivot Tables or Group By for Pandas?

Input (copy data before using pd.read_clipboard():

 Location Year Type Value
 1 1 A 1.3
 1 1 B 2.1
 1 1 C 3.6
 2 2 A 1.7
 2 2 B 2.8
 2 2 C 0.9

Code:

import pandas as pd
df=pd.read_clipboard()
df=df.pivot_table(index=['Location', 'Year'], columns='Type', values='Value').reset_index()
df

Output:

Type Location Year A    B   C
0    1        1    1.3  2.1 3.6
1    2        2    1.7  2.8 0.9
David Erickson
  • 16,433
  • 2
  • 19
  • 35