how to create an object from a python array

user_11370448 0
Viewed 45

I have the following structure, which I convert from a .txt with pandas

 [[000001, 'PEPE                  ', 'S', 'LAST_NAME   ', 'CIP  ', 'CELLPHONE'],
 [0000002, 'LUIS  ', 'S', 'ADRESS  ', '                       ', 'nan'],
 [0000003, 'PEDRO               ', 'S', 'STREET ', 'CITY', ' nan']]

My code

import pandas as pd
file = 'C:\\Users\\Admin\\Desktop\\PRUEBA.txt'

columns = ("service", "name", "Active", "reference1", "reference2", "reference3")
df = pd.read_csv(file, sep="|", names=columns, header=None)
cl = df.values.tolist()
print(cl)

but to be able to give it the treatment, which it requires, either by removing the empty strings and nan, how can I transform the service to int and create an object based on the service and the references in this way.

[
  { service: 1, name: 'PEPE', order: 0, ref: 'LAST_NAME' },
  { service: 1, name: 'PEPE', order: 1, ref: 'CIP' },
  { service: 1, name: 'PEPE', order: 2, ref: 'CELLPHONE' },
  { service: 2, name: 'LUIS', order: 0, ref: 'ADRESS' },
  { service: 3, name: 'PEDRO', order: 0, ref: 'STREET' },
  { service: 3, name: 'PEDRO', order: 1, ref: 'CITY' }
]

How can I achieve this, very grateful for your comments

pandas dataframe python-3.x list
1 Answers

Key: Use df.melt() to unpivot the table and subsequently perform df.to_dict(orient='records') to convert the dataframe to a record-oriented dict as mentioned by @QuangHoang. The rest are regular filtering and miscellaneous adjustments.

# data
ls = [['000001', 'PEPE                  ', 'S', 'LAST_NAME   ', 'CIP  ', 'CELLPHONE'],
      ['0000002', 'LUIS  ', 'S', 'ADRESS  ', '                       ', 'nan'],
      ['0000003', 'PEDRO               ', 'S', 'STREET ', 'CITY', ' nan']
      ]
df = pd.DataFrame(ls, columns=("service", "name", "Active", "reference1", "reference2", "reference3"))

# reformat and strip over each column
for col in df:
    if col == "service":
        df[col] = df[col].astype(int)
    else:
        df[col] = df[col].str.strip()  # accessor

# unpivot and adjust
df2 = df.melt(id_vars=["service", "name"],
              value_vars=["reference1", "reference2", "reference3"],
              value_name="ref")\
    .sort_values(by="service")\
    .drop("variable", axis=1)\
    .reset_index(drop=True)

# filter out empty or nan
df2 = df2[~df2["ref"].isin(["", "nan"])]

# generate order numbering by group
df2["order"] = df2.groupby("service").cumcount()
df2 = df2[["service", "name", "order", "ref"]]  # reorder

# convert to a record-oriented dict
df2.to_dict(orient='records')

Out[99]: 
[{'service': 1, 'name': 'PEPE', 'order': 0, 'ref': 'LAST_NAME'},
 {'service': 1, 'name': 'PEPE', 'order': 1, 'ref': 'CIP'},
 {'service': 1, 'name': 'PEPE', 'order': 2, 'ref': 'CELLPHONE'},
 {'service': 2, 'name': 'LUIS', 'order': 0, 'ref': 'ADRESS'},
 {'service': 3, 'name': 'PEDRO', 'order': 0, 'ref': 'STREET'},
 {'service': 3, 'name': 'PEDRO', 'order': 1, 'ref': 'CITY'}]
user_32186930
Related