Use sep=r'\s*,\s*' to parse a file where the columns may have some number of spaces preceding or following the delimiter (e.g. , ):
transactions = pd.read_csv('transactions.csv', sep=r'\s*,\s*',
header=0, encoding='ascii', engine='python')
Prove:
print(transactions.columns)
Output:
Index(['product_id', 'customer_id', 'store_id', 'promotion_id', 'month_of_year', 'quarter', 'the_year', 'store_sales', 'store_cost', 'unit_sales', 'fact_count'], dtype='object')
Alternatively, remove unquoted spaces in the CSV file, and use your command (unchanged).
Answer from MaxU - stand with Ukraine on Stack OverflowUse sep=r'\s*,\s*' to parse a file where the columns may have some number of spaces preceding or following the delimiter (e.g. , ):
transactions = pd.read_csv('transactions.csv', sep=r'\s*,\s*',
header=0, encoding='ascii', engine='python')
Prove:
print(transactions.columns)
Output:
Index(['product_id', 'customer_id', 'store_id', 'promotion_id', 'month_of_year', 'quarter', 'the_year', 'store_sales', 'store_cost', 'unit_sales', 'fact_count'], dtype='object')
Alternatively, remove unquoted spaces in the CSV file, and use your command (unchanged).
There are extra initial white spaces in your CSV file, e.g. , customer_id, store_id, promotion_id, ...
To prove this, try print(list(df.columns)) and look at the names of the columns:
>>> print(list(df.columns))
['product_id', ' customer_id', ' store_id', ' promotion_id', ...]
Solution
The direct way to solve this is to add the parameter skipinitialspace in pd.read_csv(), for example:
pd.read_csv('transactions.csv',
sep=',',
skipinitialspace=True)
Reference: TheGrimmScientist's answer on "How can I remove extra whitespace from strings when parsing a csv file in Pandas?"
Dataframe output Key Error
Key error in pandas
python - Pandas - Getting a Key Error when the Key Exists - Stack Overflow
Help with understanding why Pandas is KeyError-ing
What is the exact problem with the given line of code throwing key error
Your Trades dataframe has a single column with all the intended column names mashed together into a single string. Check the code that parses your file.
Make sure you read your file with the right seperation.
df = pd.read_csv("file.csv", sep=';')
or
df = pd.read_csv("file.csv", sep=',')
Hi all, I'm trying my best to grok this and it's just not working.
I'm following along with a tutorial and am hitting a problem with a Pandas append.
Here's the code blob where it's breaking:
def chunks(lst, n):
"""Yield successive n-sized chunks from lst."""
for i in range(0, len(lst), n):
yield lst[i:i + n]
my_columns = ['Ticker','Stock Price', 'Market Capitalization', 'Number of Shares to Buy']
symbol_groups = list(chunks(stocks['Ticker'], 100))
symbol_strings = []
final_dataframe = pd.DataFrame(columns = my_columns)
for symbol_string in symbol_strings:
batch_api_call_url = f'https://sandbox.iexapis.com/stable/stock/market/batch?symbols={symbol_string}&types=quote&token={IEX_CLOUD_API_TOKEN}'
data = requests.get(batch_api_call_url).json()
for symbol in symbol_string.split(','):
final_dataframe = final_dataframe.append(
pd.Series(
[symbol,
data[symbol]['quote']['latestPrice'],
data[symbol]['quote']['marketCap'],
'N/A'],
index = my_columns),
ignore_index=True)
final_dataframeCalling the dataframe gives me a KeyError: 'DISCA' pointing at the call to the data[symbol]['quote']['latestPrice'].
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
C:\Users\blahblahblah/ipykernel_19420/1231875517.py in <module>
15 pd.Series(
16 [symbol,
---> 17 data[symbol]['quote']['latestPrice'],
18 data[symbol]['quote']['marketCap'],
19 'N/A'],
KeyError: 'DISCA'This seems wonky to me, because I can print(data[symbol]['quote']['latestPrice']) and see the results no problem. I can print(data[symbol]) and get all my symbols no problem... I know the data is there, but Pandas can't seem to append it in for some reason.
I know it's a simple fix because I was stupidly doing edits and got it working, then backtracked through a bunch of changes to see what did it and broke it again, coz I'm dumb.
Edit: Also, the 'solved' workbook has the same issue - so I can't just go compare code for the fix. =
Edit2: Ok, backtracing the problem some more:
for symbol_string in symbol_strings:
batch_api_call_url = f'https://sandbox.iexapis.com/stable/stock/market/batch/?types=quote&symbols={symbol_string}&token={IEX_CLOUD_API_TOKEN}'
data = requests.get(batch_api_call_url).json()
print(data['A'])This will print 'A' and all of it's data, however it will still throw a keyerror out:
---------------------------------------------------------------------------
KeyError Traceback (most recent call last)
C:\Users\blahblahblah/ipykernel_11588/3831869600.py in <module>
12 data = requests.get(batch_api_call_url).json()
13 #print(data[A])
---> 14 print(data['A'])
KeyError: 'A'Sooo this is happening above this part of the blob... I think?
Edit3:
u/Available_Net_9341 has found the answer - delisted tickers that were breaking the for loop.
Ensure that the columns actually exist in the dataframe. For example, you have written CUTOMER and not CUSTOMER, which I assume is the correct name.
You can verify the column names by using list(origdf.columns.values).
And for when you don't have a typo problem, here is a solution:
Use loc instead,
subdf= origdf.loc[:, ['CUSTOMER_ID','ASSET_BAL']].values
(I'd be glad to learn why this one works, though.)
The expression 'Restaurants' in businesses['categories'] returns the boolean value False. This is passed to the brackets indexing operator for the DataFrame businesses which does not contain a column called False and thus raises a KeyError.
What you are looking to do is something called boolean indexing which works like this.
businesses[businesses['categories'] == 'Restaurants']
The reason for this is that the Series class implements a custom in operator that doesn't return an iterable like the == does, here's a workaround
businesses[['Restaurants' in c for c in list(businesses['categories'])]]
hopefully this helps someone where you're looking for a substring in the column and not a full match.