CODEWITHSUNDEEP
pythonstringpandasdataframe
joshsol
joshsol

Reputation: 61

Pandas: How to convert string to DataFrame

Hi I have the following data (string) and am struggling to convert it into a pandas dataframe.

Any help would be greatly appreciated!

pd.DataFrame with "," as the delim doesnt work given the commas else where in the data. 

[["Time","Forecast"],["2019-07-08T23:00:00Z",20],["2019-07-08T23:30:00Z",26],["2019-07-09T00:00:00Z",24],["2019-07-09T00:30:00Z",26]]

Upvotes: 2

Views: 391

Answers (3)

alec_djinn
alec_djinn

Reputation: 10819

You can make a proper dictionary out of your data and make a df with it. 

>>> import pandas as pd
>>> from collections import defaultdict
>>> data = [["Time","Forecast"],["2019-07-08T23:00:00Z",20],["2019-07-08T23:30:00Z",26],["2019-07-09T00:00:00Z",24],["2019-07-09T00:30:00Z",26]]
>>> columns = data[0]
>>> rows = data[1:]
>>> d = defaultdict(list)
>>> for item in rows:
...     d[columns[0]].append(item[0])
...     d[columns[1]].append(item[1])
...
>>> df = pd.DataFrame(d)
>>> df
                   Time  Forecast
0  2019-07-08T23:00:00Z        20
1  2019-07-08T23:30:00Z        26
2  2019-07-09T00:00:00Z        24
3  2019-07-09T00:30:00Z        26
>>>

Upvotes: 0

frankegoesdown
frankegoesdown

Reputation: 1924

import pandas as pd
from collections import defaultdict 

lst = [["Time","Forecast"],["2019-07-08T23:00:00Z",20],["2019-07-08T23:30:00Z",26],["2019-07-09T00:00:00Z",24],["2019-07-09T00:30:00Z",26]]
map = defaultdict(list) 
keys = lst[0] 
for i, el in enumerate(lst): 
    if i != 0: 
        map[keys[0]].append(el[0]) 
        map[keys[1]].append(el[1]) 

pd.DataFrame(map)                                                                                                                                                                                                                                    

   Forecast                  Time
0        20  2019-07-08T23:00:00Z
1        26  2019-07-08T23:30:00Z
2        24  2019-07-09T00:00:00Z
3        26  2019-07-09T00:30:00Z

Upvotes: 0

anky
anky

Reputation: 75130

IIUC, you can use ast.literal_eval:

s='[["Time","Forecast"],["2019-07-08T23:00:00Z",20],["2019-07-08T23:30:00Z",26],["2019-07-09T00:00:00Z",24],["2019-07-09T00:30:00Z",26]]'
l=ast.literal_eval(s) #convert to actual list of list
df=pd.DataFrame(l[1:],columns=l[0])

                   Time  Forecast
0  2019-07-08T23:00:00Z        20
1  2019-07-08T23:30:00Z        26
2  2019-07-09T00:00:00Z        24
3  2019-07-09T00:30:00Z        26

Upvotes: 3

Related Questions