Pandas. Convert table structure

Question

How can I convert this table:

iso	pref_name	pref_score
AFG	pref_01	1
AFG	pref_02	2
AFG	pref_03	3
ZMB	pref_01	3
ZMB	pref_02	2
ZMB	pref_03	1
ARM	NaN	NaN
ARM	NaN	NaN
ARM	NaN	NaN

to this table:

iso	pref_01	pref_02	pref_03
AFG	1	2	3
ZMB	3	2	1
ARM	NaN	NaN	NaN

and vice-versa

I've tried to pivot it (How can I pivot a dataframe?) but it wasn't sucessful.

score 1 · Answer 1 · answered Nov 10 '21 at 14:30

You cannot pivot directly as there is no information to pivot the ARM values of iso (all pref_name are NaN).

You can pivot without it (using dropna) and reindex with the unique values of iso afterwards:

(df.dropna()
   .pivot(index='iso', columns='pref_name', values='pref_score')
   .reindex(df['iso'].unique())
)

output:

pref_name  pref_01  pref_02  pref_03
iso                                 
AFG            1.0      2.0      3.0
ZMB            3.0      2.0      1.0
ARM            NaN      NaN      NaN

score 1 · Answer 2 · answered Nov 10 '21 at 14:37

1

I think this is a bug with pandas. I'm using version:

In [334]: pd.__version__
Out[334]: '1.2.3'

As per pivot_table docs:

dropna bool, default True. Do not include columns whose entries are all NaN.

So ideally using dropna=False should solve your issue. But it doesn't.

In [333]: pd.pivot_table(df, 'pref_score', 'iso', 'pref_name', dropna=False)
Out[333]: 
pref_name  pref_01  pref_02  pref_03
iso                                 
AFG            1.0      2.0      3.0
ZMB            3.0      2.0      1.0

It's better to open a bug with pandas.

answered Nov 10 '21 at 14:37

Mayank Porwal

33,470
8
37
58

1

@Umar.H Please read properly. I said, `dropna=False` should include the `NaN` entries, but it doesn't. That's the bug. – Mayank Porwal Nov 10 '21 at 14:49
my fault sorry! – Umar.H Nov 10 '21 at 14:53

score 1 · Answer 3 · answered Nov 10 '21 at 15:04

As a work around, you can convert your iso column to a categorical dtype first and then run pivot_table. By using a categorical dtype with the default argument of pd.pivot_table(..., observed=False) you can essentially force all values of your groupers ("iso" and/or "pref_name" in this case) to be present in the output.

df.astype({"iso": "category"}).pivot_table('pref_score', 'iso', 'pref_name', dropna=False)

pref_name  pref_01  pref_02  pref_03
iso
AFG            1.0      2.0      3.0
ARM            NaN      NaN      NaN
ZMB            3.0      2.0      1.0

score 0 · Accepted Answer · answered Nov 11 '21 at 15:55

Thanks for the answers.

Basically to convert first table to second one I used .pivot:

df2 = df1.pivot(index='iso3',columns='pref_name', values='pref_score').sort_values('iso3')

To convert second table to the first one I used .melt:

df1 = df2.melt(id_vars=['iso'], var_name='pref_name', value_name='pref_score')

It worked just fine

Pandas. Convert table structure

4 Answers4