0

I have a database column that's been converted to a Pandas dataframe and it looks like below . My actual data has much more columns and rows with different key: value pair.

df["Records"]
{"ID":"1","ID_1":"40309","type":"type1"}
{"ID":"2","ID_1":"40310","type":"type1"}
{"ID":"3","ID_1":"40311","type":"type1"}

I want to split this into multiple columns in a dataframe.

df1:
ID  ID_1   type
1   40309  type1
2   40310  type1
3   40311  type1

I tried this code

json_Str=df.to_dict()
json_dump= json.dumps(json_Str)
json_dump=json_dump.replace("\\", "")
with open("H:\\df2.json", 'w') as fp:
    # json.dump(result, fp, indent=4)
    print(json_dump, file=fp)

output file has dictionary key value pair

Output:

{"Records":"{"ID":"1","ID_1":"40309","type":"type1"}
{"ID":"2","ID_1":"40310","type":"type1"}
{"ID":"3","ID_1":"40311","type":"type1"}"}

How do I convert a json column to csv format in pandas?

unicorn
  • 496
  • 6
  • 20
  • should `df["Records"]` be set equal (`=`) to the dictionary instead of using a colon (`:`)? – Ian Thompson Aug 19 '22 at 18:14
  • input has colon. output doesnt matter . it should just be in different columns ..df1[0,1,2]: 1,40309,type1 this is nothing but df1[0]=1, df1[1]=40309,df1[2]=type1 – unicorn Aug 19 '22 at 18:17
  • can you supply a sample of data so we have something to build off of? `df["Records"]:{"ID":"1","ID_1":"40309","type":"type1"}` doesn't help much – Ian Thompson Aug 19 '22 at 18:35
  • I added few more columns . – unicorn Aug 19 '22 at 20:49
  • Does my answer below not produce what you need? – Ian Thompson Aug 19 '22 at 21:37
  • Does this answer your question? [Split / Explode a column of dictionaries into separate columns with pandas](https://stackoverflow.com/questions/38231591/split-explode-a-column-of-dictionaries-into-separate-columns-with-pandas) – ouroboros1 Aug 22 '22 at 18:28

2 Answers2

0

not entirely sure I understand the question but if youre just trying to take your data out of index, just use

df1.reset_index(drop=False)

or if youre trying to convert rows to columns you could use df1.transpose()

Michael
  • 21
  • 2
  • i am just trying to split json column from database to multiple columns in pandas dataframe.. I edited my post to make it more sense. – unicorn Aug 19 '22 at 18:22
0

I think this is what you're after

# using the same data repeatedly to highlight effect
df = pd.DataFrame({
    "Records": [
        {"ID":"1","ID_1":"40309","type":"type1"},
        {"ID":"1","ID_1":"40309","type":"type1"},
        {"ID":"1","ID_1":"40309","type":"type1"}
    ]
})

# start data looks like...
print(df)

                                         Records
0  {'ID': '1', 'ID_1': '40309', 'type': 'type1'}
1  {'ID': '1', 'ID_1': '40309', 'type': 'type1'}
2  {'ID': '1', 'ID_1': '40309', 'type': 'type1'}

# "explode" the dictionaries in df.Records
df1 = pd.DataFrame(df.Records.to_list())

print(df1)

  ID   ID_1   type
0  1  40309  type1
1  1  40309  type1
2  1  40309  type1

Reference -- Split a Pandas column of lists into multiple columns

Ian Thompson
  • 2,914
  • 2
  • 18
  • 31
  • this throws error.. it says 'DataFrame' object has no attribute 'Records' – unicorn Aug 22 '22 at 18:01
  • If your `df` has a column called `"Records"` it won't raise the error. In your question, please provide code to generate a sample of you data. The code you gave isn't currently in an acceptable `pandas` format. Or are you saying that error raises after running my code exclusively? – Ian Thompson Aug 22 '22 at 18:24
  • thanks Ian.. It does not solve the issue. there is no error but output is same wth header being 0. – unicorn Aug 22 '22 at 19:11
  • @unicorn please provide code that produces your data and your error – Ian Thompson Aug 22 '22 at 19:46
  • I added code and result i am getting.. does that make sense?. this is all i did. – unicorn Aug 22 '22 at 20:16
  • The first code block you provided isn't runnable / I'm not sure what to do with it. What is "Output" (the json looking thing at the end of your question)? Is that actually your input? Or what you want your output to look like? Give us a copy/pastable `df` or code to create a `df` that looks like your `df` so we can move forward – Ian Thompson Aug 22 '22 at 21:20