Need to reshape pandas dataframe

Question

My input dataframe looks like this:

Variable | fiscal_week_id | units 
xxxxxxxxxxx | 201801 | xx_unit_201801 
xxxxxxxxxxx | 201802 | xx_unit_201802 
xxxxxxxxxxx | 201803 | xx_unit_201803 
yyyyyyyyyyy | 201801 | yy_unit_201801 
yyyyyyyyyyy | 201802 | yy_unit_201802 
yyyyyyyyyyy | 201803 | yy_unit_201803

Need output like this:

Variable | 201801 | 201802 | 201803 
xxxxxxxxxxx | xx_unit_201801 | xx_unit_201802 | xx_unit_201803 
yyyyyyyyyyy | yy_unit_201801 | yy_unit_201802 | yy_unit_201803

Any help please, new to pandas and need solution in pandas only. I tried out with set_index, but didn't seem to work out.

jpp · Answer 1 · 2018-08-17T08:15:37.253

0

You can just use pd.pivot as @jezrael suggests:

res = df.pivot(index='Variable', columns='fiscal_week_id', values='units')

print(res)

fiscal_week_id          201801          201802          201803
Variable                                                      
xxxxxxxxxxx     xx_unit_201801  xx_unit_201802  xx_unit_201803
yyyyyyyyyyy     yy_unit_201801  yy_unit_201802  yy_unit_201803

Previous attempt:

You can use pd.pivot_table. When you don't have a "real" aggregation function, you can use 'first' to extract the only item:

res = df.pivot_table(index='Variable', columns='fiscal_week_id',
                     values='units', aggfunc='first')

edited Aug 17 '18 at 08:15

answered Aug 17 '18 at 08:10

jpp

147,904
31
244
302

@jezrael, Never used it myself tbh! Feel free to add a solution. – jpp Aug 17 '18 at 08:11
So check dupe, `pivot` is faster like `pivot_table` – jezrael Aug 17 '18 at 08:11
and aggfunc='first' is really specific reasons, so not sure if good idea here. – jezrael Aug 17 '18 at 08:12
@jezrael, True, I'm reading the pivot vs pivot_table post, you're probably right. – jpp Aug 17 '18 at 08:13
1

Ya, a bit surprise you dont know about `pivot`... ;) – jezrael Aug 17 '18 at 08:14
but i need "Variable" in the output columns with fiscal_week_id. – saswata sen Aug 17 '18 at 08:15
@saswatasen, Not sure what you mean. This matches your desired output. If column headings are your issue, there are *many* duplicates relating to this. – jpp Aug 17 '18 at 08:16
basically i need the dtypes as : Variable object,201801 object, 201802 object, 201803 object. Can I achieve this? @jpp – saswata sen Aug 17 '18 at 08:19
Just use `reset_index` afterwords. – jpp Aug 17 '18 at 08:19

Need to reshape pandas dataframe

1 Answers1