Creating a dataframe by repeating each column a certain number of times

user_2136 0
Viewed 182

I need to turn the following dataframe:

ID   | A  | B  | C  | D  | E  |
1    | 3  | 1  | 2  | 1  | 0  |
2    | 0  | 1  | 2  | 5  | 2  |
3    | 2  | 2  | 5  | 3  | 10 |

into one that has each column name as the new value, repeated the number of times specified in the value. So, three rows of 'A', one rows of 'B', etc.:

ID | VALUE |
1  | A     |
1  | A     |
1  | A     |
1  | B     |
1  | C     |
1  | C     |
1  | D     |
2  | B     |
2  | C     |
2  | C     |
...

So far I've been playing in loops, building up a new dataframe but it's taking forever (100,000 original rows, and values of 100+) and just feels wrong. Have looked at melt and explode but it's not quite what I'm after. Any elegant solutions to doing this?

data = [{'ID': 1, 'A': 3, 'B': 1, 'C': 2, 'D': 1, 'E': 0},
        {'ID': 2, 'A': 0, 'B': 1, 'C': 2, 'D': 5, 'E': 2},
        {'ID': 3, 'A': 2, 'B': 2, 'C': 5, 'D': 3, 'E': 10}]

df = pd.DataFrame(data)
pandas dataframe python-3.x python
2 Answers

One way using pandas.DataFrame.columns.repeat:

df.apply(df.columns.repeat, axis=1).explode()

Output:

ID
1    A
1    A
1    A
1    B
1    C
1    C
1    D
2    B
...
3    E
3    E
3    E
dtype: object
user_70937410

Try with melt, then repeat

s = df.melt('ID')
s = s.reindex(s.index.repeat(s.pop('value')))
s
Out[41]: 
    ID variable
0    1        A
0    1        A
0    1        A
2    3        A
2    3        A
3    1        B
4    2        B
5    3        B
5    3        B
6    1        C
6    1        C
7    2        C
7    2        C
8    3        C
8    3        C
8    3        C
8    3        C
8    3        C
9    1        D
10   2        D
10   2        D
10   2        D
10   2        D
10   2        D
11   3        D
11   3        D
11   3        D
13   2        E
13   2        E
14   3        E
14   3        E
14   3        E
14   3        E
14   3        E
14   3        E
14   3        E
14   3        E
14   3        E
14   3        E
user_79645270
Related