How to ungroup data from a column with Pandas?

Question

I am having trouble with some data formatting. I have a csv of data that looks like the data at the bottom.

I am pretty sure something to do this exists in pandas even though I haven’t had much luck with my google search terms. I am thinking of a transpose of sorts but I haven’t had luck with that. I think for the data to be easier to access I would probably need the data in a format more like this.

   Bed  Week_Num  Day
0    3        27    1
0    3        27    1
0    3        27    2
1    1        35    2
1    1        35    2
1    1        35    1
1    1        35    1

The end goal is to have this data formatted in a way that INDEX and MATCH can work properly sum up the values based on these rows.

Here is a link to the dataset that I am working with right now if you wanted to look this over. There are 3 sheets total, the second sheet is an index for the weeks and the 3rd sheet is the table which has the values used to get the final sum of each row.

EDIT: New link https://docs.google.com/spreadsheets/d/1CdUzTewb8R0kcqELYkP3uanYQ0ITGVM7HlFs0A-UYKo/edit?usp=sharing

Data

# copy the data to the clipboard and read with the following line
df = pd.read_clipboard(sep=',')

Bed,Week_Num,Day
3,"27, 27, 27","1, 1, 2"
1,"35, 35, 35, 35","2, 2, 1, 1"
2,"35, 35, 35, 36, 36","1, 1, 2, 2, 2"
1,"35, 35, 36, 36, 36, 36, 36","1, 2, 2, 2, 2, 2, 1"
3,"35, 36, 36, 36, 36, 36, 36","2, 2, 2, 2, 2, 1, 1"
2,"36, 36, 37","1, 2, 2"
3,"36, 37, 37, 37, 37, 37, 37","2, 2, 2, 2, 2, 1, 1"
2,"36, 37, 37, 37, 37, 37, 37","2, 2, 2, 2, 2, 1, 1"
2,"36, 37, 37, 37, 37, 37, 37","2, 2, 2, 2, 2, 1, 1"
3,"36, 37, 37, 37, 37, 37, 37","2, 2, 2, 2, 2, 1, 1"
2,"36, 37, 37, 37, 37, 37, 37","2, 2, 2, 2, 2, 1, 1"
3,"36, 37, 37, 37, 37, 37, 37","2, 2, 2, 2, 2, 1, 1"
4,"36, 37, 37, 37, 37, 37, 37","2, 2, 2, 2, 2, 1, 1"
2,"36, 37, 37, 37, 37, 37, 37","2, 2, 2, 2, 2, 1, 1"
2,"37, 37, 37, 38, 38, 38, 38","1, 1, 2, 2, 2, 2, 2"
2,"37, 37, 37, 38, 38, 38, 38","1, 1, 2, 2, 2, 2, 2"
2,"37, 37, 38","1, 2, 2"
2,"37, 37, 38, 38, 38, 38, 38, 38","1, 2, 2, 2, 2, 2, 1, 1"
2,"37, 38, 38, 38, 38, 38, 38","2, 2, 2, 2, 2, 1, 1"
2,"37, 38, 38, 38, 38, 38, 38","2, 2, 2, 2, 2, 1, 1"
3,"37, 38, 38, 38, 38, 38, 38","2, 2, 2, 2, 2, 1, 1"
3,"37, 38, 38, 38, 38, 38, 38","2, 2, 2, 2, 2, 1, 1"
3,"37, 38, 38, 38, 38, 38, 38","2, 2, 2, 2, 2, 1, 1"
3,"37, 38, 38, 38, 38, 38, 38","2, 2, 2, 2, 2, 1, 1"
2,"37, 38, 38, 38, 38, 38, 38","2, 2, 2, 2, 2, 1, 1"
2,"37, 38, 38, 38, 38, 38, 38","2, 2, 2, 2, 2, 1, 1"
2,"38, 38, 38, 38, 38, 38","2, 2, 2, 2, 1, 1"
2,"38, 38, 38, 38, 38","2, 2, 2, 2, 1"
2,"38, 38, 39","1, 2, 2"
3,"38, 39, 39, 39, 39, 39, 39","2, 2, 2, 2, 2, 1, 1"
2,"38, 39, 39, 39, 39, 39, 39","2, 2, 2, 2, 2, 1, 1"
3,"38, 39, 39, 39, 39, 39, 39","2, 2, 2, 2, 2, 1, 1"
3,"38, 39, 39, 39, 39, 39, 39","2, 2, 2, 2, 2, 1, 1"
2,"38, 39, 39, 39, 39, 39, 39","2, 2, 2, 2, 2, 1, 1"
4,"39, 39, 39, 39, 39, 39, 40","2, 2, 2, 1, 1, 2, 2"
2,"39, 39, 40","1, 2, 2"
4,"39, 40, 40, 40, 40, 40, 40","2, 2, 2, 2, 2, 1, 1"
2,"39, 40, 40, 40, 40, 40, 40","2, 2, 2, 2, 2, 1, 1"
3,"39, 40, 40, 40, 40, 40, 40","2, 2, 2, 2, 2, 1, 1"
2,"39, 40, 40, 40, 40, 40, 40","2, 2, 2, 2, 2, 1, 1"
4,"39, 40, 40, 40, 40, 40, 40","2, 2, 2, 2, 2, 1, 1"
1,"40, 40, 40, 40","2, 2, 2, 2"
3,"40, 41, 41, 41, 41, 41, 41","2, 2, 2, 2, 2, 1, 1"
4,"40, 41, 41, 41, 41, 41, 41","2, 2, 2, 2, 2, 1, 1"
2,"40, 41, 41, 41, 41, 41, 41","2, 2, 2, 2, 2, 1, 1"
2,"41, 41, 41, 42","1, 1, 2, 2"
2,"41, 42, 42, 42, 42, 42, 42","2, 2, 2, 2, 2, 1, 1"
2,"41, 42, 42, 42, 42, 42, 42","2, 2, 2, 2, 2, 1, 1"
2,"42, 42, 42, 43, 43, 43, 43, 43, 43, 43, 44, 44, 44, 44","1, 1, 2, 2, 2, 2, 2, 1, 1, 2, 2, 2, 2, 2"
3,"42, 43, 43, 43, 43, 43, 43","2, 2, 2, 2, 2, 1, 1"
4,"43, 43, 43, 43, 43, 43","2, 2, 2, 2, 1, 1"
2,"43, 44, 44, 44, 44, 44, 44","2, 2, 2, 2, 2, 1, 1"
2,"44, 44, 45, 45, 45, 45, 45, 45, 45","1, 2, 2, 2, 2, 2, 1, 1, 2"
2,"44, 45, 45, 45, 45, 45, 45","2, 2, 2, 2, 2, 1, 1"
2,"45, 45, 45","1, 1, 2"
2,"46, 46, 46, 46, 46, 46, 46","2, 2, 2, 2, 1, 1, 2"
2,"46, 46, 46, 46, 46, 46, 46","2, 2, 2, 2, 1, 1, 2"
2,"47, 48, 48, 48, 48, 48, 48","2, 2, 2, 2, 2, 1, 1"
2,"47, 48, 48, 48, 48, 48","2, 2, 2, 2, 2, 1"
2,"47, 48, 48, 48, 48, 48, 48","2, 2, 2, 2, 2, 1, 1"
2,"47, 48, 48, 48, 48, 48, 48","2, 2, 2, 2, 2, 1, 1"
2,"47, 48, 48, 48, 48, 48, 48","2, 2, 2, 2, 2, 1, 1"
2,"47, 48, 48, 48, 48, 48, 48","2, 2, 2, 2, 2, 1, 1"
3,"47, 48, 48, 48, 48, 48, 48","2, 2, 2, 2, 2, 1, 1"
2,"47, 48, 48, 48, 48, 48, 48","2, 2, 2, 2, 2, 1, 1"
3,"48, 48, 48, 48, 48","2, 2, 2, 2, 1"
2,"48, 48, 48, 48, 48","2, 2, 2, 2, 1"
2,"48, 48, 48, 48, 48, 48","2, 2, 2, 1, 1, 2"
2,"48, 48, 48, 48","2, 2, 1, 1"
2,"48, 48, 48, 48","2, 2, 1, 1"
2,"48, 48, 48, 48","2, 1, 1, 2"
3,"48, 48, 48, 48","2, 1, 1, 2"
2,"48, 49, 49, 49, 49, 49, 49","2, 2, 2, 2, 2, 1, 1"
2,"48, 49, 49, 49, 49, 49, 49","2, 2, 2, 2, 2, 1, 1"
2,"50, 50, 50, 50, 50, 50","2, 2, 2, 2, 1, 1"
2,"50, 50, 51, 51, 51, 51","1, 2, 2, 2, 2, 2"
3,"50, 51, 51, 51, 51, 51, 51","2, 2, 2, 2, 2, 1, 1"
2,"51, 51, 51, 51, 51, 51, 51","2, 2, 2, 2, 1, 1, 2"
2,"51, 51, 52, 52, 52","1, 2, 2, 2, 2"
2,"51, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 2, 1, 1"
2,"51, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 2, 1, 1"
2,"51, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 2, 1, 1"
2,"51, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 2, 1, 1"
2,"51, 52, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 2, 1, 1, 2"
2,"51, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 2, 1, 1"
3,"51, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 2, 1, 1"
3,"51, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 2, 1, 1"
2,"52, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 1, 1, 2"
2,"52, 52, 52, 52, 52","2, 2, 2, 2, 1"
3,"52, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 1, 1, 2"
2,"52, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 1, 1, 2"
2,"52, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 1, 1, 2"
3,"52, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 1, 1, 2"
2,"52, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 1, 1, 2"
2,"52, 52, 52, 52, 52, 52, 52","2, 2, 2, 2, 1, 1, 2"
2,"52, 52, 52, 52, 52, 52, 53","2, 2, 2, 1, 1, 2, 2"
3,"52, 52, 52, 52, 52","2, 2, 1, 1, 2"
1,"52, 52, 52, 53, 53","1, 1, 2, 2, 2"
3,"52, 52, 52, 53","1, 1, 2, 2"
1,"52, 52, 52, 53, 53, 53, 53, 53, 53, 53","1, 1, 2, 2, 2, 2, 2, 1, 1, 2"
2,"52, 52, 52, 53, 53, 53, 53","1, 1, 2, 2, 2, 2, 2"
2,"52, 52, 53, 53, 53, 53, 53","1, 2, 2, 2, 2, 2, 1"
2,"52, 53, 53, 53, 53, 53, 53","2, 2, 2, 2, 2, 1, 1"
2,"53, 53, 53, 53, 53, 53","2, 2, 2, 2, 1, 1"
2,"53, 53, 53, 53, 53, 53","2, 2, 2, 2, 1, 1"
1,"53, 53, 53, 53, 53, 53","2, 2, 2, 1, 1, 2"
2,"53, 53, 53, 1","1, 1, 2, 2"
2,"5, 6, 6, 6, 6, 6, 6","2, 2, 2, 2, 2, 1, 1"
1,"6, 6, 7, 7, 7, 7, 7","1, 2, 2, 2, 2, 2, 1"
2,"6, 7, 7, 7, 7","2, 2, 2, 2, 2"
3,"8, 9, 9, 9, 9, 9, 9","2, 2, 2, 2, 2, 1, 1"
2,"9, 9, 10, 10, 10, 10, 10","1, 2, 2, 2, 2, 2, 1"
1,"9, 10, 10, 10, 10, 10, 10","2, 2, 2, 2, 2, 1, 1"
3,"9, 10, 10, 10, 10, 10, 10","2, 2, 2, 2, 2, 1, 1"
2,"9, 10, 10, 10, 10, 10, 10","2, 2, 2, 2, 2, 1, 1"
2,"9, 10, 10, 10, 10, 10, 10","2, 2, 2, 2, 2, 1, 1"
2,"9, 10, 10, 10, 10, 10, 10","2, 2, 2, 2, 2, 1, 1"
2,"9, 10, 10, 10, 10, 10, 10","2, 2, 2, 2, 2, 1, 1"
2,"9, 10, 10, 10, 10, 10, 10","2, 2, 2, 2, 2, 1, 1"
2,"10, 10, 10, 10, 10, 10, 10","2, 2, 2, 2, 1, 1, 2"
2,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
3,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
2,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
3,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
2,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
3,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
2,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
3,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
2,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
2,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
2,"10, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 2, 1, 1"
1,"11, 11, 11, 11, 11, 11, 11","2, 2, 2, 2, 1, 1, 2"
1,"11, 12, 12, 12, 12, 12, 12","2, 2, 2, 2, 2, 1, 1"
2,"11, 12, 12, 12, 12, 12, 12","2, 2, 2, 2, 2, 1, 1"
2,"11, 12, 12, 12, 12, 12, 12","2, 2, 2, 2, 2, 1, 1"
2,"11, 12, 12, 12, 12, 12, 12","2, 2, 2, 2, 2, 1, 1"
2,"11, 12, 12, 12, 12, 12, 12","2, 2, 2, 2, 2, 1, 1"
3,"12, 13, 13, 13, 13, 13, 13","2, 2, 2, 2, 2, 1, 1"
3,"12, 13, 13, 13, 13, 13, 13","2, 2, 2, 2, 2, 1, 1"
2,"12, 13, 13, 13, 13, 13, 13","2, 2, 2, 2, 2, 1, 1"
1,"12, 13, 13, 13, 13, 13, 13","2, 2, 2, 2, 2, 1, 1"
3,"12, 13, 13, 13, 13, 13, 13","2, 2, 2, 2, 2, 1, 1"
2,"12, 13, 13, 13, 13, 13, 13","2, 2, 2, 2, 2, 1, 1"
2,"13, 13, 13","1, 1, 2"
2,"13, 14, 14, 14, 14, 14, 14","2, 2, 2, 2, 2, 1, 1"
2,"13, 14, 14, 14, 14, 14","2, 2, 2, 2, 2, 1"
3,"13, 14, 14, 14, 14, 14, 14","2, 2, 2, 2, 2, 1, 1"
2,"14, 14, 14, 14, 14, 14, 14","2, 2, 2, 2, 1, 1, 2"
2,"14, 15, 15, 15, 15, 15, 15","2, 2, 2, 2, 2, 1, 1"
2,"15, 16, 16, 16, 16, 16, 16","2, 2, 2, 2, 2, 1, 1"
2,"16, 17, 17, 17, 17, 17, 17","2, 2, 2, 2, 2, 1, 1"
2,"17, 18, 18, 18, 18, 18, 18","2, 2, 2, 2, 2, 1, 1"
2,"18, 19, 19, 19, 19, 19, 19","2, 2, 2, 2, 2, 1, 1"
2,"24, 24, 24, 24, 24, 24","2, 2, 2, 2, 1, 1"
2,"25, 25, 25, 25, 25, 25","2, 2, 2, 2, 1, 1"
2,"26, 26, 27, 27, 27, 27, 27","1, 2, 2, 2, 2, 2, 1"
2,"26, 27, 27, 27, 27, 27, 27","2, 2, 2, 2, 2, 1, 1"

https://stackoverflow.com/questions/12680754/split-explode-pandas-dataframe-string-entry-to-separate-rows — BENY, Aug 28 '20 at 19:53
Associated question: [How to unnest (explode) a column in a pandas DataFrame?](https://stackoverflow.com/questions/53218931/how-to-unnest-explode-a-column-in-a-pandas-dataframe) — Trenton McKinney, Aug 28 '20 at 20:12
@TrentonMcKinney BTW, thank you for edited ~ looks much better now~ ~ — BENY, Aug 28 '20 at 20:21
@BEN_YO You're welcome. I like to make sure useful questions stay useful for the future. — Trenton McKinney, Aug 28 '20 at 20:23
@TrentonMcKinney agreed, thanks for that. I didn't even know you could edit someone else's post and improve upon it. — Larry Flores, Aug 28 '20 at 20:24

score 2 · Accepted Answer · answered Aug 28 '20 at 19:58

2

You can use explode on lists :

df = (df
     .applymap(lambda x: str(x).split(','))
     .apply(pd.Series.explode)
     .apply(pd.to_numeric)
     .reset_index(drop=True))

print(df)

   Bed  Week_Num  Day
0    3        27    1
1    3        27    1
2    3        27    2
3    1        35    2
4    1        35    2
5    1        35    1
6    1        35    1

answered Aug 28 '20 at 19:58

YOLO

20,181
5
20
40

I had no idea pandas had something called explode. This is super useful to know. Is there a way to add an ID for each row that was exploded? – Larry Flores Aug 28 '20 at 20:03
1

@LarryFlores remove `.reset_index(drop=True)` – Trenton McKinney Aug 28 '20 at 20:04

How to ungroup data from a column with Pandas?

Data

1 Answers1