How to split data in a single column into new rows (other columns kept same in new row)

Question

I have a dataset about gun violence for a project. One of the columns includes the participant types, either victim or subject/suspect. The participant column has multiple values within it for each participant in the incident.

import pandas as pd
data = pd.read_csv('Gun violence Shortened version.csv')
data.head()

Output:

 incident_id    date    state   participant_type    
0   461105  1/1/2013    Pennsylvania    0::Victim||1::Victim||2::Victim||3::Victim||4:...   
1   460726  1/1/2013    California  0::Victim||1::Victim||2::Victim||3::Victim||4:...   
2   478855  1/1/2013    Ohio    0::Subject-Suspect||1::Subject-Suspect||2::Vic...   
3   478925  1/5/2013    Colorado    0::Victim||1::Victim||2::Victim||3::Subject-Su...   
4   478959  1/7/2013    North Carolina  0::Victim||1::Victim||2::Victim||3::Subject-Su...   

I want to take each participant and give them their own row while keeping incident_id and date the same:

incident_id date    state   participant_type    
0   461105  1/1/2013    Pennsylvania    Victim
1   461105  1/1/2013    Pennsylvania    Victim
2   461105  1/1/2013    Pennsylvania    Victim
3   461105  1/1/2013    Pennsylvania    Subject-Suspect *this was the 4:: instance that was cut off earlier*

I'm not sure how to accomplish this. I've seen example of splitting a column into two but not how to take from a column into a row.

Answer 1

I would rather prepare the data in advance using regular Python's data structures and then create a Pandas DataFrame out of it. The reason is that Pandas is not primarily designed for such operations like individual row manipulation and even though there're ways to do it, it's considered an anti-pattern and it's much slower.

The following piece of code parses the CSV data into a regular list using the CSV module from Python's Standard library, while adding multiple rows for each CSV row containing multiple items in the last column. In the last step, Pandas DataFrame is simply created from the preprocessed list:

import pandas as pd
import csv

data = []
with open('Gun violence Shortened versio.csv') as file:
    reader = csv.reader(file, delimiter=',')

    # iterate over all rows in the CSV
    for row in reader:
        # split the content of the last column by the || delimiter into a list
        # if there's no delimiter, it will produce a single-item list
        items = row[3].split('||')

        # append each item from the last column together with other columns
        # as an individual row to the data list, N items will produce N rows
        for item in items:
            data.append([row[0], row[1], row[2], item])

df = pd.DataFrame(data)

It's not the final solution, you'll need to skip the first row, clean the individual items from the last column, etc., but that should be trivial.

There are some benchmarks, where the manipulation with rows in Pandas was about 1000× slower than preparing data using Python's data structures and creating a DatFrame from them.

Answer 2

Below is another script that gets you the output , though solution shared by Dawid looks more faster

import pandas as pd
import numpy as np


if __name__ == '__main__':
    df_new = pd.DataFrame()
    pd.set_option('display.width', 100000)
    pd.set_option('display.max_columns', 500)
    dict = {'col1':[461105,460726]
            ,'col2':['0::Victim||1::Victim||2::Victim||3::Victim||4:Subject-Suspect','0::Victim||1::Victim||2::Victim||3::Victim||4:Subject-Suspect']}
    df = pd.DataFrame(dict)
    #print(df)
    col1_list=[]
    col2_list = []
    for index,row in df.iterrows():
        for rec in row.col2.split('||'):
            col1_list.append(row.col1)
            col2_list.append(rec.split(':')[-1])
    dict_new = {'col1':col1_list,'col2':col2_list}
    df_new= pd.DataFrame(dict_new)
    #df.append(df_new,ignore_index=True)
    print(df_new)