0

I'm working with Data-frames in Python and trying to insert data in MySQL Database. I know I can use df.to_sql to insert dataframe into sql but in my case, I've a list in one of the cells of dataframe and I've to insert that list into multiple columns of table in database.

Here is my dataframe:

UserId UserName Address Res
1      User1    Add1    [1,2,3...36]
2      User2    Add2    [1,2,3...36]

This is my database table structure:

UserId UserName Address Res1 Res2 Res3... Res36

Now I'm stuck here that how can I insert Res data into multiple columns in an efficient way? List data is in sequence with column names so I don't have to worry about correct col name/ data.

Any guesses??

Naila Akbar
  • 3,033
  • 4
  • 34
  • 76
  • Did you try storing list items to string variables? – Vishal Upadhyay May 01 '20 at 09:48
  • No, I didn't.. store them as a separate string variable?? – Naila Akbar May 01 '20 at 10:05
  • Can you be more specific about what the issue is? Are you just asking how to split the sequences into their own columns? – AMC May 01 '20 at 10:45
  • Does this answer your question? [Pandas split column of lists into multiple columns](https://stackoverflow.com/questions/35491274/pandas-split-column-of-lists-into-multiple-columns) – AMC May 01 '20 at 11:05

1 Answers1

2

You can split your list column to multiple columns and then use df.to_sql on the new dataframe:

In [971]: df                                                                                                                                                                                                
Out[971]: 
   UserId UserName Address           Res
0       1    User1    Add1  [1, 2, 3, 4]
1       2    User2    Add2  [2, 3, 4, 5]

In [973]: pd.concat([df, pd.DataFrame(df['Res'].values.tolist())], axis=1).drop('Res', 1)                                                                                                                   
Out[973]: 
   UserId UserName Address  0  1  2  3
0       1    User1    Add1  1  2  3  4
1       2    User2    Add2  2  3  4  5
Mayank Porwal
  • 33,470
  • 8
  • 37
  • 58
  • https://stackoverflow.com/questions/35491274/pandas-split-column-of-lists-into-multiple-columns – AMC May 01 '20 at 11:05