How to extract all the cells between two other cells in Pandas from the column with most cells between these two other cells?

By -

I have multiple financial statements and not all of them have the same entries - some have more entries than others - and I'd like to consolidate them all into a single one that has all of the entries.

I was able to do it manually in Excel since it's not that many, but I'd like to have the computer do it to double check I got them all.

So, here's what I did: I created a dataframe where each column has the entry names from one of the financial statements.

FinancialStatement1 FinancialStatement2 FinancialStatement3
REVENUES REVENUES REVENUES
Revenue1 Revenue1 Revenue1
Revenue2 Revenue2 Revenue2
EXPENSES EXPENSES Revenue3
Expense1 Expense1 EXPENSES
Expense2 PROFIT Expense1
Expense3 - Expense2
PROFIT - PROFIT
- - -

My idea was to run a script that would analyse the number of cells between two of the group titles and return to a 'Consolidated' column all of the strings between these two values, including the first, but not the last.

My end result would look like this:

FinancialStatement1 FinancialStatement2 FinancialStatement3 Consolidated
REVENUES REVENUES REVENUES REVENUES
Revenue1 Revenue1 Revenue1 Revenue1
Revenue2 Revenue2 Revenue2 Revenue2
EXPENSES EXPENSES Revenue3 Revenue3
Expense1 Expense1 EXPENSES EXPENSES
Expense2 PROFIT Expense1 Expense1
Expense3 - Expense2 Expense2
PROFIT - PROFIT Expense3
- - - PROFIT

I'm a beginner in Pandas and, so far, here's what I came up with by searching here in stack:

df = pd.read_excel(file)
df['Consolidated']=0
df.head()

df['Consolidated'].iloc[1] = df['FinancialStatement1'][df['FinancialStatement1'].between(
    'REVENUES', 'EXPENSES',
    inclusive=False
    )].tolist()

However, this code gives me "A value is trying to be set on a copy of a slice from a DataFrame". I tried using only df.iloc[3,0] but it also doesn't work. Anyway, this code won't do what I want anyway since it doesn't pick the list with most items between the two group titles.



from Recent Questions - Stack Overflow https://ift.tt/3Eqycg2
https://ift.tt/eA8V8J

Comments

Post a Comment

Popular posts from this blog

Today Walkin 14th-Sept

By -
Image
14th Sept 2017 ( GO Through all List) Read Walkin details properly , Try to call company before attending if possible !!! ==================================== 1. Myntra through fresherworld (Call Based , Venue shared to shortlisted candidates) _____________________________ 2. Magna : Hiring for Client Harman : C++
Read more

Spring Elasticsearch Operations

By -
Elasticsearch Operations Spring Data Elasticsearch uses two interfaces to define the operations that can be called against an Elasticsearch index. These are ElasticsearchOperations and ReactiveElasticsearchOperations. Whereas the first is used with the classic synchronous implementations, the second one uses reactive infrastructure. The default implementations of the interfaces offer: Read/Write mapping support for domain types. A rich query and criteria api. Resource management and Exception translation. ElasticsearchTemplate The ElasticsearchTemplate is an implementation of the ElasticsearchOperations interface using the Transport Client. @Configuration public class TransportClientConfig extends ElasticsearchConfigurationSupport {   @Bean   public Client elasticsearchClient() throws UnknownHostException {                      Settings settings = Settings.builder().put("cluster.name", "elasticsearch")....
Read more

Hibernate Search - Elasticsearch with JSON manipulation

By -
Elasticsearch with JSON manipulation Elasticsearch ships with many features. It is possible that at some point, one feature you need will not be exposed by the Search DSL. To work around such limitations, Hibernate Search provides ways to: Transform the HTTP request sent to Elasticsearch for search queries. Read the raw JSON of the HTTP response received from Elasticsearch for search queries. Direct changes to the HTTP request may conflict with Hibernate Search features and be supported differently by different versions of Elasticsearch. Similarly, the content of the HTTP response may change depending on the version of Elasticsearch, depending on which Hibernate Search features are used, and even depending on how Hibernate Search features are implemented. Thus, features relying on direct access to HTTP requests or responses cannot be guaranteed to continue to work when upgrading Hibernate Search, even for micro upgrades (x.y.z to x.y.(z+1)). Use this at your own risk....
Read more