2

I have a list abc as below. I would like to convert it to a table as I need to write this data to a file (CSV or any other delimiter) and then open that file in Excel.

The columns in that table will be company, source, time, link, title and for the first element in the list, the first row will have values - 

intel corporation, 
Motely Fool, 
4 hours ago,
http://www.fool.com/investing/general/2016/05/09/intel-corporations-strange-choice.aspx,
Intel Corporation's Strange Choice

How could I achieve the same? abc has thousands of elements

abc=[{'intel corporation': {'source': u'Motley Fool', 'time': u'4 hours ago', 'link': u'http://www.fool.com/investing/general/2016/05/09/intel-corporations-strange-choice.aspx', 'title': u"Intel Corporation's Strange Choice"}}, {'intel corporation': {'source': u'Seeking Alpha', 'time': u'33 minutes ago', 'link': u'http://seekingalpha.com/article/3973271-intels-augmented-reality-plans-coming-focus', 'title': u"Intel's Augmented Reality Plans Are Coming Into Focus"}}, {'intel corporation': {'source': u'Seeking Alpha', 'time': u'7 hours ago', 'link': u'http://seekingalpha.com/article/3973098-intel-consider-debt-large-buyback', 'title': u'Should Intel Consider More Debt For A Large Buyback?'}}, {'intel corporation': {'source': u'Schaeffers Research (blog)', 'time': u'9 hours ago', 'link': u'http://www.schaeffersresearch.com/content/news/2016/05/09/analyst-downgrades-apple-inc-berkshire-hathaway-inc-and-intel-corporation', 'title': u'Analyst Downgrades: Apple Inc., Berkshire Hathaway Inc., and Intel ...'}}, {'intel corporation': {'source': u'Money News (press release)', 'time': u'Money News (press release)-12 hours ago Explore in depth (11 more articles)', 'link': u'http://www.newsismoney.com/2016/05/09/shares-of-tech-companies-intel-corporation-nasdaqintc-microsoft-corporation-nasdaqmsft-news-update/', 'title': u'Shares of Tech Companies Intel Corporation (NASDAQ:INTC ...'}}, {'intel corporation': {'source': u'Motley Fool', 'time': u'May 6, 2016', 'link': u'http://www.fool.com/investing/general/2016/05/06/why-intel-corporation-quit-smartphones-to-focus-on.aspx', 'title': u'Why Intel Corporation Quit Smartphones to Focus on 5G'}}, {'intel corporation': {'source': u'Motley Fool', 'time': u'May 6, 2016', 'link': u'http://www.fool.com/investing/general/2016/05/06/dont-blame-x86-for-intel-corporations-smartphone-f.aspx', 'title': u"Don't Blame X86 for Intel Corporation's Smartphone Failure"}}, {'intel corporation': {'source': u'Street Updates', 'time': u'11 hours ago', 'link': u'http://www.streetupdates.com/2016/05/09/valuable-analysts-trends-to-observe-intel-corporation-nasdaqintc-taiwan-semiconductor-manufacturing-company-ltd-nysetsm/', 'title': u'Valuable Analysts Trends to Observe: Intel Corporation (NASDAQ ...'}}, {'intel corporation': {'source': u'Motley Fool', 'time': u'May 5, 2016', 'link': u'http://www.fool.com/investing/general/2016/05/05/intel-corporation-its-time-to-replace-ceo-brian-kr.aspx', 'title': u"Intel Corporation: It's Time to Replace CEO Brian Krzanich"}}, {'intel corporation': {'source': u'Amigobulls', 'time': u'Amigobulls-May 5, 2016 Explore in depth (10 more articles)', 'link': u'http://amigobulls.com/articles/intel-corporations-mobile-exit-could-drive-long-term-gains', 'title': u"Intel Corporation's Mobile Exit Could Drive Long-Term Gains"}}, {'intel corporation': {'source': u'Seneca Globe', 'time': u'7 hours ago', 'link': u'http://www.senecaglobe.com/intel-corporation-nasdaqintc-not-great-time-continues-downward-trend-trina-solar-nysetsl/323986/', 'title': u'Intel Corporation (NASDAQ:INTC) Has Not Been Having A Great ...'}}, {'intel corporation': {'source': u'The News Journal', 'time': u'19 minutes ago', 'link': u'http://news4j.com/ruling-stocks-in-todays-market-intel-corporation-nasdaqintc-5/', 'title': u"Ruling stocks in today's market: Intel Corporation (NASDAQ:INTC)"}}]
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user2543622
  • 5,760
  • 25
  • 91
  • 159
  • csv or any format table that i can open in excel – user2543622 May 09 '16 at 22:48
  • `Pandas` is a very useful library for this kind of work, but it's difficult to answer without additional info. In particular, it has great I/O methods to get a data frame into csv and many other formats (all easily named as in `.to_csv`). How does your data look to begin with? – jdg May 09 '16 at 22:55

2 Answers2

5
import csv
import sys    # sys.setdefaultencoding is cancelled by site.py
reload(sys)    # to re-enable sys.setdefaultencoding()
sys.setdefaultencoding('utf-8')

with open('data.csv', 'wb') as out:
    writer = csv.DictWriter(out, ['company', 'source', 'time', 'link', 'title'])
    writer.writeheader()

    for thing in abc:
        for company, details in thing.items():
            details['company'] = company
            writer.writerow(details)
PyNEwbie
  • 4,882
  • 4
  • 38
  • 86
Alex Hall
  • 34,833
  • 5
  • 57
  • 89
  • any idea why I am getting alternate blank rows? Also I got `UnicodeEncodeError` `UnicodeEncodeError: 'ascii' codec can't encode character u'\xf3' in position 7: ordinal not in range(128) ` Any idea how I could resolve it? - I have multiple values for company name such as "intel corporation", "intel corporation10", "intel corporation20", "intel corporation30" etc – user2543622 May 09 '16 at 23:47
  • 1
    I changed Alex's code for the file to 'wb' that will handle the alternating blank rows. You have a unicode problem and that is why you have the ascii issue - there are some q&as here on that issue – PyNEwbie May 10 '16 at 00:13
  • i need help only with alternate blank rows. I was able to resolve other error issues using `http://stackoverflow.com/questions/13485546/converting-unicode-to-in-python` – user2543622 May 10 '16 at 00:14
  • Please add `import sys # sys.setdefaultencoding is cancelled by site.py` `reload(sys) # to re-enable sys.setdefaultencoding()` `sys.setdefaultencoding('utf-8')` to you import statement as it helps to debug unicode issues and I will accept your answer. Thanks – user2543622 May 10 '16 at 01:17
  • You should accept his answer anyway as it solved the problem you asked about - the encoding issue is a different problem. – PyNEwbie May 10 '16 at 01:27
  • @PyNEwbie I agree. But i want future readers to have the best possible answer. Would it be possible to change `with open('data.csv', 'w')` to `with open('data.csv', 'wb')`? – user2543622 May 10 '16 at 02:22
1

I assume you are talking about a table like an excel spreadsheet. If so then you can take a look at the pandas package http://pandas.pydata.org/

After you install the package then you can loop over the items in your list and then iteratively insert them into a Pandas DataFrame(see the pandas link). Also take a look look at Convert Python dict into a dataframe it has some info that can help you out.

If you need to you can then export the Dataframe object to .xlsx or csv formats.

Community
  • 1
  • 1
Nitin Kashyap
  • 184
  • 1
  • 1
  • 13
  • I tried `pd.DataFrame(abc)` but it didnt create a table in the way i want. The table had only one column of name "intel corporation". But I want that to be a column and the only column that the table has needs to be split into 4 different columns – user2543622 May 09 '16 at 23:05
  • You need to iteratively add the elements of your list to a dataframe. The pd.DataFrame(abc) works for a dict and not a list. HTH. – Nitin Kashyap May 09 '16 at 23:13