Copying a tuple to an entire row in a dataframe-CodePudding

I am trying to for loop through a dataframe and copy an entire row to a temporary dataframe. row is of type tuple so how do I copy each item in the tuple to a different column of the same row in a dataframe? Please see the example of my dataframe and my desired output

Here is my dataframe df:

╔════════╦════════════════╦═════════════════════╦═══════════════════════╦═════════════╗
║   ID   ║      Name      ║       Address       ║         Email         ║    Items    ║
╠════════╬════════════════╬═════════════════════╬═══════════════════════╬═════════════╣
║ 839843 ║ John Smith     ║ 55 Apple Lane       ║ [email protected]      ║ 827,937,392 ║
║ 327569 ║ Tom Hanks      ║ 16 Cauliflower Road ║ [email protected]      ║ 947,540     ║
║ 924852 ║ Alison Johnson ║ 85 Main Street      ║ [email protected]    ║ 838         ║
║ 949325 ║ Frank Rizzo    ║ 218 Orange Road     ║ [email protected] ║ 494,386,285 ║
║ 373202 ║ Kelly Chang    ║ 19 First Avenue     ║ [email protected]      ║ 928,502,214 ║
║ 928436 ║ Lisa Thomas    ║ 95 Albany Way       ║ [email protected]   ║ 455,953     ║
╚════════╩════════════════╩═════════════════════╩═══════════════════════╩═════════════╝

This is my desired output:

╔════════╦══════╦════════════════╦═════════════════════╦═══════════════════════╗
║   ID   ║ Item ║      Name      ║       Address       ║         Email         ║
╠════════╬══════╬════════════════╬═════════════════════╬═══════════════════════╣
║ 839843 ║  827 ║ John Smith     ║ 55 Apple Lane       ║ [email protected]      ║
║ 839843 ║  937 ║ John Smith     ║ 55 Apple Lane       ║ [email protected]      ║
║ 839843 ║  392 ║ John Smith     ║ 55 Apple Lane       ║ [email protected]      ║
║ 327569 ║  947 ║ Tom Hanks      ║ 16 Cauliflower Road ║ [email protected]      ║
║ 327569 ║  540 ║ Tom Hanks      ║ 16 Cauliflower Road ║ [email protected]      ║
║ 924852 ║  838 ║ Alison Johnson ║ 85 Main Street      ║ [email protected]    ║
║ 949325 ║  494 ║ Frank Rizzo    ║ 218 Orange Road     ║ [email protected] ║
║ 949325 ║  386 ║ Frank Rizzo    ║ 218 Orange Road     ║ [email protected] ║
║ 949325 ║  285 ║ Frank Rizzo    ║ 218 Orange Road     ║ [email protected] ║
║ 373202 ║  928 ║ Kelly Chang    ║ 19 First Avenue     ║ [email protected]      ║
║ 373202 ║  502 ║ Kelly Chang    ║ 19 First Avenue     ║ [email protected]      ║
║ 373202 ║  215 ║ Kelly Chang    ║ 19 First Avenue     ║ [email protected]      ║
║ 928436 ║  455 ║ Lisa Thomas    ║ 95 Albany Way       ║ [email protected]   ║
║ 928436 ║  953 ║ Lisa Thomas    ║ 95 Albany Way       ║ [email protected]   ║
╚════════╩══════╩════════════════╩═════════════════════╩═══════════════════════╝

This is the code I have to solve the problem but so far without success:

temp_df = df.iloc[:0] 

c=0
i=0
for row in df.iterrows():
    items = row[1][4].split(',') 
    for item in items:            
        temp_df.at[i c,:] = row
        temp_df.at[-1, 'Items'] = item
        c=c 1
    i=i c
    c=0

Hopefully someone can help with a solution.

CodePudding user response：

Assuming that your_file.csv is the first table mentioned in your example, you can read it into df and:

Solution 1:

Parse its rows to get what you need in temp_df:

temp_df_records = []

for _, row in df.iterrows():
    items = row["Items"].split(',')
    row_dict = row.drop("Items").to_dict()
    for item in items:
        row_dict["Item"] = item
        temp_df_records.append(row_dict.copy())

temp_df = pd.DataFrame.from_records(temp_df_records)

Solution 2:

You can convert the values of column "Items" to list and use .explode() method to construct temp_df:

temp_df = df.copy().rename(columns={"Items": "Item"})
temp_df["Item"] = temp_df["Item"].apply(lambda items: items.split(","))
temp_df = temp_df.explode("Item").reset_index(drop=True)

Output (temp_df) in both solutions:

        ID            Name              Address                  Email Item
0   839843      John Smith        55 Apple Lane       [email protected]  827
1   839843      John Smith        55 Apple Lane       [email protected]  937
2   839843      John Smith        55 Apple Lane       [email protected]  392
3   327569       Tom Hanks  16 Cauliflower Road       [email protected]  947
4   327569       Tom Hanks  16 Cauliflower Road       [email protected]  540
5   924852  Alison Johnson       85 Main Street     [email protected]  838
6   949325     Frank Rizzo      218 Orange Road  [email protected]  494
7   949325     Frank Rizzo      218 Orange Road  [email protected]  386
8   949325     Frank Rizzo      218 Orange Road  [email protected]  285
9   373202     Kelly Chang      19 First Avenue       [email protected]  928
10  373202     Kelly Chang      19 First Avenue       [email protected]  502
11  373202     Kelly Chang      19 First Avenue       [email protected]  214
12  928436     Lisa Thomas        95 Albany Way    [email protected]  455
13  928436     Lisa Thomas        95 Albany Way    [email protected]  953