Convert Pandas Dataframe Of Lists To Dict Of Dataframes

I have a dataframe (with a DateTime index) , in which some of the columns contain lists, each with 6 elements. In: dframe.head() Out: A

Solution 1:

You can use dict comprehension with assign and for select values of lists use str[0], str[1]:

N=6dfs= {i:df.assign(B=df['B'].str[i-1], C=df['C'].str[i-1])foriinrange(1,N+1)}

print(dfs[1])timestampABCD02017-05-01 00:32:25  30-35121104  49.9312017-05-01 00:32:55  30-35191106  49.9422017-05-01 00:33:25  30-35141105  49.9132017-05-01 00:33:55  30-35171105  49.9142017-05-01 00:34:25  30-35151104  49.94

Another solution:

dfs= {i:df.apply(lambdax:x.str[i-1] iftype(x.iat[0])==listelsex)foriinrange(1,7)}

print(dfs[1])timestampABCD02017-05-01 00:32:25  30-35121104  49.9312017-05-01 00:32:55  30-35191106  49.9422017-05-01 00:33:25  30-35141105  49.9132017-05-01 00:33:55  30-35171105  49.9142017-05-01 00:34:25  30-35151104  49.94

Timings:

df = pd.concat([df]*10000).reset_index(drop=True)

In [185]: %timeit {i:df.assign(B=df['B'].str[i-1], C=df['C'].str[i-1]) foriinrange(1,N+1)}
1loop, best of 3: 420 ms per loop

In [186]: %timeit {i:df.apply(lambda x: x.str[i-1] iftype(x.iat[0]) == list else x) foriinrange(1,7)}
1loop, best of 3: 447 ms per loop

In [187]: %timeit {(i+1):df.applymap(lambda x: x[i] iftype(x) == list else x) foriinrange(6)}
1loop, best of 3: 881 ms per loop

Solution 2:

Setup

df=pd.DataFrame({'A': {'2017-05-01 00:32:25':30,
  '2017-05-01 00:32:55':30,
  '2017-05-01 00:33:25':30,
  '2017-05-01 00:33:55':30,
  '2017-05-01 00:34:25':30},'B': {'2017-05-01 00:32:25': [-3512, 375, -1025, -358, -1296, -4019],
  '2017-05-01 00:32:55': [-3519, 372, -1026, -361, -1302, -4020],
  '2017-05-01 00:33:25': [-3514, 371, -1026, -360, -1297, -4018],
  '2017-05-01 00:33:55': [-3517, 377, -1030, -363, -1293, -4027],
  '2017-05-01 00:34:25': [-3515, 372, -1033, -361, -1299, -4025]},'C': {'2017-05-01 00:32:25': [1104, 1643, 625, 1374, 5414, 2066],
  '2017-05-01 00:32:55': [1106, 1643, 622, 1385, 5441, 2074],
  '2017-05-01 00:33:25': [1105, 1643, 623, 1373, 5445, 2074],
  '2017-05-01 00:33:55': [1105, 1646, 620, 1384, 5438, 2076],
  '2017-05-01 00:34:25': [1104, 1645, 613, 1374, 5431, 2082]},'D': {'2017-05-01 00:32:25':49.93,
  '2017-05-01 00:32:55':49.94,
  '2017-05-01 00:33:25':49.1,
  '2017-05-01 00:33:55':49.91,
  '2017-05-01 00:34:25':49.94}})

Solution

Construct the df dict using dict comprehension. The sub df is generated using the applymap function. It can convert all columns with a list of 6 elements:

dict_of_dfs= {(i+1):df.applymap(lambdax:x[i] iftype(x)==listelsex)foriinrange(6)}

print(dict_of_dfs[1])ABCD2017-05-01 00:32:25  30-35121104  49.932017-05-01 00:32:55  30-35191106  49.942017-05-01 00:33:25  30-35141105  49.102017-05-01 00:33:55  30-35171105  49.912017-05-01 00:34:25  30-35151104  49.94print(dict_of_dfs[2])ABCD2017-05-01 00:32:25  303751643  49.932017-05-01 00:32:55  303721643  49.942017-05-01 00:33:25  303711643  49.102017-05-01 00:33:55  303771646  49.912017-05-01 00:34:25  303721645  49.94