How to convert a list to a table in Python

user_7870002 0
Viewed 200

I have a list like this one below. The list is a flatten version of the nested JSON file 

  array([{'content_ID_994': 'BXXXXXXXXX',
  'content_ID_995': 'BYYYYYYYYY',
  'content_ID_996': 'BZZZZZZZZZ',
  'content_ID_997': 'BAAAAAAAAA',
  'content_ID_998': 'BBBBBBBBBB',
  'content_ID_999': 'BCCCCCCCCC',
  'column A': 'value1', 
  'column B': 'value2',
  'module_1_gallery': 'N1', 
  'module_1_composite': 'F1', 
  'module_1_collection': 'P1', 
  'module_2_gallery': 'N2', 
  'module_2_composite': 'F2', 
  'module_2_collection': 'P2'}],
  dtype=object)

I want the array to be converted to a table so I can write it to a Database. The table structure is below 

 ID          Column A     Column B       Modules     Gallery   Composite   Collection   
 BXXXXXXXXX  value1       value2         1           N1        F1          P1
 BXXXXXXXXX  value1       value2         2           N2        F2          P2

How do I process my input to get the desired table structure? Any inputs would be of great help

pandas list numpy python
1 Answers

I split output to 3 DataFrames, because each of them is processing different - df1 is reshaped by DataFrame.melt, df2 by str.rsplit columns to MultiIndex and DataFrame.stack and last is used cross join of all DataFrames:

a = np.array([{'content_ID_994': 'BXXXXXXXXX',
  'content_ID_995': 'BYYYYYYYYY',
  'content_ID_996': 'BZZZZZZZZZ',
  'content_ID_997': 'BAAAAAAAAA',
  'content_ID_998': 'BBBBBBBBBB',
  'content_ID_999': 'BCCCCCCCCC',
  'column A': 'value1', 
  'column B': 'value2',
  'module_1_gallery': 'N1', 
  'module_1_composite': 'F1', 
  'module_1_collection': 'P1', 
  'module_2_gallery': 'N2', 
  'module_2_composite': 'F2', 
  'module_2_collection': 'P2'}],
  dtype=object)

df = pd.DataFrame([a[0]])

m1 = df.columns.str.startswith('content_ID')
m2 = df.columns.str.startswith('module')

df1 = df.loc[:, m1]
df2 = df.loc[:, m2]
df3 = df.loc[:, ~(m1 | m2)]
#print (df1)
#print (df2)
#print (df3)


df1 = df1.melt(value_name='ID')
df2.columns = df2.columns.str.rsplit('_', n=1, expand=True)
df2 = df2.stack(0)

df4 = df1.assign(a=1).merge(df3.assign(a=1).merge(df2.assign(a=1), on='a'), on='a')
df4 = df4.drop(['variable','a'], axis=1)
print (df4)
            ID column A column B collection composite gallery
0   BXXXXXXXXX   value1   value2         P1        F1      N1
1   BXXXXXXXXX   value1   value2         P2        F2      N2
2   BYYYYYYYYY   value1   value2         P1        F1      N1
3   BYYYYYYYYY   value1   value2         P2        F2      N2
4   BZZZZZZZZZ   value1   value2         P1        F1      N1
5   BZZZZZZZZZ   value1   value2         P2        F2      N2
6   BAAAAAAAAA   value1   value2         P1        F1      N1
7   BAAAAAAAAA   value1   value2         P2        F2      N2
8   BBBBBBBBBB   value1   value2         P1        F1      N1
9   BBBBBBBBBB   value1   value2         P2        F2      N2
10  BCCCCCCCCC   value1   value2         P1        F1      N1
11  BCCCCCCCCC   value1   value2         P2        F2      N2
user_29010020
Related