How to create json column in pandas dataframe-CodePudding

I have a df

index col1
0      a,c
1      d,f
2      o,k

I need a df like this

index col1
0     {"col1":"a,c"}
1     {"col1":"d,f"}
2     {"col1":"o,k"}

This needs to be applied for all columns in the df.

Tried with orient, but not as expected.

CodePudding user response：

For all columns use double apply, columns name is passed by x.name, get dictionary:

df = df.apply(lambda x: x.apply(lambda y: {x.name: y}))

For json use:

import json

df = df.apply(lambda x: x.apply(lambda y: json.dumps({x.name: y})))
print (df)
              col1
0  {"col1": "a,c"}
1  {"col1": "d,f"}
2  {"col1": "o,k"}

Alternative solution for dictionaries:

df = pd.DataFrame({c: [{c: x} for x in df[c]] for c in df.columns}, index=df.index)

Alterative2 solution for json (working well if all columns are filled by strings):

df = '{"'   df.columns   '": "'   df.astype(str)   '"}'

CodePudding user response：

If you want strings exactly as shown, use:

df['col1'] = '{col1:' df['col1'] '}'

# or 
c = 'col1'
df[c] = f'{{{c}:' df[c] '}'

output:

0    {col1:a,c}
1    {col1:d,f}
2    {col1:o,k}
Name: col1, dtype: object

or, with quotes:

df['col1'] = '{"col1":"' df['col1'] '"}'

# or 
c = 'col1'
df[c] = f'{{"{c}":"' df[c] '"}'

output:

   index            col1
0      0  {"col1":"a,c"}
1      1  {"col1":"d,f"}
2      2  {"col1":"o,k"}

for all columns:

df = df.apply(lambda c: f'{{"{c.name}":"' c.astype(str) '"}')

NB. ensure "index" is the index

for dictionaries:

df['col1'] = [{'col1': x} for x in df['col1']]

output:

   index             col1
0      0  {'col1': 'a,c'}
1      1  {'col1': 'd,f'}
2      2  {'col1': 'o,k'}