0

I have a Panda Dataframe where first column contain Companies name with unique code, I want to split the string from the first space and keep right part of split but if string doesn't have any space either drop that row(preferred) or skip.

file = pd.ExcelFile("MIS REPORT.xlsx")
items = file.sheet_names
    
#loading different excel tabs
for i in items:        
    locals()["book_"+str(i)] = pd.read_excel("MIS REPORT.xlsx",sheet_name=i)
    
#Manking new column with company name
for i in items:
    locals()["book_"+str(i)]["Firm"] = locals()["book_"+str(i)].iloc['B'].str.split(" ", 1)[1]

=> PRODUCES ERROR:
ValueError: Length of values (1) does not match length of index (202)

Input DATAFRAME

  In [2]: df
  Out[2]:
    A   B
    1   Company
    2   _______________________...
    3   NaN
    4   *Non CGR*
    5   COM1014 DFIT
    6   COM1380 SD RETAIL PVT LTD
    7   COM3151 LUPIN LIMITED
    8   COM9622 NETSURF COMMUNICATIONS (P) LTD.(MH)
    9   COMCRUS CARUS LABORATORIES PVT. LTD.

Desired DATAFRAME

  In [3]: df
  Out[3]:
    A   B
    4   CGR*
    5   DFIT
    6   SD RETAIL PVT LTD
    7   LUPIN LIMITED
    8   NETSURF COMMUNICATIONS (P) LTD.(MH)
    9   CARUS LABORATORIES PVT. LTD.

I don't want to use Apply() function, is there any way to catch error and work around it.

Shubham Gupta
  • 1
  • 1
  • are you for some reason not able to use `try, except:`? why even use `locals()`? – mechanical_meat Dec 28 '21 at 19:35
  • 1
    And why not use `apply()`? – MattDMo Dec 28 '21 at 19:36
  • @mechanical_meat Using 'try, except:' will skip the operation on dataframe, after first occurrence of error, also I'm using locals() to dynamically create new variable names and assigning new databases [item contain 'MMM YYYY' so there is space in variable names] – Shubham Gupta Dec 28 '21 at 19:50
  • 1
    Please [edit] your question to include a sample of your input data and expected output in the _text_ of your question, rather than a picture or link, so that we can copy and paste to make a [mcve]. See [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – G. Anderson Dec 28 '21 at 19:53

0 Answers0