Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Substr on Pandas Dataframe
#1
Hi everyone,

I have a DF and I want to set an if statement in a function to sum a value if the first part of a field = '10'. This would be easy in SAS with the substr function. Can I do it in a dataframe or do I need to put it into an array and slice?

I have pasted the DF below, the column headers don't align well but you can make it out.

Output:
HSC Country Month Imports_(NZD) Harmonised System Description 0 101210015 New Zealand 201903 191,550 Horses; live, pure-bred breeding animals, thor... 1 101210015 New Zealand 201904 190,550 Horses; live, pure-bred breeding animals, thor... 2 101290010 New Zealand 201903 76,660 Horses; live, other than pure-bred breeding an... 3 101290010 New Zealand 201904 1,187,430 Horses; live, other than pure-bred breeding an... 4 101290013 New Zealand 201904 1,257,700 Horses; live, other than pure-bred breeding an...
What i want is an output with month as the index and then a new variable summed Import by is substr(hsc,0,2) = '01' which is grouped by month. I just want help with first variable and then I am going to create a few more summs based on the HSC that are grouped by month and have them as the new columns.

I hope that makes sense. Please let me know if you need more info.

Thanks
Quote
#2
(Sep-01-2019, 06:21 AM)Scott Wrote: I have a DF and I want to set an if statement in a function to sum a value if the first part of a field = '10'.
You need to convert values to strings first and use .str.startswith method.

Take a look at the following minimal example I just wrote:

import pandas as pd
df = pd.DataFrame({"x": [100, 1000, 1000, 1919, 124], "y": [1, 2, 3, 4, 5]})
df.loc[df.x.astype(str).str.startswith('10'), 'y'].sum()
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Obtaining Correct Date In Pandas DataFrame eddywinch82 7 386 Yesterday, 04:47 PM
Last Post: sandeep_ganga
  Parse XML String in Pandas Dataframe creedX 2 283 Dec-09-2019, 07:35 PM
Last Post: creedX
  Pandas dataframe to join three tables using like condition among them sandeep_ganga 0 237 Nov-29-2019, 08:30 AM
Last Post: sandeep_ganga
  Pandas Dataframe to Google Big Query Ecniv 2 883 Nov-21-2019, 02:26 PM
Last Post: Ecniv
  manipulating a dataframe - pandas nsx200 2 217 Nov-14-2019, 10:38 AM
Last Post: nsx200
  Pandas dataframe columns collapsed in Spyder when printing UniKlixX 2 218 Nov-04-2019, 07:00 AM
Last Post: UniKlixX
  pandas dataframe iloc mystery edvvardbrian 2 300 Oct-29-2019, 02:55 PM
Last Post: jefsummers
  How to add a few empty rows into a pandas dataframe python_newbie09 2 1,252 Sep-20-2019, 08:52 AM
Last Post: python_newbie09
  Dropping a column from pandas dataframe marco_ita 6 1,639 Sep-07-2019, 08:36 AM
Last Post: marco_ita
  created a pandas series instead of pandas DataFrame ibaad1406 6 779 Sep-06-2019, 06:23 AM
Last Post: ibaad1406

Forum Jump:


Users browsing this thread: 1 Guest(s)