1

I have a situation where I need to create an excel sheet that takes all of the rows from the 1, 2, 3, 4, 5 columns and check if the string in its rows starts with "05"(each column at the time). Then, after it finds the rows, it will put them in a new data frame with the rows (All of the columns of the row). And lastly, it will delete those rows from the original data frame.

This is the code that I have, but when it tries to go to column id number 6, I get an error:

Traceback (most recent call last): File "/Users/Dd/PycharmProjects/mexcels/my_excel.py", line 19, in new_df = data.loc[data[idc].str.startswith("05", na=False)].copy() File "/Users/Dd/PycharmProjects/mexcels/venv/lib/python2.7/site-packages/pandas/core/frame.py", line 2927, in getitem indexer = self.columns.get_loc(key) File "/Users/Dd/PycharmProjects/mexcels/venv/lib/python2.7/site-packages/pandas/core/indexes/base.py", line 2659, in get_loc return self._engine.get_loc(self._maybe_cast_indexer(key)) File "pandas/_libs/index.pyx", line 108, in pandas._libs.index.IndexEngine.get_loc File "pandas/_libs/index.pyx", line 132, in pandas._libs.index.IndexEngine.get_loc File "pandas/_libs/hashtable_class_helper.pxi", line 1601, in pandas._libs.hashtable.PyObjectHashTable.get_item File "pandas/_libs/hashtable_class_helper.pxi", line 1608, in pandas._libs.hashtable.PyObjectHashTable.get_item KeyError: 6

Here is the code:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import csv
import os
import pandas as pd

data = pd.read_excel('my_excel.xlsx')

# Get columns names
data_top = data.head()

print(data)

# for col in data.columns:
#    print(col)
columns_to_check = [1, 2, 3, 4, 5]

for idc in columns_to_check:
    new_df = data.loc[data[idc].str.startswith("05", na=False)].copy()
    print new_df
    data = data.drop(data[data[idc].str.startswith("05", na=False)].index)
    # now data is without first phone column that contaigns mobile
    print data

This is the example of the original excel: enter image description here

And this is what I need: enter image description here

Any Ideas?

Dori
  • 331
  • 1
  • 6
  • 18
  • [https://stackoverflow.com/questions/11350770/select-by-partial-string-from-a-pandas-dataframe](https://stackoverflow.com/questions/11350770/select-by-partial-string-from-a-pandas-dataframe) try this. – Nik Jul 04 '21 at 17:14
  • From the image shown, it looks like you only have 5 columns, but you want to get the data from columns "5, 6, 7, 8, 9." Are there more columns that just aren't shown in the image provided? –  Jul 04 '21 at 17:50
  • Yes, It is just an example. I fixed the code example for it to be much more clear – Dori Jul 04 '21 at 17:59
  • You posted an error when your code _"tries to go to column id number 6"_, and it was _"KeyError: 6"_, but now you've changed the code, are you still getting an error ? Can you post the corresponding stack trace ? If you make changes like this, you should ensure that the text of the question, the stack trace, and the code remain consistent. – joao Jul 04 '21 at 19:07

1 Answers1

0

It was a Hebrew string that broke the code: The column title was in Hebrew. The solution was to add this:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import csv
import os
import sys
import pandas as pd

reload(sys)
sys.setdefaultencoding('utf-8')
Dori
  • 331
  • 1
  • 6
  • 18