Raise a ValueError when convert one column from vertical to horizontal in Pandas

user_8410477 0
Viewed 121

Given a dataframe as follows:

  city          id     date  price
0   sh  3910060461  2008-04  19459
1   sh  1210000631  2008-05  16727
2   bj  1210000770  2008-05  12960
3   bj  1210000829  2008-05  14445
4   bj  1210001004  2008-05  16213

How could I convert date columns horizontally and map price into it based on city and id?

The expected result is like this:

  city          id  2008-04  2008-05
0   sh  3910060461  19459.0      NaN
1   sh  1210000631      NaN  16727.0
2   bj  1210000770      NaN  12960.0
3   bj  1210000829      NaN  14445.0
4   bj  1210001004      NaN  16213.0

I try df.pivot_table(columns = 'date', index=['city', 'id'], values = 'price', fill_value ='').reset_index(), but it raises an error:

ValueError: a CategoricalDtype must be passed to perform an update, got CategoricalDtype(categories=[0], ordered=True)
pandas pivot-table dataframe python-3.x
2 Answers

You can group by all three columns of interest, then convert the dates index level to column names with unstack. Not a perfect solution, but it works.

transformed = df.groupby(['city','id','date']).first().unstack().reset_index()
#     city          id    price         
#date                   2008-04  2008-05
#0      bj  1210000770      NaN  12960.0
#1      bj  1210000829      NaN  14445.0
#2      bj  1210001004      NaN  16213.0
#3      sh  1210000631      NaN  16727.0
#4      sh  3910060461  19459.0      NaN

You can flatten the column names if you want:

transformed.columns = [(l1 or l0) for l0,l1 in transformed]
#  city          id  2008-04  2008-05
#0   bj  1210000770      NaN  12960.0
#1   bj  1210000829      NaN  14445.0
#2   bj  1210001004      NaN  16213.0
#3   sh  1210000631      NaN  16727.0
#4   sh  3910060461  19459.0      NaN
user_44929320

Groupby three columns except Price.Hold Price in list and unstack() date. Drop level=0 on columns.

print(df.groupby(['city', 'id', 'date'])['price'].apply(lambda x: pd.DataFrame(x.tolist())).unstack('date').droplevel(0, axis=1))
                            
date               2008-04  2008-05
city id                            
bj   1210000770 0      NaN  12960.0
     1210000829 0      NaN  14445.0
     1210001004 0      NaN  16213.0
sh   1210000631 0      NaN  16727.0
     3910060461 0  19459.0      NaN
user_89869750
Related