Python Pandas Reshape Data Frame

Question

It seems to be very basic knowledge, but I got stuck despite having some theoretical background in data processing (via other software). Worth to mention I'm new to python and pandas library.

So. I've got a data frame: Screenshot

My task is to put values of 'Series Name' column as separate columns (transform from long to wide). I've spent ages trying different methods, but got only errors.

For example:

mydata = mydata.pivot(index=['Country', 'Year'], columns='Series Name', values='Value')

And I got an error:

... a lot of text... ValueError: Length of passed values is 2487175, index implies 2

Could anybody guide me through that process please? Thanks.

It's for the code 'mydata = mydata.pivot(index=['Country', 'Year'], columns='Series Name', values='Value')' Error message:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-4-8169d6d374c7> in <module>
----> 1 mydata = mydata.pivot(index=['Country', 'Year'], columns='Series Name', values='Value')

~/anaconda3_501/lib/python3.6/site-packages/pandas/core/frame.py in pivot(self, index, columns, values)
   5192         """
   5193         from pandas.core.reshape.reshape import pivot
-> 5194         return pivot(self, index=index, columns=columns, values=values)
   5195 
   5196     _shared_docs['pivot_table'] = """

~/anaconda3_501/lib/python3.6/site-packages/pandas/core/reshape/reshape.py in pivot(self, index, columns, values)
    412         else:
    413             indexed = self._constructor_sliced(self[values].values,
--> 414                                                index=index)
    415     return indexed.unstack(columns)
    416 

~/anaconda3_501/lib/python3.6/site-packages/pandas/core/series.py in __init__(self, data, index, dtype, name, copy, fastpath)
    260                             'Length of passed values is {val}, '
    261                             'index implies {ind}'
--> 262                             .format(val=len(data), ind=len(index)))
    263                 except TypeError:
    264                     pass

ValueError: Length of passed values is 2487175, index implies 2

Georgina Skibinski · Accepted Answer · 2019-12-30 17:17:19Z

1

Try maybe:

mydata = mydata.pivot_table(index=['Country', 'Year'], columns='Series Name', values='Value', aggfunc='sum')

(If you want to sum your Value) it seems that you need to somehow aggregate your data explicitly. Although would be good, if you would share full error message.

I managed to reproduce your error. Like I said- you need to provide aggregating function:

import pandas as pd

df=pd.DataFrame({"a": list("xyzpqr"), "b": list("abbbaa"), "c": [4,3,6,2,7,5], "d": list("pqqppp")})

df2=df.pivot(index=["b", "d"], columns="a", values="c")
#ValueError: Length of passed values is 6, index implies 2

df2=df.pivot_table(index=["b", "d"], columns="a", values="c", aggfunc=set)
#works fine - you need aggregation function e.g. list/set to collect all/unique values or e.g. sum/max to do some numeric operation

edited Dec 30, 2019 at 17:17

answered Dec 30, 2019 at 14:38

Georgina Skibinski

13.5k2 gold badges16 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leo Over a year ago

Actually I do not want any aggregations on data. There are unique values per Country / Year and I want to keep them as they are. I've tried your code and the kernel of the notebook went busy for too long, so I've interrupted it... Could it be something like wrong data type or anything? Later on I'll try your code with smaller data set...

Georgina Skibinski Over a year ago

@Leo if you are sure, you will get just a unique value in aggregation (i.e. you want to get rid of "the brackets") just do: mydata = mydata.pivot_table(index=['Country', 'Year'], columns='Series Name', values='Value', aggfunc='first')

Leo · Accepted Answer · 2019-12-31 00:25:02Z

0

Nearly there. The resulting table is

How is it possible to to put 'Country' and 'Year' to the same level as other column names to be able to export it normally to excel? If I export like it is now 'Country' and 'Year' not included in the table.

answered Dec 31, 2019 at 0:25

Leo

376 bronze badges

Collectives™ on Stack Overflow

Python Pandas Reshape Data Frame

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related