Creating pandas dataframe out of several lists

Question

I am new to pandas and python.

I am trying to create a pandas DataFrame out of 7 lists. Each of the 7 lists has this structure:

[{'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzru7hs6V5gIVC', 'account_id': 85194250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - German', 'adgroup_name': 'bmm', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2276'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsL0mdmW5gIVjLT', 'account_id': 85994250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - French', 'adgroup_name': 'exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2056'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsfm8-qW5gIVibTtCh2Jx__D_BwE', 'account_id': 8593250, 'account_name': 'T2', 'campaign_name': 'Exact/Bmm - Italian', 'adgroup_name': 'vpn gratis | exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2380'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsj0o7GW5gID_BwE', 'account_id': 85931250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - Swedish', 'adgroup_name': 'exact/bmm', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2752'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobYASAAEgKx6fD_BwE', 'account_id': 854250, 'account_name': 'T2', 'campaign_name': 'Exact/BMM - Dutch', 'adgroup_name': 'vpn verbinding | exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2528'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobCVSrTtCh009QTtEAAYASAAEgLx9PD_BwE', 'account_id': 859350, 'account_name': 'T2', 'campaign_name': 'Exact/Bmm - German', 'adgroup_name': 'exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2276'}]
[{'date': '2019-12-02', 'gclid': 'EAIaIQobwefwefwfChMIzru7hs6V5gIVC', 'account_id': 85194250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - German', 'adgroup_name': 'bmm', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2276'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsL0mdmW5gIVjLT', 'account_id': 85994250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - French', 'adgroup_name': 'exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2056'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsfm8-qW5gIVibTtCh2Jx__D_BwE', 'account_id': 8593250, 'account_name': 'T2', 'campaign_name': 'Exact/Bmm - Italian', 'adgroup_name': 'vpn gratis | exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2380'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsj0o7GW5gID_BwE', 'account_id': 85931250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - Swedish', 'adgroup_name': 'exact/bmm', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2752'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobYASAAEgKx6fD_BwE', 'account_id': 854250, 'account_name': 'T2', 'campaign_name': 'Exact/BMM - Dutch', 'adgroup_name': 'vpn verbinding | exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2528'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobCVSrTtCh009QTtEAAYASAAEgLx9PD_BwE', 'account_id': 859350, 'account_name': 'T2', 'campaign_name': 'Exact/Bmm - German', 'adgroup_name': 'exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2276'}]
[{'date': '2019-12-02', 'gclid': 'EAIaIqdfwefwfChMIzru7hs6V5gIVC', 'account_id': 85194250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - German', 'adgroup_name': 'bmm', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2276'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsL0mdmW5gIVjLT', 'account_id': 85994250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - French', 'adgroup_name': 'exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2056'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsfm8-qW5gIVibTtCh2Jx__D_BwE', 'account_id': 8593250, 'account_name': 'T2', 'campaign_name': 'Exact/Bmm - Italian', 'adgroup_name': 'vpn gratis | exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2380'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsj0o7GW5gID_BwE', 'account_id': 85931250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - Swedish', 'adgroup_name': 'exact/bmm', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2752'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobYASAAEgKx6fD_BwE', 'account_id': 854250, 'account_name': 'T2', 'campaign_name': 'Exact/BMM - Dutch', 'adgroup_name': 'vpn verbinding | exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2528'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobCVSrTtCh009QTtEAAYASAAEgLx9PD_BwE', 'account_id': 859350, 'account_name': 'T2', 'campaign_name': 'Exact/Bmm - German', 'adgroup_name': 'exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2276'}]
...

There are 9 keys in each item in the list:

'date'
'gclid'
'account_id'
'account_name'
'campaign_name'
'adgroup_name'
'source'
'clicks'
'criteria_id_country'

I am trying to create a dataframe which would have these columns and would hold the values from those lists:

date   gclid   account_id   account_name   adgroup_name   source   clicks   criteria_id_country

I am collecting the data using this function:

client_accounts = [1,2,3,4,5,6,7]

def get_full_click_list(account, date):
    full_list = []
    for item in client_accounts:
        full_list.append(get_adwords_clicks(item, date))
    print(full_list)
get_full_click_list(client_accounts, '2019-12-02')

The outcome of my full_list has this structure:

[[items from 1st query],[items from 2nd query]...[items from 7th query]]

Each list of queries have this structure:

[items from the 1..7th queries] =

[{'date': '2019-12-02', 'gclid': 'EAIaIqdfwefwfChMIzru7hs6V5gIVC', 'account_id': 85194250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - German', 'adgroup_name': 'bmm', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2276'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsL0mdmW5gIVjLT', 'account_id': 85994250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - French', 'adgroup_name': 'exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2056'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsfm8-qW5gIVibTtCh2Jx__D_BwE', 'account_id': 8593250, 'account_name': 'T2', 'campaign_name': 'Exact/Bmm - Italian', 'adgroup_name': 'vpn gratis | exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2380'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobChMIzsj0o7GW5gID_BwE', 'account_id': 85931250, 'account_name': 'T2', 'campaign_name': 'Generic - Exact/Bmm - Swedish', 'adgroup_name': 'exact/bmm', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2752'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobYASAAEgKx6fD_BwE', 'account_id': 854250, 'account_name': 'T2', 'campaign_name': 'Exact/BMM - Dutch', 'adgroup_name': 'vpn verbinding | exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2528'}, {'date': '2019-12-02', 'gclid': 'EAIaIQobCVSrTtCh009QTtEAAYASAAEgLx9PD_BwE', 'account_id': 859350, 'account_name': 'T2', 'campaign_name': 'Exact/Bmm - German', 'adgroup_name': 'exact', 'source': 'adwords', 'clicks': 1, 'criteria_id_country': 'geoTargetConstants/2276'}

How would I move on forward trying to extract the information from my full_list? Or even how could I add information from each time I query my list to pandas dataframe?

Thank you for your suggestions.

Quang Hoang · Accepted Answer · 2019-12-02 15:03:50Z

2

You could do a concat:

ret_df = pd.concat(pd.DataFrame(lst) for lst in [lst1, lst2, lst3, ...])

Update: if you create the list one at a time, you can append to a given dataframe:

ret_df = None
for client in client_list:
    lst = get_full_click_list(client, date)
    if ret_df is None:
       ret_df = pd.DataFrame(client)
    else:
       ret_df = ret_df.append(pd.DataFrame(client))

edited Dec 2, 2019 at 15:03

answered Dec 2, 2019 at 14:43

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jonas Palačionis Over a year ago

I do not have all 7 lists at the same time, I query them one by one. How could I add your code to my code and concat the lists each time I get a list back?

Jonas Palačionis Over a year ago

I am getting an error before running the code - Assigning result of a function call, where the function has no return. Should I return full_list instead of printing it in the get_full_click_list function?

Quang Hoang Over a year ago

Yes, that's what it is.

Jonas Palačionis Over a year ago

Getting an error ret_df = pd.DataFrame(client),ValueError: DataFrame constructor not properly called!. I am not sure what pd.Dataframe(client) does.

Quang Hoang Over a year ago

Sorry, should replace that with pd.DataFrame(lst) in both occurrences. Note the capital F in DataFrame.

Nikaido · Accepted Answer · 2019-12-02 14:40:36Z

0

Simply do this:

pd.DataFrame(list_of_dicts)

where list_of_dicts is a list of every dict you have (basically the sum of the lists that you write above)

list_a = [{"key1":"value1", "key2":"value2"}]
list_b = [{"key1":"value1", "key2":"value2"}]
list_c = [{"key1":"value1", "key2":"value2"}]

list_of_dicts = list_a + list_b + list_c

pd.DataFrame(list_of_dicts)

answered Dec 2, 2019 at 14:40

Nikaido

4,6675 gold badges37 silver badges51 bronze badges

Comments

Nicolas Gervais · Accepted Answer · 2019-12-02 15:09:55Z

0

# output = your output

df = pd.concat([pd.DataFrame(i[0]) for i in output], axis=0, sort=False)

edited Dec 2, 2019 at 15:09

answered Dec 2, 2019 at 14:41

Nicolas Gervais

36.9k23 gold badges123 silver badges160 bronze badges

2 Comments

Jonas Palačionis Over a year ago

I do not have the lists, I get them each time I get a response from API. The problem is writing them to a dataframe each time I get a response in my for loop.

Jonas Palačionis Over a year ago

Hi, so my output is list of lists , [[list_one],[list_two]. Once I pass my full_list to your code I get this error - ValueError: If using all scalar values, you must pass an index

Collectives™ on Stack Overflow

Creating pandas dataframe out of several lists

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related