2

My goal here is to import the data stored as DataFrame df1 in sheet1 based on the substring.

My DataFrame looks like below: 

df:[2 x 8]

    ordercode           quantity
PMC11-AA1L1FAVWJA        20
PMC21-AA1A1CBVXJA        10
PMP11-AA1L1FAWJJ          5
PMP21-AA1A1FBWJJ          3
PMP23-AA1A1FA3EJ+JA       1
PTP31B-AA3D1HGBVXJ        1
PTC31B-AA3D1CGBWBJA       2
PTP33B-AA3D1HGB1JJ        10

But my desired result should be like 

Sheet2_df2:"1C"
   ordercode           quantity
PMC21-AA1A1CBVXJA        10
PTC31B-AA3D1CGBWBJA       2

Sheet3_df3:"1F"
PMP11-AA1L1FAWJJ          5
PMP21-AA1A1FBWJJ          3
PMC11-AA1L1FAVWJA        20

In excel it is easy by creating a criteria and use IF function. B1: "1C"

 =IF(Sheet1!A2=$B$1,Sheet1!A2,"")

and could copy to all cell.

I don't know how to perform this in python. Any solution would be appreciated.

i1100362
  • 259
  • 2
  • 10

1 Answers1

1

First read your data into a Pandas dataframe:

df = pd.read_excel('file.xlsx')

Then use a dictionary comprehension with pd.Series.str.contains:

substrings = ['1C', '1F']

d = {x: df[df['ordercode'].str.contains(x, na=False, regex=False)] for x in substrings]

You can then access dataframes via substring keys, e.g. d['1C']. If you wish, you can export each value in your dictionary to a separate worksheet or workbook. For example:

for k, v in d.items():
    v.to_excel(f'{k}.xlsx', index=False)
jpp
  • 159,742
  • 34
  • 281
  • 339
  • Thanks, Can you explain how this For function works here. whether I can export both the columns for each substring? How this 'k' works here? – i1100362 Sep 05 '18 at 11:31
  • 1
    `both the columns` not sure what you mean here. This is a simple `for` loop which iterates keys and values from your dictionary. Each key is a substring, e.g. `'1C'`, each value is a dataframe, e.g. a dataframe filtered for `'1C'` in `df['ordercode']`. – jpp Sep 05 '18 at 11:33
  • Okay. I understood. Thanks let me try this. – i1100362 Sep 05 '18 at 12:37
  • Hey. It worked. but I need the data in same Excel file in different sheet. How can I do that ?? – i1100362 Sep 05 '18 at 12:47
  • @user10309160, See [Write multiple pandas dataframes to excel](https://stackoverflow.com/questions/50315010/write-multiple-pandas-dataframes-to-excel) – jpp Sep 05 '18 at 13:07
  • I tried to export the data to different sheets in same Excel using below codes `writer=pd.ExcelFile('file.xlsx')` in For loop `v.to_excel(writer,f'{k}.xlsx',index= False)` but its overwriting in same sheet. – i1100362 Sep 05 '18 at 13:12
  • @user10309160, Please see my previous comment. This isn't a help forum. If I solved your original question, consider [accepting it](https://stackoverflow.com/help/accepted-answer). Feel free to ask a new question if my previous comment doesn't help. – jpp Sep 05 '18 at 13:18
  • I have followed the instruction in your previous comment but I'm getting error. your method helped me. Thanks – i1100362 Sep 05 '18 at 13:25