CODEWITHSUNDEEP
pythonpandasnumpy
dshajjad
dshajjad

Reputation: 9

Is there a way to groupby multiple columns under a single column in pandas?

I have a table like this ---

AREA COL1 COL2 COL3
Dhaka [1,2,3] [2,3] [4,5,6]
Sylhet [10,11] [7,9,10] [10,13,7]

I want this table in this shape--

Area total_num
Dhaka 1
2
3
4
5
6
Sylhet 10
11
7
9
13

how can I do it in pandas?

Upvotes: 0

Views: 39

Answers (1)

jezrael
jezrael

Reputation: 863166

If necessary convert values to lists:

import ast

df.iloc[:, 1:] = df.iloc[:, 1:].applymap(ast.literal_eval)

And then reshape by DataFrame.set_index with DataFrame.stack, explode lists by Series.explode, creare 2 columns DataFrame and last remove duplicates:

df = (df.set_index('AREA')
        .stack()
        .explode()
        .droplevel(1)
        .reset_index(name='total_num')
        .drop_duplicates())
print (df)
      AREA total_num
0    Dhaka         1
1    Dhaka         2
2    Dhaka         3
5    Dhaka         4
6    Dhaka         5
7    Dhaka         6
8   Sylhet        10
9   Sylhet        11
10  Sylhet         7
11  Sylhet         9
14  Sylhet        13

Upvotes: 1

Related Questions