Convert dictionary to pandas data frame-CodePudding

I am running a script to analyze find-out the related keywords search based on keywords using google pytrend library. But the output i am getting, i want to convert it as a pandas data frame, i tried it several way, but not works. csv file is empty after export.

import pandas as pd                        
from pytrends.request import TrendReq
pytrend = TrendReq()
pytrend.build_payload(kw_list=['AUDI','corona'], cat=0, timeframe='2021-03-01 2022-03-14',geo="TH")
related_queries = pytrend.related_queries()
related_queries.values()

#Covert to pandas dataframe
file1 = pd.DataFrame.from_dict(related_queries)
file1.to_csv('file2.csv' , index = True)

#Related query output
dict_values([{'top':             query  value
0       audi ราคา    100
1         audi tt     55
2         audi a5     51
3              a5     50
4         audi a4     26
5    audi มือ สอง     24
6         รถ audi     22
7             bmw     18
8         audi a6     17
9         audi q3     16
10        audi a3     14
11  audi thailand     14
12        audi q5     13
13        audi rs     13
14    audi e tron     11
15           benz     11
16        audi q7     10
17        audi a7     10
18   audi a5 ราคา     10
19   ราคา audi r8      9
20        audi q8      9
21   ราคา รถ audi      9
22   audi tt ราคา      8
23       audi car      8
24        audi a8      8, 'rising':                   query  value
0          audi wong ig  10000`}])`

CodePudding user response：

Possible solution is the following:

import pandas as pd                        
from pytrends.request import TrendReq

pytrend = TrendReq()

pytrend.build_payload(kw_list=['AUDI','corona'], cat=0, timeframe='2021-03-01 2022-03-14', geo="TH")

#get related queries
related_queries = pytrend.related_queries()
related_queries.values()

alldata = []

for i in range(len(list(related_queries.values()))):
    #build lists dataframes
    top = list(related_queries.values())[i]['top']
    rising = list(related_queries.values())[i]['rising']

    #convert lists to dataframes
    dftop = pd.DataFrame(top)
    dfrising = pd.DataFrame(rising)
    
    alldata.append(dftop)
    alldata.append(dfrising)

#join two data frames
allqueries = pd.concat(alldata, axis=0)

allqueries.reset_index(drop=True, inplace=True)

allqueries.to_csv('file.csv', index = True)

The example of allqueries dataframe presented below: