Read csv file into dictionary in a certain format-CodePudding

I am trying to read .csv file into dictionary in a certain format. Below is the reproducible format for the dataframe.

# initialize list of lists
data = [['fac1', 1,11], ['fac1', 2,12], ['fac1',3,13],['fac2',1,8],['fac2',2,9],['fac2',3,20]]

# Create the pandas DataFrame
df = pd.DataFrame(data, columns=['facility', 'customer','distance'])

print(df)

Output
  facility  customer  distance
  fac1         1        11
  fac1         2        12
  fac1         3        13
  fac2         1         8
  fac2         2         9
  fac2         3        20

I want to read this into a dictionary in certain format below

{'Fac-1': {1: 4, 2: 5, 3: 6, 4: 8, 5: 10},
 'Fac-2': {1: 6, 2: 4, 3: 3, 4: 5, 5: 8},
 'Fac-3': {1: 9, 2: 7, 3: 4, 4: 3, 5: 4}}

CodePudding user response：

Have you tried something like this :

# Create the pandas DataFrame
df = pd.DataFrame(data, columns=["facility", "customer", "distance"])

dict = {
    k.replace("fac", "Fac_"): {c.customer: c.distance for _, c in v.iterrows()}
    for k, v in df.groupby(["facility"])
}

CodePudding user response：

You can create a method to set the values inside dict to columns. The external keys are facility, the dict internal keys are customer, and your values are distance. Have you tried something like this:

import pandas as pd

def dict_to_values(key, value):
    return len(value.keys())*[key], list(value.keys()), list(value.values())


d = {'Fac-1': {1: 4, 2: 5, 3: 6, 4: 8, 5: 10}, 'Fac-2': {1: 6, 2: 4, 3: 3, 4: 5, 5: 8},  'Fac-3': {1: 9, 2: 7, 3: 4, 4: 3, 5: 4}}

facility, customer, distance = [], [], [] 

for key, value in d.items():
    samples = dict_to_values(key, value)
    facility.extend(samples[0])
    customer.extend(samples[1])
    distance.extend(samples[2])

df = pd.DataFrame({'facility': facility, 'customer': customer, 'distance': distance})
print(df)

Output:

   facility  customer  distance
0     Fac-1         1         4
1     Fac-1         2         5
2     Fac-1         3         6
3     Fac-1         4         8
4     Fac-1         5        10
5     Fac-2         1         6
6     Fac-2         2         4
7     Fac-2         3         3
8     Fac-2         4         5
9     Fac-2         5         8
10    Fac-3         1         9
11    Fac-3         2         7
12    Fac-3         3         4
13    Fac-3         4         3
14    Fac-3         5         4