Trouble pivoting in pandas (spread in R)

pandas, pivot, python-3.x

Solution

Your last try (with `unstack`) works fine for me, I'm not sure why it gave you a problem. FWIW, I think it's more readable to use the index names rather than levels, so I did it like this:

>>> df.set_index(['dt','site_id','eu']).unstack('eu')

            kw    
eu         FGE WSH
dt site_id        
1  a         8   5
   b         3   7
   c         1   5
2  a         2   3
   b         5   7
   c         2   5

But again, your way looks fine to me and is pretty much the same as what @piRSquared did (except their answer adds some more code to get rid of the multi-index).

I think the problem with `pivot` is that you can only pass a single variable, not a list? Anyway, this works for me:

>>> df.set_index(['dt','site_id']).pivot(columns='eu')

For `pivot_table`, the main issue is that 'kw' is an object/character and `pivot_table` will attempt to aggregate with `numpy.mean` by default. You probably got the error message: "DataError: No numeric types to aggregate".

But there are a couple of workarounds. First, you could just convert to a numeric type and then use your same pivot_table command

>>> df['kw'] = df['kw'].astype(int)
>>> df.pivot_table(index = ['dt','site_id'], values = 'kw', columns = 'eu')

Alternatively you could change the aggregation function:

>>> df.pivot_table(index = ['dt','site_id'], values = 'kw', columns = 'eu', 
                   aggfunc=sum )

That's using the fact that strings can be summed (concatentated) even though you can't take a mean of them. Really, you can use most functions here (including lambdas) that operate on strings.

Note, however, that `pivot_table's` `aggfunc` requires some sort of reduction operation here even though you only have a single value per cell, so there actually isn't anything to reduce! But there is a check in the code that requires a reduction operation, so you have to do one.

Problem

I'm having some issues with the pd.pivot() or pivot_table() functions in pandas. I have this: ``` df = pd.DataFrame({'site_id': {0: 'a', 1: 'a', 2: 'b', 3: 'b', 4: 'c', 5: 'c',6: 'a', 7: 'a', 8: 'b', 9: 'b', 10: 'c', 11: 'c'}, 'dt': {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1,6: 2, 7: 2, 8: 2, 9: 2, 10: 2, 11: 2}, 'eu': {0: 'FGE', 1: 'WSH', 2: 'FGE', 3: 'WSH', 4: 'FGE', 5: 'WSH',6: 'FGE', 7: 'WSH', 8: 'FGE', 9: 'WSH', 10: 'FGE', 11: 'WSH'}, 'kw': {0: '8', 1: '5', 2: '3', 3: '7', 4: '1', 5: '5',6: '2', 7: '3', 8: '5', 9: '7', 10: '2', 11: '5'}}) df Out[140]: dt eu kw site_id 0 1 FGE 8 a 1 1 WSH 5 a 2 1 FGE 3 b 3 1 WSH 7 b 4 1 FGE 1 c 5 1 WSH 5 c 6 2 FGE 2 a 7 2 WSH 3 a 8 2 FGE 5 b 9 2 WSH 7 b 10 2 FGE 2 c 11 2 WSH 5 c ``` I want this: ``` dt site_id FGE WSH 1 a 8 5 1 b 3 7 1 c 1 5 2 a 2 3 2 b 5 7 2 c 2 5 ``` I've tried everything! ``` df.pivot_table(index = ['site_id','dt'], values = 'kw', columns = 'eu') ``` or ``` df.pivot(index = ['site_id','dt'], values = 'kw', columns = 'eu') ``` should have worked. I also tried unstack(): ``` df.set_index(['dt','site_id','eu']).unstack(level = -1) ```

Original source