Pandas: Read a text file and add values to multiple columns in a dataframe

user_7675621 0
Viewed 62

I have text file with data that is separated by Indentation. I want to create a dataframe from the data. The indentation separates the Ids and then within each there are different sets and each set has their data where fields like "Main_id", "Secondary_id", "Type", Value", "Start_Date", "End Date" that will repeat for "Category" and "Capacity" (If present multiple times). The Id and set value will also repeat for the various rows. The code will take the text file as input and separate the values in the columns of a dataframe in output.

I need a loop but I can't figure out how to repeat for some data and not for others. Please any directions/suggestions?

    with open('test.txt') as file:
        data = file.read()
    list_sep=data.split('\i')
    list_1=list_sep[1].split('\n')
    q_columns=["Id", "set","Main_id", "Secondary_id","Quantity", "Type",
           "Value", "Category", "Capacity", "Start Date", "End Date"]
    df_q=pd.DataFrame(columns=q_columns)

Sample text file:

Total ids entered : 3
Id :  6050
 set count = 1
    set : 256
               Main_id : 5677002
               Secondary_id : 34248              
               Quantity : 6
               Type : AB12
               Value : crypt
               Category : DFR
               Capacity : 100
               Category: BAS
               Capacity: 500
               Start_Date : 04/01/2022
               End_Date : 1/31/2023
Id : 123 
 set count = 2
    set : 456
               Main_id : 123456789
               Secondary_id : 101112131               
               Quantity : 4
               Type : AB12
               Value : crypt
               Category : DFR
               Capacity : 100
               Start_Date : 04/01/2022
               End_Date : 1/31/2023
               
    set : 789
               Main_id : 675497072432
               Secondary_id : 265902648927
               Quantity : 1
               Type : MAC12
               Value : nocl
               Category : DFR
               Capacity : 200
               Quantity : 2
               Type : GC56
               Value : crypt
               Category : BAS
               Capacity : 300
               Start_Date : 01/01/2023
               End_Date : 01/31/2023
               
Id :  897
 set count = 1
    set : 956
               Main_id : gy4567890
               Secondary_id : ky234248              
               Quantity : 6
               Type : MAB13
               Value : nocl
               Category : gcl
               Capacity : 100
               Start_Date : 04/01/2022
               End_Date : 1/31/2023

Looking for this Output: enter image description here

pandas python
1 Answers

It's more complicated than it may seem. Here is my approach. It is far from optimal but should give you an idea:

from collections import defaultdict
import pandas as pd

# read the file line by line
with open("test.txt", "r", encoding="utf-8") as file:
    data = file.read()

# define everything between two ids as an item
items = data.split("Id :")[1:]

all_items = []
for item in items:
    lines = [l.strip() for l in item.split("\n")]
    parsed_items = defaultdict(list)
    parsed_items["Id"].append(lines[0])
    for line in lines:
        if ":" not in line:
            continue
        key, value = (item.strip() for item in line.split(":"))
        parsed_items[key].append(value)

    # now fill the duplicate rows with the last values
    max_entries = max([len(v) for v in parsed_items.values()])
    for key, value in parsed_items.items():
        for _ in range(max_entries - len(value)):
            parsed_items[key].append(value[-1])
    all_items.append(parsed_items)

df = pd.concat([pd.DataFrame(i) for i in all_items], ignore_index=True)

# if desired, can filter the relevant columns
q_columns = ["Id", "set", "Main_id", "Secondary_id", "Quantity", "Type",
             "Value", "Category", "Capacity", "Start_Date", "End_Date"]
df = df[q_columns]
user_9158690
Related