python中unstack_在Python中使用Unstack

I am trying to unstack a column in python but it isn't quite doing what I am expecting. My table (called df) looks similar to this:

station_id year Day1 Day2

210018 1916 4 7

1917 3 9

256700 1916 NaN 8

1917 6 9

I want to unstack by year so that all the days from a year per stationn are in one row. The two days from 1916 would then start first followed by the 2 days from 1917 for station 210018 and 256700.

An example would look like this:

station_id 1916 1917

210018 4 7 3 9

256700 NaN 8 6 9

I am trying to use this code:

df2=df.unstack(level='year')

df2.columns=df2.columns.swaplevel(0,1)

df2=df2.sort(axis=1)

I get an error that says AttributeError: 'Series' object has no attribute 'columns'.

Any help would be appreciated.

解决方案

You need to make year an index before you call unstack:

try:

# for Python2

from cStringIO import StringIO

except ImportError:

# for Python3

from io import StringIO

import pandas as pd

text = '''\

station_id year Day1 Day2

210018 1916 4 7

210018 1917 3 9

256700 1916 NaN 8

256700 1917 6 9'''

df = pd.read_table(StringIO(text), sep='\s+')

df = df.set_index(['station_id', 'year'])

df2 = df.unstack(level='year')

df2.columns = df2.columns.swaplevel(0,1)

df2 = df2.sort(axis=1)

print(df2)

yields

year 1916 1917

Day1 Day2 Day1 Day2

station_id

210018 4 7 3 9

256700 NaN 8 6 9

whereas, if year is a column, and not an index, then

df = pd.read_table(StringIO(text), sep='\s+')

df = df.set_index(['station_id'])

df2 = df.unstack(level='year')

df2.columns = df2.columns.swaplevel(0,1)

df2 = df2.sort(axis=1)

leads to AttributeError: 'Series' object has no attribute 'columns'.

The level='year' is ignored in df.unstack(level='year') when df does not have a index level named year (or even, say, blah):

In [102]: df

Out[102]:

year Day1 Day2

station_id

210018 1916 4 7

210018 1917 3 9

256700 1916 NaN 8

256700 1917 6 9

In [103]: df.unstack(level='blah')

Out[103]:

station_id

year 210018 1916

210018 1917

256700 1916

256700 1917

Day1 210018 4

210018 3

256700 NaN

256700 6

Day2 210018 7

210018 9

256700 8

256700 9

dtype: float64

This is the source of the surprising error.