How to Add a list to a dataframe?

Question

I know this question has been asked before, but I've tried to implement all the solutions I've found and still haven't solved the problem.

Here is my code

import csv

import pandas as pd

import helperFunctions import pandas tickers = []

f = open("watchlist.txt", "r") for i in f:
    tickers.append((helperFunctions.getTicker(i)))

head = ["Name", "Growth", "Recommendation", "Earnings Growth", "Current Ratio",
        "Total Cash", "Debt", " Revenue", "Percentage Shares Out", "Percentage Institutions", "Percentage Insiders",
        "Price to Book", "Short Ratio", "Regular Market Price"] df = pd.DataFrame() df = pd.DataFrame(columns=head) try:
    for i in tickers:
        currentTicker = []
        currentTicker.append(i.info['longName'])
        currentTicker.append(i.info['revenueGrowth'])
        currentTicker.append(i.info['recommendationKey'])
        currentTicker.append(i.info['earningsGrowth'])
        currentTicker.append(i.info['currentRatio'])
        currentTicker.append(i.info['totalCash'])
        currentTicker.append(i.info['totalDebt'])
        currentTicker.append(i.info['totalRevenue'])
        currentTicker.append(i.info['sharesPercentSharesOut'])
        currentTicker.append(i.info['heldPercentInstitutions'])
        currentTicker.append(i.info['heldPercentInsiders'])
        currentTicker.append(i.info['priceToBook'])
        currentTicker.append(i.info['shortRatio'])
        currentTicker.append(i.info['beta'])
        currentTicker.append(i.info['regularMarketPrice'])
        print(str(currentTicker + "\n"))
        '''
        # Why Don't these work??
        1.
        df.append(currentTicker)
        
        2.
        df_length = len(df)
        df.loc[df_length] = currentTicker
        
        3.
        a_series = pd.Series(currentTicker, index=df.columns)
        df = df.append(a_series, ignore_index=True)
        
        '''

except Exception as e:
    print(str(i) + repr(e))

print(df)

In the section where you see comments in the list, those are all things things I've attempted to add each iteration to the dataframe. Basically the watchlist is a txtfile with some tickers, and I would like to get that data and put it into a dataframe and I'm running into some trouble. Thanks for your time.

Please provide a [reproducible **minimal** example](https://stackoverflow.com/q/20109391/8107362). Especially, provide some [sample data](https://stackoverflow.com/q/22418895/8107362), e.g. with `print(df.to_dict())`. — mnist, Nov 28 '21 at 15:36
The sample data is gotten from the txt file that looks like aapl msft tsla etc.. it will produce lists that look like this ['Apple Inc.', 0.288, 'buy', 0.662, 1.075, 62639001600, 136521998336, 365817004032, 0.0061000003, 0.58697, 0.00071000005, 40.825306, 1.5, 1.205714, 156.81] — , Nov 28 '21 at 15:41
that print statement produces this: {'Name': {}, 'Growth': {}, 'Recommendation': {}, 'Earnings Growth': {}, 'Current Ratio': {}, 'Total Cash': {}, 'Debt': {}, ' Revenue': {}, 'Percentage Shares Out': {}, 'Percentage Institutions': {}, 'Percentage Insiders': {}, 'Price to Book': {}, 'Short Ratio': {}, 'Regular Market Price': {}} — , Nov 28 '21 at 15:42
have you tried reading csv directly from pandas? https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html — Je Je, Nov 28 '21 at 15:43
Unfortunately I'm not reading it in as a csv, ultimately I'd like to send it out as a csv — , Nov 28 '21 at 15:44
would be good to have the actual data (the one you give seems wrong: would expect this format:[[...],[...]]. And you could try pd.DataFrame(data=your_data, columns=your_columns) — Je Je, Nov 28 '21 at 15:47

score 0 · Answer 1 · answered Nov 28 '21 at 16:16

This was how I ended up solving it

import csv
import pandas as pd
import helperFunctions
import pandas
tickers = []

f = open("watchlist.txt", "r")
for i in f:
    tickers.append((helperFunctions.getTicker(i)))

head = ["Name", "Growth", "Recommendation", "Earnings Growth", "Current Ratio",
        "Total Cash", "Debt", "Revenue", "Percentage Shares Out", "Percentage Institutions", "Percentage Insiders",
        "Price to Book", "Short Ratio", "Regular Market Price"]
df = pd.DataFrame(columns=head)
try:
    for i in tickers:
        currentTicker = []
        currentTicker.append(i.info['longName'])
        currentTicker.append(i.info['revenueGrowth'])
        currentTicker.append(i.info['recommendationKey'])
        currentTicker.append(i.info['earningsGrowth'])
        currentTicker.append(i.info['currentRatio'])
        currentTicker.append(i.info['totalCash'])
        currentTicker.append(i.info['totalDebt'])
        currentTicker.append(i.info['totalRevenue'])
        currentTicker.append(i.info['sharesPercentSharesOut'])
        currentTicker.append(i.info['heldPercentInstitutions'])
        currentTicker.append(i.info['heldPercentInsiders'])
        currentTicker.append(i.info['priceToBook'])
        currentTicker.append(i.info['shortRatio'])
        currentTicker.append(i.info['beta'])
        currentTicker.append(i.info['regularMarketPrice'])

        df = df.append({'Name': currentTicker[0],
                        'Growth': currentTicker[1],
                        "Recommendation":currentTicker[2],
                        "Earnings Growth":currentTicker[3],
                        "Current Ratio": currentTicker[4],
                        "Total Cash":currentTicker[5],
                        "Debt": currentTicker[6],
                        "Revenue": currentTicker[7],
                        "Percentage Shares Out": currentTicker[8],
                        "Percentage Institutions": currentTicker[9],
                        "Percentage Insiders": currentTicker[10],
                        "Price to Book": currentTicker[11],
                        "Short Ratio": currentTicker[12],
                        "Regular Market Price": currentTicker[13],
                        },
                       ignore_index=True)
        #print(currentTicker)
except Exception as e:
    print(str(i) + repr(e))

print(df)

score 0 · Answer 2 · answered Nov 28 '21 at 16:17

The issue in your code seems to be related to the fact that the currentTicker list has an extra column, namely beta, which is not present in the columns of the df.

Once you either add that column to head or remove it from currentTicker, method 2 and 3 will work.

import csv

import pandas as pd

import helperFunctions

with open("watchlist.txt", "r") as f:
    tickers = [helperFunctions.getTicker(i) for i in f]

head = [
    "Name",
    "Growth",
    "Recommendation",
    "Earnings Growth",
    "Current Ratio",
    "Total Cash",
    "Debt",
    "Revenue",
    "Percentage Shares Out",
    "Percentage Institutions",
    "Percentage Insiders",
    "Price to Book",
    "Short Ratio",
    "Regular Market Price"
]
df = pd.DataFrame(columns=head)

columns_to_extract = [
    'longName',
    'revenueGrowth',
    'recommendationKey',
    'earningsGrowth',
    'currentRatio',
    'totalCash',
    'totalDebt',
    'totalRevenue',
    'sharesPercentSharesOut',
    'heldPercentInstitutions',
    'heldPercentInsiders',
    'priceToBook',
    'shortRatio',
    # 'beta', # <- The column for this value is missing from `head`
    'regularMarketPrice'
]

currentTicker = [i.info[key] for key in columns_to_extract]

# 1. Method - THIS DOES NOT WORK
# df.append(currentTicker)

# 2. Method
df_length = len(df)
df.loc[df_length] = currentTicker

# 3. Method
a_series = pd.Series(currentTicker, index=df.columns)
df = df.append(a_series, ignore_index=True)

print(df)

How to Add a list to a dataframe?

2 Answers2