CODEWITHSUNDEEP
pythonpandas
Nickpick
Nickpick

Reputation: 6587

Pandas restacking repeated values to columns

The below DataFrame needs to be restacked, so that I have all values for each region on one line. In the below example the new df would only have 3 lines, one for each region. The corresponding values would then expand along multiple columns.

The regions may vary, and there may be more than 3. Any suggestions are appreciated. 

>>> a
Out[26]: 
Area value 
0 EUROPE 47
1 ASIA 51
2 AMERICAS 37
3 EUROPE 39
4 ASIA 22
5 AMERICAS 24

Desired output:

Europe 47 39
Asia 51 22
Americas 37 24

The values should be spread among different columns

Upvotes: 1

Views: 720

Answers (1)

EdChum
EdChum

Reputation: 393963

You could groupby on 'Area' and apply list:

In [75]:
df.groupby('Area')['value'].apply(list).reset_index()

Out[75]:
       Area     value
0  AMERICAS  [37, 24]
1      ASIA  [51, 22]
2    EUROPE  [47, 39]

This will handle a variable number of values

If you want to split the values out you can call apply and pass pd.Series ctor:

In [90]:
df1 = df.groupby('Area')['value'].apply(lambda x: list(x)).reset_index()
df1[['val1', 'val2']] = df1['value'].apply(pd.Series)
df1

Out[90]:
       Area     value  val1  val2
0  AMERICAS  [37, 24]    37    24
1      ASIA  [51, 22]    51    22
2    EUROPE  [47, 39]    47    39

EDIT

For a variable number of columns you can't assign upfront if you don't know what the max number of values will be but you can still use the above:

In [94]:
import io
import pandas as pd

t="""index Area  value
0    EUROPE     47
1      ASIA     51
2  AMERICAS     37
3    EUROPE     39
4      ASIA     22
5  AMERICAS     24
5  AMERICAS     50"""
df = pd.read_csv(io.StringIO(t), sep='\s+')
df

Out[94]:
   index      Area  value
0      0    EUROPE     47
1      1      ASIA     51
2      2  AMERICAS     37
3      3    EUROPE     39
4      4      ASIA     22
5      5  AMERICAS     24
6      5  AMERICAS     50

In [99]:
df1 = df.groupby('Area')['value'].apply(list).reset_index()
df1

Out[99]:
       Area         value
0  AMERICAS  [37, 24, 50]
1      ASIA      [51, 22]
2    EUROPE      [47, 39]

In [102]:
df1 = pd.concat([df1, df1['value'].apply(pd.Series).fillna(0)], axis=1)
df1

Out[102]:
       Area         value   0   1   2
0  AMERICAS  [37, 24, 50]  37  24  50
1      ASIA      [51, 22]  51  22   0
2    EUROPE      [47, 39]  47  39   0

Upvotes: 3

Related Questions