how to split multiple valued single row into multiple individual rows

Question

I have CSV file which has multiple "values" in complex form for single "id" and i want that multiple values to split in different rows with respect to its "id".

My CSV file:

# To read df1=pandas.read_csv('krish.csv',encoding="ISO-8859-1")
# File have data even like 1.50% (P,KR,AU) 0.2¢/kg (AX,AU)
id  value
100.3   Free (A+,BH,CA) 0.1¢/kg (AX)
200.1   Free (MA, MX,OM)
321.5   Free (BH,CA) 1.70% (P) 7% (PE) 12.3% (KR)

OUTPUT I WANT FOR MY INPUT GIVEN ABOVE :

OUTPUT WHICH GOT ON MY CODE AND FOR WHAT I TRIED

ok, have a look at my answer and let me know if it fit your needs — crash
– crash, Commented May 31, 2019 at 11:30
Hey SS would you accept my answer if you don't have anything more to add? Thanks — crash
– crash, Commented Jun 3, 2019 at 10:56

crash · Accepted Answer · 2019-05-31 13:56:28Z

1

I'm pretty sure there are more efficient/elegant ways, but this should work

def split_elements(s):
    elements = s[s.find('(')+1:-1].split(',')
    key = s[:s.find('(')]
    return ['{} ({})'.format(key, el) for el in elements]

input_data = {'values': ['Free (A+,BH,CA) 0.1¢/kg (AX)', 'Free (MA, MX,OM)', 'Free (BH,CA) 1.70% (P) 7% (PE) 12.3% (KR)'], 'ids': [100.3, 200.1, 321.5]}
df = pd.DataFrame(input_data)

temp_values = []
temp_ids = []
# iterate through rows
for idr, r in df.iterrows():
    # extract elements
    elements = [el.strip()+')' for el in r['values'].split(')') if el != '']
    # split subelements
    for element in elements:
        split_el = split_elements(element)
        temp_values.extend(split_el)
        temp_ids.extend([r['ids']]*len(split_el))
# create dataset
df1 = pd.DataFrame({'ids': temp_ids, 'values': temp_values})
df1.set_index('ids')

Which gives

ids     values
100.3   Free (A+)
100.3   Free (BH)
100.3   Free (CA)
100.3   0.1¢/kg (AX)
200.1   Free (MA)
200.1   Free ( MX)
200.1   Free (OM)
321.5   Free (BH)
321.5   Free (CA)
321.5   1.70% (P)
321.5   7% (PE)
321.5   12.3% (KR)

edited May 31, 2019 at 13:56

answered May 31, 2019 at 11:24

crash

4,5829 gold badges36 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

S S Over a year ago

I have my ID column which as i have mentioned in my input

crash Over a year ago

I don't understand what you mean

S S Over a year ago

In your code you have used input with only my "value " column but i need my "id" data be to same Just now i edited my Id values it will be similar to that can you please have a look sir

S S Over a year ago

Please have a look at my input sir

crash Over a year ago

I still cannot understand. If I look at the picture you posted after the message OUTPUT I WANT FOR MY INPUT GIVEN ABOVE : this is what I achieved with my code. Update that picture to reflect the result you're looking after and then I'll update my answer accordingly

|

Collectives™ on Stack Overflow

how to split multiple valued single row into multiple individual rows

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related