CODEWITHSUNDEEP
pythonpython-3.xpandas
SDROB
SDROB

Reputation: 125

Inserting Pandas Dataframe row to list, with headers for each value

I have a Dataframe being returned from a google trends API and contains values for date, keyword and search volume. I need to return a list of lists that will contain the following keyword, date 1, value 1, date 2, value 2, date 3, value 3, date n, value n...]

I have the following function that will take a set of keywords and send them to the API, then converts the returned dataframe to a list

def list_to_api(keyword_list):

    (pytrends.build_payload(keyword_list, cat=0, timeframe='today 12-m', geo='', gprop=''))
    df = (pytrends.interest_over_time())
    google_data_list = df.values.tolist()
    print(type(google_data_list))
    print("Resting 5 seconds for next API Call")
    print("Converted to  list ")
    insert_list.append(google_data_list)

The following screenshot1 shows what the output looks like as a dataframe

dataframe

That gives the list output [[[1, 93, 29, 7, 0, False], [1, 95, 31, 8, 0, False], [1, 91, 31, 8, 0, False], [1, 93, 34, 7, 0, False], [1, 96, 32, 8, 0, False]

I have transposed the dataframe by updating these two lines 

df = (pytrends.interest_over_time())
google_data_list = df_.values.tolist()

to 

df_new = df.transpose()
google_data_list = df_new.values.tolist()

Screenshot2 shows what this table looks like

df transposed and it which creates the list output for the first two values:

[[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1], 
[92, 94, 92, 94, 98, 100, 85, 87, 88, 87, 95, 89, 89, 93, 94, 88, 86, 87, 84,
 87, 82, 80, 81, 81, 76, 78, 78, 77, 73, 77, 76, 76, 79, 73, 87, 88, 91, 92, 88, 90, 
85, 88, 95, 94, 89, 91, 91, 91, 89, 85, 86]

So for the first example my desired output would be 

[0 balance transfer, date1, 1, date2, 1, date3, 1, dateN, 1...]

But I am struggling to take the date from the header and adding it alongside the corresponding value for the list. Any help much appreciated.

Upvotes: 0

Views: 296

Answers (1)

ilja
ilja

Reputation: 2682

Instead of transpose() and tolist() you could use a loop & list comprehension for e.g.

df = pd.DataFrame([[1, 93, 29, 7, 0, False], [1, 95, 31, 8, 0, False], [1, 91, 31, 8, 0, False], [1, 93, 34, 7, 0, False], [1, 96, 32, 8, 0, False]])
df.columns = ['0 balance transfer', 'car insurance', 'travel insurance', 'pet insurance', 'ww travel insurance', 'isPartial']
df.index = ['2018-05-06','2018-05-13','2018-05-20','2018-05-27','2018-06-03']

out =[]
for col in df:
    tmp = [col]
    [tmp.extend((date, value)) for date, value in zip(df[col].index, df[col])]
    out.append(tmp)

print(out)

>> [['0 balance transfer', '2018-05-06', 1, '2018-05-13', 1, '2018-05-20', 1, '2018-05-27', 1, '2018-06-03', 1], ['car insurance', '2018-05-06', 93, '2018-05-13', 95, '2018-05-20', 91, '2018-05-27', 93, '2018-06-03', 96], ['travel insurance', '2018-05-06', 29, '2018-05-13', 31, '2018-05-20', 31, '2018-05-27', 34, '2018-06-03', 32], ['pet insurance', '2018-05-06', 7, '2018-05-13', 8, '2018-05-20', 8, '2018-05-27', 7, '2018-06-03', 8], ['ww travel insurance', '2018-05-06', 0, '2018-05-13', 0, '2018-05-20', 0, '2018-05-27', 0, '2018-06-03', 0], ['isPartial', '2018-05-06', False, '2018-05-13', False, '2018-05-20', False, '2018-05-27', False, '2018-06-03', False]]

Edit based on comment (Drop isPartial column and filter Dates):

del df['isPartial']
out =[]
for col in df:
    tmp = [col]
    [tmp.extend((date, value)) for date, value in zip(df[col].index, df[col]) if date > '2018-05-15']
    out.append(tmp)

print(out)

Upvotes: 1

Related Questions