CODEWITHSUNDEEP
pythonpandasdataframe
M.K
M.K

Reputation: 1495

Create a new column in pandas using a value of a row

First of all, this is not a duplicate! I have searched in several SO questions as well as the Pandas doc, and I have not found anything conclusive!To create a new column with a row value, like this and this!

Imagine I have the following table, opening an .xls and I create a dataframe with it. As this is a small example created from the real proble, I created this simple Excel table which can be easily reproduceable:

Table

What I want now is to find the row that has "Population Month Year" (I will be looking at different .xls, so the structure is the same: population, month and year. 

xls='population_example.xls'
sheet_name='Sheet1'
df = pd.read_excel(xls, sheet_name=sheet_name, header=0, skiprows=2)
df

What I thought is:

  1. Get the value of that row with startswith

  2. Create a column, pythoning that value and getting the month and year value.

I have tried several things similar to this:

dff=df[s.str.startswith('Population')]
dff

But errors won't stop coming. In this above's code error, specifically:

IndexingError: Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match

I have several guesses:

Edit:

The problem on using this:

df[df['Area'].str.startswith('Population')] Is that it will check the na values.

And this:

df['Area'].str.startswith('Population')

Will give me a true/false/na set of values, which I am not sure how I can use.

Upvotes: 0

Views: 134

Answers (2)

René
René

Reputation: 4827

You could try:

import pandas as pd
import numpy as np

pd.DataFrame({'Area': [f'Whatever{i+1}' for i in range(3)] + [np.nan, 'Population April 2017.'],
              'Population': [3867, 1675, 1904, np.nan, np.nan]}).to_excel('population_example.xls', index=False)

df = pd.read_excel('population_example.xls').fillna('')

population_date = df[df.Area.str.startswith('Population')].Area.values[0].lstrip('Population ').rstrip('.').split()

Result:

['April', '2017']

Or (if Population Month Year is always on the last row):

df.iloc[-1, 0].lstrip('Population ').rstrip('.').split()

Upvotes: 1

M.K
M.K

Reputation: 1495

Thanks to @Erfan , I got to the solution:

Using properly the line of code in the comments and not like I was trying, I managed to:

dff=df[df['Area'].str.startswith('Population', na=False)] dff

Which would output: Population and household forecasts, 2016 to 20... NaN NaN NaN NaN NaN NaN

Now I can access this value like

value=dff.iloc[0][0] value

To get the string I was looking for: 'Population and household forecasts, 2016 to 2041, prepared by .id , the population experts, April 2019.' And I can python around with this to create the desired column. Thank you!

Upvotes: 1

Related Questions