Pandas more time efficient flatten

Question

In this question I got help with flatten of each row in a column of a dataframe.

[{'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137417352000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137417352000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}]
[{'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137166688000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137166688000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}]
[{'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137288213000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137288213000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}]
[{'first_open_time': {'int_value': '1653195600000', 'set_timestamp_micros': '1653193960416000'}}]
[{'ga_session_number': {'int_value': '3', 'set_timestamp_micros': '1653165977727000'}}, {'User_activity': {'string_value': '1_10', 'set_timestamp_micros': '1653109414730000'}}, {'Minutes_in_app': {'string_value': '1_10', 'set_timestamp_micros': '1653109414735000'}}, {'first_open_time': {'int_value': '1653102000000', 'set_timestamp_micros': '1653098744032000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653109414734000'}}, {'ga_session_id': {'int_value': '1653165977', 'set_timestamp_micros': '1653165977727000'}}]

The code is

    df = pd.DataFrame()

for d in data:
    df_tmp = pd.json_normalize(d)
    row = pd.DataFrame(df_tmp.to_numpy().flatten()).T.dropna(axis=1)
    row.columns = df_tmp.columns
    df = pd.concat([df, row])

print(df.reset_index(drop=True))

The code turns data into

  first_open_time.int_value first_open_time.set_timestamp_micros User_dedication.string_value  ... Paying_user.set_timestamp_micros ga_session_id.int_value ga_session_id.set_timestamp_micros
0             1652796000000                     1652792823456000                            1  ...                 1653136561498000              1653136552                   1653136552555000
1             1652796000000                     1652792823456000                            1  ...                 1653136561498000              1653136552                   1653136552555000
2             1652796000000                     1652792823456000                            1  ...                 1653136561498000              1653136552                   1653136552555000
3             1653195600000                     1653193960416000                          NaN  ...                              NaN                     NaN                                NaN
4             1653102000000                     1653098744032000                            1  ...                              NaN              1653165977                   1653165977727000

This code works good but takes enormous amount of time. To go over 1 column of 1 day of data (660k rows) it took about 10 hours. And there are 2 such columns. Means python should work 24/7.

Is there a way to rewrite it to make more time efficient?

P.S. Also, to make that column I used this code:

    def transformation_user_properties(row):
                return [{elem['key']: elem['value']} for elem in row['user_properties']]
    df['user_properties'] = df.apply(transformation_user_properties, axis=1)

Which transform colums with rows of this form

[{'a': 'b', 'c': {'c1': 'v1', 'c2': 'v2'}}, {'a': 'b1', 'c': {'c1': 'x1', 'c2': 'x2'}}, {'a': 'b2', 'c': {'c1': 'n1', 'c2': 'n2'}}]

into this form

[{'b': {'c1': 'v1', 'c2': 'v2'}}, {'b1': {'c1': 'x1', 'c2': 'x2'}}, {'b2': {'c1': 'n1', 'c2': 'n2'}}]

Answer 1

You can use nested list and dict comprehension:

data = [[{'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137417352000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137417352000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}],
[{'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137166688000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137166688000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}],
[{'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137288213000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137288213000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}],
[{'first_open_time': {'int_value': '1653195600000', 'set_timestamp_micros': '1653193960416000'}}],
[{'ga_session_number': {'int_value': '3', 'set_timestamp_micros': '1653165977727000'}}, {'User_activity': {'string_value': '1_10', 'set_timestamp_micros': '1653109414730000'}}, {'Minutes_in_app': {'string_value': '1_10', 'set_timestamp_micros': '1653109414735000'}}, {'first_open_time': {'int_value': '1653102000000', 'set_timestamp_micros': '1653098744032000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653109414734000'}}, {'ga_session_id': {'int_value': '1653165977', 'set_timestamp_micros': '1653165977727000'}}]
]

---

L = [{f'{k}.{k1}': v1 for y in x 
                      for k, v in y.items()
                      for k1, v1 in v.items()} 
                      for x in data] 

df = pd.DataFrame(L)  

---

print (df)

  first_open_time.int_value first_open_time.set_timestamp_micros  \
0             1652796000000                     1652792823456000   
1             1652796000000                     1652792823456000   
2             1652796000000                     1652792823456000   
3             1653195600000                     1653193960416000   
4             1653102000000                     1653098744032000   

  User_dedication.string_value User_dedication.set_timestamp_micros  \
0                            1                     1653137417352000   
1                            1                     1653137166688000   
2                            1                     1653137288213000   
3                          NaN                                  NaN   
4                            1                     1653109414734000   

  User_activity.string_value User_activity.set_timestamp_micros  \
0                          1                   1653136561498000   
1                          1                   1653136561498000   
2                          1                   1653136561498000   
3                        NaN                                NaN   
4                       1_10                   1653109414730000   

  Minutes_in_app.string_value Minutes_in_app.set_timestamp_micros  \
0                      60_300                    1653137417352000   
1                      60_300                    1653137166688000   
2                      60_300                    1653137288213000   
3                         NaN                                 NaN   
4                        1_10                    1653109414735000   

  ga_session_number.int_value ga_session_number.set_timestamp_micros  \
0                          10                       1653136552555000   
1                          10                       1653136552555000   
2                          10                       1653136552555000   
3                         NaN                                    NaN   
4                           3                       1653165977727000   

  Paying_user.string_value Paying_user.set_timestamp_micros  \
0                        0                 1653136561498000   
1                        0                 1653136561498000   
2                        0                 1653136561498000   
3                      NaN                              NaN   
4                      NaN                              NaN   

  ga_session_id.int_value ga_session_id.set_timestamp_micros  
0              1653136552                   1653136552555000  
1              1653136552                   1653136552555000  
2              1653136552                   1653136552555000  
3                     NaN                                NaN  
4              1653165977                   1653165977727000  

---

Performance for 5k values:

data = [[{'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137417352000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137417352000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}],
[{'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137166688000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137166688000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}],
[{'Minutes_in_app': {'string_value': '60_300', 'set_timestamp_micros': '1653137288213000'}}, {'Paying_user': {'string_value': '0', 'set_timestamp_micros': '1653136561498000'}}, {'first_open_time': {'int_value': '1652796000000', 'set_timestamp_micros': '1652792823456000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653137288213000'}}, {'User_activity': {'string_value': '1', 'set_timestamp_micros': '1653136561498000'}}, {'ga_session_number': {'int_value': '10', 'set_timestamp_micros': '1653136552555000'}}, {'ga_session_id': {'int_value': '1653136552', 'set_timestamp_micros': '1653136552555000'}}],
[{'first_open_time': {'int_value': '1653195600000', 'set_timestamp_micros': '1653193960416000'}}],
[{'ga_session_number': {'int_value': '3', 'set_timestamp_micros': '1653165977727000'}}, {'User_activity': {'string_value': '1_10', 'set_timestamp_micros': '1653109414730000'}}, {'Minutes_in_app': {'string_value': '1_10', 'set_timestamp_micros': '1653109414735000'}}, {'first_open_time': {'int_value': '1653102000000', 'set_timestamp_micros': '1653098744032000'}}, {'User_dedication': {'string_value': '1', 'set_timestamp_micros': '1653109414734000'}}, {'ga_session_id': {'int_value': '1653165977', 'set_timestamp_micros': '1653165977727000'}}]
]*1000

---

In [92]: %%timeit
    ...: df = pd.DataFrame()
    ...: 
    ...: for d in data:
    ...:     df_tmp = pd.json_normalize(d)
    ...:     row = pd.DataFrame(df_tmp.to_numpy().flatten()).T.dropna(axis=1)
    ...:     row.columns = df_tmp.columns
    ...:     df = pd.concat([df, row])
    ...: 
    ...: df.reset_index(drop=True)
    ...: 
    ...: 
36.9 s ± 1.87 s per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [93]: %%timeit
    ...: L = [{f'{k}.{k1}': v1  
    ...:        for y in x 
    ...:        for k, v in y.items()
    ...:        for k1, v1 in v.items()} for x in data] 
    ...: 
    ...: pd.DataFrame(L)
    ...: 
    ...: 

40.8 ms ± 1.24 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)