Pandas: Splitting a non-numeric identifier code into multiple rows

user_18968118 0
Viewed 56

Suppose I have a data set that looks like this

Unique_Identifier  Score1 Score2
112                   50     60 
113-114               50     70 
115                   40     20 
116-117               30     90 
118                   70     70

Notice how some of my unique identifiers are listed as ranges, rather than exact values. I want to split up those ranges to each be 2 separate rows with the same scores so that it would look like this:

Unique_Identifier  Score1 Score2
112                   50     60 
113                   50     70
114                   50     70
115                   40     20 
116                   30     90
117                   30     90 
118                   70     70

How would I go about doing this in Python using Pandas? I think there may be a way to test for rows that have a "-" in them, but I'm not sure how I would go about splitting those rows. I should also note that some identifier ranges have more than just 2 identifiers in them, such as 120-124.

pandas rows indexing python
2 Answers
df.assign(Unique_Identifier=df.Unique_Identifier.str.split('-')).explode('Unique_Identifier')

  Unique_Identifier  Score1  Score2
0               112      50      60
1               113      50      70
1               114      50      70
2               115      40      20
3               116      30      90
3               117      30      90
4               118      70      70
user_83802720

split on "-" and create a list with the desired range. Then explode to individual rows:

df["Unique_Identifier"] = df["Unique_Identifier"].apply(lambda x: list(range(int(x.split("-")[0]),int(x.split("-")[1])+1)) if "-" in x else [int(x)])
df = df.explode("Unique_Identifier")

>>> df
  Unique_Identifier  Score1  Score2
0               112      50      60
1               113      50      70
1               114      50      70
2               115      40      20
3               116      30      90
3               117      30      90
4               118      70      70
5               120      80      80
5               121      80      80
5               122      80      80
5               123      80      80
5               124      80      80
user_98576310
Related